I am facing an awkward issue connecting to a Postgres database deployed on AWS Aurora. Connection attempts from R on Mac OS, using the DBI and RPostgres frameworks are rejected with the message:
Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection is made through:
conn <- DBI::dbConnect(
drv = RPostgres::Postgres(),
dbname = 'xxx',
host = 'xxx',
port = 5432,
user = 'xxx',
password = 'xxx')
Troubleshooting this issue revealed the following - all systems are running the same version of R (4.0.4), DBI (1.1.1), RPostgres (1.3.2) and Rcpp (1.0.6):
- running the connection from a centos7 distribution works fine
- running the connection from windows 10 works fine
- running the connection with RPostgres from osx 10.15.7 fails
- running the connection with RPostgresSQL from osx 10.15.7 succeeds
- connecting through Python client (sql alchemy) succeeds
- netcat confirms successful connection from osx 10.15.7
Unfortunately, I cannot afford to move to RPostgresSQL and I need to solve the problem currently experienced with RPostgres on Mac connecting to Amazon RDS.
Other useful information:
- the same database I am trying to access on Arora is accessible on a Centos7 distribution with no issues
- the only difference between the two DB instances seems to be the encoding: SHOW SERVER_ENCODING returns SQL_ASCII on the Centos instance and UTF8 on the aurora cluster. As far as I understand though, RPostgres forces UTF8 and my local environment on the Mac is also set to UTF8.
I am no expert in DB administration and would tremendously appreciate any suggestions to solve this problem.