postgres9.5.7 wal日志归档和任意时间点恢复数据

wal日志备份

• 目录规划

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

• 参数调整

checkpoint_completion_target = 0.1
checkpoint_timeout = 40min
wal_level = hot_standby
wal_log_hints = on
wal_compression = on
archive_mode = on
wal_keep_segments = 256
archive_command = './archive.sh %p %f >> walbak.log'
max_worker_processes=16
max_wal_senders = 10

• 更改表的压缩比(选做)

 alter table test set (fillfactor=90);

• 创建归档脚本(archive.sh 每天生成一个目录存放当天生成的wal日志)

#!/bin/bash
export LANG=en_US.UTF-8
export PGHOME=/usr/pgsql-9.5
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="/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

• 重新加载配置文件

pg_ctl reload -D $PGDATA

• 检查一下归档是否正常

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

数据库基础备份

• 使用pg_basebackup进行基础备份

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

   创建用户
   create role replica_walbak login replication encrypted password 'replica';
   配置pg_hba.conf
   host    replication     replica_walbak        127.0.0.1/32            md5  
   重新加载配置
   pg_ctl reload -D $PGDATA
  

  • pg_basebackup免密码操作(使用系统用户postgres操作),在当前用户下的根目录下创建.pgpass文件,修改文件为以下内容

  touch  ~/.pgpass
  127.0.0.1:5432:*:replica_walbak:replica
  pg_ctl -D $PGDATA reload

  • 基础备份

  pg_basebackup -D /mnt2/postgresql_basebak/data -F p -X stream -h 127.0.0.1 -p 5432 -U replica_walbak -w

PITR还原举例

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

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

  rm -rf /mnt2/postgresql_recover/data/pg_xlog/*
  rm -rf /mnt2/postgresql_recover/data/pg_clog/*

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

  cp /usr/pgsql-9.5/share/recovery.conf.sample /mnt2/postgresql_recover/data/recovery.conf

• 创建恢复脚本(recovery.sh)

  
  #!/bin/bash
  
  export LANG=en_US.utf8  
  export PGHOME=/usr/pgsql-9.5
  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 \; 

• 赋予可执行权限

  chmod 700 recovery.sh

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

  restore_command = './recovery.sh /mnt2/postgresql_walbak %f %p'            # e.g. 'cp /mnt/server/archivedir/%f %p'  
  recovery_target_time = '2017-07-27 15:39:57.033134+08'  
  recovery_target_inclusive = false  
  recovery_target_timeline = 'latest' 

• 修改postgresql.conf

  archive_mode = off

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

• 说明

  • 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.5
  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="/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 5432 -U replica_walbak -w
  fi
  

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

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

———2017-11-25 补充———

有可能wal日志要备份到另一台机器上

• 需要ssh两台机器互信

#!/bin/bash
export LANG=en_US.UTF-8
export PGHOME=/usr/pgsql-9.5
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="/mnt2/postgresql_walbak"

ssh 192.168.1.100 "[ -d $BASEDIR/$DATE ]" >/dev/null 2>&1
if [ $? != 0 ]; then
    ssh 192.168.1.100 mkdir -p $BASEDIR/$DATE
        ssh 192.168.1.100 "[ -d $BASEDIR/$DATE ]" >/dev/null 2>&1
        if [ $? != 0 ]; then
                echo "error mkdir -p $BASEDIR/$DATE"
        exit 1
        fi
fi

scp $1 192.168.1.100:$BASEDIR/$DATE/$2

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

if [ $? -eq 0 ]; then
  exit 0
else
  echo -e "cp $1 192.168.1.62:/$BASEDIR/$DATE/$2 error"
  exit 1
fi
echo -e "backup failed"
exit 1

参考:

https://yq.aliyun.com/articles/59359 
https://yq.aliyun.com/articles/234