PostgreSQL-增量备份及恢复

创建归档目录

/home/postgres/pg13/archive_log

修改参数

WAL_LEVEL

可选值：minimal，replica，logical

开启wal归档至少设置为replica

alter system set wal_level='replica';

ARCHIVE_MODE

可选值：on，off，always

开启wal归档需要设置为on

alter system set archive_mode='on';

ARCHIVE_COMMAND

alter system set archive_command='cp %p /home/postgres/pg13/archive_log/%f';

wal_level和archive_mode修改完需要重启数据库，archive_command不需要，只需要reload即可。

执行基础备份

创建repuser用户

create user repuser replication login connection limit 5 password '123456';

创建基础备份

# 构建测试表，插入第一条数据
postgres=# create table bak_test(id serial,insert_time time default localtime);
CREATE TABLE

postgres=# \d bak_test;
                                      Table "public.bak_test"
   Column    |          Type          | Collation | Nullable |               Default
-------------+------------------------+-----------+----------+--------------------------------------
 id          | integer                |           | not null | nextval('bak_test_id_seq'::regclass)
 insert_time | time without time zone |           |          | LOCALTIME
 
postgres=# insert into bak_test(id) values(1);
INSERT 0 1
postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
(1 row)

# 创建基础备份
[postgres@devin-enmo ~]$ pg_basebackup -D /home/postgres/backup/ -Fp -Xs -v -P -h127.0.0.1 -p5432 -Urepuser
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 3/B3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_22226"
31191/31191 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 3/B3000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

构建测试数据

# 插入第二条数据（测试用于恢复到指定时间点）
postgres=# insert into bak_test(id) values(2);
INSERT 0 1
postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
  2 | 15:23:42.02228
(2 rows)


#2，插入第三条数据，创建还原点（测试用于恢复到指定point）
postgres=# insert into bak_test(id) values(3);
INSERT 0 1
postgres=# select pg_create_restore_point('my_restore_point');
 pg_create_restore_point
-------------------------
 3/B40002A8
(1 row)

postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
  2 | 15:23:42.02228
  3 | 15:24:14.376994
(3 rows)


#插入第四条数据，记录事务ID（测试用于恢复到指定事务）
postgres=# insert into bak_test(id) values(4);
INSERT 0 1
postgres=# select txid_current();
 txid_current
--------------
          609
(1 row)

postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
  2 | 15:23:42.02228
  3 | 15:24:14.376994
  4 | 15:24:45.047839
(4 rows)

# 插入第五条数据（测试用于恢复到最近时间点）
postgres=# insert into bak_test(id) values(5);
INSERT 0 1
postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
  2 | 15:23:42.02228
  3 | 15:24:14.376994
  4 | 15:24:45.047839
  5 | 15:25:41.962718
(5 rows)

执行恢复

restore数据库

mv /home/postgres/pg13/data /home/postgres/pg13/data.bak
mv /home/postgres/backup /home/postgres/pg13/data

恢复到指定时间点

# vi postgresql.conf
restore_command = 'cp /home/postgres/pg13/archive_log/%f %p'
recovery_target_time = '2021-10-01 15:23:43'

# 启动数据库
[postgres@devin-enmo data]$ pg_ctl start

# 验证数据
postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
  2 | 15:23:42.02228
(2 rows)

恢复到指定point

# vi postgresql.conf
restore_command = 'cp /home/postgres/pg13/archive_log/%f %p'
recovery_target_name = 'my_restore_point'  

# 启动数据库
[postgres@devin-enmo data]$ pg_ctl start

# 验证数据
postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
  2 | 15:23:42.02228
  3 | 15:24:14.376994
(3 rows)

恢复到指定事务

# vi postgresql.conf
restore_command = 'cp /home/postgres/pg13/archive_log/%f %p'
recovery_target_xid = 609

# 启动数据库
[postgres@devin-enmo data]$ pg_ctl start

# 验证数据
postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
  2 | 15:23:42.02228
  3 | 15:24:14.376994
  4 | 15:24:45.047839
(4 rows)

恢复到最近的时间点

# vi postgresql.conf
restore_command = 'cp /home/postgres/pg13/archive_log/%f %p'
recovery_target_timeline = 'latest' 

# 启动数据库
[postgres@devin-enmo data]$ pg_ctl start

# 验证数据
postgres=# select * from bak_test;
 id |   insert_time
----+-----------------
  1 | 15:21:13.445397
  2 | 15:23:42.02228
  3 | 15:24:14.376994
  4 | 15:24:45.047839
  5 | 15:25:41.962718
(5 rows)