pgbackrest를 사용한 PostgreSQL 15 백업 및 복구 Incrementalbackup

증분백업 테스트

• 이번엔 full + diff 를 가지고 증분 백업을 진행한다.

데이터 추가

• 증분 데이터 확인

  postgres@lkmpg:~$ sudo -u postgres psql -d testdb -c "INSERT INTO test_table (data) VALUES ('Incremental Backup Test');"
  INSERT 0 1

증분백업

postgres@lkmpg:~$ sudo -u postgres pgbackrest --stanza=demo --type=incr backup
2025-03-15 13:00:22.819 P00   INFO: backup command begin 2.54.2: --exec-id=1581-421f3c55 --log-level-console=info --pg1-path=/var/lib/postgresql/15/main --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --type=incr
2025-03-15 13:00:22.849 P00   INFO: last backup label = 20250315-123833F_20250315-125408D, version = 2.54.2
2025-03-15 13:00:22.849 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2025-03-15 13:00:23.084 P00   INFO: backup start archive = 000000070000000000000019, lsn = 0/19000028
2025-03-15 13:00:23.084 P00   INFO: check archive for prior segment 000000070000000000000018
2025-03-15 13:00:24.000 P00   WARN: a timeline switch has occurred since the 20250315-123833F_20250315-125408D backup, enabling delta checksum
                                    HINT: this is normal after restoring from backup or promoting a standby.
2025-03-15 13:00:24.581 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2025-03-15 13:00:24.601 P00   INFO: backup stop archive = 000000070000000000000019, lsn = 0/19000100
2025-03-15 13:00:24.604 P00   INFO: check archive for segment(s) 000000070000000000000019:000000070000000000000019
2025-03-15 13:00:24.922 P00   INFO: new backup label = 20250315-123833F_20250315-130022I
2025-03-15 13:00:24.968 P00   INFO: incr backup size = 32.6KB, file total = 1570
2025-03-15 13:00:24.968 P00   INFO: backup command end: completed successfully (2153ms)
2025-03-15 13:00:24.968 P00   INFO: expire command begin 2.54.2: --exec-id=1581-421f3c55 --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
2025-03-15 13:00:24.969 P00   INFO: expire command end: completed successfully (1ms)

backup list 확인

• full+diff 와 증분백업이 확인되었음

  postgres@lkmpg:~$ sudo -u postgres pgbackrest --stanza=demo info
  stanza: demo
    status: ok
    cipher: none
  
    db (current)
        wal archive min/max (15): 000000050000000000000010/000000070000000000000019
  
        full backup: 20250315-123833F
            timestamp start/stop: 2025-03-15 12:38:33+09 / 2025-03-15 12:40:14+09
            wal start/stop: 000000050000000000000014 / 000000050000000000000015
            database size: 68.6MB, database backup size: 68.6MB
            repo1: backup set size: 4.9MB, backup size: 4.9MB
  
        diff backup: 20250315-123833F_20250315-125408D
            timestamp start/stop: 2025-03-15 12:54:08+09 / 2025-03-15 12:54:09+09
            wal start/stop: 000000060000000000000017 / 000000060000000000000017
            database size: 68.6MB, database backup size: 64.5KB
            repo1: backup set size: 4.9MB, backup size: 4.3KB
            backup reference total: 1 full
  
        incr backup: 20250315-123833F_20250315-130022I
            timestamp start/stop: 2025-03-15 13:00:22+09 / 2025-03-15 13:00:24+09
            wal start/stop: 000000070000000000000019 / 000000070000000000000019
            database size: 68.6MB, database backup size: 32.6KB
            repo1: backup set size: 4.9MB, backup size: 935B
            backup reference total: 1 full, 1 diff

data 삭제

postgres@lkmpg:~$ sudo -u postgres psql -d testdb -c "DROP TABLE test_table;"
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/15/main/*
DROP TABLE

복구진행

• 13:00까지의 데이터 복구가 확인되었음

  postgres@lkmpg:~$ sudo -u postgres pgbackrest --stanza=demo restore
  sudo systemctl start postgresql
  2025-03-15 13:02:05.058 P00   INFO: restore command begin 2.54.2: --exec-id=1664-68014c97 --log-level-console=info --pg1-path=/var/lib/postgresql/15/main --repo1-path=/var/lib/pgbackrest --stanza=demo
  2025-03-15 13:02:05.071 P00   INFO: repo1: restore backup set 20250315-123833F_20250315-130022I, recovery will start at 2025-03-15 13:00:22
  2025-03-15 13:02:08.574 P00   INFO: write updated /var/lib/postgresql/15/main/postgresql.auto.conf
  2025-03-15 13:02:08.581 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
  2025-03-15 13:02:08.582 P00   INFO: restore size = 68.6MB, file total = 1570
  2025-03-15 13:02:08.582 P00   INFO: restore command end: completed successfully (3527ms)

data 존재 확인

postgres@lkmpg:~$ sudo -u postgres psql -d testdb -c "SELECT * FROM test_table;"
 id |           data
----+--------------------------
  1 | Full Backup Test
  2 | Differential Backup Test
  3 | Incremental Backup Test
(3 rows)