PostgreSQL Backups and Restore Techniques

Backing up your PostgreSQL database is critical for data protection, disaster recovery, and migration. PostgreSQL provides multiple methods to backup and restore databases, ranging from simple SQL dumps to continuous archiving.

1. Logical Backups with pg_dump

pg_dump creates a logical backup of a database by generating SQL statements to recreate the database objects and insert data.

-- Backup entire database to a SQL file
            pg_dump -U username -F c mydatabase > mydatabase_backup.sql

            -- Backup only schema
            pg_dump -U username -s mydatabase > schema_backup.sql

            -- Backup only data
            pg_dump -U username -a mydatabase > data_backup.sql

2. Restoring from pg_dump

-- Restore full database
            psql -U username -d mydatabase -f mydatabase_backup.sql

            -- Restore with pg_restore for custom format backups
            pg_restore -U username -d mydatabase mydatabase_backup.sql

3. Backup with pg_dumpall

pg_dumpall is used to backup all databases in a cluster including roles and tablespaces.

-- Backup all databases
            pg_dumpall -U username > all_databases_backup.sql

4. Physical Backups with pg_basebackup

Physical backups copy the actual database files at the storage level. This method is used for large databases or streaming replication setups.

-- Backup the entire database cluster
            pg_basebackup -D /path/to/backup -F tar -z -P -U replication_user

5. Continuous Archiving / Point-in-Time Recovery (PITR)

PostgreSQL supports continuous archiving using WAL (Write-Ahead Logging) files to restore a database to a specific point in time.

-- Enable WAL archiving in postgresql.conf
            wal_level = replica
            archive_mode = on
            archive_command = 'cp %p /path/to/archive/%f'

6. Best Practices for Backups

• Regularly schedule backups to prevent data loss.
• Store backups in multiple locations (local and cloud storage).
• Test restore procedures to ensure backup reliability.
• Use a combination of logical and physical backups for large production databases.
• Secure backup files with encryption and restricted access.

7. Automating Backups

Use cron jobs or scripts to automate backup creation and rotation. Example:

#!/bin/bash
            DATE=$(date +%F)
            BACKUP_DIR="/backups/postgresql"
            mkdir -p $BACKUP_DIR
            pg_dump -U username -F c mydatabase > $BACKUP_DIR/mydatabase_$DATE.sql
            find $BACKUP_DIR -type f -mtime +7 -exec rm {} \;  # Remove backups older than 7 days

Conclusion

PostgreSQL offers flexible backup and restore options including logical backups with pg_dump, cluster backups with pg_basebackup, and point-in-time recovery using WAL archiving. Following best practices for backup scheduling, storage, and testing ensures data safety and helps in disaster recovery scenarios. In the next tutorial, we will explore PostgreSQL Performance Tuning and Optimization for efficient and scalable database operations.