PostgreSQL has been holding its own in the open-source database market for many years. With features that usually only very expensive enterprise database systems have, PostgreSQL has proven itself to be the database of choice for many large projects.
In contrast to MySQL/MariaDB, PostgreSQL not only provides the classic relational database model but also an object-oriented approach that enables the inheritance of tables, for example.
What’s been said before for Docker and MariaDB also applies to PostgreSQL: running many PostgreSQL containers in parallel contradicts the concept of this database. For production environments with many database accesses, a highly available, dedicated database environment is the tool of choice.
However, PostgreSQL in a Docker container is definitely suitable for development purposes or smaller projects. You’ll even benefit from being able to determine exactly which version of PostgreSQL you want to use and that the files aren’t distributed in the operating system of your developer machine during installation but can be deleted completely with a single command.
The Docker images of PostgreSQL on Docker Hub are very well maintained and are available in the past five versions, one each based on Debian and Alpine Linux, although the difference in size for this image is significant.
Like MariaDB/MySQL, PostgreSQL also takes into account shell scripts (*.sh) or SQL files (*.sql, *.sql.gz) in the /docker-entrypoint-initdb.d directory, which are executed or imported into the database, respectively, when the container is started. In conjunction with docker compose or docker stack deploy, this allows you to create database users or stored procedures without having to build your own Docker image.
PostgreSQL and pgAdmin with “docker compose”
For many database professionals, the command line is the tool of choice for performing work on the database. For a quick overview, however, a graphical user interface (GUI) can be very helpful. For PostgreSQL, there has been a tool called pgAdmin available for quite some time. What used to be a desktop application has become a very modern and easy-to-use web interface since version 4.
pgAdmin 4 provides many things that make the day of a database administrator: workload graphs, a query tool, and, of course, an overview of all elements of the database.
Sample Data: We’ve put a small sample data set as a PostgreSQL dump for you on GitHub. Just clone the Git repository from https://github.com/docbuc/postgresql or download the compressed dump from the init folder. The database contains more than 190,000 geographically referenced objects in Germany from the free data set of www.geonames.org (and 2.2 million in the US).
The following docker-compse.yml file starts a container with the current PostgreSQL version and another container with the pgAdmin web frontend (in this case, on local port 5050):
# File: db/postgres/docker-compose.yml
version: '3'
services:
db:
image: postgres:13
environment:
POSTGRES_PASSWORD: secret
POSTGRES_DB: geonames
volumes:
- ./init:/docker-entrypoint-initdb.d
- pgdata:/var/lib/postgresql/data
pgadmin:
image: fenglc/pgadmin4
ports:
- 5050:80
volumes:
- pgadmindata:/var/lib/pgadmin
environment:
DEFAULT_USER: info@dockerbuch.info
DEFAULT_PASSWORD: secret
volumes:
pgdata:
pgadmindata:
As mentioned previously, PostgreSQL supports the database initialization during startup in the /docker-entrypoint-initdb.d folder. If you saved the sample file from GitHub in the init directory or placed another PostgreSQL dump there, you’ll see an output similar to this (shortened here) when you start the containers:
[...]
db_1 | server started
db_1 | CREATE DATABASE
db_1 | /usr/local/bin/docker-entrypoint.sh: running /docker-e...
db_1 | SET
[...]
db_1 | CREATE TABLE
db_1 | ALTER TABLE
Once the database server has been started, PostgreSQL will create the database you defined as the POSTGRES_DB environment variable in the docker-compose.yml file. Then, the docker-entrypoint.sh script imports the database dump. With or without demo data, you can now reach pgAdmin at http://localhost:5050. At the first login, you must enter the user name and password from the environment variables of the pgadmin container, and then add a server using the Add New Server link in the dashboard. In the dialog that opens, you need to enter the Host name (“db”), Username (“postgres”), and Password (“secret”) for the database (see figure below).
If you’ve imported the demo data, you can now navigate to the geonames database in the left area of the user interface. You’ll find the imported data under Schemas > public > Tables (see figure below).
Backups with “docker compose”
Regarding backups, the same applies to PostgreSQL as to MariaDB: the easiest way without turning off the database is via docker exec:
docker exec postgres_db_1 pg_dump --username=postgres \
geonames > geonames_backup.sql
Here we want to show you yet another way to conveniently create backups with docker compose. For this purpose, you should create another configuration file in addition to the existing docker-compose.yml. We’ll name it docker-compose.backup.yml.
# File: db/postgresql/docker-compose.backup.yml
version: '3'
services:
backup:
image: postgres:13-alpine
depends_on:
- db
volumes:
- backup_vol:/backup
command: >
pg_dump --host db -F c -f /backup/geonames.dump
--username=postgres geonames
environment:
PGPASSWORD: secret
volumes:
backup_vol:
This file contains only the backup service. This service uses the current PostgreSQL image and uses the depends_on statement to indicate that the database server must also be started. As a command for the backup service, it’s not the PostgreSQL database that should be started but pg_dump (here, in the multiline YAML Ain’t Markup Language [YAML] notation).
The backup file is stored in /backup/geonames.dump. In this case, it’s a volume managed by Docker, but you can of course mount a local directory of your computer here. The pg_dump command reads from the environment variable PGPASSWORD, which is the database password defined in the environment section.
The trick with this setup is that docker compose is called with both configuration files and the run statement for the backup service:
docker compose -f docker-compose.yml \
-f docker-compose.backup.yml run --rm backup
This starts a container that runs the backup service and uses the pg_dump command to save the backup to /backup. After that, the container gets deleted again (--rm), and the volume remains with the backup.
Editor’s note: This post has been adapted from a section of the book Docker: Practical Guide for Developers and DevOps Teams by Bernd Öggl and Michael Kofler.
Comments