pg maintenance

Minimalist periodic maintenance script for PostgreSQL.

Disclaimer and licensing

This software has been created for personal purposes and may therefore not suit your needs. However, you may use it under the terms of the MIT License.

Requirements

• Python 3
• psql / pg_dump
• rsync

Actions

This script:

• runs VACUUM and ANALYZE on a specified database;
• creates compressed backup file and sends it to the destination (requires a destination);
• builds a reports and email it to recipients (requires at least one recipient).

To know more about routine vacuuming, please read PostgreSQL's documentation.

Basic usage

pg_maintenance.py --help
pg_maintenance.py "db_name" --destination "user@remote:/your/backup/path" --email-recipient "admin@exemple.org"

Restore a backup:

zstdcat "2024-04-04_db_name.sql.zst" | psql --set ON_ERROR_STOP=on