POSTGRESQL - ONLINE BACKUP AND OFFLINE BACKUP

Both online and offline backup techniques are supported by PostgreSQL, and each has specific requirements and use cases. This is a thorough comparison:
ONLINE BACKUP :
It is an online backup that is carried out while the database server is operational and available to users. The backup procedure does not interfere with regular database functions.

Important Features :
To guarantee consistency, archive mode and WAL archiving are necessary. frequently combined with third-party utilities like pgBackRest and Barman, file system snapshots, and programs like pg_basebackup.

Tools :
pg_basebackup : Makes creating a physical backup online easier. Use pg_start_backup and pg_stop_backup to create a custom file system backup. While the database is operating, logical backups (pg_dump, pg_dumpall) can also be made.

Benefits :
No downtime : Throughout the backup, the database is accessible. supports WAL archiving in conjunction with point-in-time recovery (PITR). Perfect for systems with high availability.
Cons : Extra setup is needed for consistency (e.g., turning on WAL archiving). increased chance of mistakes if improperly handled. Large datasets may cause the system to lag when using logical backups.
Use cases :
Includes production databases with unacceptable downtime. Systems that need PITR and ongoing data security.

« Previous Next Topic » (Logical Backups : Single Table, Multiple Tables, Single Database, Logical Backup Options, Usage)