Postgresql dumps in Docker: tips for using it in docker-entrypoint-initdb.d, a way of adding additional initialization and data to containers.
If we create a PostgreSQL dump by default it does with owner and privileges. And this is usually fine, except if we want it for feeding our local environments. In this case (I hope), we use different users and passwords. So it would be nice if we can do the dump without that kind of permissions (without ownership and privileges).
pg_dump -O -x postgresql://$DB_USER:$DB_PASS@$DB_SERVICE:$DB_PORT/$DB_NAME
So it's super easy to do it, you just need to add -O and -x to get it. I usually use it with a script to be able to call it from my docker container of PostgreSQL or Django.
#!/bin/sh
exec pg_dump -O -x postgresql://$DB_USER:$DB_PASS@$DB_SERVICE:$DB_PORT/$DB_NAME > "dump-$(date +%F).sql"
It will give you a dump with the current date. With this dump, it's easy to load it in your dev/production container just sharing it with a volume inside of your /docker-entrypoint-initdb.d/. Warning: scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue on with your scripts.
postgres:
image: postgres:alpine
volumes:
- pgdata:/var/lib/postgresql/data/
- ./scripts/db/:/docker-entrypoint-initdb.d/
environment:
- POSTGRES_USER=${DB_USER}
- POSTGRES_PASSWORD=${DB_PASS}
- POSTGRES_DB=${DB_NAME}
adminer:
image: adminer
ports:
- 8001:8080
By the way, I added at the bottom a super lightweight container for DB client: adminer. Adminer is a full-featured database management tool written in PHP. Conversely to phpMyAdmin, it consists of a single file ready to deploy to the target server. Adminer is available for MySQL, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch, and MongoDB.