PG Backup & Restore

    Failures can be divided into two categories: hardware/resource failures and software/human errors.

    Replication aims for the former, while delayed replica & cold backups aim for the latter.

    Pigsty has complete support for backups. There is battery-included physical replication that can be configured by simply declaring replica & delayed roles. And handy cold backup scripts for cold backup.

    In Pigsty, physical backups are created by specifying roles () for the database instances. For example, the following configuration declares a HA database cluster with one primary & two replicas.

    It is consistent with the primary and will take over the work of the primary when it fails, and will also take over online read-only traffic. A hot standby that uses sync replication to keep up with the primary in real-time can also be called a sync backup. Under normal circumstances, the latency of physical replication can be in the range of 1ms-100ms / tens of KB to several MB, depending on the network conditions and load level.

    Please refer to Classic Physical Replication.

    Please refer to offline deployment.

    Use sync commit replica, also called sync standby. Please refer to sync standby deployment for details.

    Delayed is a quick measure of software failure/human error. Changes are received in real-time from the primary using the standard primary-replica stream replication mechanism but are delayed for a specific period (e.g., one hour, a day) before the application is executed. Thus, it is a copy of the historical state of the original primary. When there is a problem like mistaken data deletion, the delay provides a time window to salvage: immediately query the data from the delayed and backfill the original primary.

    A delayed replica can be created using the function standby cluster. For example, now you want to specify a delayed replica for the pg-test cluster: pg-testdelay, which is the state of pg-test 1 hour ago. If there is a mis-deletion of data, it can be immediately retrieved from the delayed and poured back into the original cluster.

    After creation, edit the Patroni config file for the delayed cluster using in the meta node and change standby_cluster.recovery_min_apply_delay to the delay value you expect.

    Pigsty provides a script for making cold backups pg-backup, which can be executed as dbsu on the database node to create a full physical backup of the current instance and place it in the /pg/backup (by default located in {{ pg_fs_bkup }}/backup).

    With parameters, you can specify the backup database URL, backup-dir, file name, encryption method, retention policy for existing backups, etc.

    This script will use pg_basebackup to initiate a backup from the specified PGURL (default is the local database instance), using a archive with lz4 compression and optional openssl RC4 stream encryption.

    The backup file is placed in the /pg/backup/ dir by default, and the default file name consists of a prefix, cluster name, and date, e.g., backup_pg-meta_20210805.tar.lz4.

    The default backup cleanup policy is to clean up old backup files 1200 minutes (20 hours old) when the latest backup completes.

    To use this backup, you need to set the cluster to maintenance mode (pt pause), stop the data cluster primary, and empty the dataset cluster dir. Then the backup file is unpacked to /pg/data.

    There are other handy tools can be used for manage backups: pg_backrest & pg_probackup.