Postgresql - 恢复数据库到指定时间 Point-in-Time Recovery (PITR)

在有些时候需要用备份数据恢复到任意的时间点，该如何操作。

###################################

CentOS 7 + pg 10.4

###################################

1. 初始化数据库参数

# 创建archive目录，并授权到postgres用户

mkdir /data/archive/

chown -R postgres:postgres /data/archive/

# 修改数据库配置文件

vim /usr/local/pgsql/data/postgresql.conf

archive_mode = onarchive_timeout = 300 # 单位是秒，此处以5分钟为限强制归档，仅作测试archive_command = 'cp -i %p /data/archive/%f' # 会将pg_wal中的日志cp过去。wal_level = archive

# 初始化数据

# 创建表所用序列

create sequence seq_test01_id;

# 创建表

create table test01(

id int not null default nextval('seq_test01_id'::regclass) primary key,

col1 varchar(128),

ctime time);

2. 做基础备份

# 开启热备

psql -U postgres -d postgres -c "select pg_start_backup('label');"

# 备份data目录

cd /usr/local/pgsql/data

tar -cvf /data/backup/data.tar *

# 关闭热备

psql -U postgres -d mytest -c "select pg_stop_backup();"

# 切日志

psql -U postgres -d mytest -c "select pg_switch_wal();"

3.

##########################################

# 为表插入数据

create or replace function insert_test01() returns void

AS $$

begin

for i in 1..10 loop

PERFORM pg_sleep(2);

insert into test01(col1,ctime) values('aaa', now());

end loop;

end;

$$ LANGUAGE plpgsql;

select insert_test01();

##########################################

# 为表插入数据

insert into test01(col1,ctime) values('aaa', now());

select pg_sleep(2);

insert into test01(col1,ctime) values('aaa', now());

select pg_sleep(2);

insert into test01(col1,ctime) values('aaa', now());

select pg_sleep(2);

insert into test01(col1,ctime) values('aaa', now());

select pg_sleep(2);

insert into test01(col1,ctime) values('aaa', now());

select pg_sleep(2);

insert into test01(col1,ctime) values('aaa', now());

select pg_sleep(2);

insert into test01(col1,ctime) values('aaa', now());

select pg_sleep(2);

insert into test01(col1,ctime) values('aaa', now());

mytest=# select * from test01 ;

id | col1 | ctime

----+------+-----------------

1 | aaa | 05:52:03.675805

2 | aaa | 05:52:05.687527

3 | aaa | 05:52:07.695315

4 | aaa | 05:52:09.702641

5 | aaa | 05:52:11.711283

6 | aaa | 05:52:13.718943

7 | aaa | 05:52:15.726526

8 | aaa | 05:52:19.710031

(8 rows)

# 损坏数据

select now();

2018-06-19 06:03:29.391014-04

delete from test01 ;

4. 恢复

# 停服务

service postgresql stop

# 把之前的备份文件恢复到data目录

cd /usr/local/pgsql/data

rm -rf *

tar xvf data.tar

# 配置xlog文件

rm -rf pg_xlog

mkdir -p pg_xlog/archive_status

chown -R postgres:postgres pg_xlog

# 修改recovery.conf配置，恢复数据库到指定时间点

vim recovery.conf

restore_command = 'cp /data/archive/%f "%p"' # archive 目录

archive_cleanup_command='pg_archivecleanup /data/archive %r'

recovery_target_time='2018-06-19 06:03:05'

# 启动数据库服务

service postgresql start

5. 查看恢复数据

psql -U mytest -d mytest

mytest=# select * from test01 ;

id | col1 | ctime

----+------+-----------------

1 | aaa | 06:02:53.992508

2 | aaa | 06:02:56.111994

3 | aaa | 06:02:58.11978

4 | aaa | 06:03:00.127555

5 | aaa | 06:03:02.134798

6 | aaa | 06:03:04.142521