R postgres connection to remote survey error in RS

问题:

i cannot for the life of me get postgres connection a remote postgres db.

"Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect"

I have RPostgreSQL installed, i have postgres running in the background, my macbook is up to date 10.12.5 macOS sierra.

and still cannot connect

  drv <- dbDriver("RPostgreSQL")
 > con <- dbConnect(drv, host=hostName, 
 +                  port=portName,
+                  dbname=databaseName, 
+                  user=userName, 
+                  password=passwordName)
Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (could not connect

notably..i CAN connect with all the same credentials via postico app.

回答1:

I am using Windows and here is the code that worked well for me: Something I thought was really weird that I needed both RPostgreSQL and RPostgres. I tried using only RPostgreSQL or RPostgres and failed with the error you got or something else. But this following order is necessary and worked.

install.packages("RPostgreSQL")
require(RPostgreSQL)
install.packages("RPostgres")
require(RPostgres)
install.packages("DBI")
require(DBI)

#Create a connection
con <- dbConnect(RPostgres::Postgres(),
                 dbname = "REMOTE_DB_NAME",
                 host = "xx-redshift-xx.yyy.com",
                 port = "XXXX",
                 user = "****", password = "****")

dbListTables(con)

Let me know if you run into issues.

回答2:

I had the same problem. I ended up using the RPostgres package.

Disclaimer: I wan't able to install the package by following the git_hub instructions exactly;

Here is what I did to successfully install it on windows:

1) Install Rtools following the instructions here:

2) Install devtools:

install.packages("devtools")

3) Install Rcpp and DBI as the instruction:

devtools::install_github("RcppCore/Rcpp")
devtools::install_github("rstats-db/DBI")

4) Download the RPostgres source file on github by clicking clone and download; Extract it to a folder;

5) Install RPostgres from the source folder:

install.packages("path_to_downloads/Downloads/RPostgres-master/", repos = NULL, type="source")