Search code examples
rpostgresqldbirpostgres

R DBI connection closed unexpectedly by server + server terminated abnormally before or while processing request

I am using DBI and pqdriver for Postgres via RPostgres::Postgres() to establish a connection like the following.

con <- DBI::dbConnect(RPostgres::Postgres(), dbname = "my_db", host = "10.10.10.194", port = 5432, user = "my_user", password = "my_password")

Using the same connector, I am able to connect to a UAT version of our environment but unable to connect to production - despite all credens etc. being correct.

The error I get is:

Error: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

For the life of me, I am unable to understand how to debug this issue. I am able to successfully telnet, but only while connecting like the above my attempt fails.

Update 1

I tried checking with IT/DB admin, and they wonder if this is happening because dbConnect is using any proxies to access. Not sure how I can see this?

Also, I tried traceback to get the following (I am sure there will be better ways of debugging which I am unaware of...) enter image description here

Update 2

This is what I get if I run via JDBC:

enter image description here

Solution

  • Maybe it's related to stricter permission settings on your production postgreSQL instance. Even when user/pwd is the same, the production instance might only accept connections from localhost or specific IP addresses. Furthermore, authentication via username/password could be disabled, allowing e.g. only the "cert" method.

    Please check /etc/postgresql/VERSION/main/pg_hba.conf (or have it checked/explained to you by your DBA/IT).

    Also, it could be due to specific firewall/security settings for your production instance. Which would allow connections only from specific IPs, or it would simply drop all packets from the DBMS to your machine.

    HtH