postgres备份与恢复资料

创建归档目录

/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)