相关知识
pg_rewind
介绍
pg_rewind是一个工具，用于在集群的时间轴发散后， 将PostgreSQL集群与同一集群的另一个副本同步，pg_rewind在进行新的基本备份或者像rsync 之类的工具方面的优点是，pg_rewind不需要读取集群中所有未更改的文件。这使得它在数据库很大时更快，并且只有一小部分在集群之间不同。pg_rewind检查源和目标集群的时间线历史，以确定它们发散的点， 并且期望在目标集群的 pg_xlog 目录中找到WAL，一直到达分歧点。
pg_rewind要求目标服务器在 postgresql.conf 中启用wal_log_hints选项，或者在使用initdb初始化集群时启用数据校验和。默认情况下，这两个都不是当前启用的。full_page_writes也必须启用。这是默认值。
运行机制
扫描旧集群的WAL日志，从新集群的时间线历史记录从旧集群分出的点之前的最后一个检查点开始。对于每个WAL记录，记下被触及的数据块。这将产生在新群集分叉之后在旧群集中更改的所有数据块的列表。
将所有更改的块从新集群复制到旧集群。
将所有其他文件（如 clog 和配置文件） 从新集群复制到旧集群，除关系文件外的所有内容。
从在故障切换时创建的检查点开始，从新集群应用WAL。（严格地说， pg_rewind不应用WAL， 它只是创建一个备份标签文件，指示当PostgreSQL启动时， 它将从该检查点开始重放，并应用所有需要的WAL。）
使用语法
Usage:
pg_rewind [ option ...]{ -D | --target-pgdata } directory { --source-pgdata= directory |
--source-server= connstr }
Options:
-D, --target-pgdata=DIRECTORY existing data directory to modify
--source-pgdata=DIRECTORY source data directory to synchronize with
--source-server=CONNSTR source server to synchronize with
-n, --dry-run stop before modifying anything
-P, --progress write progress messages
--debug write a lot of debug messages
-V, --version output version information, then exit
-?, --help show this help, then exit
数据库主备情况
主:sdw3 192.168.43.175
备:sdw4 192.168.43.176
操作流程实践
1.sdw3上查看当前流复制正常
Expanded display is on.
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 15117
usesysid | 16384
usename | rep
application_name | walreceiver
client_addr | 192.168.43.176
client_hostname |
client_port | 33431
backend_start | 2020-03-16 22:10:40.231369+08
backend_xmin |
state | streaming
sent_location | 0/3000060
write_location | 0/3000060
flush_location | 0/3000060
replay_location | 0/3000060
sync_priority | 0
sync_state | async
2.sdw4配置文件recovery.conf有trigger_file配置项触发激活主库，或直接使用命令pg_ctl promote
也可以激活
[postgres@sdw4 pgdata]$ cat recovery.conf
standby_mode = on
primary_conninfo = 'port=6432 user=rep host=sdw3'
trigger_file = '/data/pgdata/pgsql.recovery.trigger'
recovery_target_timeline = 'latest'
3.激活主库
[postgres@sdw4 ~]$ pg_ctl promote
server promoting
4.此时主库和激活的从库都可以插入数据，相当于是两个单独数据库
往sdw3插入数据库
[postgres@sdw3 ~]$ psql
psql (9.5.1)
Type "help" for help.
postgres=# create table test(a text);
insert into test values('sdw3');CREATE TABLE
postgres=# insert into test values('sdw3');
INSERT 0 1
往sdw4插入数据库
[postgres@sdw4 ~]$ psql
psql (9.5.1)
Type "help" for help.
postgres=# create table test(a text);
insert into test values('sdw4');CREATE TABLE
postgres=# insert into test values('sdw4');
INSERT 0 1
5.接下来把原来的主库sdw3变成sdw4的从库
可以先在sdw4上查看切换点日志
[postgres@sdw4 pg_xlog]$ cat 00000002.history
1 0/3000060 no recovery target specified
确保sdw4上的文件pg_hba.conf有sdw3的认证配置
host replication rep 192.168.43.175/32 md5
停止sdw3库
[postgres@sdw3 ~]$ pg_ctl stop
waiting for server to shut down....... done
server stopped
在sdw3上使用pg_rewind同步sdw4变更的信息数据
[postgres@sdw3 ~]$ pg_rewind --target-pgdata $PGDATA --source-server='host=192.168.43.176 port=6432 user=postgres dbname=postgres' -P
connected to server
servers diverged at WAL position 0/3000060 on timeline 1
rewinding from last common checkpoint at 0/2000060 on timeline 1
reading source file list
reading target file list
reading WAL in target
need to copy 52 MB (total source directory size is 69 MB)
53289/53289 kB (100%) copied
creating backup label and updating control file
Done!
sdw3上创建配置文件recovery.conf
cat > /data/pgdata/recovery.conf <<EOF
standby_mode = on
primary_conninfo = 'port=6432 user=rep host=sdw4'
trigger_file = '/data/pgdata/pgsql.recovery.trigger'
recovery_target_timeline = 'latest'
EOF
sdw3确认认证文件.pgpass有连接sdw4的信息
cat ~/.pgpass > <<EOF
192.168.43.176:6432:replication:rep:rep123
sdw4:6432:replication:rep:rep123
EOF
启动sdw3库，作为sdw4的从库，并且在sdw4查看从库sdw3流复制正常
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 17855
usesysid | 16384
usename | rep
application_name | walreceiver
client_addr | 192.168.43.175
client_hostname |
client_port | 41471
backend_start | 2020-03-16 22:22:45.361633+08
backend_xmin |
state | streaming
sent_location | 0/303FE38
write_location | 0/303FE38
flush_location | 0/303FE38
replay_location | 0/303F080
sync_priority | 0
sync_state | async
查看数据库表的数据，只有激活成主库sdw4时的数据
postgres=# select * from test;
a
------
sdw4
(1 row)
自动切换脚本
当我们知道这切换原理后，我们可以写成自动切换的脚本activity_master.sh
#/bin/sh -x
source ~/.bashrc
target_host=$1
target_port=$PGPORT
target_data="$PGDATA"
source_host=`hostname`
source_port=$PGPORT
source_data="$PGDATA"
ssh $target_host "pg_ctl stop"
pg_ctl promote
ssh $target_host "rm $target_data/recovery.*"
ssh $target_host "cat > $target_data/recovery.conf <<EOF
standby_mode = on
primary_conninfo = 'port=$source_port user=rep host=$source_host'
recovery_target_timeline = 'latest'
EOF"
sleep 5
ssh $target_host "pg_rewind --target-pgdata $target_data --source-server='host=$source_host port=$source_port user=postgres dbname=postgres' -P"
ssh $target_host "pg_ctl start" &
sleep 5
psql -ac "select client_addr,state from pg_stat_replication ;"
现在把sdw3切换回来变成主库
[postgres@sdw3 ~]$ sh activity_master.sh sdw4
waiting for server to shut down.... done
server stopped
server promoting
connected to server
servers diverged at WAL position 0/B6000098 on timeline 39
no rewind required
server starting
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
select client_addr,state from pg_stat_replication ;
client_addr | state
----------------+-----------
192.168.43.176 | streaming
(1 row)
再测试把sdw4切换成主库
[postgres@sdw4 ~]$ sh activity_master.sh sdw3
waiting for server to shut down.... done
server stopped
server promoting
connected to server
servers diverged at WAL position 0/B7000098 on timeline 40
no rewind required
server starting
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
select client_addr,state from pg_stat_replication ;
client_addr | state
----------------+-----------
192.168.43.175 | streaming
(1 row)
小结
必需开启wal_log_hints和full_page_writes的参数。
需要做pg_rewind的库：从激活点开始，所有的WAL必须存在pg_xlog目录中。如果WAL已经被覆盖，只要有归档，拷贝到pg_xlog目录即可。
新的主库：从激活点开始，所有的WAL必须存在pg_xlog目录中。如果WAL已经被覆盖，需要有归档， 并且被做pg_rewind的库可以使用restore_command访问到这部分WAL 。
