Dump and Restore Postgres DB with Docker

I wanted to do some tests for backup and restore of a postgres db in a container.

For this reason, I wrote down a simple docker-compose for keycloak (something else that I’m working on), just to validate the migration from postgres 12.x to 13.x.

Docker-compose file

---
version: '3'

services:
  postgres:
      image: postgres:12.6-alpine # old version
      #image: postgres:13.2-alpine # new version
      volumes:
        - /opt/keycloak/data:/var/lib/postgresql/data
      environment:
        POSTGRES_DB: keycloak
        POSTGRES_USER: keycloak
        POSTGRES_PASSWORD: password
  keycloak:
      image: quay.io/keycloak/keycloak:12.0.4
      environment:
        DB_VENDOR: POSTGRES
        DB_ADDR: postgres
        DB_DATABASE: keycloak
        DB_USER: keycloak
        DB_SCHEMA: public
        DB_PASSWORD: password
        KEYCLOAK_USER: admin
        KEYCLOAK_PASSWORD: password
      ports:
        - 8080:8080
      depends_on:
        - postgres

Executed as usual from the folder with the docker-compose file with

docker-compose up -d

and checked the logs with

docker-compose logs -f

After starting the two container, I connected to keycloak webui only to create a test realm and random users.

Dump the db

A simple way to backup the db is with pg_dump. You can use this command to generate a file db.dump in the current execution folder.

docker-compose exec postgres pg_dump -c -U keycloak keycloak --no-owner > db-dump.sql

or if you want it compressed, you can use

docker-compose exec postgres pg_dump -c -U keycloak keycloak --no-owner | gzip -9 > db-dump.sql.gz

Just to explain:

• docker-compose exec postgres: execute cmd in the container defined by postgres service
• pg_dump: command for dumping the db
  • -c: drop the db in the target before restore
  • -U keycloak keycloak: use the user keycloak and select db keycloak
  • –no-owner: the backup can be importable into any other database, without checking user privileges

Restore the db

Before importing, I changed the postgres db from 12.6 to 13.2 in the docker-compose.yml file.

So after that a run of this command to start all the things again, but updated.

docker-compose down && docker-compose up -d

Importing the db can be done in different ways (that I will not explain here and now), for simplicity I’ll use this procedure. This will read the dump line by line and restore to the postgres container.

cat db-dump.sql | docker-compose exec -T postgres psql -U keycloak keycloak

Use this if you have the file compressed

gunzip -c db-dump.sql.gz | docker-compose exec -T postgres psql -U keycloak keycloak

The option -T is needed to disable the pseudo-tty allocation.

So, it’s pretty simple and it works 😉