5

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 [email protected]: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.

Improve this question
3
  • 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.) Commented Feb 6, 2019 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: 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.) Commented Feb 6, 2019 at 18:21
  • 2
    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. Commented Feb 6, 2019 at 18:23

1 Answer 1

Reset to default
0

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.