setting up pitrtools

setting up pitrtools

pitrtools也是一种管理postgresql备库的工具集, 由2个python编写的小脚本组成
cmd_archiver, 调用rsync复制归档日志文件, 支持向多个备用库服务器传输文件, 这里简单介绍了传输文件的流程PITRTools: Multiple slave support
cmd_standby, 将数据库从主库备份到备库, 调用pg_standby在备库上应用归档日志


1. 测试环境:
primary(master) db: pgdb01
standby(slave) db: pgdb02
db version: Postgresql 8.3.7
os version: CentOS 5.3


2. 配置ssh登录
略, 见前篇博客setting up postgresql warm standby


3. 安装Pitrtools
登录pgdb01,pgdb02, 下载安装

wget -O - http://files.commandprompt.com/pitrtools/pitrtools-1.2.tar.bz2|tar -C $HOME -jxf -
chmod +x $HOME/pitrtools-1.2/{cmd_archiver,cmd_standby}

4. 主库配置archiver

编辑配置文件

cat >$HOME/pitrtools-1.2/cmd_archiver.ini <<EOF
[DEFAULT]
state: online
pgdata: /var/lib/pgsql/data
r_archivedir: /var/lib/pgsql/archive
l_archivedir: /var/lib/pgsql/archive
rsync_bin: /usr/bin/rsync
rsync_version = 2
slaves: pgdb02
user: postgres
timeout: 10
notify_ok: echo OK
notify_warning:  echo WARNING
notify_critical: echo CRITICAL
debug: on
ssh_debug: off
EOF

初始化建归档目录

$HOME/pitrtools-1.2/cmd_archiver -C $HOME/pitrtools-1.2/cmd_archiver.ini -I

We are initializing queues, one moment.

NOTICE: init_env_func()
NOTICE: generate_slave_list_func()
NOTICE: Your slaves are: ['pgdb02']

为备库建归档日志目录

ssh pgdb02 "mkdir -p /var/lib/pgsql/archive"

为cmd_standby创建辅助函数

psql -U postgres <$HOME/pitrtools-1.2/cmd_standby.sql

5. 主库打开归档模式

sed -i "
\$a \
archive_mode = 'on'\\
archive_command = '\$HOME/pitrtools-1.2/cmd_archiver -C \$HOME/pitrtools-1.2/cmd_archiver.ini -F %p \>\>\$HOME/pitrtools-1.2/cmd_archiver.\$(date \"+%%Y%%m%%d\").log'
/^archive_timeout/s/^/#/g
/^archive_mode\|^archive_command/d
" /var/lib/pgsql/data/postgresql.conf

pg_ctl restart -m fast

6. 备库配置standby

cat >$HOME/pitrtools-1.2/cmd_standby.ini <<EOF
[DEFAULT]
pgversion: 8.3
numarchives: 10
ssh: /usr/bin/ssh
rsync: /usr/bin/rsync
pg_standby: /usr/bin/pg_standby
pg_ctl: /usr/bin/pg_ctl
r_psql: /usr/bin/psql
port: 5432
master_public_ip: pgdb01
master_local_ip: 127.0.0.1
user: postgres
debug: off
ssh_timeout: 30
archivedir: /var/lib/pgsql/archive
pgdata: /var/lib/pgsql/data
postgresql_conf: $HOME/pitrtools-1.2/postgresql.conf
pg_hba_conf: $HOME/pitrtools-1.2/pg_hba.conf
notify_critical: echo critical.
notify_warning: echo warning.
notify_ok: echo ok.
action_failover: echo failover.
EOF

初始化创建备库目录

scp pgdb01:/var/lib/pgsql/data/postgresql.conf $HOME/pitrtools-1.2/
scp pgdb01:/var/lib/pgsql/data/pg_hba.conf $HOME/pitrtools-1.2/
sed -i '/^archive_/s/^/#/g' $HOME/pitrtools-1.2/postgresql.conf
rm -rf /var/lib/pgsql/data
$HOME/pitrtools-1.2/cmd_standby -C $HOME/pitrtools-1.2/cmd_standby.ini -I

7. 备份主库到备库

$HOME/pitrtools-1.2/cmd_standby -C $HOME/pitrtools-1.2/cmd_standby.ini -B

8. 启动备库

cp -p $HOME/pitrtools-1.2/{postgresql.conf,pg_hba.conf} /var/lib/pgsql/data/
$HOME/pitrtools-1.2/cmd_standby -C $HOME/pitrtools-1.2/cmd_standby.ini -S

至此备库配置完成


9. 测试主库是否连通

$ $HOME/pitrtools-1.2/cmd_standby -C $HOME/pitrtools-1.2/cmd_standby.ini -P
SUCCESS: Master returned: 1

10. 打开备库

$ $HOME/pitrtools-1.2/cmd_standby -C $HOME/pitrtools-1.2/cmd_standby.ini -F999
waiting for server to shut down... done
server stopped
server starting
failover.
NOTICE: Statistics are not replicated in warm standy mode.
HINT: Execute ANALYZE on your databases

11. 总结
同Walmgr相比, pitrtools用户必须手工创建一些目录和修改配置文件, 不够自动化, 不能保留主库备份, 程序也写得略显粗糙, 不够专业, 有不少有待完善的地方




外部链接:
Pitr Made Easy



-fin-