Connect R to Redshift with RPostgreSQL on Mac 10.11.3

Question

[Original post]

I saw a couple of similar SO questions, but I don’t think they are OS related.

In short, this is what I see when I try to establish an ssl connection with 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 [e-mail] on dbname "db1") 

I tried the following and none of them worked.

• Connect to Redshift over SSL using R
• Import files from PostgreSQL to R
• Connect to Postgres over SSL using R
• https://groups.google.com/forum/#!topic/rpostgresql-dev/ELnVUJqjDbk

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

The Linux system works by copying and pasting the same code . Here is Sys.info() on a 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 same error message. Here is 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 might be different from the Mac and Linux configuration that causes this error message?

[Update 2016/02/10]

I tried to remove 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 [e-mail] on dbname "db1") 

FYI, I can connect to other Redshift clusters other than ssl using the same code, so it can be associated with openssl on a Mac.

[Update 2016/02/11]

More info from @MasashiMiyazaki's comment:

I used psql via the CLI to connect, and it works fine. In addition, I can successfully connect using the Python module psycopg2 . However, I need to disconnect this line from .bash_profile so that they can work:

 export DYLD_LIBRARY_PATH=/usr/lib:$DYLD_LIBRARY_PATH 

Answer 1

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

Set it like this

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

Then check 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")