PostgreSQL

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 postgres-certs.

docker-compose.yml

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

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

3. Connect

Use a connection string like the following to connect your apps:

postgresql://username:password@host/db?sslmode=require

Note: use require for 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.