Allow standby recovery to switch to a new timeline automatically (Heikki Linnakangas)
Now standby servers scan the archive directory for new timelines periodically .
解释一下什么是时间线文件 : 文件名如 000000xx.history
这个文件在standby 数据库被 promote 时产生，放在被promote的standby数据库的pg_xlog目录 。
这个文件产生的同时，被promote的数据库的timeline随之改变。可以在被promote的数据库的服务器上执行pg_controldata查看到Latest checkpoint's TimeLineID : 这条记录的改变 。
假设以上的切换是在primary节点干净的关闭(-m fast或-m smart)数据库的情况下做的，并且关库过程中standby节点和primary节点保持畅通，那么正常情况下standby节点将接收到primary节点关库后的最后一次checkpoint信息。也就是说，standby在promote后，原来的主节点可以作为新的主节点的standby库的基础备份，但是需要设置recovery.conf的recovery_target_timeline = 'latest' , 并且需要000xxxx.history这个文件作为恢复文件。其实就是本文开篇时提到的这个动作 “
scan the archive directory for new timelines periodically . "
PostgreSQL 9.0 已经存在这个过程，可以参考我以前写的BLOG ：
下面来测试一下9.1 :
首先是创建一个primary-standby环境。
primary库操作 :
1. 新建Primary 数据库，略 .
2. 修改Primary 数据库相关参数 .
3. 新增replica 用户.
4. 修改pg_hba.conf
vi $PGDATA/postgresql.conf
wal_level = hot_standby
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 32
wal_sender_delay = 10ms
wal_keep_segments = 500
replication_timeout = 60s
# 以下部分可以先配置好,省的待会切换为standby的时候再配置
hot_standby = on
wal_receiver_status_interval = 10s
hot_standby_feedback = on3. 新增replica 用户.
postgres=# create role replica nosuperuser nocreatedb nocreaterole noinherit login replication encrypted password 'Replica_123';4. 修改pg_hba.conf
增加一条
5. 新建~/.pgpass文件,省的待会切换为standby的时候再配置.为对方的IP。
host replication replica 0.0.0.0/0 md55. 新建~/.pgpass文件,省的待会切换为standby的时候再配置.为对方的IP。
vi /home/postgres/.pgpass
standby库操作 :
172.16.3.xx:1921:replication:replica:Replica_123
chmod 400 /home/postgres/.pgpassstandby库操作 :
1. 安装PostgreSQL软件
编译条件与原库一样。
2. 编译与主库一样PG插件,
如果主库新增了插件，那么备库需要新增一样的插件。
譬如说我的主库新增了pgfincore和oracle_fdw的插件，那么在备库也要新增这两个插件，否则使用到这些插件的时候会报错。甚至数据库会起不来。
3. 新建相关目录(主数据目录,表空间目录,pg_xlog目录,或者赋予对应的父目录权限,日志目录)。
如果不使用pg_basebackup来建立standby的话，这些操作会更加明朗一下。
4. 新增.pgpass , 为对方的IP。
vi /home/postgres/.pgpass
5. 使用pg_basebackup这个9.1新增的命令来创建standby基备。在此之前, 重启一下主库使之配置生效.
6. 拷贝/opt/pgsql/share/recovery.conf.example到$PGDATA下面
172.16.3.xx:1921:replication:replica:Replica_123
chmod 400 /home/postgres/.pgpass5. 使用pg_basebackup这个9.1新增的命令来创建standby基备。在此之前, 重启一下主库使之配置生效.
postgres@172_168_3_xx-> pg_basebackup -D $PGDATA -F p -x -l basebackup -P -v -h 172.16.3.xxx -p 1921 -U replica
WARNING: skipping special file "./.s.PGSQL.1921"
xlog start point: 1/28000020
WARNING: skipping special file "./.s.PGSQL.1921"pgdata/pg_root/global/12)
2209131/2209131 kB (100%), 2/2 tablespaces
xlog end point: 1/280001F8
pg_basebackup: base backup completed6. 拷贝/opt/pgsql/share/recovery.conf.example到$PGDATA下面
修改
postgres@172_168_3_xx-> cat recovery.done |grep -v "^#"
7. 启动standby数据库
至此环境搭建完毕。
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.16.3.xx port=1921 user=replica' # e.g. 'host=localhost port=5432'
trigger_file = '/home/pgdata/pg_root/postgresql.trigger.1921'7. 启动standby数据库
pg_ctl start -D $PGDATA至此环境搭建完毕。
接下来测试的是角色切换，譬如说我们要对主库添加内存或者维护主机的服务器或者存储。需要把主库停掉。但是在维护完后还要起来。
维护可能需要几个小时，业务肯定受不了。那么需要激活备库先顶一段时间。
接下来就是整个切换过程。
一、 检查操作：
检查主库和备库的复制正常进行中。
二、 主库操作：
停止主库 , 千万不要用-m immediate,这个不会产生关机checkpoint,也不会发送checkpoint给standby。会造出过程不可逆。
停库日志解析 :
pg_ctl stop -m fast -D $PGDATA停库日志解析 :
2011-09-23 12:55:13.002 CST,,,6764,,4e7c0cca.1a6c,3,,2011-09-23 12:36:26 CST,,0,LOG,00000,"received fast shutdown request",,,,,,,,,""
2011-09-23 12:55:13.002 CST,,,6764,,4e7c0cca.1a6c,4,,2011-09-23 12:36:26 CST,,0,LOG,00000,"aborting any active transactions",,,,,,,,,""
2011-09-23 12:55:13.002 CST,,,6804,,4e7c0d2a.1a94,2,,2011-09-23 12:38:02 CST,1/0,0,LOG,00000,"autovacuum launcher shutting down",,,,,,,,,""
2011-09-23 12:55:13.004 CST,,,6767,,4e7c0cca.1a6f,3,,2011-09-23 12:36:26 CST,,0,LOG,00000,"shutting down",,,,,,,,,""
2011-09-23 12:55:13.052 CST,,,6767,,4e7c0cca.1a6f,4,,2011-09-23 12:36:26 CST,,0,LOG,00000,"checkpoint starting: shutdown immediate",,,,,,,,,""
2011-09-23 12:55:14.344 CST,,,6767,,4e7c0cca.1a6f,5,,2011-09-23 12:36:26 CST,,0,LOG,00000,"checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.007 s, sync=0.000 s, total=1.316 s; sync files=0, longest=0.000 s, average=0.000 s",,,,,,,,,""
2011-09-23 12:55:14.344 CST,,,6767,,4e7c0cca.1a6f,6,,2011-09-23 12:36:26 CST,,0,LOG,00000,"database system is shut down",,,,,,,,,""
停库结束后，主库还有几个进程，一个是主进程，一个是记录日志的进程，一个是SENDER进程。所以这种关闭是会把所有的信息发送给STANDBY库的。
进程如下 :
/opt/pgsql/bin/postgres
postgres: logger process
postgres: wal sender process replica 172.16.3.xxx(29599) streaming 1/46BE0000
数据发完，这些进程会自动关闭。
备库日志 :
2011-09-23 12:55:12.912 CST,,,22646,,4e7c0d86.5876,12,,2011-09-23 12:39:34 CST,1/0,0,LOG,00000,"redo starts at 1/40000078",,,,,,,,,""
2011-09-23 12:56:09.309 CST,,,22723,,4e7c0e85.58c3,2,,2011-09-23 12:43:49 CST,,0,FATAL,XX000,"could not receive data from WAL stream: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
",,,,,,,,,""
在主库关闭过程中，备库上可以看到类似以上的日志，表明主库正在发关闭过程的XLOG信息给备库。主库关闭后，备库连不到主库，因此有后面那段报错，这是正常的。此时就可以对备库进行激活操作了，这样的主备角色切换不会丢失任何数据。
四、 备库操作：
确认主库正常关库后，激活备库.
pg_ctl promote -D $PGDATA
备库激活成主库后，把应用改为连接新的主库。
接到promote信号后，数据库日志如下 :
2011-09-23 12:37:59.218 CST,,,6766,,4e7c0cca.1a6e,6,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"received promote request",,,,,,,,,""
2011-09-23 12:37:59.218 CST,,,6766,,4e7c0cca.1a6e,7,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"redo done at 1/3C00C3B0",,,,,,,,,""
2011-09-23 12:37:59.218 CST,,,6766,,4e7c0cca.1a6e,8,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2011-09-23 12:17:04.838732+08",,,,,,,,,""
2011-09-23 12:37:59.218 CST,,,6766,,4e7c0cca.1a6e,9,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"selected new timeline ID: 5",,,,,,,,,""
2011-09-23 12:38:00.508 CST,,,6766,,4e7c0cca.1a6e,10,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2011-09-23 12:38:00.539 CST,,,6767,,4e7c0cca.1a6f,1,,2011-09-23 12:36:26 CST,,0,LOG,00000,"checkpoint starting: end-of-recovery immediate wait",,,,,,,,,""
2011-09-23 12:38:01.939 CST,,,6767,,4e7c0cca.1a6f,2,,2011-09-23 12:36:26 CST,,0,LOG,00000,"checkpoint complete: wrote 9 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.009 s, sync=0.099 s, total=1.430 s; sync files=8, longest=0.057 s, average=0.012 s",,,,,,,,,""
2011-09-23 12:38:02.030 CST,,,6804,,4e7c0d2a.1a94,1,,2011-09-23 12:38:02 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
2011-09-23 12:38:02.032 CST,,,6764,,4e7c0cca.1a6c,2,,2011-09-23 12:36:26 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
激活前后进程差异解析 :
激活前,recovery状态.进程如下 :
激活后,open状态.进程如下 :
/opt/pgsql/bin/postgres
postgres: logger process
postgres: startup process waiting for 000000040000000100000010
postgres: writer process
postgres: stats collector process
postgres: wal receiver process streaming 1/40000078激活后,open状态.进程如下 :
/opt/pgsql/bin/postgres
postgres: logger process
postgres: writer process
postgres: stats collector process
postgres: wal writer process
postgres: autovacuum launcher process
postgres: archiver process last was 00000005.history
postgres: wal sender process replica 172.16.3.xxx(26059) streaming 1/40000078
激活后多了 wal writer process , autovacuum launcher process , archiver process , wal sender process .
少了 startup process , wal receiver process .
五、 维护原来的主库。
六、 维护好后，把原来的主库作为standby 启动。
1. 拷贝/opt/pgsql/share/recovery.conf.example到$PGDATA下面
修改
2. 启动数据库
3. 此时，这个老主库会报错，
这个就是在找时间线文件。这里的时间线文件内容如下 :
postgres@172_168_3_xx-> cat recovery.done |grep -v "^#"
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.16.3.xx port=1921 user=replica' # e.g. 'host=localhost port=5432'
trigger_file = '/home/pgdata/pg_root/postgresql.trigger.1921'2. 启动数据库
pg_ctl start -D $PGDATA3. 此时，这个老主库会报错，
2011-09-23 10:38:21.965 CST,,,4054,,4e7bf11d.fd6,1,,2011-09-23 10:38:21 CST,,0,FATAL,XX000,"timeline 2 of the primary does not match
recovery target timeline 1",,,,,,,,,""这个就是在找时间线文件。这里的时间线文件内容如下 :
postgres@172_168_3_xxx-> cat 00000002.history
1 00000001000000010000000B no recovery target specified
这个报错可在源码文件src/backend/replication/libpqwalreceiver/libpqwalreceiver.c中找到。
对应部分 :
/*
* Confirm that the current timeline of the primary is the same as the
* recovery target timeline.
*/
standby_tli = GetRecoveryTargetTLI();
PQclear(res);
if (primary_tli != standby_tli)
ereport(ERROR,
(errmsg("timeline %u of the primary does not match recovery target timeline %u",
primary_tli, standby_tli)));
4. 我们到新主库的pg_xlog找到这个文件，然后再老的主库pg_xlog目录新建这个文件写入同样的内容就解决了。或者直接scp过去。
5. 检查复制，确保复制正常。
6. 主库和备库接近一致后，再来一次主库和备库角色切换的操作。
6.1 关闭新主库。
6.2 激活老主库。
激活完后，新增了一个时间线文件内容如下，每次时间线变更的记录都将累计下来 :
postgres@172_168_3_xxx-> cat 00000003.history
1 00000001000000010000000B no recovery target specified
2 00000002000000010000000D no recovery target specified
6.3 然后配置新主库变回STANDBY。
当然，这个时间线文件也可以放在archive directory。
然后通过以下的recovery.conf里面配置的命令来寻找。
restore_command = '' # e.g. 'cp /mnt/server/archivedir/%f %p'
