How to Set Up a Simple PostgreSQL Backup

PostgreSQL is the most robust and reliable open-source relational database system in use today, powering development and production environments worldwide. Managing databases for web applications, internal tools, or infrastructure services requires setting up an efficient and consistent backup strategy. A well-planned backup is essential for avoiding data loss and ensuring operational continuity in the event of failure, accidental deletion, or system corruption.

This article gives you the practical and in-depth knowledge you need to set up a simple PostgreSQL backup system. It covers multiple backup methods, configuration tips, automation through scripts, and safe backup storage — including the use of rsync for remote synchronization.

Understanding PostgreSQL Backup Approaches

PostgreSQL provides both logical and physical backup options, suitable for different purposes:

    - Logical Backup with pg_dump
    - Physical Backup with pg_basebackup
    - Point-in-Time Recovery using WAL archiving

For a straightforward setup, pg_dump and pg_basebackup are often sufficient. These tools are available by default in most PostgreSQL installations and can be used with minimal configuration.

Logical Backup Using pg_dump

The pg_dump tool creates a logical backup of your database. It extracts the database schema and data into a file that can later be used for restoration.

Example Command:

    pg_dump -U postgres -d your_database -F c -f /backups/your_database.backup

Explanation:

    -U postgres: database user
    -d your_database: name of the database to back up
    -F c: custom format (recommended for flexibility and compression)
    -f: output file path

Logical backups are portable, smaller in size due to compression, and can be selectively restored (e.g., a specific table). However, they take longer to restore than physical backups, especially for large databases.

Physical Backup Using pg_basebackup

pg_basebackup creates a binary-level copy of the entire PostgreSQL data directory, preserving all configurations, indexes, and WAL logs necessary for full restoration.

Example Command:

    pg_basebackup -U replication_user -D /var/lib/postgresql/backup/ -Ft -z -P

To use this method, enable replication access by updating pg_hba.conf:

    host replication replication_user 127.0.0.1/32 md5

And configure postgresql.conf:

    wal_level = replica
    max_wal_senders = 3

Restart PostgreSQL after applying these settings.

Note: This method is particularly suited for high-availability setups or when creating replicas, but it can also be used for simple full backups.

Automating PostgreSQL Backups

Manual backups can be error-prone and easily forgotten. A simple shell script combined with cron can automate daily backups efficiently.

Example Script (pg_backup.sh):

    #!/bin/bash
    DATE=$(date +%F)
    BACKUP_DIR="/var/backups/postgresql"
    DB_NAME="your_database"
    PG_USER="postgres"

    mkdir -p "$BACKUP_DIR"
    pg_dump -U "$PG_USER" -F c "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_$DATE.backup"
    find "$BACKUP_DIR" -type f -name "*.backup" -mtime +7 -exec rm -f {} \;

Make the script executable:

    chmod +x /usr/local/bin/pg_backup.sh

Schedule it via crontab:

    0 2 * * * /usr/local/bin/pg_backup.sh

This example creates a new backup daily at 2:00 AM and deletes any backups older than 7 days.

Other useful articles in our Blog:

    - A Comprehensive Guide: How to Find and Optimize Slow Queries in MySQL

    - Creating a New User and Granting Permissions in MySQL

    - Adding a new user to PostgreSQL

    - How to Choose the Right Server Configuration for Your Needs

Transferring Backups Using rsync

To ensure redundancy and data availability, backups should be copied to a different physical or virtual machine. One of the simplest and most reliable tools for this is rsync.

Example rsync Command:

    rsync -avz /var/backups/postgresql/ user@192.168.1.100:/mnt/backup-node/postgresql/

This command synchronizes the backup directory to a remote host over SSH. The -z flag compresses data during transfer, and -a preserves file attributes. You can also integrate rsync into your backup script:

    rsync -az "$BACKUP_DIR/" user@backupserver:/remote/backup/postgresql/

To make this more secure and automated, set up SSH key-based authentication between the servers.

Using rsync ensures fast, incremental transfers and reduces storage requirements on the destination by avoiding redundant copies.

Verifying and Restoring Backups

A backup strategy is only reliable if it can be tested and restored successfully.

Test Restore Command:

    createdb test_restore
    pg_restore -U postgres -d test_restore /var/backups/postgresql/your_database_2025-05-11.backup

This command creates a test database and restores the backup to verify integrity. Make this part of a monthly maintenance plan to catch issues early.

Advanced Option: Point-in-Time Recovery (PITR)

For scenarios where you need to recover data to a specific point (e.g., just before accidental deletion), enable WAL archiving:

In postgresql.conf:

    archive_mode = on
    archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Then, make periodic base backups with pg_basebackup and retain the corresponding WAL files.

Recovery is done by restoring the base backup and replaying the WAL logs up to the desired moment. While more complex, this method offers powerful recovery capabilities.

Backup Best Practices

    Automate everything: Use cron for backups and rsync for remote copies.
    Verify regularly: Test restore procedures monthly.
    Limit retention: Keep backups only as long as needed to save disk space.
    Separate storage: Use a different server, external drive, or mounted NAS for redundancy.
    Document recovery steps: Ensure that the recovery process is written and known by your team.

Conclusion

Setting up a simple PostgreSQL backup solution requires no complex infrastructure or external services. Native tools like pg_dump, pg_basebackup, and rsync provide a reliable and efficient backup workflow. You must start with a simple automated backup to achieve daily data protection, long-term archiving, or disaster recovery.

It is essential to plan for failures before they happen. You must automate, verify, and store your PostgreSQL backups wisely. Your data depends on it.