Configure PostgreSQL and pgAdmin Docker Containers - CentLinux

Latest

Thursday, 5 March 2020

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 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.

 

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, please follow our previous articles to install Docker on Ubuntu Server or 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 pgAdmin 4 Docker Container:

    Pull the pgAdmin 4 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

     

    Testing PostgreSQL and pgAdmin Docker Containers:

    Open URL http://docker-01.centlinux.com:5050 in a browser.

    01-postgresql-docker-pgadmin-login

    Login as

    • User: ahmer@centlinux.com
    • Password: 123

    02-postgresql-docker-pgadmin-dashboard

    Click on Add New Server to add a PostgreSQL database server.

    03-postgresql-docker-pgadmin-add-new-server

    04-postgresql-docker-pgadmin-add-new-server

    Provide connection settings as we have provided above and click on Save.

    05-postgresql-docker-pgadmin-dashboard

    Our PostgreSQL database server has been added in pgAdmin 4 docker container.

    We have successfully configure and started PostgreSQL and pgAdmin Docker containers.

    No comments:

    Post a comment