数据库 
首页 > 数据库 > 浏览文章

postgresql连续归档及时间点恢复的操作

(编辑:jimmy 日期: 2024/11/18 浏览:3 次 )

简介

前面我们介绍了通过pgsql的流复制在生产环境中搭建高可用环境来保证服务的可持续性;我们也要对数据库进行周期备份,来防止数据的丢失,这就需要连续归档,它不仅可以用于大型数据库的增量备份和恢复,也可以用于搭建standby镜像备份。 "htmlcode">

#源库
ssh-ketgen -t rsa
scp id_rsa.pub postgres@10.10.10.60:/var/lib/pgsql/.ssh/authorized_keys
#备份库
ssh-ketgen -t rsa
scp id_rsa.pub postgres@10.10.10.61:/var/lib/pgsql/.ssh/authorized_keys

**注意:**yum安装postgresql时,默认生成的postgres用户的家目录在/var/lib/pgsql

2.配置备份库的归档目录

#备份库
mkdir -p /data/pg_archive
chmod postgres.postgres /data/pg_archive

说明:源库产生的归档日志,要存到到异地备份库的/data/pg_archive下。

3.修改源库的postgresql.conf

在postgresql.conf中添加以下几行

#开启归档模式
archive_mode = on   
archive_command = 'ssh 10.10.10.60 test ! -f /data/pg_archive/%f && scp %p 10.10.10.60:/data/pg_archive/%f'

其中: %p表示wal日志文件的路径,%f表示wal日志文件名称。archive_command表示先验证备份库的归档目录下是否存在同名文件,以免发生覆盖丢失数据,若不存在将源库上产生的归档日志保存到备份库的/data/pg_archive目录下。

注意:

(a)archive_timeout强制N秒以后进行一次归档,若设置太小,很快就会超过wal_keep_segments = 16,导致数据覆盖丢失,因此不要盲目设置。

(b)归档模式的开启,只有在wal_level = hot_standby或archive

4.重载源库并查看

pg_ctl reload -D /data/pgsql/data
postgres=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

模拟归档备份

1.查看源库上的pg_xlog目录

-bash-4.2$ ll pg_xlog
total 16388
-rw-------. 1 postgres postgres 16777216 Apr 21 13:42 000000010000000000000001
drwx------. 2 postgres postgres  4096 Apr 21 13:36 archive_status

此时archive_status目录为存放归档日志的状态,若归档已经产生,但没有传输成功则为xxx.ready,并且一直会保留直至传输成功,然后状态变为xxx.done;此时目录为空

2.在源库上添加数据

此时由于数据库为空,我们来创建testdb库,并添加数据

postgres=# create database testdb;
CREATE DATABASE
postgres=# create table t1(id int4,create_time timestamp(0) without time zone);
CREATE TABLE
postgres=# insert into t1 values(1,now());
INSERT 0 1
postgres=# insert into t1 values(2,now());
INSERT 0 1
postgres=# select * from t1;
 id |  create_time  
----+---------------------
 1 | 2016-04-21 13:49:34
 2 | 2016-04-21 13:49:48
(2 rows)

3.在源库上手动切换归档

postgres=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/1821010
(1 row)

正常情况下,wal日志段在达到16M后会自动归档,由于测试我们使用手动切换归档。

4.查看源库pg_xlog目录

-bash-4.2$ ll pg_xlog/
total 16388
-rw-------. 1 postgres postgres 16777216 Apr 21 13:42 000000010000000000000001
drwx------. 2 postgres postgres  4096 Apr 21 13:36 archive_status
-bash-4.2$ ls pg_xlog/
000000010000000000000001 000000010000000000000002 archive_status
-bash-4.2$ ls pg_xlog/archive_status/
000000010000000000000001.ready

此时归档日志的状态为ready,说明此日志没有传输成功,查看日志

vim /data/pgsql/pg_log/postgresql-Thu.log
ssh: connect to host 10.10.10.60 port 22: Connection timed out^M
FATAL: archive command failed with exit code 255
DETAIL: The failed archive command was: ssh 10.10.10.68 test ! -f /data/pg_archive/000000010000000000000001 && scp pg_xlog/000000010000000000000001 10.10.10.60:/data/pg_archive/000000010000000000000001
LOG: archiver process (PID 22284) exited with exit code 1

原来是由于ip地址错误导致无法通过ssh传输,更改ip为10.10.10.61后,再次产生归档才能再次重新传输。

注意:触发归档有三种方式:

1.手动切换wal日志,select pg_switch_xlog()

2.wal日志写满后触发归档,配置文件默认达到16M后就会触发归档,wal_keep_segments = 16

3.归档超时触发归档,archive_timeout

在此我们使用的是手挡切换归档。

postgres=# insert into t1 values(3,now());
INSERT 0 1
postgres=# insert into t1 values(4,now());
INSERT 0 1
postgres=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/2000310
(1 row)
postgres=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/3000000
(1 row)
postgres=# select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/30000D8
(1 row)

再次查看pg_xlog目录

-bash-4.2$ ll pg_xlog/archive_status/
total 0
-rw-------. 1 postgres postgres 0 Apr 21 13:51 000000010000000000000001.done
-rw-------. 1 postgres postgres 0 Apr 21 14:00 000000010000000000000002.done
-rw-------. 1 postgres postgres 0 Apr 21 14:04 000000010000000000000003.done

5.查看备份库上的归档目录

-bash-4.2$ ll /data/pg_archive/
total 49152
-rw-------. 1 postgres postgres 16777216 Apr 21 14:04 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 Apr 21 14:04 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 Apr 21 14:04 000000010000000000000003

至此,归档备份已经完成,下面我们要介绍利用归档进行恢复。

模拟从归档进行PITR恢复

PITR恢复是基于文件系统备份和wal文件的备份,因此首先我们需要个基础备份,然后在此基础备份上对wal归档日志进行回放。具体步骤如下:

1.使用pg_basebackup进行基础备份

pg_basebackup使用replication复制协议,因此需要在源库上配置pg_hba.conf文件以允许replication,无论是本地还是通过网络。

vim pg_hba.conf
#添加以下两行,允许本地和网络上的replication用于pg_basebackup
host replication  rep    127.0.0.1/32   md5
host replication  rep    10.10.10.61/8   md5
#重载
pg_ctl reload -D /data/pgsql/data

添加完毕后请重载pgsql

在备份库上执行pg_basebackup进行远程的基础备份

-bash-4.2$ pg_basebackup -D /data/pgsql/data -Fp -Xs -v -P -h 10.10.10.61 -p 5432 -U rep
Password: 
transaction log start point: 0/5000020
pg_basebackup: starting background WAL receiver
26664/26664 kB (100%), 1/1 tablespace         
transaction log end point: 0/50000E0
pg_basebackup: waiting for background process to finish streaming...
pg_basebackup: base backup completed

-D 表示接受基础备份的目录,我们将基础备份放到/data/pgsql/data

-X 参数,在备份完成之后,会到主库上收集 pg_basebackup 执行期间产生的 WAL 日志,在 9.2 版本之后支持 -Xs 即stream 形式,这种模式不需要收集主库的 WAL 文件,而能以 stream 复制方式直接追赶主库。

2.修改备库上配置文件

由于所有的配置文件是从源库上的备份过来的,因此我们需要修改:

vim postgresql.conf
屏蔽以下两行
#archive_mode = on
#archive_command = 'ssh 192.168.3.139 test ! -f /data/pg_archive/%f && scp %p 192.168.3.139:/data/pg_archive/%f' 

3.查看源库上的时间确认需要的恢复时间点

postgres=# select * from t1;
 id |  create_time  
----+---------------------
 1 | 2016-04-21 13:49:34
 2 | 2016-04-21 13:49:48
 3 | 2016-04-21 14:00:22
 4 | 2016-04-21 14:00:25
 5 | 2016-04-21 14:49:11
 6 | 2016-04-21 14:49:14
 7 | 2016-04-21 14:49:17
(4 rows)

由于此次基础备份是在“ 4 | 2016-04-21 14:00:25”这条记录后归档,而后面的5,6,7三条记录是在基础备份后生成的,因此若恢复5,6,7中的记录需要在基础备份上通过回放5,6,7的归档日志达到。

在此我们要将数据恢复到6这条记录下,需要在recovery.conf中做如下设置:

cp /usr/share/pgsql/recovery.conf.sample /data/pgsql/data/recovery.conf
vim recovery.conf
restore_command = 'cp /data/pg_archive/%f %p'
recovery_target_time = '2016-04-21 14:49:14'

**注意:**recovery.conf中standby_mode要为off,否则备份库将会以备库身份启动,而不是即时恢复。

4.启动备份库

备份库启动过程中,会进行PITR恢复到指定的时间点

pg_ctl start -D /data/pgsql/data
#查看日志
vim /data/pgsql/pg_log/postgresql-Thu.log 
LOG: database system was interrupted; last known up at 2016-04-21 14:34:29 CST
LOG: starting point-in-time recovery to 2016-04-21 14:49:14+08
LOG: restored log file "000000010000000000000005" from archive
LOG: redo starts at 0/5000020
LOG: consistent recovery state reached at 0/50000E0
LOG: restored log file "000000010000000000000006" from archive
LOG: recovery stopping before commit of transaction 1898, time 2016-04-21 14:49:16.635744+08
LOG: redo done at 0/6000398
LOG: last completed transaction was at log time 2016-04-21 14:49:13.786388+08
cp: cannot stat ‘/data/pg_archive/00000002.history': No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat ‘/data/pg_archive/00000001.history': No such file or directory
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
#查看数据
postgres=# select * from t1;
 id |  create_time  
----+---------------------
 1 | 2016-04-21 13:49:34
 2 | 2016-04-21 13:49:48
 3 | 2016-04-21 14:00:22
 4 | 2016-04-21 14:00:25
 5 | 2016-04-21 14:49:11
 6 | 2016-04-21 14:49:14
(6 rows)

7.查看备份库pg_xlog

-bash-4.2$ ll pg_xlog
total 49160
-rw-------. 1 postgres postgres 16777216 Apr 21 15:00 000000010000000000000005
-rw-------. 1 postgres postgres 16777216 Apr 21 15:00 000000010000000000000006
-rw-------. 1 postgres postgres 16777216 Apr 21 15:00 000000020000000000000006
-rw-------. 1 postgres postgres  64 Apr 21 15:00 00000002.history
drwx------. 2 postgres postgres  4096 Apr 21 15:00 archive_status
-bash-4.2$ cat pg_xlog/00000002.history 
1 000000010000000000000006 before 2016-04-21 14:49:16.635744+08

从pg_xlog我们看到设置好recovery.conf文件后,启动数据库,将会产生新的timeline,id=2,而且会生成一个新的history文件00000002.history,里面记录的是新时间线2从什么时间哪个时间线什么原因分出来的,该文件可能含有多行记录。

另外,恢复的默认行为是沿着与当前基本备份相同的时间线恢复。如果你想恢复到某些时间线,你需要指定的recovery.conf目标时间线recovery_target_timeline,不能恢复到早于基本备份分支的时间点。

注意:如果恢复过一次,并重新设置recovery_target_time,重新启动触发恢复,并不会基于时间线1进行恢复,而是基于时间线2进行恢复的,但是此时间线上在/data/pg_archive/并没有时间线为2的归档日志,因此会报错。

补充postgres修改归档模式

步骤一:

修改postgresql的配置文件(postgresql.conf)

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

ps:%p 是指相对路径 %f是指文件名

步骤二:创建归档路径

mkdir -p /home/postgres/arch     
chown -R postgres:postgres /home/postgres/arch

步骤三:重启数据库

步骤四:验证归档是否正常

postgres=# checkpoint;
               CHECKPOINT
      postgres=# select pg_switch_xlog();
       pg_switch_xlog 
       ----------------
       1/760000E8
       (1 row)
     postgres@ubuntu:~$ cd /home/postgres/data/data_1999/arch/
     postgres@ubuntu:~/data/data_1999/arch$ ls
      20150603
     postgres@ubuntu:~/data/data_1999/arch$ cd 20150603/
      postgres@ubuntu:~/data/data_1999/arch/20150603$ ls
      000000010000000100000074 000000010000000100000075 000000010000000100000076

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。

上一篇:PostgreSQL 对IN,EXISTS,ANY/ALL,JOIN的sql优化方案
下一篇:postgresql 13.1 insert into select并行查询的实现
友情链接:杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网 站点导航 SiteMap