8

[Original Post]

I have seen a couple of similar SO questions but I don't think any of them are related to OS.

Long story short, this is what I see when I try to establish a ssl connection to Redshift.

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,
                 host = "XXXXXXXX.us-east-1.redshift.amazonaws.com",
                 port = 5439,
                 user = "XXXXXXXX",
                 password = "XXXXXXXX",
                 dbname = "db1")

> Error in postgresqlNewConnection(drv, ...) : 
    RS-DBI driver: (could not connect XXXXXXXX@XXXXXXXX.us-east-1.redshift.amazonaws.com on dbname "db1")

I have tried the following and none of them worked.

  1. Connect to Redshift via SSL using R
  2. Importing files from PostgreSQL to R
  3. Connect to Postgres via SSL using R
  4. https://groups.google.com/forum/#!topic/rpostgresql-dev/ELnVUJqjDbk

I have an EC2 node (Ubuntu) in AWS and another Mac Pro in office, so I tried to use the same code to connect from both.

The Linux system works with copying & pasting the exact same code. Here is the Sys.info() on the Linux server:

sysname                                              Linux
release                                  3.13.0-48-generic
version        #80-Ubuntu SMP Thu Mar 12 11:16:15 UTC 2015
nodename                                   ip-xx-xxx-xx-xx
machine                                             x86_64
login                                              unknown
user                                                  bcui
effective_user                                        bcui

However, the Mac Pro failed with the exact same error message. Here is the Mac Sys.info() (same as my Mac laptop):

sysname                                             Darwin
release                                             15.3.0
version                      Darwin Kernel Version 15.3.0:
                             Thu Dec 10 18:40:58 PST 2015;
                       root:xnu-3248.30.4~1/RELEASE_X86_64
nodename                                    bcui-MBP.local
machine                                             x86_64
login                                                 bcui
user                                                  bcui
effective_user                                        bcui

I am wondering what could be different between the Mac and Linux configuration that causes this error message?

[Update 2016/02/10]

I have tried to uninstall R and all related files, and then reinstall R from homebrew:

brew tap homebrew/science
brew install R

Same error message:

> Error in postgresqlNewConnection(drv, ...) : 
    RS-DBI driver: (could not connect XXXXXXXX@XXXXXXXX.us-east-1.redshift.amazonaws.com on dbname "db1")

FYI, I can connect to other non-ssl Redshift clusters using the same code, so it could be something related to openssl on Mac.

[Update 2016/02/11]

More info from @MasashiMiyazaki's comment:

I have used psql via CLI to connect and it works fine. In addition, I can also connect successfully using the Python module psycopg2. However, I will have to disable this line from .bash_profile in order for them to work:

export DYLD_LIBRARY_PATH=/usr/lib:$DYLD_LIBRARY_PATH
Community
  • 1
  • 1
Boxuan
  • 4,937
  • 6
  • 37
  • 73
  • Please let me know if you need additional system information, so that I can provide in the OP. – Boxuan Feb 01 '16 at 20:09
  • Have you ever tried "psql" (PostgreSQL client command) to access your Redshift from your MacOS machine? – Masashi M Feb 11 '16 at 07:12
  • @MasashiMiyazaki Yes I have used `psql` to connect and it works fine. In addition, I have also tried the Python module `psycopg2` and it works fine too. – Boxuan Feb 11 '16 at 15:54
  • Are you trying to avoid using `RJDBC` + the redshift jar ( http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html )? That works nicely for me – arvi1000 Feb 11 '16 at 17:02
  • @arvi1000 I tried that too, but am getting JVM initialization error using that method: `Error in .jinit(classPath) : JNI_GetCreatedJavaVMs returned -1`. Maybe that belongs to a separate SO question. I didn't go down that path since all my existing setup is based on `RPostgreSQL`. However, I might try to solve the JVM problem if I don't see a solution to this. – Boxuan Feb 12 '16 at 16:19
  • @arvi1000 Since you brought this up, does `echo $JAVA_HOME` return `/Library/Java/JavaVirtualMachines/jdk1.8.0_45.jdk/Contents/Home` for you? Thanks! – Boxuan Feb 12 '16 at 16:21
  • no, it returns nothing (I don't have any such environment variable set) – arvi1000 Feb 12 '16 at 18:16
  • Have you tried local port forwording? So: `ssh -i ~/.ssh/YOUR-KEY.pem -L 5439:localhost:5439 ec2-user@XXXXXXXX.us-east-1.redshift.amazonaws.com` this forwards your local-port 5439 to the servers port 5439. Then try in R to connect to `localhost:5439`. For more Information see http://docs.aws.amazon.com/ElasticMapReduce/latest/DeveloperGuide/emr-ssh-tunnel-local.html – Rentrop Feb 12 '16 at 23:42

1 Answers1

3

Taking a shot in the dark here. Have you tried RPostgres?

Install it like so

# install.packages("devtools")
devtools::install_github("RcppCore/Rcpp")
devtools::install_github("rstats-db/DBI")
devtools::install_github("rstats-db/RPostgres")

Then test it out

library(DBI)
library(RPostgres)

con <- dbConnect(RPostgres::Postgres(),
           host = "XXXXXXXX.us-east-1.redshift.amazonaws.com",
           port = 5439,
           user = "XXXXXXXX",
           password = "XXXXXXXX",
           dbname = "db1")
JackStat
  • 1,593
  • 1
  • 11
  • 17
  • Last time I tried this and it didn't work, and it works this time. Not sure what has changed. – Boxuan Feb 17 '16 at 21:43