Zero-Downtime PostgreSQL Backups on Ubuntu

Contents

• The reality of database disaster in vibe coding
• How PostgreSQL executes non-blocking backups
• Backup strategies: logical versus physical
• Automated backup pipeline architecture
• Writing the production backup script
• Automating execution with systemd timers
• Offsite replication and retention pruning
• Automated restore drills in isolated sandboxes
• FAQ

The reality of database disaster in vibe coding

When developers build with autonomous agents and rapid prototyping frameworks, databases often start as unmanaged instances on a single Ubuntu virtual server or inside a Docker container. In the early stages of a project, schema migrations run rapidly, agent processes execute hundreds of automated writes, and application state accumulates quickly.

Eventually, an inevitable failure occurs:

• An autonomous coding agent executes a destructive migration or deletes a production table.
• A disk volume runs out of inodes or storage space, corrupting active data pages.
• A developer runs an accidental docker compose down -v, wiping local persistent volumes.
• The underlying VPS provider experiences hardware failure or network isolation.

In our guide on VPS and infrastructure basics, we emphasized that production reliability is defined by recovery time, not optimistic uptime assumptions. If recovering from a database disaster requires taking down your web services for hours or piecing together fragmented CSV exports, your architecture is brittle.

Achieving production durability requires an automated, zero-downtime backup pipeline. Your services must continue serving traffic and accepting writes while consistent point-in-time snapshots are generated, encrypted, compressed, and mirrored offsite to independent object storage.

How PostgreSQL executes non-blocking backups

Many developers assume that taking a database backup requires pausing writes or shutting down the database process. In PostgreSQL, this assumption is incorrect due to Multi-Version Concurrency Control (MVCC).

When pg_dump connects to a PostgreSQL instance, it begins a transaction with REPEATABLE READ transaction isolation. Under MVCC, PostgreSQL maintains multiple physical versions of table rows. When active transactions update or insert rows while pg_dump is running, the database engine creates new row versions without overwriting the data visible to the backup transaction.

flowchart TD
    A["Active Application Traffic"] -->|"Concurrent INSERT, UPDATE, DELETE"| B["PostgreSQL Database Engine (MVCC)"]
    C["Automated Backup Process"] -->|"pg_dump -Fc (REPEATABLE READ)"| B
    B -->|"Unblocked Queries"| A
    B -->|"Consistent Snapshot Stream"| D["Compressed Archive (.dump)"]
    style A fill:#2563eb,stroke:#1d4ed8,color:#ffffff
    style C fill:#059669,stroke:#047857,color:#ffffff
    style D fill:#7c3aed,stroke:#6d28d9,color:#ffffff

Because pg_dump only requires an ACCESS SHARE lock on tables, it allows concurrent SELECT, INSERT, UPDATE, and DELETE statements to execute freely. The only operations that conflict with ACCESS SHARE locks are exclusive data definition language (DDL) operations, such as ALTER TABLE, DROP TABLE, or VACUUM FULL.

As a result, you can execute automated snapshots against production databases under active load with zero user-facing downtime and zero read or write interruptions.

Backup strategies: logical versus physical

Selecting the correct backup approach depends on database size, write throughput, and recovery time objectives:

For most vibe-coded applications, indie platforms, and small engineering teams running on Ubuntu VPS servers, logical backups using PostgreSQL custom format (-Fc) represent the optimal balance of simplicity, non-blocking execution, and rapid recoverability.

The custom format produces a compressed binary archive that allows selective restoration of individual tables, supports parallel table restoration via pg_restore -j, and embeds table schemas and indexes in an optimized format.

Automated backup pipeline architecture

A production-grade backup pipeline must operate autonomously without manual intervention. It consists of four distinct operational stages:

flowchart LR
    A["1. Snapshot Engine"] -->|"pg_dump -Fc"| B["2. Local Retention Vault"]
    B -->|"Prune Backups Older Than 7 Days"| B
    B -->|"Encrypted TLS Sync"| C["3. Offsite Object Storage"]
    C -->|"Mirror to MinIO / S3"| C
    B -->|"Daily Verification Drill"| D["4. Sandboxed Restore Test"]
    D -->|"Pass / Alert"| E["Operational Telemetry"]
    style A fill:#0284c7,stroke:#0369a1,color:#ffffff
    style B fill:#0d9488,stroke:#0f766e,color:#ffffff
    style C fill:#6366f1,stroke:#4f46e5,color:#ffffff
    style D fill:#e11d48,stroke:#be123c,color:#ffffff

1. Snapshot Creation: An automated script connects via local socket or protected network address, executing a consistent non-blocking dump.
2. Local Archival and Pruning: The snapshot is written to a designated local backup directory with timestamped filenames and SHA-256 checksums. Obsolete local backups are automatically rotated and pruned based on disk limits.
3. Offsite Object Storage Mirroring: The verified snapshot is immediately mirrored over encrypted TLS to an independent object storage endpoint (such as MinIO or AWS S3), isolating backups from single-server failure.
4. Restore Integrity Testing: A scheduled test container restores the latest archive into a temporary database to verify schema consistency and record counts.

Writing the production backup script

To implement this workflow on an Ubuntu VPS, create a dedicated automation script. This script handles database authentication securely without hardcoding credentials in world-readable files.

Following the principles detailed in our guide on secrets, API keys, and rate limits, store connection credentials in a protected PostgreSQL password file (~/.pgpass) restricted to user-only read permissions (chmod 600).

Create the backup directory structure:

sudo mkdir -p /opt/backups/postgressudo mkdir -p /opt/backups/scriptssudo chown -R ubuntu:ubuntu /opt/backupschmod 700 /opt/backups/postgres

Now create the production backup script at /opt/backups/scripts/backup_postgres.sh:

#!/usr/bin/env bash# Zero-Downtime PostgreSQL Automated Backup Script# Targets local and Docker-hosted PostgreSQL instances on Ubuntuset -euo pipefail# Configuration parametersDB_HOST="${DB_HOST:-127.0.0.1}"DB_PORT="${DB_PORT:-5432}"DB_USER="${DB_USER:-postgres}"DB_NAME="${DB_NAME:-production_db}"BACKUP_DIR="${BACKUP_DIR:-/opt/backups/postgres}"RETENTION_DAYS="${RETENTION_DAYS:-7}"TIMESTAMP="$(date -u +"%Y%m%d_%H%M%SZ")"BACKUP_FILENAME="${DB_NAME}_${TIMESTAMP}.dump"BACKUP_FILEPATH="${BACKUP_DIR}/${BACKUP_FILENAME}"CHECKSUM_FILEPATH="${BACKUP_FILEPATH}.sha256"# Ensure output directory existsmkdir -p "${BACKUP_DIR}"echo "[$(date -u)] Starting zero-downtime backup for database: ${DB_NAME}"# Execute non-blocking custom-format backup# -Fc: Custom binary format (compressed, reorderable, parallel-restorable)# -v: Verbose output for logging# --no-owner: Do not record database object ownership (eases cross-environment restore)# --no-acl: Do not record table privileges/grantspg_dump \  -h "${DB_HOST}" \  -p "${DB_PORT}" \  -U "${DB_USER}" \  -d "${DB_NAME}" \  -Fc \  --no-owner \  --no-acl \  -f "${BACKUP_FILEPATH}"# Generate SHA-256 integrity checksumsha256sum "${BACKUP_FILEPATH}" > "${CHECKSUM_FILEPATH}"BACKUP_SIZE="$(du -h "${BACKUP_FILEPATH}" | cut -f1)"echo "[$(date -u)] Backup completed successfully: ${BACKUP_FILENAME} (${BACKUP_SIZE})"# Prune local backups older than retention windowecho "[$(date -u)] Pruning local backups older than ${RETENTION_DAYS} days..."find "${BACKUP_DIR}" -type f -name "${DB_NAME}_*.dump" -mtime +"${RETENTION_DAYS}" -deletefind "${BACKUP_DIR}" -type f -name "${DB_NAME}_*.dump.sha256" -mtime +"${RETENTION_DAYS}" -deleteecho "[$(date -u)] Local retention pruning completed."

Make the script executable:

chmod +x /opt/backups/scripts/backup_postgres.sh

Automating execution with systemd timers

While traditional cron jobs are common, Ubuntu systems benefit substantially from systemd timers. Systemd service units provide structured execution logging via journalctl, precise environment sandboxing, dependency management, and deterministic failure restarts.

Create a systemd service unit at /etc/systemd/system/postgres-backup.service:

[Unit]Description=Automated Zero-Downtime PostgreSQL Backup ServiceAfter=network.target postgresql.service[Service]Type=oneshotUser=ubuntuGroup=ubuntuWorkingDirectory=/opt/backupsEnvironment="DB_HOST=127.0.0.1"Environment="DB_PORT=5432"Environment="DB_USER=postgres"Environment="DB_NAME=production_db"Environment="PGPASSFILE=/home/ubuntu/.pgpass"ExecStart=/opt/backups/scripts/backup_postgres.shStandardOutput=journalStandardError=journal# Security sandbox directivesNoNewPrivileges=trueProtectSystem=strictReadWritePaths=/opt/backups/postgresProtectHome=read-onlyPrivateTmp=true

Create the companion timer at /etc/systemd/system/postgres-backup.timer to run the backup every 6 hours, randomized over a 15-minute window to avoid load spikes:

[Unit]Description=Timer for Automated PostgreSQL Backups[Timer]OnCalendar=*-*-* 00,06,12,18:00:00RandomizedDelaySec=900Persistent=true[Install]WantedBy=timers.target

Enable and start the timer:

sudo systemctl daemon-reloadsudo systemctl enable --now postgres-backup.timer

Verify timer status:

systemctl list-timers postgres-backup.timer

Offsite replication and retention pruning

Keeping backups exclusively on the local VPS disk provides zero protection if the server disk corrupts or the VPS host becomes unavailable. In our architecture for zero-public-port production behind Tailscale, we demonstrated how private overlay networks allow secure machine-to-machine communication without public port exposure.

Use the MinIO client (mc) or AWS CLI over private network connections to mirror backups to an offsite S3-compatible bucket.

Create an offsite sync script at /opt/backups/scripts/sync_offsite.sh:

#!/usr/bin/env bash# Mirrors local PostgreSQL backups to offsite S3/MinIO bucketset -euo pipefailBACKUP_DIR="/opt/backups/postgres"REMOTE_TARGET="minio/database-backups/production-postgres"RETENTION_DAYS_REMOTE=30echo "[$(date -u)] Mirroring local archives to offsite storage: ${REMOTE_TARGET}"# Synchronize local directory with remote bucketmc mirror --overwrite "${BACKUP_DIR}" "${REMOTE_TARGET}"# Prune remote archives older than 30 daysmc rm --recursive --force --older-than "${RETENTION_DAYS_REMOTE}d" "${REMOTE_TARGET}"echo "[$(date -u)] Offsite mirroring and remote pruning completed."

Append the execution of sync_offsite.sh to the main backup script or trigger it as a dependent systemd service unit. This guarantees that every local snapshot is replicated offsite within minutes of completion.

Automated restore drills in isolated sandboxes

The most common operational mistake in infrastructure management is assuming that a backup is valid simply because the backup command exited with code zero. Backups can easily produce empty archives, miss table relations, or fail to restore due to missing extensions or version mismatches.

A backup pipeline is only proven when you can restore it into an isolated test environment and verify its data.

Using Docker on your Ubuntu host or a separate staging worker, you can execute an automated, non-destructive restore drill in less than 60 seconds:

#!/usr/bin/env bash# Automated Non-Destructive Restore Drillset -euo pipefailLATEST_DUMP="$(ls -t /opt/backups/postgres/*.dump | head -n 1)"TEMP_CONTAINER="postgres_restore_test_$$"echo "Verifying backup archive: ${LATEST_DUMP}"# 1. Verify SHA-256 checksumsha256sum -c "${LATEST_DUMP}.sha256"# 2. Spin up an ephemeral PostgreSQL containerdocker run -d --name "${TEMP_CONTAINER}" \  -e POSTGRES_PASSWORD=drill_test_pass \  postgres:16-alpine# Wait for database engine readinesssleep 5until docker exec "${TEMP_CONTAINER}" pg_isready -U postgres; do  sleep 2done# 3. Create target restore databasedocker exec "${TEMP_CONTAINER}" createdb -U postgres restore_verification# 4. Copy dump into container and execute pg_restoredocker cp "${LATEST_DUMP}" "${TEMP_CONTAINER}:/tmp/test.dump"docker exec "${TEMP_CONTAINER}" pg_restore \  -U postgres \  -d restore_verification \  --clean \  --if-exists \  --no-owner \  /tmp/test.dump# 5. Query table count to verify restore integrityTABLE_COUNT=$(docker exec "${TEMP_CONTAINER}" psql -U postgres -d restore_verification -t -c \  "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';")echo "Restore drill verified successfully. Restored public tables: ${TABLE_COUNT}"# 6. Teardown temporary containerdocker rm -f "${TEMP_CONTAINER}"

Running this verification drill weekly guarantees that your backup archives are syntactically valid, consistent, and ready for deployment if your primary production database ever fails.

Combined with self-hosting autonomous agents on Ubuntu, automated backups transform fragile developer prototypes into resilient production systems.

FAQ

Does pg_dump lock database tables during backup execution?

No. PostgreSQL uses Multi-Version Concurrency Control (MVCC). When pg_dump runs, it acquires an ACCESS SHARE lock on tables, which allows concurrent SELECT, INSERT, UPDATE, and DELETE queries to proceed without interruption. Only exclusive schema modification commands such as ALTER TABLE or DROP TABLE are blocked during the dump.

What is the difference between SQL text dumps and custom format (-Fc) dumps?

A plain text SQL dump (-Fp) generates raw SQL statements that must be executed sequentially through psql. The custom format (-Fc) generates a compressed binary archive. Custom format archives are significantly smaller, allow selective restoration of specific tables, and support multi-threaded parallel restoration using pg_restore -j.

How frequently should production PostgreSQL backups run?

For applications with moderate write volume, running full logical snapshots every 6 hours with a 7-day local retention window and a 30-day offsite retention window is standard practice. If your workload requires recovery point objectives (RPO) measured in minutes or seconds, implement Continuous Archiving with Write-Ahead Logging (WAL) using tools like pgBackRest or wal-g.

How do you verify database backup integrity without risking production data?

Never test restores against your live production database. Instead, execute automated restore drills inside temporary Docker containers or ephemeral staging environments. Spin up a clean PostgreSQL container matching your production version, restore the latest .dump file, run automated query assertions against record counts, and terminate the container.