Pirate PostgreSQL backup script
Pirate PostgreSQL backup script written in Python.
This script backs up an entire PostgreSQL database to a remote server using scp. Backup files are compressed and labeled with a precise timestamp.
Configuration is taken from environment variables.
Dependencies
- PostgreSQL
- Python 3.9+
Configuration environment variables
| Name | Purpose |
|---|---|
| PG_DUMPALL_CMD | Full path to the pg_dumpall command included with the PostgreSQL installation. Defaults to /usr/bin/pg_dumpall. |
| DATABASE_HOST | Database server host or socket directory. Defaults to localhost. |
| DATABASE_PORT | Database server port number. Defaults to 5432. |
| DATABASE_USER | Connect as specified database user. Defaults to postgres. |
| SSH_HOST | Scp connect string. E.g. user@host:/some/path/ |
Usage example
SSH_HOST="user@host:~/pirate-backup-directory/" /usr/bin/python3 pirate-backup.py
Notes
-
This script relies on Trust Authentication of the backup user on the local server and therefore does not support passing a password through an environment variable. This is usually sufficient for local connections, although peer authentication is often times recommended.
-
This script relies on a working ssh connection for the local user to the remote machine using a public-private key pair. See How To Configure SSH Key-Based Authentication on a Linux Server for instructions.