PostgreSQL PgAdmin Docker

Launching PostgreSQL with Docker

This guide will walk you through launching PostgreSQL using Docker with persistent data storage. It also covers configuring the database for external connections, stopping and restarting the container, and deleting the container and its data when it's no longer needed.

Prerequisites

Ensure you have Docker installed on your system. To verify:

docker --version

If Docker is not installed, refer to the Docker installation guide.

Launch PostgreSQL with Persistent Data

Step 1: Pull the PostgreSQL Image

Pull the latest PostgreSQL image from Docker Hub:

docker pull postgres:latest

Step 2: Create a Volume for Data Persistence

Create a Docker volume to persist PostgreSQL data:

docker volume create postgres-data

This volume ensures that the data remains available even if the container is stopped or removed.

Step 3: Run PostgreSQL Container

Launch the PostgreSQL container with persistent data and external access:

docker run -d \
  --name postgres-container \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydatabase \
  -v postgres-data:/var/lib/postgresql/data \
  -p 5432:5432 \
postgres:latest
  • Replace myuser, mypassword, and mydatabase with your desired username, password, and database name.
  • -v postgres-data:/var/lib/postgresql/data mounts the persistent volume.
  • -p 5432:5432 maps PostgreSQL's port for external access.

Step 4: Verify the Container

Ensure the container is running:

docker ps

You should see the postgres-container listed.

Allow Connections from Another System

By default, PostgreSQL only allows local connections. To enable external access:

Step 1: Update postgresql.conf

Access the container and modify the postgresql.conf file:

docker exec -it postgres-container bash

Locate the file (typically in /var/lib/postgresql/data/):

vi /var/lib/postgresql/data/postgresql.conf

Set listen_addresses to accept connections from all addresses:

listen_addresses = '*'

Step 2: Update pg_hba.conf

Edit the pg_hba.conf file:

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

Add the following line to allow all IP connections with a password, or specify a particular IP range for more restricted access (e.g., use 10.0.3.23/32 to allow only the single IP 10.0.3.23):

host all all 0.0.0.0/0 md5

Step 3: Restart the Container

Before restarting the container, exit the container session:

exit

Apply the changes by restarting the container:

docker restart postgres-container

Step 4: Open Port in Firewall (if applicable)

Allow traffic on port 5432:

sudo ufw allow 5432/tcp

Step 5: Test Connection from Another System

Use a PostgreSQL client (e.g., psql, pgAdmin, or DBeaver) to connect:

  • Host: <server-IP>
  • Port: 5432
  • Username: myuser
  • Password: mypassword
  • Database: mydatabase

Manage the PostgreSQL Container

Stop the Container

To stop the running container:

docker stop postgres-container

Restart the Container

To restart the container:

docker start postgres-container

Delete the Container and Data

Remove the Container

If the container is no longer needed, remove it:

docker rm postgres-container

Remove the Persistent Volume

To delete the data permanently, remove the Docker volume:

docker volume rm postgres-data

Warning: Removing the volume will delete all database data permanently.

Launch pgAdmin with PostgreSQL

pgAdmin is a popular management tool for PostgreSQL. You can launch it alongside your PostgreSQL container.

Step 1: Pull the pgAdmin Image

Pull the official pgAdmin image from Docker Hub:

docker pull dpage/pgadmin4:latest

Step 2: Create a Volume for pgAdmin Data Persistence

Create a Docker volume to persist pgAdmin data:

docker volume create pgadmin-data

Step 3: Run the pgAdmin Container

Launch the pgAdmin container with persistent data storage to retain configurations and link it to your PostgreSQL setup:

docker run -d \
  --name pgadmin-container \
  -e PGADMIN_DEFAULT_EMAIL=admin@admin.com \
  -e PGADMIN_DEFAULT_PASSWORD=admin \
  -p 5430:80 \
  -v pgadmin-data:/var/lib/pgadmin \
dpage/pgadmin4:latest
  • Replace admin@admin.com and admin with your desired email and password for pgAdmin login.
  • -p 5430:80 maps port 5430 on the host to port 80 in the container.
  • -v pgadmin-data:/var/lib/pgadmin ensures that pgAdmin configurations and user data persist even if the container is stopped or removed.
  • Replace admin@admin.com and admin with your desired email and password for pgAdmin login.
  • -p 5430:80 maps port 5430 on the host to port 80 in the container.
  • Replace admin@admin.com and admin with your desired email and password for pgAdmin login.
  • -p 8080:80 maps port 8080 on the host to port 80 in the container.

Step 3: Access pgAdmin

  1. Open your browser and navigate to:
    http://<server-IP>:5430
  2. Log in using the email and password specified during container creation.
  3. Add a new server in pgAdmin to connect to PostgreSQL:
    • Name: Any descriptive name.
    • Host: postgres-container (or the IP address of the server).
    • Port: 5432.
    • Username: myuser.
    • Password: mypassword.

Step 4: Open Port in Firewall (if applicable)

Allow traffic on port 5430 to ensure pgAdmin is accessible. The following command is used in Ubuntu.

sudo ufw allow 5430/tcp
  1. Open your browser and navigate to:
    http://<server-IP>:5430
  2. Log in using the email and password specified during container creation.
  3. Add a new server in pgAdmin to connect to PostgreSQL:
    • Name: Any descriptive name.
    • Host: postgres-container (or the IP address of the server).
    • Port: 5432.
    • Username: myuser.
    • Password: mypassword.

Issues and Resolutions

Text Editors Not Found in Container

Issue: When trying to edit configuration files inside the PostgreSQL container, commands like vi, vim, or nano result in:

bash: vi: command not found

Resolution: A. Install a Text Editor Inside the Container:

Access the container:

docker exec -it postgres-container bash

Install vi (tried in Bbuntu):

apt-get update
apt-get install -y vim-tiny

Edit the file:

vi /var/lib/postgresql/data/postgresql.conf

B. Use docker cp to Edit the File Externally:

Copy the configuration file to the host:

docker cp postgres-container:/var/lib/postgresql/data/postgresql.conf ./postgresql.conf

Edit it on the host using any editor.

Copy the file back to the container:

docker cp postgresql.conf postgres-container:/var/lib/postgresql/data/postgresql.conf

Restart the container:

docker restart postgres-container

Permission Denied While Accessing Docker

Issue: When running Docker commands (e.g., docker pull), you encounter an error like:

permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.ck

Resolution:

Check Docker Group Membership: Ensure your user is part of the docker group:

groups

If docker is not listed, add your user to the group:

sudo usermod -aG docker $USER

Log out and log back in or run:

newgrp docker

Conclusion

You've successfully launched PostgreSQL and pgAdmin using Docker with persistent data storage and configured them for external connections. Additionally, you've learned how to manage, stop, and delete containers and their data. These setups are now ready to support your PostgreSQL database operations and administration.

Post a comment

POST COMMENT