Physical PostgreSQL Backup

June 18, 2021

Physical PostgreSQL Backup

Is file system-level copy of files that comprise the database cluster can be either Offline or Online mode.

Advantage :
1) Generally faster to backup and restore.
2) Allow Point-In-Time-Recovery PITR (If WAL are archived)
3) Full or Incremental backup.
4) Will allow DDL during backup.

Disadvantage :
1) Lack of flexibility:- Backup/Restore entire DB Cluster. Selective restoration options are not possible
2) Inconsistent backup.

Backup Methods:

Offline :– Shutdown the DB Cluster – Copy Datafiles – Startup DB Cluster.

Advantage:-
Any file copy tool can be used to copy datafiles
No need to copy associated WAL files
Disadvantage:–
Database unavailable during backup.
PITR is not possible.

Online :- Tell PostgreSQL that you are taking a backup – Copy the database files – Copy the Wal files – Tell PostgreSql you have finished the backup.

Advantage:-
DB available during backup
Disadvantage:-
WAL files must be backed up
WAL parameters must be set correctly

Continuous Archiving :- Extension of Online backup is Continuous archiving
All WAL files are backed up
Allow PITR

Storage Snapshots :- Is essentially an image of file system at given point of time
Consistent snapshot of the file system
WAL files must be backed up
Perform checkpoint just before the snapshot
Checkpoint of FS containing WAL and each tablespace must be simultaneous
Example LVM Snapshot

Tools (Utilities) for Physical Backup :

pg_basebackup:-
Use to take hot backup of postgresql cluster and its is not possible to take backup of the individual database or
database object.
Does not affect other clients to the DB.
Can be used for PITR.
Makes a binary copy of database cluster files.
Put a system in and out of backup mode.
Does not manage the backup library.
Backup is made over a regular PostgreSQL connection and uses the replication protocols.
The connection must be made with a user having replication permission.
The user must be granted explicit permissions in pg_hba.confg
Eg: pg_basebackup -h myserverdb -z -D /my/backup/location

pg_BackRest:-
Open Source tool.
Parallel Backup & Restore.
Local & Remote operation.
Full, Incremental & differential backup.
Backup rotation & archive expiration.
Delta restore.
Parallel, Async WAL push and get.
Tablespace & Link support.
Encryption.
Limit restore of a specific database.
Eg : pgbackrest stanza=<server name> backup
Eg : pgbackrest stanza=<server name> restore
Eg : pgbackrest stanza=<server name> info

BART (EDB Backup & Recovery Tool):-
Proprietary licensed product by EDB (EnterpriseDB)
Eg : bart backup -s <server name>
Eg : bart restore -s <server name>
Eg : bart show-backups -s <server name>

Barman (Backup & Recovery Manager):-
Open Source Tool.
Remote backup and restore of multiple Servers.
Backup catalogs.
Incremental backup.
Retention policies.
Archiving and compression of Wal files and backups.
rsync or postgresql protocols.
Eg : barman backup <server name>
Eg : barman restore <server name>
Eg : barman list-backup <server name>

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Recent articles

BACKUP, POSTGRESQL