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

차등백업 테스트

차등백업을 위해서 데이터 insert

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

 

차등백업 수행

postgres@lkmpg:~$ sudo -u postgres pgbackrest --stanza=demo --type=diff backup
2025-03-15 12:54:08.439 P00   INFO: backup command begin 2.54.2: --exec-id=1469-c095f788 --log-level-console=info --pg1-path=/var/lib/postgresql/15/main --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo --type=diff
2025-03-15 12:54:08.460 P00   INFO: last backup label = 20250315-123833F, version = 2.54.2
2025-03-15 12:54:08.460 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2025-03-15 12:54:08.789 P00   INFO: backup start archive = 000000060000000000000017, lsn = 0/17000028
2025-03-15 12:54:08.790 P00   INFO: check archive for prior segment 000000060000000000000016
2025-03-15 12:54:09.001 P00   WARN: a timeline switch has occurred since the 20250315-123833F backup, enabling delta checksum
                                    HINT: this is normal after restoring from backup or promoting a standby.
2025-03-15 12:54:09.398 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2025-03-15 12:54:09.415 P00   INFO: backup stop archive = 000000060000000000000017, lsn = 0/17000100
2025-03-15 12:54:09.417 P00   INFO: check archive for segment(s) 000000060000000000000017:000000060000000000000017
2025-03-15 12:54:09.527 P00   INFO: new backup label = 20250315-123833F_20250315-125408D
2025-03-15 12:54:09.570 P00   INFO: diff backup size = 64.5KB, file total = 1570
2025-03-15 12:54:09.571 P00   INFO: backup command end: completed successfully (1134ms)
2025-03-15 12:54:09.571 P00   INFO: expire command begin 2.54.2: --exec-id=1469-c095f788 --log-level-console=info --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
2025-03-15 12:54:09.571 P00   INFO: expire command end: completed successfully (0ms)
postgres@lkmpg:~$

백업 list 확인

• full backup 본과 diff backup이 같이 존재

postgres@lkmpg:~$ sudo -u postgres pgbackrest --stanza=demo 

info stanza: demo status: ok cipher: none db (current) wal archive min/max (15): 000000050000000000000010/000000060000000000000017 
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

 

 

 

데이터 강제삭제 후 복구

log를 확인해보면 차등백업시간인 12:54 까지의 데이터 복구가 수행되었음.

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
postgres@lkmpg:~$
postgres@lkmpg:~$
postgres@lkmpg:~$ sudo -u postgres pgbackrest --stanza=demo restore
2025-03-15 12:56:30.848 P00   INFO: restore command begin 2.54.2: --exec-id=1510-e69ae219 --log-level-console=info --pg1-path=/var/lib/postgresql/15/main --repo1-path=/var/lib/pgbackrest --stanza=demo
2025-03-15 12:56:30.856 P00   INFO: repo1: restore backup set 20250315-123833F_20250315-125408D, recovery will start at 2025-03-15 12:54:08
2025-03-15 12:56:32.681 P00   INFO: write updated /var/lib/postgresql/15/main/postgresql.auto.conf
2025-03-15 12:56:32.686 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2025-03-15 12:56:32.687 P00   INFO: restore size = 68.6MB, file total = 1570
2025-03-15 12:56:32.688 P00   INFO: restore command end: completed successfully (1842ms)

 

재기동 후 데이터가 다 있음을 확인

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