Geek Answers Handbook

Problems remotely with PostgreSQL on Heroku from R using RPostgreSQL

I use the RPostgreSQL 0.4 library (compiled according to R 2.15.3) on R 2.15.2 under the 64-bit version of Windows 7 to interact with PostgreSQL. This works great when connecting to my PostgreSQL databases on localhost. I am trying to run my R code with a remote PostgreSQL database on Heroku. I can connect to the Heroku PostgreSQL database from the psql command shell on my machine and it connects without problems. I get a 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)

Obviously psql uses SSL to connect. However, when I try to connect using the RPostgreSQL dbConnect () library routine, supplying exactly the same credentials using dname =, host =, port =, user =, password =, the connection fails with a 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 SSL connection if you want to remotely access your database, so it seems likely that the R dcConnect () interface routine is not trying to use SSL. Is there anything else I can do to get a remote connection from R to PostgreSQL on Heroku to work?

+4
source share
4 answers

To get the JDBC URL for a heroku instance:

  • Get the host name, username and password using [pg: credentials].
  • Your jdbc url will be: jdbc:postgresql://[hostname]/[database]?user=[user]&password=[password]&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
  • Continue as usual with JDBC.
+3
source

Please note that to connect to the Heroku database with JDBC from the outside, it is also important to set the sslfactory parameter. The Hope Heroku team goes through it and changes its documentation.

 String dbUri = "jdbc:postgresql://ec2-54-243-202-174.compute-1.amazonaws.com:5432/**xxxxxxx**"; Properties props = new Properties(); props.setProperty("user", "**xxxxx**"); props.setProperty("password", "**xxxxx**"); props.setProperty("ssl", "true");//ssl to be set true props.setProperty("sslfactory", "org.postgresql.ssl.NonValidatingFactory");// sslfactory to be set as shown above Connection c=DriverManager.getConnection(dbUri,props);
0
source

See the answer to linked Q at fooobar.com/questions/1019608 / .... The suggestion of using RPostgres ( https://github.com/rstats-db/RPostgres ) instead of RPostgreSQL solved the same problem for me.

0
source

More articles:


All Articles