SoFunction
Updated on 2025-03-04

Four ways to backup and restore PostgreSQL database

In the era when data is king, the information stored in the database is the lifeline of the enterprise. As a widely used open source database, PostgreSQL is a necessary skill for every developer and operation staff. Today, we will explore PostgreSQL-related backup and recovery strategies in depth and attach rich code examples.

1. pg_dump: a tool for logical backup

pg_dumpIt is a tool that comes with PostgreSQL for generating logical backups of databases. It can export the database structure and data in the form of SQL scripts, making it easy to rebuild in other environments.

  1. Backup a single databaseSuppose we have a nametest_dbTo complete backup of the database, the operation is as follows:
pg_dump -U your_username -Fc test_db > test_db.dump

here,-USpecify the username,-FcIt means exporting in a custom compressed binary format, which is faster to backup and the generated file size is smaller. Finally redirect the backup content totest_db.dumpin the file.

  1. Backup the specified tableIf you only want to back up some tables in the database, for exampletest_dbIn-houseusersTable andorderssurface:
pg_dump -U your_username -t users -t orders -Fc test_db > partial_dump.dump

-tThe parameter is used to specify the table name that needs to be backed up. You can use this parameter multiple times to add multiple target tables.

2. pg_restore: Recovery operation of logical backup

With backup files,pg_restoreIt can bring data back to life.

  1. Recover the complete databaseFirst create an empty target database, assuming it is callednew_test_db, and then perform recovery:
createdb -U your_username new_test_db
pg_restore -U your_username -d new_test_db test_db.dump

createdbUsed to create a new database,-dParameters are inpg_restoreIndicates the target database, according to the backup filetest_db.dumpInstructions in, fill data and structure into newnew_test_dbdatabase.

  1. Recover some tablesIf you want to restore only some tables in the previous backup, for example, only restoreuserssurface:
pg_restore -U your_username -d new_test_db -t users test_db.dump

-tThe meaning of the parameters is consistent with the backup time, and the tables that need to be restored are accurately positioned.

3. pg_basebackup: the implementation of physical backup

Physical backups will directly copy database files, which is faster than logical backups and is suitable for large databases.

  1. Basic physical backupExecute the following command as a superuser:
pg_basebackup -D /path/to/backup -U your_superuser -P

-DSpecify the directory where backup files are stored.-UIt is the super user name,-PThe backup progress will be displayed, allowing us to know the backup status in real time. However, it is important to note that the target directory must be empty, and the database read and write operations should be minimized during the backup to avoid data inconsistencies.

  1. Incremental physical backupComplete a basic physical backup first, and subsequent incremental backups are done based on this basis. We need to record the WAL (Write-Ahead Logging) file location of the basic backup. In incremental backup:
pg_basebackup -D /path/to/incremental_backup -U your_superuser -P -X stream -C -R \
--checkpoint=fast --target-wal-segment-size=16MB \
--write-recovery-conf --recovery-target-time='2025-01-15 12:00:00'

-X streamTurn on stream mode to transmit WAL logs.-CIt means that the WAL segment that is no longer needed after the backup is completed.-RAutomatically generate recovery configuration files, and the following several parameters are used to finely adjust the backup checkpoint and target WAL segment size.--recovery-target-timeSet the time point to restore.

4. Recovery based on WAL logs

WAL logs are the key to PostgreSQL physical recovery. Even if the database fails, as long as the WAL log is complete, it can be restored to the pre-failed state.

Assuming that the database crashes, first copy the physical backup file to the appropriate location, and then use the WAL log to restore:

cp -R /path/to/backup /var/lib/postgresql/data
# Find the relevant WAL logs, assuming they are stored in /var/lib/postgresql/wal_logscp /var/lib/postgresql/wal_logs/*.log /var/lib/postgresql/data/pg_wal
# Start PostgreSQL service, it will automatically restore data based on WAL logssudo systemctl start postgresql

5. Summary

Mastering these backup and recovery methods is like putting a solid armor on PostgreSQL database. Whether it encounters accidental deletion, hardware failure or other data crises, you can calmly respond to it and protect the security and integrity of data assets.

This is the end of this article about four methods of PostgreSQL database backup and recovery. For more related PostgreSQL database backup and recovery content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!