在Mac 10.11.3上使用RPostgreSQL将R连接到Redshift

[Original Post]

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

我已经看到了几个类似的SO问题,但我不认为它们中的任何一个与操作系统有关。

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

长话短说,这是我在尝试建立与Redshift的ssl连接时看到的。

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.

我尝试了以下内容,但没有一个有效。

Connect to Redshift via SSL using R

使用R通过SSL连接到Redshift

Importing files from PostgreSQL to R

将文件从PostgreSQL导入R

Connect to Postgres via SSL using R

使用R通过SSL连接到Postgres

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.

我在AWS中有一个EC2节点(Ubuntu),办公室里有另一台Mac Pro,所以我尝试使用相同的代码从两者连接。

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

Linux系统可以复制和粘贴完全相同的代码。这是Linux服务器上的Sys.info():

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):

但是,Mac Pro失败并显示完全相同的错误消息。这是Mac Sys.info()(与我的Mac笔记本电脑相同):

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?

我想知道导致此错误消息的Mac和Linux配置之间可能有什么不同?

[Update 2016/02/10]

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

我试图卸载R和所有相关文件,然后从自制程序重新安装R:

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.

仅供参考,我可以使用相同的代码连接到其他非ssl Redshift集群,因此它可能与Mac上的openssl相关。

[Update 2016/02/11]

More info from @MasashiMiyazaki's comment:

更多信息来自@ MasashiMiyazaki的评论:

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:

我已经通过CLI使用psql连接,它工作正常。另外,我还可以使用Python模块psycopg2成功连接。但是,我必须从.bash_profile禁用此行才能使它们工作:

export DYLD_LIBRARY_PATH=/usr/lib:$DYLD_LIBRARY_PATH

1 个解决方案

#1

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

在这里黑暗中拍摄。你试过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")

#1