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   replication   postgres     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直接恢复.应该把基础备份拷贝到/home/mnt2/postgresql_recover/data下进行还原,不应该破坏基础备份

删除/home/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-29 13: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.重启，此时恢复的库变为主库）