Installing PostgreSQL and pgAdmin with Docker

Introduction

This tutorial explains how to install the PostgreSQL database and its pgAdmin tool on a virtual server with Docker and how to connect pgAdmin to PostgreSQL. pgAdmin is a web-based administration tool for the PostgreSQL database.

Requirements

You need a virtual server from netcup with Debian or Ubuntu installed. This tutorial also requires the setup of Docker and Docker Compose. Refer to the netcup community tutorial install-docker for instructions on how to set up Docker and Docker Compose on Ubuntu.

Step 1 - Check your Docker and Docker Compose versions

Check the Docker version:

docker -v

The output should look like this, maybe with a different version:

Docker version 20.10.10, build b485636

Check the Docker Compose version:

docker-compose version

The output should look like this, maybe with a different version:

Docker Compose version v2.1.0

Step 2 - Create a docker-compose.yaml file with PostgreSQL and pgAdmin

Use a text editor to create the docker-compose.yaml file: vi ./docker-compose.yaml

Insert the following:

version: '3'

services:
  db:
    image: postgres:14.0
    container_name: db
    restart: always
    ports:
      - 5432:5432
    environment:
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_USER=postgres
    volumes:
      - db_volume:/var/lib/postgresql
    networks:
      - backend_network
  pgadmin:
    image: dpage/pgadmin4:6.1
    container_name: pgadmin
    restart: always
    environment:
      - PGADMIN_DEFAULT_EMAIL=admin@your-netcup-domain.net
      - PGADMIN_DEFAULT_PASSWORD=secret
      - PGADMIN_LISTEN_PORT=8080
    ports:
      - "8080:8080"
    volumes:
      - pgadmin_volume:/var/lib/pgadmin
    networks:
      - backend_network
volumes:
  db_volume:
  pgadmin_volume:
networks:
  backend_network:
    driver: bridge

Save the docker-compose.yaml file.

Two services are defined in this file: one for PostgreSQL and one for pgAdmin. Both services have a fixed container name and both have their own volume. "Environments-definition" contains the credentials for accessing the services. The PostgreSQL service will be exposed to the outside on port 5432 and pgAdmin on port 8080. Both services use the same Docker network "backend_network", which is defined in "networks-definition".

Step 3 - Start all Docker services

To start all Docker services run:

docker-compose up -d

If you use the "-d" parameter, all services are started in "detached mode". This means that all services run in the background without blocking the command line.

Step 4 - Check the status of Docker containers

Check the status of the Docker containers with the command: docker ps -a: The output should look like this:

CONTAINER ID   IMAGE                             COMMAND                  CREATED         STATUS                     PORTS                                                        NAMES
5b9a82c2d403   postgres:14.0                     "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes               0.0.0.0:5432->5432/tcp, :::5432->5432/tcp                    db
37c02d5fb68c   dpage/pgadmin4:6.1                "/entrypoint.sh"         2 minutes ago   Up 2 minutes               80/tcp, 443/tcp, 0.0.0.0:8080->8080/tcp, :::8080->8080/tcp   pgadmin

In the example above, both services are running and both have the status "Up 2 minutes".

Step 5 - Check container logs

Begin by checking the logs of the PostgreSQL database container:

docker logs db

The log should look like this:

2021-11-07 21:21:06.225 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-11-07 21:21:06.225 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2021-11-07 21:21:06.230 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-11-07 21:21:06.236 UTC [62] LOG:  database system was shut down at 2021-11-07 21:21:06 UTC
2021-11-07 21:21:06.243 UTC [1] LOG:  database system is ready to accept connections

Then check the logs of the pgAdmin container: docker logs pgadmin The log should look like this:

[2021-11-07 21:21:35 +0000] [1] [INFO] Starting gunicorn 20.1.0
[2021-11-07 21:21:35 +0000] [1] [INFO] Listening at: http://[::]:8080 (1)
[2021-11-07 21:21:35 +0000] [1] [INFO] Using worker: gthread
[2021-11-07 21:21:35 +0000] [88] [INFO] Booting worker with pid: 88

Step 6 - Open pgAdmin in your browser

Open the URL http://<your-ip>:8080/ in the browser. Enter the credentials in the login form:

Username: admin@your-netcup-domain.net
Password: secret

Submit the login form with a click on the "Submit" button. After your successful login, you see that there is an entry named "Servers" on the left side . Right-click on "Servers" to open the dropdown. In the dropdown, click on the "Create > Server" entry and enter the following values:

Name: postgres

Then click on the next tab labeled "Connection" and enter these values:

Hostname/address: db
Port: 5432
Username: postgres
Password: postgres

Now click on "Save" to establish the server connection between the UI and the database. In the list of servers on the left side, you'll now see one entry: "Postgres". The server connection opens automatically and the first page displays live statistics of the running Postgres database, like for example server sessions. If you click on "postgres > Databases (1) > postgres > Schemas > public" on the left side, you'll see the default schema "public", which is automatically created on the first setup.

Step 7 - Stop and remove all services

If you don't need the PostgreSQL database and pgAdmin anymore, you can stop and remove the two services with one command: Run in the command line:

docker-compose down

Conclusion

You have now learned how to install PostgreSQL and pgAdmin with Docker and connect pgAdmin to PostgreSQL. pgAdmin makes it easy to manage the database with a web-based user interface (UI). You only need a browser for administration, which is very helpful for your local development.

License

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicence, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Contributor's Certificate of Origin

By making a contribution to this project, I certify that:

1. The contribution was created in whole or in part by me and I have the right to submit it under the license indicated in the file; or

2. The contribution is based upon previous work that, to the best of my knowledge, is covered under an appropriate license and I have the right under that license to submit that work with modifications, whether created in whole or in part by me, under the same license (unless I am permitted to submit under a different license), as indicated in the file; or

3. The contribution was provided directly to me by some other person who certified (a), (b) or (c) and I have not modified it.

4. I understand and agree that this project and the contribution are public and that a record of the contribution (including all personal information I submit with it, including my sign-off) is maintained indefinitely and may be redistributed consistent with this project or the license(s) involved.