Day 11: Backup and Restore

In Day 11 of our PostgreSQL learning series, we’ll delve into backup and restore operations in PostgreSQL. Backing up your database is essential to protect your data against loss due to hardware failures, human errors, or other unforeseen circumstances. We’ll explore various backup and restore methods, along with detailed commands and examples for each scenario.

1. SQL Dump Backup and Restore

SQL dump backups involve exporting the database schema and data into a SQL file, which can be used to recreate the database.

Backup:

To perform an SQL dump backup, you can use the pg_dump utility:

pg_dump -U username -d dbname -f backup.sql

Replace username with your PostgreSQL username, dbname with the name of the database you want to backup, and backup.sql with the filename for the backup SQL file.

Example:

pg_dump -U myuser -d mydatabase -f backup.sql

Restore:

To restore a database from an SQL dump backup, you can use the psql utility:

psql -U username -d dbname -f backup.sql

Replace username with your PostgreSQL username, dbname with the name of the database you want to restore into, and backup.sql with the filename of the backup SQL file.

Example:

psql -U myuser -d mydatabase -f backup.sql

Here are some additional scenarios for using SQL dump backups:

1. Backup Compression:

You can compress the SQL dump backup to save disk space and reduce backup file size. You can use tools like gzip or bzip2 for compression.

pg_dump -U username -d dbname | gzip > backup.sql.gz

To restore a compressed backup:

gunzip -c backup.sql.gz | psql -U username -d dbname

2. Customizing Dump Output:

You can customize the dump output using various options provided by pg_dump. For example, you can include only schema or data, exclude specific tables, or include additional options like --inserts to generate INSERT statements instead of COPY.

pg_dump -U username -d dbname -s -t table1 -t table2 > schema_backup.sql

3. Incremental Backup:

You can perform incremental backups by dumping only the changes since the last backup using the --data-only and --schema-only options along with a timestamp-based naming convention.

pg_dump -U username -d dbname --data-only --file=data_backup_$(date +%Y%m%d).sql

4. Periodic Automated Backup:

You can set up a cron job or scheduled task to perform regular automated backups at specific intervals.

0 0 * * * pg_dump -U username -d dbname -Ft > /path/to/backup/db_backup_$(date +\%Y\%m\%d_\%H\%M\%S).tar

This example schedules a backup to run every day at midnight.

5. Remote Backup:

You can directly dump the backup to a remote server using SSH or similar methods.

pg_dump -U username -d dbname | ssh user@remote_host "cat > /path/to/remote/backup.sql"

These scenarios provide additional flexibility and options for using SQL dump backups effectively in PostgreSQL backup strategies.

2. Continuous Archiving and Point-in-Time Recovery (PITR)

Continuous archiving and PITR provide the ability to restore a database to a specific point in time, useful for recovering from catastrophic failures or human errors.

Backup:

To enable continuous archiving, you need to configure archive_mode and archive_command in the PostgreSQL configuration file (postgresql.conf), along with setting up a WAL archive location.

archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

Restore:

To perform point-in-time recovery, you need to restore the base backup using pg_basebackup and apply the archived WAL (Write-Ahead Log) files using pg_wal_replay.

pg_basebackup -U username -D /path/to/data/directory -Ft -Xs -P

pg_wal_replay -D /path/to/data/directory --recovery-target-time 'YYYY-MM-DD HH:MI:SS'

3. Physical Backup and Restore

Physical backups involve copying the PostgreSQL data directory to create a backup.

Backup:

To perform a physical backup, you can use tools like pg_basebackup or filesystem-level backup tools.

pg_basebackup -U username -D /path/to/backup/directory -Ft -Xs -P

Restore:

To restore a physical backup, simply copy the backup data directory to the appropriate location and start the PostgreSQL server.

cp -r /path/to/backup/directory /var/lib/postgresql/data

Summary:

• SQL dump backups provide a portable and human-readable backup format.
• Continuous archiving and PITR enable restoring databases to specific points in time.
• Physical backups offer a direct copy of the PostgreSQL data directory for fast restoration.

Implementing backup and restore strategies is crucial for maintaining data availability and integrity in PostgreSQL. Stay tuned for more PostgreSQL learning!