WAL-G를 사용한 PostgreSQL 15 백업 및 복구 테스트 시나리오

WAL-G를 사용한 PostgreSQL 15 백업 및 복구 테스트 시나리오

-WAL-G는 증분 백업을 지원해 저장 공간을 절약하고 빠른 백업을 제공
-병렬 복구 (Parallel Restore)
-PITR

사전 준비

OS: Ubuntu 22.04
PostgreSQL 버전: 15
WAL-G: 최신 버전 (예: v3.0.0 이상 권장)
로컬 디렉토리: 백업 및 WAL 파일 저장용 (예: /backups)

1. WAL-G 설치
   아래 url에 맞는 OS 버전 및 DB 버전에 다운로드

https://github.com/wal-g/wal-g/releases

root@lkmpg:/usr/local/bin# wget https://github.com/wal-g/wal-g/releases/latest/download/wal-g-pg-ubuntu-24.04-amd64.tar.gz
--2025-03-08 23:14:52--  https://github.com/wal-g/wal-g/releases/latest/download/wal-g-pg-ubuntu-24.04-amd64.tar.gz
Resolving github.com (github.com)... 20.200.245.247
Connecting to github.com (github.com)|20.200.245.247|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://github.com/wal-g/wal-g/releases/download/v3.0.5/wal-g-pg-ubuntu-24.04-amd64.tar.gz [following]
--2025-03-08 23:14:52--  https://github.com/wal-g/wal-g/releases/download/v3.0.5/wal-g-pg-ubuntu-24.04-amd64.tar.gz
Reusing existing connection to github.com:443.
HTTP request sent, awaiting response... 302 Found
Location: https://objects.githubusercontent.com/github-production-release-asset-2e65be/95143992/9d59beee-6dc6-45cd-8784-0d18396d81ac?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=releaseassetproduction%2F20250308%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20250308T141452Z&X-Amz-Expires=300&X-Amz-Signature=a127da08d916a929fe0da5754bafe8edb462635d19b930036ce14d8f0d220b39&X-Amz-SignedHeaders=host&response-content-disposition=attachment%3B%20filename%3Dwal-g-pg-ubuntu-24.04-amd64.tar.gz&response-content-type=application%2Foctet-stream [following]
--2025-03-08 23:14:52--  https://objects.githubusercontent.com/github-production-release-asset-2e65be/95143992/9d59beee-6dc6-45cd-8784-0d18396d81ac?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=releaseassetproduction%2F20250308%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20250308T141452Z&X-Amz-Expires=300&X-Amz-Signature=a127da08d916a929fe0da5754bafe8edb462635d19b930036ce14d8f0d220b39&X-Amz-SignedHeaders=host&response-content-disposition=attachment%3B%20filename%3Dwal-g-pg-ubuntu-24.04-amd64.tar.gz&response-content-type=application%2Foctet-stream
Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.110.133, 185.199.108.133, 185.199.109.133, ...
Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.110.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 16459140 (16M) [application/octet-stream]
Saving to: ‘wal-g-pg-ubuntu-24.04-amd64.tar.gz’

wal-g-pg-ubuntu-24.04-amd64 100%[===========================================>]  15.70M  28.0MB/s    in 0.6s

2025-03-08 23:14:53 (28.0 MB/s) - ‘wal-g-pg-ubuntu-24.04-amd64.tar.gz’ saved [16459140/16459140]

root@lkmpg:/usr/local/bin# ll
total 16084
drwxr-xr-x  2 root root     4096 Mar  8 23:14 ./
drwxr-xr-x 10 root root     4096 Aug 27  2024 ../
-rw-r--r--  1 root root 16459140 Jan 10 22:16 wal-g-pg-ubuntu-24.04-amd64.tar.gz
root@lkmpg:/usr/local/bin# tar -zxvf wal-g-pg-ubuntu-24.04-amd64.tar.gz
wal-g-pg-ubuntu-24.04-amd64
root@lkmpg:/usr/local/bin# ll
total 78956
drwxr-xr-x  2 root root     4096 Mar  8 23:15 ./
drwxr-xr-x 10 root root     4096 Aug 27  2024 ../
-rwxr-xr-x  1 root root 64378432 Jan 10 22:16 wal-g-pg-ubuntu-24.04-amd64*
-rw-r--r--  1 root root 16459140 Jan 10 22:16 wal-g-pg-ubuntu-24.04-amd64.tar.gz
root@lkmpg:/usr/local/bin# mv wal-g-pg-ubuntu-24.04-amd64 wal-g
root@lkmpg:/usr/local/bin# ll
total 78956
drwxr-xr-x  2 root root     4096 Mar  8 23:15 ./
drwxr-xr-x 10 root root     4096 Aug 27  2024 ../
-rwxr-xr-x  1 root root 64378432 Jan 10 22:16 wal-g*
-rw-r--r--  1 root root 16459140 Jan 10 22:16 wal-g-pg-ubuntu-24.04-amd64.tar.gz
root@lkmpg:/usr/local/bin# wal-g --version
wal-g version v3.0.5    94bf839 2025.01.10_13:15:40     PostgreSQL

2. PostgreSQL 설정 및 WAL-G 환경 구성

- PostgreSQL WAL 아카이빙 활성화

2.1 postgresql conf 설정 및 재기동

archive_mode = on
archive_command = '/usr/local/bin/wal-g wal-push %p --config=/etc/wal-g.yaml'
wal_level = replica
archive_timeout = 60

sudo systemctl restart postgresql

2.2 WAL-G 설정 파일 생성

vi /etc/wal-g.yaml

WALG_FILE_PREFIX: "/backups"
PGDATA: "/var/lib/postgresql/15/main"
PGHOST: "/var/run/postgresql"

sudo chown postgres:postgres /etc/wal-g.yaml

2.3 백업 디렉토리 생성

sudo mkdir -p /backups
sudo chown postgres:postgres /backups

-- postgresql 상태 확인 (main일 경우 f, slave일 경우 t)
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

-- postgresql과 pg-wal 연결테스트
sudo -u postgres /usr/local/bin/wal-g wal-push /var/lib/postgresql/15/main/pg_wal/000000010000000000000001 --config=/etc/wal-g.yaml

3. 테스트 데이터베이스 생성

3.0 기초데이터 생성

sudo -u postgres psql -c "CREATE DATABASE test_db;"
sudo -u postgres psql -d test_db -c "CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, hire_date TIMESTAMP);"
sudo -u postgres psql -d test_db -c "INSERT INTO employees (name, hire_date) VALUES ('Alice', '2025-03-01 09:00:00'), ('Bob', '2025-03-02 14:00:00');"

3.1 풀 백업본 생성

/backups/basebackups에 저장됨

sudo -u postgres /usr/local/bin/wal-g backup-push /var/lib/postgresql/15/main --config=/etc/wal-g.yaml  

백업목록확인

sudo -u postgres /usr/local/bin/wal-g backup-list --config=/etc/wal-g.yaml  

postgres@lkmpg:~$ sudo -u postgres /usr/local/bin/wal-g backup-list --config=/etc/wal-g.yaml  
INFO: 2025/03/10 09:54:16.991478 List backups from storages: \[default\]  
backup\_name                   modified                  wal\_file\_name            storage\_name  
base\_000000010000000000000002 2025-03-09T22:48:44+09:00 000000010000000000000002 default  

3.2 추가데이터 발생

sudo -u postgres psql -d test\_db -c "INSERT INTO employees (name, hire\_date) VALUES ('Charlie', '2025-03-08 10:00:00');"  

/backups/wal에 아카이빙 발생  

4. 강제로 데이터 손실 발생

4.0 데이터 확인

```
postgres@lkmpg:~$ psql -d test\_db -c "select \* from employees;"  
id |  name   |      hire\_date  
\----+---------+---------------------  
1 | Alice   | 2025-03-01 09:00:00  
2 | Bob     | 2025-03-02 14:00:00  
36 | Charlie | 2025-03-08 10:00:00  
(3 rows)  
```

4.1 복구시점을 위한 시간 확인

sudo -u postgres psql -c "SELECT NOW();"  

postgres@lkmpg:~$ psql -c "select now()"  
              now  
\-------------------------------  
 2025-03-10 09:59:48.352577+09  
(1 row)  

4.2 테이블 drop 강제 발생

sudo -u postgres psql -d test\_db -c "DROP TABLE employees;"  

4.3 데이터 손실 확인

sudo -u postgres psql -d test\_db -c "\\dt"  
postgres@lkmpg:~$  psql -d test\_db -c "select \* from employees;"  
ERROR:  relation "employees" does not exist  
LINE 1: select \* from employees;  

5. 복구

5.1 postgresql 중지

postgres@lkmpg:/backups/wal\_005$ sudo systemctl stop postgresql  

5.2 data 디렉토리 삭제

postgres@lkmpg:/backups/wal\_005$ rm -rf /var/lib/postgresql/15/main/\*  

5.3 최신 백업본으로 복구

sudo -u postgres /usr/local/bin/wal-g backup-fetch /var/lib/postgresql/15/main LATEST --config=/etc/wal-g.yaml  

shell  
postgres@lkmpg:/backups/wal\_005$ sudo -u postgres /usr/local/bin/wal-g backup-fetch /var/lib/postgresql/15/main LATEST --config=/etc/wal-g.yaml  
INFO: 2025/03/10 10:07:11.783381 Selecting the latest backup...  
INFO: 2025/03/10 10:07:11.783568 Backup to fetch will be searched in storages: \[default\]  
INFO: 2025/03/10 10:07:11.784304 LATEST backup is: 'base\_000000010000000000000002'  
INFO: 2025/03/10 10:07:15.251287 Finished extraction of part\_001.tar.lz4  
INFO: 2025/03/10 10:07:15.270370 Finished extraction of pg\_control.tar.lz4  
INFO: 2025/03/10 10:07:15.276258 Finished extraction of backup\_label.tar.lz4  
INFO: 2025/03/10 10:07:15.276530  
Backup extraction complete.  

5.4 postgresql conf 설정 및 restore signal파일 생성

vi /etc/postgresql/15/main/postgresql.conf  
conf에 restore 명령어 설정  
restore\_command = '/usr/local/bin/wal-g wal-fetch %f %p --config=/etc/wal-g.yaml'  
recovery\_target\_time = '2025-03-10 10:57:52'  
recovery\_target\_action = 'promote'  

touch /var/lib/postgresql/15/main/recovery.signal  

5.5 postgresql 시작

postgres@lkmpg:/backups/wal\_005$ sudo systemctl start postgresql  

5.6 데이터 복구 확인

postgres@lkmpg:/backups/wal\_005$ sudo -u postgres psql -d test\_db -c "select \* from employees;"  
 id |  name   |      hire\_date  
\----+---------+---------------------  
  1 | Alice   | 2025-03-01 09:00:00  
  2 | Bob     | 2025-03-02 14:00:00  
  3 | Charlie | 2025-03-08 10:00:00  
(3 rows)