💾 Archived View for wilw.capsule.town › notes › postgres.gmi captured on 2024-05-26 at 14:57:19. Gemini links have been rewritten to link to archived content

-=-=-=-=-=-=-

PostgreSQL

Last updated on 31 October 2023

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 [1] 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 [2].

Find out more about the Postgres SSL flags on the documentation [3].

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

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 [5].

Back to notes