[PostgreSQL - Wal-g 백업 & 복구] part 2

▶ 데이터 입력

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
repmgr    | repmgr   | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
test2     | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
(5 rows)


test2=# create table kim(id int);
CREATE TABLE


test2=# insert into kim select id from generate_series(1,100000) as kim(id);
INSERT 0 100000


test2=# checkpoint;
CHECKPOINT


test2=# select pg_switch_wal();
pg_switch_wal
---------------
0/8B004250
(1 row)


test2=# insert into kim select id from generate_series(1,100000) as kim(id);
INSERT 0 100000


test2=# checkpoint;
CHECKPOINT


test2=# select pg_switch_wal();
pg_switch_wal
---------------
0/8D0020F8
(1 row)

 

 

 

▶ backup 실행 및 확인

postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g backup-push /mnt/pgsql/12/data/
INFO: 2021/11/25 13:56:05.717861 Doing full backup.
INFO: 2021/11/25 13:56:05.726534 Calling pg_start_backup()
INFO: 2021/11/25 13:56:05.770057 Walking ...
INFO: 2021/11/25 13:56:05.770339 Starting part 1 ...
INFO: 2021/11/25 13:56:06.759460 Finished writing part 1.
INFO: 2021/11/25 13:56:06.759486 Starting part 2 ...
INFO: 2021/11/25 13:56:06.759495 /global/pg_control
INFO: 2021/11/25 13:56:06.760742 Finished writing part 2.
INFO: 2021/11/25 13:56:06.762914 Calling pg_stop_backup()
INFO: 2021/11/25 13:56:07.777239 Starting part 3 ...
INFO: 2021/11/25 13:56:07.778842 backup_label
INFO: 2021/11/25 13:56:07.778903 tablespace_map
INFO: 2021/11/25 13:56:07.778970 Finished writing part 3.
INFO: 2021/11/25 13:56:07.783241 Wrote backup with name base_0000000700000000000000B3


postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g wal-show
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+
| TLI | PARENT TLI | SWITCHPOINT LSN | START SEGMENT            | END SEGMENT              | SEGMENT RANGE | SEGMENTS COUNT | STATUS | BACKUPS COUNT |
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+
|   7 |          0 |               0 | 000000070000000000000032 | 0000000700000000000000B3 |           130 |            130 | OK     |             3 |
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+


postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g wal-verify
INFO: 2021/11/25 13:56:29.742262 Detected earliest available backup: base_00000007000000000000003F
+-----+--------------------------+--------------------------+----------------+--------+
| TLI | START                    | END                      | SEGMENTS COUNT | STATUS |
+-----+--------------------------+--------------------------+----------------+--------+
|   7 | 00000007000000000000003F | 0000000700000000000000B3 |            117 |  FOUND |
+-----+--------------------------+--------------------------+----------------+--------+
WAL storage status: OK


postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g backup-list
name                          last_modified             wal_segment_backup_start
base_00000007000000000000003F 2021-11-25T12:04:07+09:00 00000007000000000000003F
base_0000000700000000000000AD 2021-11-25T13:51:21+09:00 0000000700000000000000AD
base_0000000700000000000000B3 2021-11-25T13:56:07+09:00 0000000700000000000000B3


postgres@primary:/pg_archive# pwd
/pg_archive
postgres@primary:/pg_archive# ll
합계 16
drwxr-xr-x. 5 postgres postgres 4096 11월 25 13:56 basebackups_005
drwx------. 2 postgres postgres 8192 11월 25 14:06 wal_005

...생략
-rw-------. 1 postgres postgres   67852 11월 25 13:47 0000000700000000000000A8.lz4
-rw-------. 1 postgres postgres   67794 11월 25 13:48 0000000700000000000000A9.lz4
-rw-------. 1 postgres postgres   67788 11월 25 13:49 0000000700000000000000AA.lz4
-rw-------. 1 postgres postgres   75139 11월 25 13:50 0000000700000000000000AB.lz4
-rw-------. 1 postgres postgres   73507 11월 25 13:51 0000000700000000000000AC.lz4
-rw-------. 1 postgres postgres     269 11월 25 13:51 0000000700000000000000AD.00000028.backup.lz4
-rw-------. 1 postgres postgres   68395 11월 25 13:51 0000000700000000000000AD.lz4
-rw-------. 1 postgres postgres   69001 11월 25 13:52 0000000700000000000000AE.lz4
-rw-------. 1 postgres postgres   67802 11월 25 13:53 0000000700000000000000AF.lz4
-rw-------. 1 postgres postgres   67790 11월 25 13:54 0000000700000000000000B0.lz4
-rw-------. 1 postgres postgres   67785 11월 25 13:55 0000000700000000000000B1.lz4
-rw-------. 1 postgres postgres   67339 11월 25 13:56 0000000700000000000000B2.lz4
-rw-------. 1 postgres postgres     268 11월 25 13:56 0000000700000000000000B3.00000028.backup.lz4

 

 

 

 

▶ 마지막 archive time 확인

 xxxx.backup 파일은 base backup 수행 시점을 나타내주는 파일
postgres=# select * from pg_stat_archiver ;
archived_count |              last_archived_wal           |      last_archived_time       | failed_count | last_failed_wal | last_failed_time |          stats_reset          
----------------+--------------------------+-------------------------------+--------------+-----------------+------------------+-------------------------------
 154           | 0000000700000000000000B3.00000028.backup | 2021-11-25 13:56:06.858767+09 |            0 |                 |                  | 2021-10-19 16:12:09.958238+09

 

 

 

▶ Data파일 삭제

postgres@primary:/home/postgres# cd /mnt/pgsql/12/data/
postgres@primary:/mnt/pgsql/12/data# rm -rf ./*

 

▶ RESTORE

postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g backup-fetch /mnt/pgsql/12/data/ LATEST
INFO: 2021/11/25 14:07:29.631135 LATEST backup is: 'base_0000000700000000000000B3'
INFO: 2021/11/25 14:07:29.795597 Finished extraction of part_003.tar.lz4
INFO: 2021/11/25 14:07:29.795608 Finished decompression of part_003.tar.lz4
INFO: 2021/11/25 14:07:31.261302 Finished decompression of part_001.tar.lz4
INFO: 2021/11/25 14:07:31.261313 Finished extraction of part_001.tar.lz4
INFO: 2021/11/25 14:07:31.262550 Finished decompression of pg_control.tar.lz4
INFO: 2021/11/25 14:07:31.262557 Finished extraction of pg_control.tar.lz4
INFO: 2021/11/25 14:07:31.262564
Backup extraction complete.



## recovery signal파일 생성(data파일 위치에 생성)
postgres@primary:/home/postgres# touch /mnt/pgsql/12/data/recovery.signal



## restore command 입력 
postgres@primary:/mnt/pgsql/12/data# vi postgresql.conf
restore_command = 'WALG_FILE_PREFIX=/pg_archive/ /mnt/pgsql/12/bin/wal-g wal-fetch %f %p'

 

 

▶ db 기동&data 확인

postgres@primary:/home/postgres# /usr/pgsql-12/bin/pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start


postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
postgres  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
repmgr    | repmgr   | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
template1 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
test2     | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |
(5 rows)



test2=# select count(*) from kim;
count  
--------
200000
(1 row)

 

 

▶ BACKUP list DELETE

▶ 백업 list확인

postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g wal-verify
INFO: 2021/11/25 15:00:40.061659 Detected earliest available backup: base_00000007000000000000003F
+-----+--------------------------+--------------------------+----------------+--------+
| TLI | START                    | END                      | SEGMENTS COUNT | STATUS |
+-----+--------------------------+--------------------------+----------------+--------+
|   7 | 00000007000000000000003F | 0000000700000000000000BD |            127 |  FOUND |
+-----+--------------------------+--------------------------+----------------+--------+
WAL storage status: OK


postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g wal-show
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+
| TLI | PARENT TLI | SWITCHPOINT LSN | START SEGMENT            | END SEGMENT              | SEGMENT RANGE | SEGMENTS COUNT | STATUS | BACKUPS COUNT |
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+
|   7 |          0 |               0 | 000000070000000000000032 | 0000000700000000000000BD |           140 |            140 | OK     |             3 |
|   8 |          7 |      3187671040 | 0000000800000000000000BE | 0000000800000000000000BE |             1 |              1 | OK     |             0 |
+-----+------------+-----------------+--------------------------+--------------------------+---------------+----------------+--------+---------------+



postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g backup-list
name                          last_modified             wal_segment_backup_start
base_00000007000000000000003F 2021-11-25T12:04:07+09:00 00000007000000000000003F
base_0000000700000000000000AD 2021-11-25T13:51:21+09:00 0000000700000000000000AD
base_0000000700000000000000B3 2021-11-25T13:56:07+09:00 0000000700000000000000B3



##2번째 backup 삭제 
//FULL 2 는 최신 전체백업 2개와 중간에 있는 모든 항목을 유지하여 전체 백업과 WAL을 삭제
postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g delete retain FULL 2 --confirm
INFO: 2021/11/25 16:28:26.127478 Start delete
INFO: 2021/11/25 16:28:26.127488 retrieving permanent objects
INFO: 2021/11/25 16:28:26.128174 Objects in folder:
INFO: 2021/11/25 16:28:26.128187     will be deleted: basebackups_005/base_00000007000000000000003F_backup_stop_sentinel.json
INFO: 2021/11/25 16:28:26.128220     will be deleted: wal_005/000000070000000000000032.lz4
INFO: 2021/11/25 16:28:26.128227     will be deleted: wal_005/000000070000000000000033.lz4
INFO: 2021/11/25 16:28:26.128234     will be deleted: wal_005/000000070000000000000034.lz4
INFO: 2021/11/25 16:28:26.128241     will be deleted: wal_005/000000070000000000000035.lz4



postgres@primary:/home/postgres# WALG_FILE_PREFIX=/pg_archive/ wal-g backup-list
name                          last_modified             wal_segment_backup_start
base_0000000700000000000000AD 2021-11-25T13:51:21+09:00 0000000700000000000000AD
base_0000000700000000000000B3 2021-11-25T13:56:07+09:00 0000000700000000000000B3