PostgreSQL Backup¶
Deploy PostgreSQL with automated backups using pgBackRest.
Overview¶
This example demonstrates:
- Database server setup
lineinfile()for pg_hba.conf modifications- Systemd timers for scheduled tasks
- Backup tool configuration (pgBackRest)
Architecture¶
┌─────────────────────────────────────────────────────────┐
│ PostgreSQL Server │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ PostgreSQL │───▶│ pgBackRest │───▶│ Backups │ │
│ │ Server │ │ │ │ /backups/ │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │ │
│ ┌──────┴──────┐ │
│ │ Systemd │ │
│ │ Timers │ │
│ └─────────────┘ │
└─────────────────────────────────────────────────────────┘
Files¶
examples/postgres_backup/
├── deploy.py
├── templates/
│ ├── postgresql.conf.j2
│ ├── pgbackrest.conf.j2
│ └── backup.timer.j2
└── README.md
Configuration¶
# PostgreSQL configuration
PG_VERSION = "15"
PG_DATA_DIR = "/var/lib/postgresql/15/main"
PG_PORT = 5432
# Database configuration
DB_NAME = "myapp"
DB_USER = "myapp"
DB_PASSWORD = "secure_password"
# Backup configuration
BACKUP_DIR = "/var/backups/postgresql"
BACKUP_RETENTION_FULL = 2 # Keep 2 full backups
BACKUP_RETENTION_DIFF = 7 # Keep 7 differential backups
Usage¶
# Preview changes
python examples/postgres_backup/deploy.py --dry-run
# Deploy
sudo python examples/postgres_backup/deploy.py
# Remote deployment
python examples/postgres_backup/deploy.py \
--remote ubuntu@db.example.com \
--sudo-password
What Gets Deployed¶
1. PostgreSQL Installation¶
def install_postgresql():
s.pkgs_install(
f"postgresql-{PG_VERSION}",
f"postgresql-client-{PG_VERSION}",
"pgbackrest",
sudo=True
)
2. PostgreSQL Configuration¶
/etc/postgresql/15/main/postgresql.conf modifications:
def configure_postgresql():
conf_file = f"/etc/postgresql/{PG_VERSION}/main/postgresql.conf"
# Enable archiving for point-in-time recovery
lineinfile(conf_file, line="archive_mode = on", regexp=r"^#?archive_mode")
lineinfile(conf_file, line="archive_command = 'pgbackrest --stanza=main archive-push %p'",
regexp=r"^#?archive_command")
# Performance tuning
lineinfile(conf_file, line="shared_buffers = 256MB", regexp=r"^#?shared_buffers")
lineinfile(conf_file, line="effective_cache_size = 1GB", regexp=r"^#?effective_cache_size")
# Logging
lineinfile(conf_file, line="log_destination = 'stderr'", regexp=r"^#?log_destination")
lineinfile(conf_file, line="logging_collector = on", regexp=r"^#?logging_collector")
3. Authentication Configuration¶
/etc/postgresql/15/main/pg_hba.conf:
def configure_authentication():
hba_file = f"/etc/postgresql/{PG_VERSION}/main/pg_hba.conf"
# Allow local connections
lineinfile(hba_file, line=f"local {DB_NAME} {DB_USER} scram-sha-256",
regexp=rf"^local\s+{DB_NAME}")
# Allow network connections (adjust as needed)
lineinfile(hba_file, line=f"host {DB_NAME} {DB_USER} 10.0.0.0/8 scram-sha-256",
regexp=rf"^host\s+{DB_NAME}")
4. Database and User¶
def create_database():
# Create user
run(f"sudo -u postgres psql -c \"CREATE USER {DB_USER} WITH PASSWORD '{DB_PASSWORD}'\"",
check=False) # May already exist
# Create database
run(f"sudo -u postgres psql -c \"CREATE DATABASE {DB_NAME} OWNER {DB_USER}\"",
check=False)
# Grant privileges
run(f"sudo -u postgres psql -c \"GRANT ALL PRIVILEGES ON DATABASE {DB_NAME} TO {DB_USER}\"")
5. pgBackRest Configuration¶
/etc/pgbackrest/pgbackrest.conf:
[global]
repo1-path=/var/backups/postgresql
repo1-retention-full=2
repo1-retention-diff=7
process-max=2
log-level-console=info
log-level-file=detail
[main]
pg1-path=/var/lib/postgresql/15/main
pg1-port=5432
6. Initialize Backup Repository¶
def initialize_backups():
# Create backup directory
mkdir(BACKUP_DIR, mode="0750", owner="postgres:postgres")
# Initialize pgBackRest stanza
run("sudo -u postgres pgbackrest --stanza=main stanza-create")
# Verify configuration
run("sudo -u postgres pgbackrest --stanza=main check")
7. Systemd Timer for Backups¶
/etc/systemd/system/pgbackrest-backup.service:
[Unit]
Description=pgBackRest Backup
[Service]
Type=oneshot
User=postgres
ExecStart=/usr/bin/pgbackrest --stanza=main --type=diff backup
/etc/systemd/system/pgbackrest-backup.timer:
[Unit]
Description=Daily PostgreSQL Backup
[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true
[Install]
WantedBy=timers.target
Weekly full backup timer:
[Unit]
Description=Weekly Full PostgreSQL Backup
[Timer]
OnCalendar=Sun *-*-* 03:00:00
Persistent=true
[Install]
WantedBy=timers.target
Key Code Sections¶
Backup Schedule¶
def setup_backup_schedule():
# Daily differential backup service
diff_service = """[Unit]
Description=pgBackRest Differential Backup
[Service]
Type=oneshot
User=postgres
ExecStart=/usr/bin/pgbackrest --stanza=main --type=diff backup
"""
file("/etc/systemd/system/pgbackrest-diff.service", diff_service)
# Daily timer
diff_timer = """[Unit]
Description=Daily PostgreSQL Backup
[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true
[Install]
WantedBy=timers.target
"""
file("/etc/systemd/system/pgbackrest-diff.timer", diff_timer)
# Weekly full backup
full_service = """[Unit]
Description=pgBackRest Full Backup
[Service]
Type=oneshot
User=postgres
ExecStart=/usr/bin/pgbackrest --stanza=main --type=full backup
"""
file("/etc/systemd/system/pgbackrest-full.service", full_service)
full_timer = """[Unit]
Description=Weekly Full PostgreSQL Backup
[Timer]
OnCalendar=Sun *-*-* 03:00:00
Persistent=true
[Install]
WantedBy=timers.target
"""
file("/etc/systemd/system/pgbackrest-full.timer", full_timer)
# Enable timers
run("systemctl daemon-reload", sudo=True)
run("systemctl enable pgbackrest-diff.timer pgbackrest-full.timer", sudo=True)
run("systemctl start pgbackrest-diff.timer pgbackrest-full.timer", sudo=True)
Backup Operations¶
Manual Backup¶
# Full backup
sudo -u postgres pgbackrest --stanza=main --type=full backup
# Differential backup
sudo -u postgres pgbackrest --stanza=main --type=diff backup
# Incremental backup
sudo -u postgres pgbackrest --stanza=main --type=incr backup
List Backups¶
Restore¶
# Stop PostgreSQL
sudo systemctl stop postgresql
# Restore latest backup
sudo -u postgres pgbackrest --stanza=main restore
# Or restore to specific time
sudo -u postgres pgbackrest --stanza=main --type=time \
--target="2024-01-15 10:00:00" restore
# Start PostgreSQL
sudo systemctl start postgresql