Connecting multiple server PostgreSQL R

Question

I needed to put 3 shards of a database on three different servers. So I created 3 servers in pgAdmin(s1,s2,s3), then I put each server one shard. Then, I tried to connect one of the servers in R; however, I couldn't make the connection. I always get an error:

Error in postgresqlNewConnection(drv, ...) : RS-DBI driver: (could not connect [email protected]:5432 on dbname "postgres": could not connect to server: Operation timed out Is the server running on host "172.17.0.1" and accepting TCP/IP connections on port 5432?

My code is:

#install.packages("RPostgreSQL")
require("RPostgreSQL")
library(DBI)

# create a connection
# save the password that we can "hide" it as best as we can by collapsing it
pw <- {
  "postgres"
}

# loads the PostgreSQL driver
drv <- dbDriver("PostgreSQL")
# creates a connection to the postgres database
con <- dbConnect(
  drv,
  dbname = "postgres",
  host = "172.17.0.1",
  port = 5432,
  user = "postgres",
  password = pw
)
rm(pw) # removes the password

pgAdmin snap

Did I write something wrong?

I’m not sure if this will make a difference, but I usually put my arguments in single quotes and try to change the port for postgres to 5439. Also, I’m not sure why you put your pw in {}. That might change things. You can try to put it in your .Renviron — Jacky

Raj Verma Raj Verma · Accepted Answer · 2020-02-04T04:52:17

if this is using container make sure to forward the port 5432 on 0.0.0.0 i.e the container is listening on the port 5432.

Also you've gotta check this setting if you are not doing the connection locally ONLY>, in the postgresql.conf file:

# - Connection Settings -
#listen_addresses = 'localhost'  >>>> This should be = '*'  instead of localhost

Save the conf and restart the service. Hope this helps!

Connecting multiple server PostgreSQL R

My code is:

1 Answers