创建归档目录
/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)
标签：15,postgres,备份,id,pg,资料,test,bak
From： https://www.cnblogs.com/zhhy/p/17035752.html