I'm using the RPostgreSQL 0.4 library (compiled on R 2.15.3) on R 2.15.2 under Windows 7 64-bit to interface to PostgreSQL. This works fine when connecting to my PostgreSQL databases on localhost. I'm trying to get my R code to run with a remote PostgreSQL database on Heroku. I can connect to Heroku's PostgreSQL database from the psql command shell on my machine, and it connects without a problem. I get the message:
psql (9.2.3, server 9.1.9)
WARNING: psql version 9.2, server version 9.1.
Some psql features might not work.
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Clearly, psql uses SSL to connect. When I try to connect using the RPostgreSQL library routine dbConnect(), however, supplying exactly the same credentials using dname=, host=, port=, user=, password=, the connection fails with the complaint:
Error in postgresqlNewConnection(drv, ...) :
RS-DBI driver: (could not connect <user>@<hostname> on dbname <dbname>)
Calls: source ... .valueClassTest -> is -> is -> postgresqlNewConnection -> .Call
Execution halted
I know that Heroku insists on an SSL connection if you want to access their database remotely, so it seems likely that the R interface routine dbConnect() isn't trying SSL. Is there something else that I can do to get a remote connection from R to PostgreSQL on Heroku to work?
hd1 :
To get the JDBC URL for your heroku instance:\n\n\nGet your hostname, username and password using [pg:credentials].\nYour jdbc URL is going to be:\njdbc:postgresql://[hostname]/[database]?user=[user]&password=[password]&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory\nProceed as you would normally with JDBC.\n",
2013-05-08T22:02:14
Christiaan :
Apparently there is a way using RJDBC. See:\n\nhttp://ryepup.unwashedmeme.com/blog/2010/11/17/working-with-r-postgresql-ssl-and-mssql/",
2013-05-07T06:52:39
kunal :
Please note that in order to connect to Heroku database with JDBC externally, it is important to set the sslfactory parameter as well. Hope Heroku team goes through it and modifies their documentation.\n\nString dbUri = \"jdbc:postgresql://ec2-54-243-202-174.compute-1.amazonaws.com:5432/**xxxxxxx**\";\nProperties props = new Properties();\nprops.setProperty(\"user\", \"**xxxxx**\");\nprops.setProperty(\"password\", \"**xxxxx**\");\nprops.setProperty(\"ssl\", \"true\");//ssl to be set true\nprops.setProperty(\"sslfactory\", \"org.postgresql.ssl.NonValidatingFactory\");// sslfactory to be set as shown above\nConnection c=DriverManager.getConnection(dbUri,props);\n",
2016-07-31T11:46:12