[PostgreSQL] PG91 的 Replication + Hot Stanby 配

转自：http://godbms.blogspot.com/2011/11/pg91-replication-hot-stanby.html

1. 初始化
$ initdb -D path/to/pg911-prim --encoding=UTF-8 --no-locale --pwprompt --auth=md5

2. 免密码设置
(hostname:port:database:username:password)
$ echo localhost:9112:postgres:go:go > ~/.pgpass
$ echo localhost:9113:postgres:go:go >> ~/.pgpass
$ chmod 600 ~/.pgpass
3. 创建 replication 用户名密码
$ psql -p 9112 -d postgres
psql (9.1.1)
Type "help" for help.
postgres=# CREATE ROLE reprole REPLICATION PASSWORD 'reppass';
CREATE ROLE
postgres=# ALTER ROLE reprole LOGIN;
ALTER ROLE
4. 修改 primary 配置
$ vim path/to/pg911-prim/postgresql.conf
listen_addresses = '*'
port = 9112
wal_level = hot_standby
max_wal_senders = 2 # standby + 1
wal_keep_segments = 8 # 8 - 32
synchronous_standby_names = ''
hot_standby = on # primary 中无效
$ vim path/to/pg911-prim/pg_hba.conf (追加)
host replication reprole 127.0.0.1/32 md5
5. 重启动 primary,使设置生效
$ pg_ctl restart -w
6. basebackup 到 stanby 目录
$ pg_basebackup -x -h localhost -p 9112 -U reprole -D path/to/pg911-stby
7. 修改 stanby 配置
$ vim path/to/pg911-stby/postgres.conf
port = 9113
$ vim path/to/pg911-stby/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=localhost port=9112 user=reprole password=reppass application_name=stby'
8. 启动 stanby
$ pg_ctl start -w
waiting for server to start....LOG: database system was interrupted; last known up at 2011-11-16 15:36:32 JST
LOG: creating missing WAL directory "pg_xlog/archive_status"
LOG: entering standby mode
LOG: redo starts at 0/2000020
LOG: consistent recovery state reached at 0/3000000
LOG: database system is ready to accept read only connections
LOG: streaming replication successfully connected to primary
done
server started
9. 查看启动状态
$ netstat -ano | grep PGSQL
unix 2 [ ACC ] STREAM LISTENING 50435 /tmp/.s.PGSQL.9113
unix 2 [ ACC ] STREAM LISTENING 49068 /tmp/.s.PGSQL.9112
10. 测试
$ psql -p 9112 -c "CREATE TABLE members(id integer,name text)" -d postgres
$ psql -p 9112 -c "INSERT INTO members VALUES(123,'Foo')" -d postgres
$ psql -p 9112 -c "SELECT * FROM members" -d postgres
$ psql -p 9113 -c "SELECT * FROM members" -d postgres
$ psql -p 9113 -c "INSERT INTO members VALUES(456,'Bar')" -d postgres
11. 提升 stanby 为 primary (一旦升级后,再降级要重新配置)
pg_ctl -D path/to/pg911-stby promote
12. 相关视图
12-1 显示 replication 状态 (各参数说明？？？)
$ psql -x -p 9112 -c "SELECT * FROM pg_stat_replication" -d postgres
-[ RECORD 1 ]----+------------------------------
procpid | 22856
usesysid | 16384
usename | reprole
application_name | stby
client_addr | 127.0.0.1
client_hostname |
client_port | 34689
backend_start | 2011-11-16 15:43:42.736069+09
state | streaming
sent_location | 0/301C028
write_location | 0/301C028
flush_location | 0/301C028
replay_location | 0/301C028
sync_priority | 0
sync_state | async
12-2 primary 现在 wal 位置
$ psql -x -p 9112 -c "SELECT * FROM pg_current_xlog_location()" -d postgres
12-3 stanby 现在写入 wal 位置
$ psql -x -p 9113 -c "SELECT * FROM pg_last_xlog_receive_location()" -d postgres
12-4 standby 到显示的时间为止的更新被应用
$ psql -p 9113 -c "SELECT pg_last_xact_replay_timestamp()"
pg_last_xact_replay_timestamp
-------------------------------
2011-04-25 23:20:56.912261+09
(1 row)
12-5 确认各进程
$ ps x | grep postgres
17829 pts/1 S 0:00 /usr/local/pgsql9.1.1/bin/postgres
17835 ? Ss 0:00 postgres: writer process
17836 ? Ss 0:00 postgres: wal writer process
17837 ? Ss 0:00 postgres: autovacuum launcher process
17838 ? Ss 0:00 postgres: stats collector process
17918 pts/1 S 0:00 /usr/local/pgsql9.1.1/bin/postgres
17923 ? Ss 0:00 postgres: startup process recovering 000000010000000000000004
17924 ? Ss 0:00 postgres: writer process
17925 ? Ss 0:00 postgres: stats collector process
17926 ? Ss 0:00 postgres: wal receiver process streaming 0/4088390
17927 ? Ss 0:00 postgres: wal sender process reprole 127.0.0.1(26093) streaming 0/4088390
...
13 同期 replication 配置
13-1 修改配置
synchronous_standby_names = 'stby' # 参照 path/to/pg911-stby/recovery.conf
13-2 重启 primary 和 stanby
$ pg_ctl -D path/to/pg911-prim restart -w
$ pg_ctl -D path/to/pg911-stby restart -w
此时,如果 stanby 停机,primary 的 commit 会被暂停,stanby 恢复后 commit 将成功
通过 synchronous_commit = local (on) 参数,可以使 primary 单独 (协同) 工作
$ pg_ctl -D path/to/pg911-prim -o "-c synchronous_commit=local" restart
$ pg_ctl -D path/to/pg911-prim -o "-c synchronous_commit=on" restart
14 primary/stanby 切换 (同期 or 非同期)
$ pg_ctl -D path/to/pg911-prim -m immediate stop
$ pg_ctl -D path/to/pg911-stby promote (新 primary)
$ vim path/to/pg911-prim/postgres.conf (修改)
hot_standby = on
$ vim path/to/pg911-prim/recovery.conf (创建)
primary_conninfo = 'host=localhost port=9113 user=reprole password=reppass application_name=prim'
recovery_target_timeline = 'latest'
restore_command = 'cp /home/go/data/pg911-stby/pg_xlog/%f "%p" 2> /dev/null'
$ pg_ctl -D path/to/pg911-prim start -w (新 stanby)