PostgreSQL Incremental Backup and Point In Time Recovery using Archive Log

Описание

Digital Ocean : https://m.do.co/c/02680c522b4f


sudo -u postgres mkdir basebackup
sudo -u postgres mkdir wal_archive




Wal_Level =replica or archive
archive_mode=on
archive_command = 'cp -i %p /var/lib/pgsql/12/wal_archive/%f'
archive_timeout = 60




--- Normal recovery


sudo -u postgres psql -c "SELECT pg_switch_wal();"


sudo -u postgres pg_basebackup -D /var/lib/pgsql/12/basebackup -Ft -z -P -Xs
sudo -u postgres pg_basebackup -D /var/lib/pgsql/12/basebackup -Ft -P




rm -rf /var/lib/pgsql/12/data/*
sudo -u postgres mkdir /var/lib/pgsql/12/data/pg_wal


restoration :


tar -xvf /var/lib/pgsql/12/basebackup/base.tar -C /var/lib/pgsql/12/data/
tar -xvf /var/lib/pgsql/12/basebackup/pg_wal.tar -C /var/lib/pgsql/12/data/pg_wal




Restore_command = 'cp /var/lib/pgsql/12/wal_archive/%f %p'




--- PITR
create database test_db1;


\c test_db1;
create table test_tbl1(id int,name varchar(255));
insert into test_tbl1
SELECT generate_series(1,10) AS id, md5(random()::text) AS descr;
select count(1) from test_tbl1; - 10
select now();
SELECT pg_switch_wal();


rm -rf /var/lib/pgsql/12/basebackup/*
sudo -u postgres pg_basebackup -D /var/lib/pgsql/12/basebackup -Ft -P




\c test_db1;
insert into test_tbl1
SELECT generate_series(1,10) AS id, md5(random()::text) AS descr;
select count(1) from test_tbl1; /* 20 */
select now(); /* restore point 2021-09-23 11:03:25 */




insert into test_tbl1
SELECT generate_series(1,10) AS id, md5(random()::text) AS descr;
select count(1) from test_tbl1; /* 30 recoreds */
select now();


sudo systemctl stop postgresql-12


rm -rf /var/lib/pgsql/12/data/*
sudo -u postgres mkdir /var/lib/pgsql/12/data/pg_wal


tar -xvf /var/lib/pgsql/12/basebackup/base.tar -C /var/lib/pgsql/12/data/
no need to restore wal file
2021-09-23 09:36:21




sudo -u postgres vi /var/lib/pgsql/12/data/recovery.signal
restore_command = 'cp /var/lib/pgsql/12/wal_archive/%f %p'
recovery_target_time = '2021-09-23 11:03:25'


select pg_wal_replay_resume();


select count(1) from test_tbl1; /* 20 */


reference :
https://www.digitalocean.com/community/tutorials/how-to-set-up-continuous-archiving-and-perform-point-in-time-recovery-with-postgresql-12-on-ubuntu-20-04
https://pgdash.io/blog/postgres-incremental-backup-recovery.html