Simple pg_dump setup

README.md

Simple PostgreSQL backup

Introduction

WARNING: Use the script at your own risk. I'm not responsible for any database lost or corruption.

This is a simple database dump script for a Podman Quadlet PostgreSQL container.

It may not be production safe (better alternatives are available), but useful for your homelab.

systemd/Timers

Create a service, replace myapp with your container-name (e.g. hub-pgsql):

$ systemctl --user edit container-dump --full --force
[Unit]
Description=Run pg_dump on PostgreSQL container
Requires=myapp-pgsql.service
After=myapp-pgsql.service

[Service]
Type=oneshot
ExecStart=%h/.local/bin/backup-container.sh

[Install]
WantedBy=multi-user.target

Test the service:

$ systemctl --user start container-dump

To create the systemd timer:

$ systemctl --user edit container-dump.timer --full --force
[Unit]
Description=Run pg_dump on PostgreSQL container daily

[Timer]
OnCalendar=*-*-* 4:00:00
Persistent=true

[Install]
WantedBy=timers.target

To enable the systemd timer:

$ systemctl --user enable container-dump.timer --now

backup-container.sh

#!/bin/bash

USERNAME=myuser
PASSWORD=mypass
DATABASE=mydb
EXPORT_PATH=/var/home/archie/dumps
EXPORT_FILENAME="${DATABASE}-$(date +%Y%m%d%H%M%S).sql.zstd"

podman exec -it systemd-myapp-pgsql /bin/bash -c "PGPASSWORD=$PASSWORD pg_dump -h localhost -U $USERNAME -Z zstd:3 $DATABASE" > "${EXPORT_PATH}/${EXPORT_FILENAME}"