Logical and physical backups are the two forms of online database backups available in PostgreSQL. Physical backups require taking a picture of the physical files that make up the database, whereas logical backups entail producing a backup by exporting the logical structure and data of the database.
Logical backups can be time-consuming, especially for large databases, despite having benefits such offering a flexible way to extract and edit data. On the other hand, huge databases can be backed up and restored more quickly and effectively with physical backups.
Base Backup: A base backup is the technical term for the creation of a physical backup in PostgreSQL. The steps below make up the normal process for making a basic backup:
Use the pg_backup_start command (or, in previous versions, pg_start_backup).
Making use of the preferred archiving command, take a snapshot of the database cluster.
Use the pg_backup_stop command (or, in earlier versions, pg_stop_backup).
The pg_backup_start command switches to the current Write-Ahead Log (WAL) segment file and creates a checkpoint to prepare the database for the backup procedure. Additionally, it generates a backup_label file that includes crucial details about the base backup, like the location of the checkpoint.
By switching the WAL segment file, writing a backup end XLOG record, resetting the full-page write mode, and generating a backup history file, the pg_backup_stop command ends the backup operation. The contents of the backup_label file and extra data are contained in the backup history file.
With the help of a base backup and the archive logs produced by continuous archiving, you can restore a database cluster to any point in time using the PostgreSQL functionality known as Point-in-Time Recovery (PITR). This capability can be used to fix serious errors or return the database to a specific state.
PostgreSQL determines the beginning point for the recovery procedure by reading the checkpoint location from the backup_label file. It reads the archive logs from the archival path supplied in the archive_command option and replays the WAL data from those logs.
A timeline history file is created in the pg_xlog (or pg_wal in version 10 or later) subfolder once the recovery operation is finished. The LSN (Log Sequence Number) where the WAL segment switches occurred, the timeline change's cause, and the timelineId of the restored database cluster are all recorded in this file. The timeline history file, which offers a history of recovered clusters, is crucial for later PITR procedures.
Top comments (0)