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