pg_basebackup (incremental backup)

Postgresql 17부터 pg_basebackup을 이용한 증분 백업 기능이 추가되었다. 

해당 기능 사용을 위해선 postgresql.conf에 summarize_wal=on 설정이 필요하다.

 

 

 

## 백업 폴더 생성
$mkdir -p /data1/as17/pg_basebackup /data1/as17/pg_basebackup/inc1 /data1/as17/pg_basebackup/inc2 /data1/as17/pg_basebackup/inc3

## 현재 backup_tab 테이블 데이터 확인
testdb=> select * from backup_tab ;'
 a |             b
---+---------------------------
 1 | 02-MAY-25 14:00:40.149758
 2 | 02-MAY-25 14:00:40.173127
 3 | 02-MAY-25 14:00:40.173906
 4 | 02-MAY-25 14:00:40.174561
 5 | 02-MAY-25 14:00:40.175225
(5개 행)

## Full 백업 수행

$ pg_basebackup -D /data1/as17/pg_basebackup/full -X fetch -P -p 5444

## Full 백업 확인
$ ll /data1/as17/pg_basebackup/full

## 데이터 삽입 및 조회
insert into backup_tab values (6, now());

testdb=> select * from backup_tab ;
 a |             b
---+---------------------------
 1 | 02-MAY-25 10:04:35.724826
 2 | 02-MAY-25 10:04:35.726175
 3 | 02-MAY-25 10:04:35.727003
 4 | 02-MAY-25 10:05:37.589441
 5 | 02-MAY-25 10:05:39.974549
 6 | 02-MAY-25 10:18:12.74439

 

## Incremental 백업 수행
pg_basebackup -D /data1/as17/pg_basebackup/inc1 --incremental=/data1/as17/pg_basebackup/full/backup_manifest -P -p 5444

## 데이터 삽입 및 조회
insert into backup_tab values (7, now());

testdb=> select * from backup_tab ;
 a |             b
---+---------------------------
 1 | 02-MAY-25 10:04:35.724826
 2 | 02-MAY-25 10:04:35.726175
 3 | 02-MAY-25 10:04:35.727003
 4 | 02-MAY-25 10:05:37.589441
 5 | 02-MAY-25 10:05:39.974549
 6 | 02-MAY-25 10:18:12.74439
 7 | 02-MAY-25 13:58:44.821869

 

## Incremental 백업 수행 2
pg_basebackup -D /data1/as17/pg_basebackup/inc2 --incremental=/data1/as17/pg_basebackup/full/backup_manifest -P -p 5444

## 데이터 삽입 및 조회
insert into backup_tab values (8, now());

testdb=> select * from backup_tab ;
 a |             b
---+---------------------------
 1 | 02-MAY-25 14:00:40.149758
 2 | 02-MAY-25 14:00:40.173127
 3 | 02-MAY-25 14:00:40.173906
 4 | 02-MAY-25 14:00:40.174561
 5 | 02-MAY-25 14:00:40.175225
 6 | 02-MAY-25 14:01:15.758906
 7 | 02-MAY-25 14:01:51.621624
 8 | 02-MAY-25 14:02:51.390934

 

## Incremental 백업 수행 3
pg_basebackup -D /data1/as17/pg_basebackup/inc3 --incremental=/data1/as17/pg_basebackup/full/backup_manifest -P -p 5444

## 데이터 삽입 및 조회
insert into backup_tab values (9, now());

testdb=> select * from backup_tab ;
 a |             b
---+---------------------------
 1 | 02-MAY-25 14:00:40.149758
 2 | 02-MAY-25 14:00:40.173127
 3 | 02-MAY-25 14:00:40.173906
 4 | 02-MAY-25 14:00:40.174561
 5 | 02-MAY-25 14:00:40.175225
 6 | 02-MAY-25 14:01:15.758906
 7 | 02-MAY-25 14:01:51.621624
 8 | 02-MAY-25 14:02:51.390934
 9 | 02-MAY-25 14:03:35.093776

 

 

복구하는 법

## combinebackup 백업 수행 (Incremental 2까지만 살려보기)
pg_combinebackup --output=/data1/as17/pg_basebackup/combine /data1/as17/pg_basebackup/full/ /data1/as17/pg_basebackup/inc2

## DB 정지
pg_ctl stop -D $PGDATA

## EPAS Data 교체
$ mv /data1/as17/data /data1/as17/data_incbak
$ mv /data1/as17/pg_basebackup/combine /data1/as17/data
$ chmod 750 /data1/as17/data

## DB 기동
$ pg_ctl start -D $PGDATA

##데이터 조회

testdb=> select * from backup_tab ;
 a |             b
---+---------------------------
 1 | 02-MAY-25 14:00:40.149758
 2 | 02-MAY-25 14:00:40.173127
 3 | 02-MAY-25 14:00:40.173906
 4 | 02-MAY-25 14:00:40.174561
 5 | 02-MAY-25 14:00:40.175225
 6 | 02-MAY-25 14:01:15.758906
 7 | 02-MAY-25 14:01:51.621624