pg_basebackup备份恢复

一、pg_basebackup备份与恢复

####pg_basebackup备份

[postgres@pgdkcs pgdata]$ pg_basebackup -D /pgdata/pg_backup/ -Ft -Pv -Upostgres -R
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/13000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot “pg_basebackup_35590”
368780/368780 kB (100%), 2/2 tablespaces
pg_basebackup: write-ahead log end point: 0/13000100
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

####恢复
cd /pgdata/pg_backup/
tar xf base.tar -C /pgdata/12/data
tar xf pg_wal.tar -C /pgdata/arch

vi /pgdata/12/data/postgresql.auto.conf

restore_command = ‘cp /pgdata/arch/%f %p’
recovery_target = ‘immediate’

touch /pgdata/12/data/recovery.signal

pgctl start

select pg_wal_replay_resume();

二、PITR实战
场景介绍：每天23:00 PBK备份，周二下午14:00误删除数据，如何恢复？

1、恢复全备数据 tar xf xxx -C yyyy
2、归档恢复，归档备份+ 23点-14点区间的归档 + 在线redo

===模拟测试
postgres=# create database pitdb;
CREATE DATABASE
postgres=# \c pitdb;
You are now connected to database “pitdb” as user “postgres”.
pitdb=# create table t1(id int);
CREATE TABLE

pitdb=# create table t1(id int);
CREATE TABLE
pitdb=# insert into t1 values (1);
INSERT 0 1
pitdb=# insert into t1 values (2);
INSERT 0 1
pitdb=# insert into t1 values (3);
INSERT 0 1
pitdb=# select * from t1;
 id 
----
  1
  2
  3

----进行全备
pg_basebackup -D /pgdata/pg_backup/ -Ft -Pv -Upostgres -R

----继续其他操作

pitdb=# create table t2 (id int);
CREATE TABLE
pitdb=# insert into t2 values (1);
INSERT 0 1
pitdb=# insert into t2 values (111);
INSERT 0 1
pitdb=# select * from t2;
 id  
-----
   1
 111

pitdb=# \c postgres
You are now connected to database “postgres” as user “postgres”.
postgres=# drop database pitdb;
DROP DATABASE

–进行恢复到drop database pitdb 前
$ tar -xf base.tar -C /pgdata/restorbak
$ tar xf pg_wal.tar -C /pgdata/restorbak/arch

–查看要恢复的点（解析归档）
pg_waldump 000000010000000000000019 <<<<<,846

–修改postgresql.auto.conf
[postgres@pgdkcs restorbak]$ pwd
/pgdata/restorbak

[postgres@pgdkcs restorbak]$ vi postgresql.auto.conf

restore_command = ‘cp /pgdata/arch/%f %p’
recovery_target_xid = ‘436’

–启动
pg_ctl start -D /pgdata/restorbak -l /pgdata/restorbak/alert.log

–最后执行，数据库才可写
select pg_wal_replay_resume();