043.PGSQL-备份和恢复-重做WAL日志-恢复到最近时间点

修改配置文件 postgresql.conf

[root@s101 /var/lib/pgsql/13/data]#nano postgresql.conf

archive_command ='test ! -f /var/lib/pgsql/13/archive_wals/%f && cp %p /var/lib/pgsql/13/archive_wals/%f'
restore_command = 'cp /var/lib/pgsql/13/archive_wals/%f %p'
recovery_target_timeline = 'latest'

重启数据库

[root@s101 /var/lib/pgsql/13/data]#sudo systemctl restart postgresql-13

插入条数据

insert into "myschema"."o_ls_test"(
rid
,name
)
select n ,n || 'EEE' || n
from
generate_series(1,3) n ;

手动进行一次wal切换

select pg_switch_wal();

创建基础备份

/usr/pgsql-13/bin/pg_basebackup   -D /var/lib/pgsql/13/backups  -Ft  -Pv  -Xf  -z  -Z5  -h 192.168.17.101 -U postgres  -p 5432

插入条数据

insert into "myschema"."o_ls_test"(
rid
,name
)
select n ,n || 'new' || n
from
generate_series(1,3) n ;

手动进行一次wal切换

select pg_switch_wal();

关闭数据库

[root@s101 /var/lib/pgsql/13/data]#sudo systemctl stop postgresql-13

将data目录备份

[root@s101 /var/lib/pgsql/13]#mv data dataold

创建data目录

[postgres@s101 /var/lib/pgsql/13]$mkdir data

给文件权限

[root@s101 /var/lib/pgsql/13]#chmod 700 data

解压基础备份到data目录

[postgres@s101 /var/lib/pgsql/13]$tar -xvf /var/lib/pgsql/13/backups/base.tar.gz -C /var/lib/pgsql/13/data

data目录中创建 recovery.signal

[postgres@s101 /var/lib/pgsql/13/data]$ touch recovery.signal

重启数据库

[root@s101 /var/lib/pgsql/13/data]#sudo systemctl restart postgresql-13

查看数据库状态

[root@s101 /var/lib/pgsql/13]#systemctl status postgresql-13

查看基础备份后的数据是否已经恢复

mydb@[local]:5432=#select * from mydb.myschema.o_ls_test;
rid | name | timeflag
-----+-------+----------------------------
1 | 1EEE1 | 2021-07-29 19:28:30.102212
2 | 2EEE2 | 2021-07-29 19:28:30.102603
3 | 3EEE3 | 2021-07-29 19:28:30.10261
1 | 1new1 | 2021-07-29 20:13:35.989382
2 | 2new2 | 2021-07-29 20:13:35.98944
3 | 3new3 | 2021-07-29 20:13:35.989449
(6 rows)