POSTGRESQL - BACKUPS : TYPES, LEVELS IN POSTGRES

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.


    (Logical and Physical Backups)