Enhancing your Docker Workflow with Local PostgreSQL Integration

Posted on:2024-04-18 | 6 min read

I’m a proponent of using Docker containers locally when running 3rd party software except when it’s PostgreSQL. Here are a couple of reasons why would you prefer local PostgreSQL over containerized PostgreSQL:

By preferring a local installation of PostgreSQL, you leverage the benefits of direct integration with the host system, improved performance, and simplified management, all of which can enhance development and operational workflows.

So, how do you share your local PostgreSQL server running on @localhost:5432 with any Docker container? Let’s see two different methods by using networking: Docker Host and Bridge network.

Table of contents

Open Table of contents

Setup

For this experiment, we need the following:

Docker Host Network

As the name implies, when using the host option, the containers will not run in their network stack. Instead, the containers will share the host’s network and IP address.

You can set up the container’s PostgreSQL URL as you would normally do when setting up a PostgreSQL client (ORMs, libraries, etc) in a development environment, i.e. postgres://postgres@localhost:5432/db_name.

Let’s test this!

Note:

Run:

docker run --rm -it --network=host alpine sh

# Inside the container run
apk add postgresql-client
psql -h localhost -U postgres postgres;

# Inside psql. You should be able to list all the databases from your local PostgreSQL server
\list
# Hit Ctrl+D to exit.

Now you can run any docker container and connect it to your running local PostgreSQL server.

Docker Bridge Network

The Bridge Network is the default network driver used by containers when the --network option is not specified when running containers. The bridge network, as the name implies, is a bridge that enables containers to communicate within the same network but isolated from the host’s network.

To enable communication from any container in the default bridge network, we need to:

First, let’s test the network communication from the container to a host server. Run two terminals:

In the first terminal, you should see a connection from the terminal 2. In the second terminal, you should see Host: host.docker.internal:8080 and the response HTTP/1.1 200 OK.

Now that we’ve verified the container can connect to localhost through the bridge network, let’s configure our local PostgreSQL server to allow connections from the same bridge network.

Docker Bridge Network Gateway and Subnet

To allow incoming connections from the Docker bridge network into PostgreSQL, we first need to know the network’s gateway and subnet.

docker network inspect bridge | jq -r '.[] .IPAM.Config[].Gateway'
docker network inspect bridge | jq -r '.[] .IPAM.Config[].Subnet'

The default bridge network gateway is: 172.17.0.1, and the default network subnet is: 172.17.0.0/16.

Configure local PostgreSQL server

Depending on your OS, the PostgreSQL config files can be in different directories. The easiest way to identify the files is by running the following commands in psql postgres locally.

postgresql.conf

SHOW config_file;

Copy the file path, edit postgresql.conf, uncomment list_addresses, and add the Bridge Network Gateway.

# - Connection Settings -
# Allow connections from Docker Bridge Network Gateway
listen_addresses = 'localhost, 172.0.0.1'

pg_hba.conf

SHOW hba_file;

Copy the file path, edit pg_hba.conf, add this to the bottom of the file with the Docker Bridge Network Subnet.

# Allow trusted connections from Docker Bridge Network Subnet
host    all             all             172.17.0.0/16           trust

Run container and connect to local PostgreSQL Server

Now let’s verify that a container can connect to our local PostgreSQL server.

Run:

docker run --rm -it --add-host=host.docker.internal:host-gateway alpine sh

# Inside the container run
apk add postgresql-client

# We use the 'host.docker.internal' host
psql -h host.docker.internal -U postgres postgres;

# Inside psql. You should be able to list all the databases from your local PostgreSQL server
\list
# Hit Ctrl+D to exit.

You should be able to see the internal docker host (specified in the --add-host option) in /etc/hosts by running cat /etc/hosts.

As you would have guessed, you need to use the host.docker.internal host to set up the PostgreSQL connection in your ORM, library, or postgres:// DB URL.

What method should you choose?

As always, it depends.

I recommend the Bridge Network, here are my reasons:

Containers are here to stay. It’s best to learn networking 101 (see the links), and learn Docker’s network drivers.

Here are some links that helped me understand what I wrote in this post.

Do you need help setting it up? Let me know in the comments or send me an email at [email protected] 😃.

Thank you @ethanppl for reviewing this post.