Postgresql之通过wal日志恢复数据到任意时间点

wal日志备份

目录规划

1 2 3 4

/home/mnt2/postgresql_basebak/data #基础备份目录 /home/mnt2/postgresql_recover/data #还原目录 /home/mnt2/postgresql_increase_basebak/ #每个月基础备份目录 /home/mnt2/postgresql_walbak/ #wal日志备份目录

将mnt2下所有内容赋权给postgres用户

参数调整

1 2 3 4 5 6 7 8 9 10

checkpoint_completion_target = 0.1 checkpoint_timeout = 20min wal_level =replica wal_log_hints = on wal_compression = on archive_mode = on wal_keep_segments = 64 archive_command = './archive.sh %p %f >> walbak.log' max_worker_processes=10 max_wal_senders = 8

创建归档脚本

(archive.sh 每天生成一个目录存放当天生成的wal日志)

#!/bin/bash

export LANG=en_US.UTF-8

export PGHOME=/usr/pgsql-9.6

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH

export DATE=`date +"%Y%m%d"`

export PATH=$PGHOME/bin:$PATH:.

BASEDIR="/home/mnt2/postgresql_walbak"

if [ ! -d $BASEDIR/$DATE ]; then

mkdir -p $BASEDIR/$DATE

if [ ! -d $BASEDIR/$DATE ]; then

echo "error mkdir -p $BASEDIR/$DATE"

exit 1

fi

fi

cp $1 $BASEDIR/$DATE/$2

echo `date "+%Y-%m-%d %H:%M:%S"` $1 " to " $BASEDIR/$DATE/$2

if [ $? -eq 0 ]; then

exit 0

else

echo -e "cp $1 $BASEDIR/$DATE/$2 error"

exit 1

fi

echo -e "backup failed"

exit 1

赋予可执行权限

chmod 700 archive.sh

检查一下归档是否正常

，必须是超级用户

1 2 3

checkpoint; select pg_switch_xlog(); select pg_xlogfile_name(pg_switch_xlog());

You are now connected to database "MH" as user "postgres".

MH=# checkpoint;

CHECKPOINT

MH=# select pg_switch_xlog();

pg_switch_xlog

----------------

8/BC7D5B98

(1 row)

MH=# select pg_xlogfile_name(pg_switch_xlog());

pg_xlogfile_name

--------------------------

0000000100000008000000BD

(1 row)

MH=#

数据库基础备份

使用pg_basebackup进行基础备份

pg_basebackup备份数据库需要用到超级用户或者replication角色用户

本示例中用的是postgres用户

配置pg_hba.conf

host replicationpostgres 127.0.0.1/32 trust

基础备份命令

pg_basebackup -D /home/mnt2/postgresql_basebak/data -F p -X stream -h 127.0.0.1 -p 1025 -U postgres -w

PITR还原举例

注意任何时候都不应该在/home/mnt2/postgresql_basebak/data直接恢复.应该把基础备份拷贝到/mnt2/postgresql_recover/data下进行还原,不应该破坏基础备份

删除/mnt2/postgresql_recover/data备份文件下pg_xlog和pg_clog的文件

1 2	rm -rf /home/mnt2/postgresql_recover/data/pg_xlog/* rm -rf /home/mnt2/postgresql_recover/data/pg_clog/*

拷贝recovery.conf

拷贝recovery.conf到/mnt2/postgresql_recover/data

1	cp /usr/pgsql-9.6/share/recovery.conf.sample /home/mnt2/postgresql_recover/data/recovery.conf

创建恢复脚本recovery.sh

export LANG=en_US.utf8

export PGHOME=/usr/pgsql-9.6

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH

export DATE=`date +"%Y%m%d"`

export PATH=$PGHOME/bin:$PATH:.

BASEDIR=$1

find $BASEDIR -name $2 -exec cp {} $3 ;

赋予可执行权限

1	chmod 700 recovery.sh

修改 recovery.conf 文件,关闭wal归档

修改 recovery.conf

1 2 3 4

restore_command = './recovery.sh /home/mnt2/postgresql_walbak %f %p' # e.g. 'cp recovery_target_time = '2018-03-2913:57:27.033134+08' recovery_target_inclusive = false recovery_target_timeline = 'latest'

修改postgresql.conf

1	archive_mode = off

启动数据库(如果在同一台机器,可以改变postgresql.conf的port端口不影响生产库)查看数据

说明

sudo -u postgres /usr/pgsql-9.6/bin/postgres -D /home/mnt2/postgresql_recover/data

recovery_target_timeline 不能恢复到早于基本备份分支的时间点。

时间线

每当归档文件恢复完成后，创建一个新的时间线用来区别新生成的WAL记录。WAL文件名由时间线和日志序号组成00000002.history,

时间线ID号是WAL文件名组成之一，因此一个新的时间线不会覆盖由以前的时间线生成的WAL。

每个时间线类似一个分支，在当前时间线的操作不会对其他时间线WAL造成影响。有了时间线，我们就可以恢复到之前的任何时间点。

自动备份

备份脚本(/mnt/postgresql/basebackupscript/backup.sh)

#!/bin/bash

export LANG=en_US.utf8

export PGHOME=/usr/pgsql-9.6

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH

export DATE=`date +"%Y%m%d"`

export PATH=$PGHOME/bin:$PATH:.

BASEDIR="/home/mnt2/postgresql_increase_basebak"

if [ ! -d $BASEDIR/$DATE/data ]; then

mkdir -p $BASEDIR/$DATE/data

pg_basebackup -D $BASEDIR/$DATE/data -F p -X stream -h 127.0.0.1 -p 1025 -U postgres –w

每月1号凌晨1点开始备份

0 1 1 * * postgres sh /home/mnt/postgresql/basebackupscript/backup.sh >>/home/mnt/postgresql/basebackupscript/backup.log

注意：

1.mnt2下所有赋权给postgres用户2.脚本改为700权限，recovery.conf,recovery.sh,还有postgresql_revcover目录下的data目录3.恢复时，将基础数据拷贝到recovery目录后，删除pg_xlog,pg_clog下内容；4.注意恢复到指定时间的格式，在recovery.conf文件中的5.注意 backup_label 名称是否被改变（我这里由于其它原因启动失败时，出现了backup_label.old，如果出现，改回原来的名称）6.在同一台机器上恢复，注意端口。（比如：生产库占用了1025，恢复时需要指定另一个端口。数据恢复前，设置archive_mode = off，数据恢复完后，停掉生产库，设置archive_mode = on.重启，此时恢复的库变为主库）