Backup and DR Service for PostgreSQL

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

PostgreSQL backup API used by Backup and DR

  • Volume level (Linux change block tracking and LVM snapshot) backups: Backup and DR uses the PostgreSQL pg_start_backup(label) and pg_stop_backup() commands when performing backups with Linux change block tracking and LVM snapshot.

  • Full+Incremental (file-based traditional) backups: Backup and DR uses the PostgreSQL pg_dump file-based backups commands. The pg_dump command doesn't have an option to capture incremental changes of a database, so each backup is a full database dump backup.

    Restore of the database recovers the database by physically overwriting the data area.

  • PostgreSQL log backup: During a log backup, Backup and DR physically copies all the PostgreSQL WALs (WriteAheadLog). To purge logs, Backup and DR uses an OS-level command.

How it works: Backup and DR volume-based backup with Linux CBT

Only changed blocks are tracked in the bitmap: no copy-on-writes, no I/O-intensive operations.

The following steps describe the process for data backup and recovery:

  1. The Backup and DR agent has CBT to track changed blocks in the database data area.

  2. The agent calls the database API to freeze or pause database for data backup.

  3. Agent creates LVM snapshot of database data area and synthesizes a bitmap.

  4. Agent call to database API to unfreeze database.

  5. Agent copies changed blocks to backup/recovery appliance, which then deletes the snapshot and catalogs the backup.

  6. The appliance issues an internal snapshot and synthesizes a point-in-time virtual full backup.

  7. For data recovery, Backup and DR instantly mounts a rewritable staging disk and brings the database online.

How it works: PostgreSQL with Linux change block tracking and LVM snapshot.

The quiesce or unquiesce command is run on the primary for a volume-level snapshot job:

select pg_start_backup(<label>);
select pg_stop_backup();

The quiesce and unquiesce commands are run on a standby node for a volume-level snapshot job:

If PostgreSQL version is 10.x or later:

select pg_wal_replay_pause();
select pg_wal_replay_resume();

If PostgreSQL version is 9.x:

select pg_xlog_replay_pause();
select pg_xlog_replay_resume();

How it works: traditional file-based backup

The following steps describe the process for how to perform data backup and recovery with traditional file-based backup images:

  1. Backup and DR agent is deployed in the database server.

  2. Mount staging disk on the database server.

  3. Invoke full backup using traditional dump backup command, writing the backup to the mounted disk.

  4. Backup and DR takes an internal snapshot. Log backups are done in a similar fashion directly from the file-system at any schedule that you configure.

  5. For data recovery, Backup and DR instantly mounts the staging disk to the database server and initiates the database restore operation. Logs can be played to any point in time after the database is restored.

How it works: PostgreSQL with file-based traditional backup.

(The pg_dump API doesn't have an option to capture incremental changes of a database, so each backup is a full database dump backup.)

What's next

Prepare the database for Backup and DR

Other documentation for Backup and DR for PostgreSQL