Logical and Physical Backups

PostgreSQL allows backups to be grouped according to their level and kind. Depending on the needs for data recovery and storage efficiency, each fulfillparticular use cases.
A synopsis is provided below:
Backup Types
1.Logical Backup
  • Description: Stores information in a human-readable format, like SQL commands,at the database level.
  • Instruments:A single database or a subset of tables can be backed up using pg_dump.
  • pg_dumpall: Creates a backup of every database in a cluster, including tablespaceand roles.
  • Benefits: Transferable between various PostgreSQL architectures and versions. permits things to be restored selectively.
  • Cons: Takes longer for big databases.Unsuitable for PITR (point-in-time recovery).
  • Use Case: Backup small to medium-sized datasets or migrate databases.

2.Physical Backup
  • Description: Produces a binary copy of the files in the database cluster.
  • Resources:
  • pg_basebackup:A straightforward tool for creating physical backups. Manual file system-level backup (usually done with the use of the pg_start_backup/pg_stop_backup commands and write-ahead logging) necessitates that the database be in a consistent state.
  • Benefits: Quicker for big databases.supports WAL archiving in conjunction with point-in-time recovery (PITR).
  • Cons: Not compatible with other PostgreSQL architectures or versions. More storage space is needed.
  • Use Case: Replication configuration, high-volume databases, or PITR specifications.
Backup Levels
1. Full Backup
  • Description: An entire clone of the cluster or database, replete with all metadata and data.
  • Features: Self-sufficient and restores without the need for previous backups. time-consuming when dealing with big datasets.
  • Use Case: Initial backups for new systems or routine periodic backups.
2.Incremental Backup
    li>Description: Backs up only the modifications made since the last backup, which Is typically block-level.
  • programs: PostgreSQL does not handle this natively; third-party programs like pgBackRest or Barman are usually used.
  • Features: Needs a base complete backup in order to restore, along with maybe further incremental backups.
  • Use Case: Improving backup and storage times for big databases.
3.Differential Backup
  • Description:Backs up modifications made since the last complete backup.
  • Tools: Employed externally, these tools function similarly to incremental backups.
  • Characteristics: Larger in size but easier to restore than incremental back
  • Use Case: A balance for big systems between incremental and complete backups. Other Related Concepts:
  • WAL Archiving: Allows WAL files for PITR to be continuously archived. Set archive_mode = on and archive_command to configure.
  • Snapshot Backup: For quick backups, use file system or storage-level snapshots. For instance, ZFS and LVM snapshots.


(Backup Tools : pg_dump, pg_dumpall)