0

I'm having an issue figuring out why I can't connect to a PSql DB from R. I am able to access the database from the terminal using the psql command, but when connecting through DBI and R I get the following message [with some information redacted]:

RS-DBI driver: (could not connect [username]@[database URI] on dbname "[dbname]"

The database string works fine both the terminal and this code works fine on the machine I am porting it from. I have reinstalled the versions of the libraries that match what was on the dev machine, and am still having problems.

Any advice?

Edit: I was able to get it working by fiddling around with the library(...) statements. It seems changing the order of the DBI and RPostgreSQL libraries have an effect. RPostgreSQL requires DBI, but importing just RPostgreSQL still produced the could not connect error.

To future readers with this issue: fiddle with the order, it may help!

Jeremy Freeman
  • 53
  • 6

1 Answers1

0

Just an educated guess: your psql is from the same machine, so uses the local connection. The DBI-based methods using the Postgresql library will use network connection so you actually have to open that the corresponding config file.

See eg here about pg_hba.conf.

Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
  • I appreciate the input, but would that matter if I'm providing authentication as username and password through the library? I'm not entirely sure how the pg library handles auth. – Jeremy Freeman Jun 09 '16 at 05:58
  • I have used Postgresql since the 1990s and I am fairly sure that this is your issue. There is an additional toggle you generally _must_ enable to listen to network connection -- this is orthogonal to auth/password as it governs _where_ it listens as opposed to _whom_ it grants access. – Dirk Eddelbuettel Jun 09 '16 at 06:28