PostgreSQL15-增量备份

15.增量备份

PIRT （point-in-timt recovery）时间点恢复
发生wal日志切换，就会触发归档
1.手动切换
2.写满16M
3.达到规定时间间隔（隐含条件：当前WAL日志中仍有未归档的WAL，才会归档）

创建WAL日志归档目录
mkdir -p /pgdata/10/{backups,archive_wals}
chown -R postgres:postgres /pgdata/10

开启WAL归档
vim postgresql.conf
wal_level = 'replica'  
archive_mode = on
archive_command = '/usr/bin/gzip %p  /var/lib/pgsql/9.6/archive_wals/%f.gz'
max_wal_senders = 1

archive_command = 'DATE=`date +%Y%m%d`;DIR="/home/arch/$DATE";\
                   (test -d $DIR||mkdir -p $DIR) && cp /home/data/%p $DIR/%f'

wal_level 和 archive_mode 重启数据库生效，一般在安装完成后就打开这两项，并将archive_command设置为/bin/true，需要归档时只需要改archive_command即可

权限
vim pg_hba.conf
local   replication     postgres      md5

创建基础备份,生成base.tar.gz
pg_basebackup -Ft -Pv -Xf -z -Z5 -P 5432 -D /pgdata/10/backups/

插入测试数据
create table t2(id serial primary key, description text, created_time timestamptz not null default now());
insert into t2(description) values(1);

查看当前日志序号(log sequence number)并触发归档
select pg_walfile_name(pg_current_wal_lsn());  pg10 
select pg_current_xlog_location();    pg9

postgres=# select pg_current_wal_lsn(),
     pg_walfile_name(pg_current_wal_lsn()),            
     pg_walfile_name_offset(pg_current_wal_lsn());

postgres=# select pg_current_xlog_location(),
     pg_xlogfile_name(pg_current_xlog_location()),
     pg_xlogfile_name_offset(pg_current_xlog_location());
      

select pg_switch_wal();       pg10
select pg_switch_xlog();      pg9

模拟数据丢失
su - postgres
pg_ctl stop -mi
rm -rf /database/pg10/pg_root

恢复基础数据
tar xzf /pgdata/10/backups/base.tar.gz -C /database/pg10/pg_root/

cp /opt/psql/share/recovery.conf/sample /database/pg10/pg_root/recovery.conf
chmod 0600 /database/pg10/pg_root/recovery.conf

----------------------------------
PG的share目录下有样例，复制到数据目录

恢复增量数据
vim recovery.conf
restore_command = '/usr/bin/lz4 -d /pgdata/10/archive_wals/%f.lz4 %p'

恢复到最近时间点
restore_target_timeline = 'latest'

恢复到指定时间点
select current_timestamp();
recovery_target_time = '2020-02-01 16:27:22.184399+08'

恢复到指定还原点
select pg_create_restore_point('restore_point'); # 创建名称为restore_point的还原点
restore_target_name = 'restore_point'

恢复到指定事务
BEGIN;
select txid_current(); # 查看未删除前的事务ID
delete from product; # 模拟误删除
END;
restore_target_name = '500'

恢复到指定时间线
pg_controldata | grep TimeLineTD
recovery_target_timeline = 2
recovery_target_time = '2020-01-20 23:00:00'  # 感觉这种方式选择的时间自由

pg_ctl start

可能需要
mv recovery.conf recovery.done

archive_command = 'DATE=`date +%Y%m%d`;DIR="/var/lib/pgsql/9.6/arch/$DATE";\
                   (test -d $DIR||mkdir -p $DIR) && cp /var/lib/pgsql/9.6/data/%p $DIR/%f'

vim pg_backup.sh
DATA=`data +%Y%m%d`
pg_basebackup -Ft -Pv -Xf -z -Z5 -P 5432 -D /var/lib/pgsql/9.6/backup/$DATA.tar.gz

1.停止数据库服务
systemctl stop postgresql-9.6
2.将data目录改名
mv /var/lib/pgsql/9.6/data{,_old}
3.将基础备份数据解压至data目录
mkdir /var/lib/pgsql/9.6/data
tar zxf /var/lib/pgsql/9.6/backup/$DATA.tar.gz/ -C /var/lib/pgsql/9.6/data
4.拷贝旧data目录中的wal归档至新的data目录下的归档目录
cp /var/lib/pgsql/9.6/data_old/pg_xlog/* /var/lib/pgsql/9.6/data/pg_xlog/
5.在data目录下创建recovery.conf,并填写恢复命令和恢复程度
vim /var/lib/pgsql/9.6/data/recovery.conf
restore_command = 'test ! -f /var/lib/pgsql/9.6/data/pg_xlog/%f && cp /var/lib/pgsql/9.6/arch/%f %p'
recovery_target_timeline = 'latest'
6.确定文件权限无误后，重启数据库服务
systemctl start postgresql