How to Connect to PostgreSQL in a Docker Container from Outside?

Connecting to PostgreSQL in a Docker Container from Outside

To connect to a PostgreSQL database running in a Docker container from an external application or client, you need to configure the container’s network settings and expose the PostgreSQL port (usually 5432). This allows connections from the host machine or other networked devices.

Prerequisites

• A running Docker container with PostgreSQL.
• Basic knowledge of Docker commands and configurations.

Steps to Connect to PostgreSQL in a Docker Container

Step 1: Start a PostgreSQL Docker Container with Port Exposed

When launching the PostgreSQL container, make sure to publish (-p) the container’s internal port (5432) to an external port on the host machine.

Example Command:

# Start a PostgreSQL container and expose port 5432
docker run --name my_postgres_container -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres
-p 5432:5432: Maps port 5432 on the Docker container to port 5432 on the host.
-e POSTGRES_PASSWORD=mysecretpassword: Sets the postgres user password.
-d postgres: Starts the PostgreSQL container in detached mode.

Step 2: Find the Host IP Address (If Needed)

If you’re connecting from another machine on the same network, you’ll need the host IP where the container is running. Use hostname -I to get the IP of the host machine:

# Get the host IP address
hostname -I

Step 3: Enable Listening for External Connections

By default, PostgreSQL in Docker may only listen to connections from localhost. Update the postgresql.conf file to allow connections from all IPs by setting listen_addresses to '*'.

1. Connect to the running container:

docker exec -it my_postgres_container bash

2. Edit postgresql.conf (typically found in /var/lib/postgresql/data):

# Inside the container, allow connections from all IP addresses
echo "listen_addresses = '*'" >> /var/lib/postgresql/data/postgresql.conf

Step 4: Configure pg_hba.conf for External Access

Modify the pg_hba.conf file to allow external connections by adding a rule for host access:

1. Inside the container, open pg_hba.conf:

nano /var/lib/postgresql/data/pg_hba.conf

2. Add the following line to allow external connections:

host    all             all             0.0.0.0/0               md5

Where -

• 0.0.0.0/0: Allows connections from any IP address. Use more specific IP ranges for better security.

3. Restart PostgreSQL within the container to apply changes:

pg_ctl restart

Step 5: Connect to PostgreSQL from Outside the Container

With PostgreSQL configured, you can now connect using psql or any PostgreSQL client. Specify the host’s IP and mapped port.

Example Command:

• # Connect to the PostgreSQL database from the host or another machine
• psql -h [host_ip] -U postgres -p 5432

Explanation:

• -h [host_ip]: The IP address of the host where Docker is running.
• -U postgres: Username for PostgreSQL (default is postgres).
• -p 5432: Port number mapped to the container.

Full Example and Explanation

1. Start PostgreSQL Docker Container

Code:

# Start the container with the required port mapped to the host
docker run --name my_postgres_container -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

2. Allow PostgreSQL to Listen on All IP Addresses

Code:

# Enter the container shell
docker exec -it my_postgres_container bash

# Configure PostgreSQL to listen on all IP addresses
echo "listen_addresses = '*'" >> /var/lib/postgresql/data/postgresql.conf

3. Configure pg_hba.conf for External Access

Code:

# Open pg_hba.conf and allow external connections
nano /var/lib/postgresql/data/pg_hba.conf

# Add the following line to allow all IP connections with md5 password authentication
host    all             all             0.0.0.0/0               md5

4. Restart PostgreSQL

Code:

# Restart the PostgreSQL service within the container
pg_ctl restart

5. Connect from an External Client

Code:

# Connect from an external PostgreSQL client
psql -h [host_ip] -U postgres -p 5432

Important Notes:

• Security: Allowing 0.0.0.0/0 in pg_hba.conf can be a security risk. Restrict to specific IP addresses where possible.
• Firewall Settings: Ensure the firewall on the host machine allows incoming connections on port 5432.
• Networking Mode: Docker’s bridge networking mode is suitable for basic use. For more advanced setups, consider Docker’s custom networking options.

All PostgreSQL Questions, Answers, and Code Snippets Collection.