Configure PostgreSQL and pgAdmin Docker Containers

configure-postgresql-pgadmin-docker-containers

PostgreSQL is a free and open source RDBMS (Relational Database Management System). PostgreSQL (or postgres) is emphasizing on extensibility and technical standards. PostgreSQL is the default database for macOS Server and it is also available for Linux, FreeBSD, OpenBSD and Windows.

We have already installed PostgreSQL on CentOS and Ubuntu based Linux Servers. Now, we are going to use the Docker containerization platform to configure PostgreSQL and pgAdmin Docker containers.

If you are new to Docker platform, then you should read Docker in Action by Manning Publications before moving forward with this article.

This Article Provides:

Environment Specification:

We are using a Ubuntu Server based Docker Host with following specification.

CPU - 3.4 Ghz (2 cores)
Memory - 2 GB
Storage - 20 GB
Operating System - Ubuntu Server 18.04 LTS
Hostname – docker-01.centlinux.com
IP Address - 192.168.116.218 /24

We are using a pre-configured Docker host in this article. For setting up the required environment on Red Hat based Linux server, please follow our previous article to install Docker on CentOS.

Configure PostgreSQL Docker Container:

Connect with docker-01.centlinux.com as a privileged user by using a ssh tool like PuTTY.

Create a directory to store configuration and data files related to PostgreSQL Docker container.

ahmer@docker-01:~$ mkdir ~/postgres-01
ahmer@docker-01:~$ cd postgres-01

Create a directory for PostgreSQL data files.

ahmer@docker-01:~/postgres-01$ mkdir postgres_data

Create a docker-compose.yml file.

ahmer@docker-01:~/postgres-01$ vi docker-compose.yml

And define postgres service therein.

version: "3.1"
services:
  db:
    image: "postgres:11"
    container_name: "postgres-01.centlinux.com"
    ports:
      - "5432:5432"
 environment:
      POSTGRES_PASSWORD: "123"
    volumes:
      - ./postgres_data:/var/lib/postgresql/data
 links:
   - "pgadmin"

Pull the required postgres image from Docker Hub.

ahmer@docker-01:~/postgres-01$ sudo docker image pull postgres:11
11: Pulling from library/postgres
6d28e14ab8c8: Pull complete
64da04b335f8: Pull complete
c2c855795327: Pull complete
1de39762bafa: Pull complete
e72de942a5bc: Pull complete
5ed4b3beed09: Pull complete
bc4fa92d5de6: Pull complete
881ba21508a4: Pull complete
a1f3f1480763: Pull complete
0a652322d262: Pull complete
a4c4345d4a61: Pull complete
84378331cde0: Pull complete
e4b51862ee73: Pull complete
ef2d3c443e8e: Pull complete
Digest: sha256:bd4aafe95150214ecb2388f6b16eb2e89ebe18f10831ff9d2fc810821fc481c5
Status: Downloaded newer image for postgres:11

Starting postgres container using our docker-compose.yml file.

ahmer@docker-01:~/postgres-01$ sudo docker-compose up
[sudo] password for ahmer:
Creating postgres-01.centlinux.com ... done
Attaching to postgres-01.centlinux.com
postgres-01.centlinux.com | The files belonging to this database system will be owned by user "postgres".
postgres-01.centlinux.com | This user must also own the server process.
postgres-01.centlinux.com |
postgres-01.centlinux.com | The database cluster will be initialized with locale "en_US.utf8".
postgres-01.centlinux.com | The default database encoding has accordingly been set to "UTF8".
postgres-01.centlinux.com | The default text search configuration will be set to "english".
postgres-01.centlinux.com |
postgres-01.centlinux.com | Data page checksums are disabled.
postgres-01.centlinux.com |
postgres-01.centlinux.com | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres-01.centlinux.com | creating subdirectories ... ok
postgres-01.centlinux.com | selecting default max_connections ... 100
postgres-01.centlinux.com | selecting default shared_buffers ... 128MB
postgres-01.centlinux.com | selecting default timezone ... Etc/UTC
postgres-01.centlinux.com | selecting dynamic shared memory implementation ... posix
postgres-01.centlinux.com | creating configuration files ... ok
postgres-01.centlinux.com | running bootstrap script ... ok
postgres-01.centlinux.com | performing post-bootstrap initialization ... ok
postgres-01.centlinux.com | syncing data to disk ... ok
postgres-01.centlinux.com |
postgres-01.centlinux.com | Success. You can now start the database server using:
postgres-01.centlinux.com |
postgres-01.centlinux.com |
postgres-01.centlinux.com | WARNING: enabling "trust" authentication for local connections
postgres-01.centlinux.com | You can change this by editing pg_hba.conf or using the option -A, or
postgres-01.centlinux.com | --auth-local and --auth-host, the next time you run initdb.
postgres-01.centlinux.com |     pg_ctl -D /var/lib/postgresql/data -l logfile start
postgres-01.centlinux.com |
postgres-01.centlinux.com | waiting for server to start....2020-03-04 18:48:31.011 UTC [46] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres-01.centlinux.com | 2020-03-04 18:48:31.054 UTC [47] LOG:  database system was shut down at 2020-03-04 18:48:30 UTC
postgres-01.centlinux.com | 2020-03-04 18:48:31.098 UTC [46] LOG:  database system is ready to accept connections
postgres-01.centlinux.com |  done
postgres-01.centlinux.com | server started
postgres-01.centlinux.com |
postgres-01.centlinux.com | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
postgres-01.centlinux.com |
postgres-01.centlinux.com | waiting for server to shut down...2020-03-04 18:48:31.181 UTC [46] LOG:  received fast shutdown request
postgres-01.centlinux.com | .2020-03-04 18:48:31.183 UTC [46] LOG:  aborting any active transactions
postgres-01.centlinux.com | 2020-03-04 18:48:31.186 UTC [46] LOG:  background worker "logical replication launcher" (PID 53) exited with exit code 1
postgres-01.centlinux.com | 2020-03-04 18:48:31.188 UTC [48] LOG:  shutting down
postgres-01.centlinux.com | 2020-03-04 18:48:31.236 UTC [46] LOG:  database system is shut down
postgres-01.centlinux.com |  done
postgres-01.centlinux.com | server stopped
postgres-01.centlinux.com |
postgres-01.centlinux.com | PostgreSQL init process complete; ready for start up.
postgres-01.centlinux.com |
postgres-01.centlinux.com | 2020-03-04 18:48:31.296 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres-01.centlinux.com | 2020-03-04 18:48:31.296 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres-01.centlinux.com | 2020-03-04 18:48:31.314 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres-01.centlinux.com | 2020-03-04 18:48:31.343 UTC [55] LOG:  database system was shut down at 2020-03-04 18:48:31 UTC
postgres-01.centlinux.com | 2020-03-04 18:48:31.371 UTC [1] LOG:  database system is ready to accept connections

Open a new ssh session and connect with postgres container.

ahmer@docker-01:~$ sudo docker exec -it postgres-01.centlinux.com bash
root@7bb0d4f1e4a6:/# su - postgres
postgres@7bb0d4f1e4a6:~$ psql
psql (11.7 (Debian 11.7-2.pgdg90+1))
Type "help" for help.

postgres=# exit
postgres@7bb0d4f1e4a6:~$ exit
logout
root@7bb0d4f1e4a6:/# exit
exit

PostgreSQL docker container is successfully configured.

Configure pgAdmin4 Docker Container:

Pull the pgAdmin4 image from Docker Hub.

ahmer@docker-01:~/postgres-01$ sudo docker pull dpage/pgadmin4
Using default tag: latest
latest: Pulling from dpage/pgadmin4
89d9c30c1d48: Pull complete
910c49c00810: Pull complete
7efe415eb85a: Pull complete
7d8d53519b81: Pull complete
519124ac136c: Pull complete
fbfa5cf626f8: Pull complete
f53a64187e16: Pull complete
feae0c230730: Pull complete
87ae2307a1ad: Pull complete
0a609eb1a1ca: Pull complete
b8bb05efc354: Pull complete
bccaa4da228f: Pull complete
084101fb937a: Pull complete
774874c72d7a: Pull complete
45a1fa7f66b7: Pull complete
b19cea40abf6: Pull complete
76e3d6803955: Pull complete
Digest: sha256:1141073018353f91953c1523f170821e139dbd1c2d7808d3804962b2ba7e89e3
Status: Downloaded newer image for dpage/pgadmin4:latest

Edit docker-compose.yml file and add pgadmin service.

ahmer@docker-01:~/postgres-01$ vi docker-compose.yml

Now define pgadmin service under the services section.

 pgadmin:
    image: "dpage/pgadmin4"
    container_name: "pgadmin4.centlinux.com"
    ports:
      - "5050:80"
    environment:
      PGADMIN_DEFAULT_EMAIL: "ahmer@centlinux.com"
      PGADMIN_DEFAULT_PASSWORD: "123"

Allow the pgAdmin service port in Ubuntu firewall on Docker Host.

ahmer@docker-01:~/postgres-01$ sudo ufw allow 5050/tcp
[sudo] password for ahmer:
Rule added
Rule added (v6)

Start PostgreSQL and pgAdmin4 Docker containers using docker-compose command.

ahmer@docker-01:~/postgres-01$ sudo docker-compose up
Starting pgadmin4.centlinux.com ... done
Starting postgres-01.centlinux.com ... done
Attaching to pgadmin4.centlinux.com, postgres-01.centlinux.com
postgres-01.centlinux.com |
postgres-01.centlinux.com | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres-01.centlinux.com |
postgres-01.centlinux.com | 2020-03-05 14:56:19.547 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres-01.centlinux.com | 2020-03-05 14:56:19.547 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres-01.centlinux.com | 2020-03-05 14:56:19.566 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres-01.centlinux.com | 2020-03-05 14:56:19.600 UTC [24] LOG:  database system was shut down at 2020-03-05 14:49:21 UTC
postgres-01.centlinux.com | 2020-03-05 14:56:19.622 UTC [1] LOG:  database system is ready to accept connections
pgadmin4.centlinux.com | [2020-03-05 14:56:19 +0000] [1] [INFO] Starting gunicorn 19.9.0
pgadmin4.centlinux.com | [2020-03-05 14:56:19 +0000] [1] [INFO] Listening at: http://[::]:80 (1)
pgadmin4.centlinux.com | [2020-03-05 14:56:19 +0000] [1] [INFO] Using worker: threads
pgadmin4.centlinux.com | [2020-03-05 14:56:19 +0000] [81] [INFO] Booting worker with pid: 81