This is a reference quick-start note for deploying PostgreSQL via Docker, and with working self-signed TLS.
1. Generate keys for TLS
E.g. with one year expiry:
mkdir postgres-certs && cd postgres-certs openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key sudo chown 999 server.key sudo chmod 0600 server.key
Postgres key permissions are fussy. In this case, we set the key to be owned by the
postgres user in the container (
999), which you may not want to do if you’re on a shared environment. See this thread for more info.
2. Create a Docker Compose file
Ensure to reference the correct locations via volume mounts. In my example, this file is created in the same directory as
services: postgres: image: postgres:15 command: -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key restart: always ports: - "5432:5432" environment: POSTGRES_PASSWORD: <PASSWORD> volumes: - /data/postgres:/var/lib/postgresql/data - ./postgres-certs:/var/lib/postgresql
<PASSWORD> to set the default superuser password for the DB. More info on the image’s README.
Find out more about the Postgres SSL flags on the documentation.
Use a connection string like the following to connect your apps:
sslmode, as this uses SSL but doesn’t do the full CA verification, which we cannot do due to using a self-signed certificate. Find more info on the documentation.
4. Back-up and restore
Dump the DB:
docker exec -t <CONTAINER_ID> pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql
Restore from a dump:
cat <FILE>.sql | docker exec -i <CONTAINER_ID> psql -U postgres
I also maintain a Docker image for automating Postgres backups, which is available here.