Deploying PostgreSQL on a Docker Container

PostgreSQL, often simply “Postgres”, is an object-relational database management system (ORDBMS) with an emphasis on extensible and standards-compliance. As a database server, its primary function is to store data, securely and supporting best practices, and retrieve it later, as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

Docker makes it very easy to spin up a PostgreSQL database management system.Docker modernized the way we build and deploy the application. It allows us to create lightweight, portable, self sufficient containers that can run any application easily.

Container Creation

With the following command it’s possible to start your PostgreSQL Docker container on your server or local machine.

$ docker run --name postgresql -e POSTGRES_PASSWORD=XXXXXXXX -d postgres

The main parameter you will need to provide to postgres is a root db password. Replace XXXXXXXX with a good password.The default postgres user and database are created in the entrypoint with initdb.

If we need to deploy the container in a user defined network and map the port use command below :

$ docker network create --driver bridge postgres-network
$ sudo docker network ls
$ docker run --name postgresql --network postgres-network -e POSTGRES_PASSWORD=XXXXXXXX -p 5432:5432 -d postgres

This command will start a PostgreSQL database in postgres-network and map ports using the following pattern: -p <host_port>:<container_port>.
Port 5432 of our container will be mapped on port 5432 of our host or server.

Use below command to confirm the container creation.

$ sudo docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
82df972c618d postgres "docker-entrypoint.s…" 24 minutes ago Up 24 minutes 5432/tcp postgresql

Launch psql and connect to Postgres

Let’s try to connect to the Postgres container from another container within the same Docker network which we created earlier.Here, we have used psql client to connect to the Postgres. We used the Postgres container name as a hostname.

$ docker run -it --rm --link postgresql:postgres postgres psql -h postgres -U postgres --password

Login using user defined network :

$ docker run -it --rm --network postgres-network --link postgresql:postgres postgres psql -h postgres -U postgres --password

Environmental Variables

The PostgreSQL image uses several environment variables :

POSTGRES_PASSWORD : This environment variable sets the superuser password for PostgreSQL. The default superuser is defined by the POSTGRES_USER environment variable.

The PostgreSQL image sets up trust authentication locally so you may notice a password is not required when connecting from localhost (inside the same container). However, a password will be required if connecting from a different host/container.

POSTGRES_USER : This optional environment variable is used in conjunction with POSTGRES_PASSWORD to set a user and its password. This variable will create the specified user with superuser power and a database with the same name. If it is not specified, then the default user of postgres will be used.

PGDATA : This optional environment variable can be used to define another location – like a subdirectory – for the database files. The default is /var/lib/postgresql/data, but if the data volume you’re using is a fs mountpoint (like with GCE persistent disks), Postgres initdb recommends a subdirectory (example /var/lib/postgresql/data/pgdata ) be created to contain the data.

POSTGRES_DB : This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.

POSTGRES_INITDB_ARGS :This optional environment variable can be used to send arguments to postgres initdb.

POSTGRES_INITDB_WALDIR : This optional environment variable can be used to define another location for the Postgres transaction log. By default the transaction log is stored in a subdirectory of the main Postgres data folder (PGDATA).

POSTGRES_PASSWORD_FILE :  This can be used to load passwords from Docker secrets stored in /run/secrets/<secret_name> files.

Using a mounted volume for persistence

Docker containers are ephemeral in nature, i.e. data which is used or generated by the container is not stored anywhere implicitly. We lose the data whenever the container gets restarted or deleted. Docker provides volumes on which we can store the persistent data. It is a useful feature by which we can provision another container using the same volume or data in case of disaster.

SELinux users should update the security context of the host mountpoint.

Docker Volume Creation :

$ sudo docker volume create pgdata

$ sudo docker volume ls

DRIVER              VOLUME NAME
local                   pgdata

Now we have to use this data volume while running the Postgres container.

$ sudo docker run --name postgresql -p 5432:5432  -e POSTGRES_PASSWORD=XXXXXXXX -v pgdata:/var/lib/pgsql/10/data -d postgres

Create a new table in Postgres to check data persistence.

$ docker run -it --rm --link postgresql:postgre postgres psql -h postgresql -U postgres --password

postgres=# \dt
Did not find any relations.
postgres=# create table test(id int);
CREATE TABLE
postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

Delete the Postgres container.

$ sudo docker container rm postgresql -f
postgresql

Create a new Postgres container and confirm the test table present or not.

$ sudo docker container rm postgresql -f
postgresql

 

$ sudo docker run --name postgresql -p 5432:5432 -e POSTGRES_PASSWORD=XXXXXX -v pgdata:/var/lib/pgsql/10/data -d postgres
9bbb78a86dba5c92f8d994e8cc324e8dbf3aa70a757e503bc6715c2bf60210cf
$ docker run -it --rm --link postgresql:postgres postgres psql -h postgresql -U postgres --password
Password:
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)

Create a data directory on the host system (outside the container) and mount this to a directory visible from inside the container. 

First, remove the previous container.

$ docker stop postgresql && docker rm postgresql

Launch a container and use /postgresdata as the host directory to mount as a volume mount, which will be mounted in the container in /var/lib/postgresql/data, which is the default location where Postgres stores it’s data.

$ docker run --name postgresql -p 5432:5432 -v /postgresdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=XXXXX -d postgres

List the contents of /postgresdata and you should see several Postgres files:

# cd /postgresdata/
# ls
base    pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
global  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf       postmaster.pid

Container Deletion

To delete the container, we need to stop the running container first and then delete the container using rm command.

$ docker stop postgresql && docker rm postgresql

Forceful shutdown and deletion

$ sudo docker container rm postgresql -f

 

Docker PostgreSQL Official Repo : https://hub.docker.com/_/postgres/