How to connect to PostgreSQL running in Docker container from local R?

Question

I have a local Docker container running PostgreSQL. I want to be able to connect to and interact with this database from R running on my host machine (Mac OS).

I can connect using pgadmin4 via the following address

http://0.0.0.0:5434/browser/

then adding a new server:

Add new server. General Tab --> name: tagbase. Connection Tab --> Host name/address: postgres. Connection Tab --> Port: 5432. Connection Tab --> Maintenance database: postgres. Connection Tab --> Username: tagbase

This works perfectly.

However, to connect from R I try:

require("RPostgreSQL")

# load the PostgreSQL driver
drv <- dbDriver("PostgreSQL")

# create a connection to the postgres database
con <- RPostgreSQL::dbConnect(drv, dbname = "postgres",
                 host = "localhost", port = 5434,
                 user = "tagbase", password = "tagbase")

This attempt simply hangs until it crashes R.

Perhaps a viable solution is something similar to this. Many thanks for any help.

EDIT - 20190207

Thanks for the comments. I have made the changes with no improvement but agreed the changes were necessary.

I successfully start this docker network (of 3 containers) via terminal as below. It looks to me like I want to connect to the postgres container at 0.0.0.0 on port 5432, correct?

$ docker-compose up
Starting tagbase-server_postgres_1_3f42d4fc1a77 ... done
Starting tagbase-server_pgadmin4_1_52ab92a49f22 ... done
Starting tagbase-server_tagbase_1_9d3a22c8be46  ... done
Attaching to tagbase-server_postgres_1_3f42d4fc1a77, tagbase-server_pgadmin4_1_52ab92a49f22, tagbase-server_tagbase_1_9d3a22c8be46
postgres_1_3f42d4fc1a77 | 2019-02-05 19:35:45.999 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432

I thought I was connecting to the server via R exactly as I've done using pgadmin but the following doesn't seem to work:

# create a connection to the postgres database
con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), dbname = "postgres",
                  host = "0.0.0.0", port = 5432,
                  user = "tagbase", password = "tagbase")

Error in postgresqlNewConnection(drv, ...) : 
RS-DBI driver: (could not connect tagbase@0.0.0.0:5432 on dbname "postgres": 
FATAL:  role "tagbase" does not exist)

I now realize pgadmin is also running in the docker container network. Thus, local host for the pgadmin connection is the database server. Seems like I need a solution like this

Note the source for the docker builds is here following the instructions here.

Ideally, you'd use `library(DBI); con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), ...)`, though the differences are minor. It might be that the docker port binding is not on localhost, have you tried other addresses? I'm not accustomed to using `0.0.0.0` in this way, but does that work? (I know what `0.0.0.0` means, but I've always seen it as *"bind to any/all interfaces"*, not which interface to bind to.) — r2evans, Feb 06 '19 at 18:19
Additional side note: you should almost always use `library`, not `require`. The latter never stops following code when the package is not available, which is almost never what is intended. Refs: https://stackoverflow.com/a/51263513/3358272. Put another way: if you want `require`, capture and inspect its return value. If you do not check the return *and your follow-on code depends on its functions*, then use `library`. (If your follow-on code does not depend on its return ... then something else is going on.) — r2evans, Feb 06 '19 at 18:21
The port number needs to match the first port number in the `docker run -p` option or Docker Compose `ports:` section, for the database itself. With what you’ve shown you’re trying to connect to PGAdmin as though it were the database; `port=5432` would be more typical. — David Maze, Feb 06 '19 at 18:23

score 0 · Answer 1 · answered Oct 06 '19 at 14:46

If you want to connect directly to a postgres database inside a docker from outside docker world, you must expose a port on postgres docker. So first, you need to edit the file "Dockerfile-postgres", and add EXPOSE 5432

FROM postgres:10

COPY ./sqldb/tagbase-schema.sql /docker-entrypoint-initdb.d/
# Expose default postgres port
EXPOSE 5432

Then build and run the dockers according to the provided instrucctions (Checked on October 6, 2019)

$ docker-compose build
$ docker-compose up

Add the database using pgAdmin

Add New Server  General Tab --> name: tagbase  Connection Tab --> Host name/address: postgres  Connection Tab --> Port: 5432  Connection Tab --> Maintenance database: postgres  Connection Tab --> Username: tagbase

Edit your R scritp according to the databse name and port:

# install.packages('RPostgreSQL')
library(RPostgreSQL)

# load the PostgreSQL driver
drv <- dbDriver("PostgreSQL")

# create a connection to the postgres database
con <- RPostgreSQL::dbConnect(drv, dbname = "tagbase",
                              host = "localhost", port = 5432,
                              user = "tagbase", password = "tagbase")

# Test query
temp <- dbGetQuery(con, 'select * from public.metadata_types')

# Evaluate output
str(temp)
# 'data.frame': 142 obs. of  8 variables:
#   $ attribute_id  : num  1 2 3 4 5 6 7 8 9 10 ...
# $ category      : chr  "instrument" "instrument" "instrument" "instrument" ...
# $ attribute_name: chr  "instrument_name" "instrument_type" "firmware" "manufacturer" ...
# $ type          : chr  "string" "string" "string" "string" ...
# $ description   : chr  "Append an identifer that is unique within your organization. This is essential if a device is recycled." "Type of instrument" "Version number of the firmware used to build the device" "Name of manufacturer" ...
# $ example       : chr  "16P0100-Refurb2" "archival, popup, satellite, acoustic tag, or acoustic receiver" NA "Wildlife Computers, Microwave Telemetry, Lotek Wireless, Desert Star Systems, CEFAS, StarOddi, Sea Mammal Resea"| __truncated__ ...
# $ comments      : chr  "Devices might be reused, so the serial number will be the same. The only way to distinguish is by providing a u"| __truncated__ "Should be restricted to the examples provided." NA NA ...
# $ necessity     : chr  "required" "required" "required" "required" ...

# Disconnect from database
dbDisconnect(con)

How to connect to PostgreSQL running in Docker container from local R?

EDIT - 20190207

1 Answers1