PostgreSQL特性矩阵解析系列15之Named restore points

增量备份
切换到 postgres 用户下
mkdir -p /opt/pg_root/backups 存放基础备份
mkdir -p /opt/pg_root/archive_wals 存放归档

修改postgresql.conf中的配置
postgres@bogon-> vi $PGDATA/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = '/usr/bin/lz4 -q -z %p opt/pg_root/archive_wals/%f.lz4'

postgres@bogon-> pg_ctl restart  重启DB 

创建基础备份
postgres@bogon-> pg_basebackup -Ft -Pv -Xf -z -Z5 -h 127.0.0.1 -p 5432 -D opt/pg_root/backups/
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/7000028 on timeline 1
52727/52727 kB (100%), 2/2 tablespaces                                         
pg_basebackup: write-ahead log end point: 0/7000138
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

设置还原点
创建还原点的系统函数为：pg_create_restore_point
postgres=# SELECT pg_create_restore_point('domac-202107211846');
2021-07-12 06:46:35.487 EDT [126444] LOG:  restore point "domac-202107211846" created at 0/80000C8
2021-07-12 06:46:35.487 EDT [126444] STATEMENT:  SELECT pg_create_restore_point('domac-202107211846');
 pg_create_restore_point 
-------------------------
 0/80000C8
(1 row)

恢复到指定还原点
创建一张测试表
postgres=# CREATE TABLE restore(
postgres(#  id SERIAL PRIMARY KEY,
postgres(#  ival INT NOT NULL DEFAULT 0,
postgres(#  description TEXT,
postgres(#  created_time TIMESTAMPTZ NOT NULL DEFAULT now());
CREATE TABLE

插入测试数据
postgres=# INSERT INTO restore (ival) VALUES (1);
postgres=# INSERT INTO restore (ival) VALUES (2);
postgres=# INSERT INTO restore (ival) VALUES (3);
postgres=# INSERT INTO restore (ival) VALUES (4);

postgres=# select * from restore;
 id | ival | description |         created_time          
----+------+-------------+-------------------------------
  1 |    1 |             | 2021-07-12 06:49:12.006791-04
  2 |    2 |             | 2021-07-12 06:49:12.156632-04
  3 |    3 |             | 2021-07-12 06:49:12.165001-04
  4 |    4 |             | 2021-07-12 06:49:13.176974-04
(4 rows)

切换下WAL产生归档
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/801B388
(1 row)

创建一个还原点
postgres=# select pg_create_restore_point('domac-1014');
2021-07-12 06:51:49.665 EDT [126444] LOG:  restore point "domac-1014" created at 0/90000C8
2021-07-12 06:51:49.665 EDT [126444] STATEMENT:  select pg_create_restore_point('domac-1014');
 pg_create_restore_point 
-------------------------
 0/90000C8
(1 row)

删除表中全部数据
postgres=# delete from restore;
DELETE 4

恢复试验
停止数据库
postgres@bogon-> pg_ctl -m f stop

移除旧的数据目录,将基础备份还原
postgres@bogon-> mv opt/pg_root13/ /opt/pg_root13_bak
postgres@bogon-> mkdir opt/pg_root13
postgres@bogon-> tar -xvf opt/pg_root/backups/base.tar.gz -C /opt/pg_root13


修改restore_command  recovery_target_name参数
restore_command = '/usr/bin/lz4 -d /opt/pg_root/archive_wals/%f.lz4 %p'
recovery_target_name = 'domac-1014' 


然后启动数据库进入恢复状态
postgres@bogon-> pg_ctl start
waiting for server to start....2021-07-12 07:00:32.863 EDT [127392] LOG:  starting PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-07-12 07:00:32.864 EDT [127392] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-07-12 07:00:32.866 EDT [127392] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-07-12 07:00:32.872 EDT [127393] LOG:  database system was interrupted; last known up at 2021-07-12 06:43:14 EDT
2021-07-12 07:00:32.916 EDT [127393] LOG:  redo starts at 0/7000028
2021-07-12 07:00:32.917 EDT [127393] LOG:  consistent recovery state reached at 0/7000138
2021-07-12 07:00:32.917 EDT [127393] LOG:  redo done at 0/7000138
2021-07-12 07:00:33.028 EDT [127392] LOG:  database system is ready to accept connections
 done
server started