How to run PostgreSQL and pgAdmin on Docker?

Table of content:

• Prerequisite
• Setting up the Docker network
• Setting up the PostgreSQL container
• Setting up the pgAdmin container
• Creating a new database on the containerized PostgreSQL
• Accessing the new database on the containerized pgAdmin
• Tips

Prerequisite

Install all Docker tools for your computer from here

Setting up the Docker network

We're going to install PostgreSQL and pgAdmin on separate containers.
To enable these two containers to communicate with each other, they need to be available on the same Docker network.

1. Create the Docker network with the following command on the terminal:
   

   docker network create my-network
   

2. This command creates a new Docker network with the following options:

   • create my-network: sets the name of the network to "my-network", this can bet set it to any name preferred

Setting up the PostgreSQL container

Two steps are involved here. Please perform the following from the terminal.

1. Pull the latest PostgreSQL Docker image from the Docker Hub repository with the following command:
   

   docker pull postgres
   

2. Wait for the image to be downloaded. Docker will automatically download the necessary layers and dependencies.

3. Create and run a new container based on the image just pulled with the command:
   

   docker run --name my-postgres -e POSTGRES_PASSWORD=password -network my-network -d -p 5432:5432 postgres
   

4. The above command starts a new PostgreSQL container with the following options:

   • --name my-postgres: sets the name of the container to "my-postgres", we can set it to any name we like.
   • -e POSTGRES_PASSWORD=password: sets the password for the default "postgres" user to "password".
   • -network my-network: attaches the Docker network to "my-network" which was the network we created earlier.
   • -d: runs the container in detached mode (in the background).
   • -p 5432:5432: maps the container's port 5432 to the host's port 5432, allowing access to the PostgreSQL server from outside the container.
   • postgres: specifies the Docker image to use to create the container; in our case it's the image we pulled from the Docker Hub in step 1.

Setting up the pgAdmin container

This involves two steps as well.

1. Pull the official Docker distribution of pgAdmin 4 from the Docker Hub repository with the following command:
   

   docker pull dpage/pgadmin4
   

2. Wait for the image to be downloaded. Docker will automatically download the necessary layers and dependencies.

3. Create and run a new container based on the image just pulled with the command:
   

   docker run --name my-pgadmin -p 80:80 -v /path/to/local/directory:/var/lib/pgadmin -e PGADMIN_DEFAULT_EMAIL=your_email@example.com -e PGADMIN_DEFAULT_PASSWORD=your_password --network my-network -d dpage/pgadmin4
   

4. The above command starts a new PostgreSQL container with the following options:

   • --name my-pgadmin: sets the name of the container to "my-pgadmin", we can set it to any name we like.
   • -p 80:80: maps port 80 of the container to port 80 of the host machine. This allows accessing pgAdmin via a web browser.
   • -v /path/to/local/directory:/var/lib/pgadmin: mounts a local directory from your host machine (/path/to/local/directory) to the /var/lib/pgadmin directory inside the container. Replace /path/to/local/directory with the actual path on your host machine where you want to store the pgAdmin data. By mounting a volume, the pgAdmin data will be persisted outside the container, on your host machine. Even if you stop and remove the container, the data will remain intact in the specified local directory.
   • -e PGADMIN_DEFAULT_EMAIL=your_email@example.com: sets the default email for logging into pgAdmin. Replace your_email@example.com with your desired email address.
   • -e PGADMIN_DEFAULT_PASSWORD=your_password: sets the default password for logging into pgAdmin. Replace your_password with your desired password.
   • -network my-network: attaches the Docker network to "my-network" which was the network we created earlier.
   • -d: runs the container in detached mode, meaning it runs in the background.
   • dpage/pgadmin4: specifies the Docker image to use for creating the container.

Creating a new database on the containerized PostgreSQL

Now that we have the PostgreSQL container set up and running, let's create a new database on its server.

1. Ensure the PostgreSQL container is running:

   • open a terminal or command prompt.
   • run the following command to list the running Docker containers and then verify that your PostgreSQL container is listed with the status running:

   docker ps
   

2. Open a shell inside the PostgreSQL container with the following command:

   • replace with the actual name or ID of the PostgreSQL container (in our case it'll be my-postgres).
   • this command opens an interactive shell session inside the container.

   docker exec -it <container_name_or_id> bash
   

3. Inside the container's shell, use the psql command to connect to the PostgreSQL database with the following command:
   

   psql -U postgres
   

4. Next create a new database using the following SQL command:

   • replace with the desired name for the new database

   CREATE DATABASE <database_name>;
   

Accessing the new database on the containerized pgAdmin

1. Ensure the pgAdmin container is running:

   • open a terminal or command prompt.
   • run the following command to list the running Docker containers & verify the pgAdmin container is listed and running.

   docker ps
   

2. Access pgAdmin in the web browser at http://localhost and enter the username and password used during set up.

3. In pgAdmin, create a new server connection with the following details:

   • Host name/address: my-postgres (the name of the PostgreSQL container)
   • Port: 5432 (the default PostgreSQL port inside the container)
   • Username: postgres (the default PostgreSQL username)
   • Password: The password specified when running the PostgreSQL container

4. Now you should be able to connect to the PostgreSQL container from the pgAdmin container using the specified server connection. Both containers are communicating through the "my-network" network.

Tips

1. If we already having PostgreSQL running locally, it's very likely to have been configured to run on the default port 5432.

   • To prevent conflict, map the PostgreSQL container to a different port.
   • In the example below the PostgreSQL container is still using port 5432 internally, but it's mapped to port 5433 on the host machine

   docker run --name my-postgres -e POSTGRES_PASSWORD=password -network my-network -d -p 5433:5432 postgres
   

2. Instead of accessing pgAdmin at http://localhost, we can map it to a specific URL like http://local.pgadmin for a more user-friendly access.

   • We accomplish this by using a reverse proxy server like Nginx on the host machine. I'll write about this in the future.

3. What if we have to programatically access the containerized database from the code?

   • Nothing changes here, we access it like how we access a local database, for e.g at:

   postgres://postgres:password@localhost:5433/<the_containerized_database_name>?sslmode=prefer