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