Setting up a Postgres Docker container for a homelab

This is simply a personal reminder about setting up a PostgreSQL Docker container for my homelab. I have multiple services running on a Raspberry Pi, such as somafm-song-history, that need a PostgreSQL server. The best place to host it is my Synology NAS, with a little backup script.

Creating the container

Download the image. On a Synology NAS, I will simply use Container Manager.
Create a new Postgres container from that image. Useful parameters:
- Resources limitations: 2048 MB is fine for a start.
- Local port: 55432 to 5432.
- Volume settings (this is for PostgreSQL v18): /volume1/docker/postgres -> /var/lib/postgresql/
- Environment: set POSTGRES_PASSWORD to password. It is best use a temporary password here and change it afterwards since it will stay visible in Container Manager's GUI.
- Run it.

Configuring users

Just log in with postgres / password 🙂 I use IntelliJ Ultimate for this.
Change the password associated to the postgres user:
```
alter role postgres with password 'YourNewStrongPassword';
```
Remember, use ' to escape ' (so ' will be '').

I use the mighty pass to generate and store passwords.

Create a user with reasonable privileges:

create role alc login password 'YourStrongPassword' nosuperuser nocreatedb nocreaterole noreplication;

Create your databases. They will contain a single public schema.
```
create database myservice owner alc;
```
Log in with the new user. It should work.
Configure and test your services with this user and the right database.

Backing things up

Create a script on the NAS. I use my user's directory.

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/your/backup/directory"
DB_USER="alc"
DB_PASSWORD="YourStrongPassword"
DB_LIST=("myservice")

mkdir -p "${BACKUP_DIR}"

for DB in "${DB_LIST[@]}"; do
  echo "Backing up ${DB}..."
  docker exec postgres env PGPASSWORD="${DB_PASSWORD}" \
    pg_dump -U "${DB_USER}" -F p "${DB}" \
  | gzip > "${BACKUP_DIR}/${DB}-$(date +%F).dump.gz"
done

# Keep 14 days
find "${BACKUP_DIR}" -type f -name '*.dump.gz' -mtime +14 -delete

Keeping the password in there is not ideal, but acceptable for a homelab. I will address this later.

Configure Synology's Task Scheduler to execute this script daily.