Monday, June 24, 2024

EDB - pgBackRest - Part III: RESTORE

 Theory
  • The restore command automatically defaults to selecting the latest backup from the first repository where backups exist. The order in which the repositories are checked is dictated by the pgbackrest.conf (e.g. repo1 will be checked before repo2). To select from a specific repository, the --repo option can be passed (e.g. --repo=1). The --set option can be passed if a backup other than the latest is desired.
  • Restoration Scenarios:
    • Restoring the backup on a different host
    • Restoring a single database from the Postgres cluster
    • Restoring the backup to a specific point in time
    • Restoring only the delta changes
    • Restoring the backup on a different host to start as a streaming standby
1. Verify backup information
[enterprisedb@edb-nhatrang ~]$ pgbackrest info --stanza=employees

2. Restoring the backup on a different host (IP : 192.168.56.77)
  • Setup Passwordless SSH Connection between Backup Server (192.168.56.79) and Database Server (192.168.56.77)
    • Backup Server 
      • ssh-keygen -t rsa
      • ssh-copy-id enterprisedb@192.168.56.77
    • Database Server
      • ssh-keygen -t rsa
      • ssh-copy-id enterprisedb@192.168.56.79
  • Database Server - Setup pgBackRest Repository
        [root@edb-quynhon ~]# vi /etc/pgbackrest.conf 
        [global]
        repo1-host=192.168.56.79
        repo1-host-user=enterprisedb
        log-level-console=info
        log-level-file=debug

        [employees]
        pg1-path=/u01/edb/as16/data
        pg1-database=edb
        pg1-port=5444
        pg-version-force=16

2.1 Database Server - Restore the Lastest Backup 

[enterprisedb@edb-quynhon ~]$ pgbackrest --log-level-console=info --stanza=employees --process-max=2 restore
2024-06-25 15:47:07.509 P00   INFO: restore command begin 2.52: --exec-id=9675-76a8160f --log-level-console=info --log-level-file=debug --pg1-path=/u01/edb/as16/data --pg-version-force=16 --process-max=2 --repo1-host=192.168.56.79 --repo1-host-user=enterprisedb --stanza=employees
2024-06-25 15:47:08.481 P00   INFO: repo1: restore backup set 20240624-221733F_20240624-221842I, recovery will start at 2024-06-24 22:18:42
2024-06-25 15:47:17.598 P00   INFO: write updated /u01/edb/as16/data/postgresql.auto.conf
2024-06-25 15:47:17.625 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-06-25 15:47:17.628 P00   INFO: restore size = 740.5MB, file total = 2201
2024-06-25 15:47:17.628 P00   INFO: restore command end: completed successfully (10124ms)

==> The latest backup from the first repository will be restored

2.2 Database Server - Restore the specific Backup

[enterprisedb@edb-quynhon ~]$ pgbackrest --log-level-console=info --stanza=employees --set=20240624-143058F --type=immediate --process-max=2 restore
2024-06-25 18:05:07.136 P00   INFO: restore command begin 2.52: --exec-id=16886-5a77b111 --log-level-console=info --log-level-file=debug --pg1-path=/u01/edb/as16/data --pg-version-force=16 --process-max=2 --repo1-host=192.168.56.79 --repo1-host-user=enterprisedb --set=20240624-143058F --stanza=employees
2024-06-25 18:05:08.268 P00   INFO: repo1: restore backup set 20240624-143058F, recovery will start at 2024-06-24 14:30:58
2024-06-25 18:05:13.883 P00   INFO: write updated /u01/edb/as16/data/postgresql.auto.conf
2024-06-25 18:05:13.919 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-06-25 18:05:13.923 P00   INFO: restore size = 417.3MB, file total = 2190
2024-06-25 18:05:13.923 P00   INFO: restore command end: completed successfully (6794ms)

2.3 Database Server - Restore a Single Database

[enterprisedb@edb-quynhon ~]$ pgbackrest --stanza=employees --set=20240626-141854F_20240626-142055I info
stanza: employees
    status: ok
    cipher: none

    db (current)
        wal archive min/max (16): 000000010000000000000058/00000006000000000000004D

        incr backup: 20240626-141854F_20240626-142055I
            timestamp start/stop: 2024-06-26 14:20:55+07 / 2024-06-26 14:20:56+07
            wal start/stop: 000000010000000000000060 / 000000010000000000000060
            lsn start/stop: 0/60000028 / 0/60000100
            database size: 440.6MB, database backup size: 3.8MB
            repo1: backup set size: 92.6MB, backup size: 481.2KB
            backup reference list: 20240626-141854F, 20240626-141854F_20240626-141953I
            database list: betta (16493), edb (15355), employees (16388), guppy (16494), postgres (5), taolaoxibup (16489)
            tablespaces:
                tbs_ihrp_data (16386) => /u01/ihrp/data
                tbs_ihrp_index (16387) => /u01/ihrp/index

[enterprisedb@edb-quynhon ~]$ pgbackrest --log-level-console=info --stanza=employees --db-include=employees --type=immediate --target-action=promote --process-max=2 restore 

Note:
  • Recovery may error unless --type=immediate is specified. This is because after consistency is reached PostgreSQL will flag zeroed pages as errors even for a full-page write. For PostgreSQL ≥ 13 the ignore_invalid_pages setting may be used to ignore invalid pages. In this case it is important to check the logs after recovery to ensure that no invalid pages were reported in the selected databases.
  • If having the error after starting the service, we must rename "recovery.signal" and restart service again.
  • After restoring the specific database you will still get to see the other user databases as well. However,

2.4 Database Server - Restore to a specific Point in Time
[enterprisedb@edb-quynhon ~]$ pgbackrest --log-level-console=info --stanza=employees --type=time "--target=2024-06-25 17:00:00" --target-action=promote restore
2024-06-25 19:04:46.900 P00   INFO: restore command begin 2.52: --exec-id=20215-ee8445a1 --log-level-console=info --log-level-file=debug --pg1-path=/u01/edb/as16/data --pg-version-force=16 --repo1-host=192.168.56.79 --repo1-host-user=enterprisedb --stanza=employees --target="2024-06-25 17:00:00" --target-action=promote --type=time
2024-06-25 19:04:47.377 P00   INFO: repo1: restore backup set 20240624-221733F_20240624-221842I, recovery will start at 2024-06-24 22:18:42
2024-06-25 19:04:58.145 P00   INFO: write updated /u01/edb/as16/data/postgresql.auto.conf
2024-06-25 19:04:58.173 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-06-25 19:04:58.176 P00   INFO: restore size = 740.5MB, file total = 2201
2024-06-25 19:04:58.176 P00   INFO: restore command end: completed successfully (11279ms)

Note:
  • pgBackRest will choose right backup set to restore. We can get info from "pgbackrest info"
  • If having the error after starting the service, we must rename "recovery.signal" and restart service again.

2.5 Restoring only the delta changes
Theory
  • Restore a Backup required the database cluster directory to be cleaned before the restore could be performed. The --delta option allows pgBackRest to automatically determine which files in the database cluster directory can be preserved and which ones need to be restored from the backup — it also removes files not present in the backup manifest so it will dispose of divergent changes.
  • This is accomplished by calculating a SHA-1 cryptographic hash for each file in the database cluster directory. If the SHA-1 hash does not match the hash stored in the backup then that file will be restored.
  • This operation is very efficient when combined with the process-max option
Backup:
  1. Create a backup, named A
  2. Change data
  3. Create a backup, named B
Restore
  1. Restore A backup set
  2. Check data
  3. Restore B backup set with --delta option
  4. Check restore log in detail
  5. Check data

Show data before first backup
employees=> \dt
                List of relations
  Schema   |        Name         | Type  | Owner  
-----------+---------------------+-------+--------
 employees | department          | table | cherry
 employees | department_employee | table | cherry
 employees | department_manager  | table | cherry
 employees | employee            | table | cherry
 employees | hello_01            | table | cherry
 employees | hello_02            | table | cherry
 employees | salary              | table | cherry
 employees | title               | table | cherry
(8 rows)

Backup Full Database
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees --log-level-console=info --process-max=2 --type=full backup

Change data
employees=> create table hello_11 as select * from department ;
employees=> create table hello_22 as select * from department ;

Show data before second backup
employees=> \dt
                List of relations
  Schema   |        Name         | Type  | Owner  
-----------+---------------------+-------+--------
 employees | department          | table | cherry
 employees | department_employee | table | cherry
 employees | department_manager  | table | cherry
 employees | employee            | table | cherry
 employees | hello_01            | table | cherry
 employees | hello_02            | table | cherry
 employees | hello_11            | table | cherry
 employees | hello_22            | table | cherry
 employees | salary              | table | cherry
 employees | title               | table | cherry
(10 rows)

Backup incr database
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees --log-level-console=info --process-max=2 --type=incr backup

Verify backup info
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees info
stanza: employees
    status: ok
    cipher: none

    db (current)
        wal archive min/max (16): 00000001000000000000005C/00000006000000000000004D

        full backup: 20240626-141854F
            timestamp start/stop: 2024-06-26 14:18:54+07 / 2024-06-26 14:19:18+07
            wal start/stop: 00000001000000000000005C / 00000001000000000000005C
            database size: 783.3MB, database backup size: 783.3MB
            repo1: backup set size: 182.1MB, backup size: 182.1MB

        incr backup: 20240626-141854F_20240626-141953I
            timestamp start/stop: 2024-06-26 14:19:53+07 / 2024-06-26 14:19:54+07
            wal start/stop: 00000001000000000000005E / 00000001000000000000005E
            database size: 440.6MB, database backup size: 2.6MB
            repo1: backup set size: 92.6MB, backup size: 328.4KB
            backup reference list: 20240626-141854F

        incr backup: 20240626-141854F_20240626-142055I
            timestamp start/stop: 2024-06-26 14:20:55+07 / 2024-06-26 14:20:56+07
            wal start/stop: 000000010000000000000060 / 000000010000000000000060
            database size: 440.6MB, database backup size: 3.8MB
            repo1: backup set size: 92.6MB, backup size: 481.2KB
            backup reference list: 20240626-141854F, 20240626-141854F_20240626-141953I

        full backup: 20240626-154002F
            timestamp start/stop: 2024-06-26 15:40:02+07 / 2024-06-26 15:40:16+07
            wal start/stop: 000000010000000000000062 / 000000010000000000000062
            database size: 440.6MB, database backup size: 440.6MB
            repo1: backup set size: 92.6MB, backup size: 92.6MB

        incr backup: 20240626-154002F_20240626-154106I
            timestamp start/stop: 2024-06-26 15:41:06+07 / 2024-06-26 15:41:08+07
            wal start/stop: 000000010000000000000064 / 000000010000000000000064
            database size: 440.6MB, database backup size: 3.8MB
            repo1: backup set size: 92.6MB, backup size: 482KB
            backup reference list: 20240626-154002F

Restore Full Backup
[enterprisedb@edb-quynhon ~]$ pgbackrest --log-level-console=info --stanza=employees --set=20240626-154002F --type=immediate --process-max=2 restore

[root@edb-quynhon backup]# systemctl start edb-as-16.service 

Verify data
employees=> \dt
                List of relations
  Schema   |        Name         | Type  | Owner  
-----------+---------------------+-------+--------
 employees | department          | table | cherry
 employees | department_employee | table | cherry
 employees | department_manager  | table | cherry
 employees | employee            | table | cherry
 employees | hello_01            | table | cherry
 employees | hello_02            | table | cherry
 employees | salary              | table | cherry
 employees | title               | table | cherry
(8 rows)

Restore database to current with --delta option
[root@edb-quynhon backup]# systemctl stop edb-as-16.service 

[enterprisedb@edb-quynhon ~]$ pgbackrest --log-level-console=detail --stanza=employees --delta --type=immediate --process-max=2 restore > /source/restore-delta.log

[root@edb-quynhon backup]# systemctl start edb-as-16.service 

[enterprisedb@edb-quynhon data]$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

# Recovery settings generated by pgBackRest restore on 2024-06-27 10:04:10
restore_command = 'pgbackrest --stanza=employees archive-get %f "%p"'
recovery_target = 'immediate'
recovery_target_timeline = 'current'

Verify data
employees=> \dt
                List of relations
  Schema   |        Name         | Type  | Owner  
-----------+---------------------+-------+--------
 employees | department          | table | cherry
 employees | department_employee | table | cherry
 employees | department_manager  | table | cherry
 employees | employee            | table | cherry
 employees | hello_01            | table | cherry
 employees | hello_02            | table | cherry
 employees | hello_11            | table | cherry
 employees | hello_22            | table | cherry
 employees | salary              | table | cherry
 employees | title               | table | cherry
(10 rows)

2.6 Restoring the backup on a different host to start as a streaming standby
(...coming soon)

Ref:
at
Labels: ,