当前版本 PG 9.5.3
主库上操作
创建复制用户
postgres@postgres:5532 # CREATE USER repuser
postgres-# REPLICATION
postgres-# LOGIN;
CREATE ROLE
设置在pg_hba.conf,添加如下规则:
host replication repuser 192.168.56.0/24 trust
设置主库postgres.conf
checkpoint_segments = 16
archive_mode = on
archive_command = 'cp -i %p /postgres/pgsql/archive_dir/%f'
max_wal_senders = 3
wal_keep_segments = 16
max_wal_senders = 3
重载配置文件
[postgres@dang-db pgdata]$$PGHOME/bin/pg_ctl reload
server signaled
查看表空间目录
postgres@postgres:5532 # \db
List of tablespaces
Name | Owner | Location
------------+----------+------------------------------
pg_default | postgres |
pg_global | postgres |
tbs_test | user01 | /postgres/pgsql/tbs/tbs_test
(3 rows)
查看数据目录
[postgres@dang-db pgdata]$echo $PGDATA
/postgres/pgsql/pgdata/
备库上操作
创建目录
mkdir -p /postgres/pgsql/pg_slave/data
mkdir -p /postgres/pgsql/pg_slave/tbs
chmod 0700 /postgres/pgsql/pg_slave/data
使用pg_basebackup 生成备库
[postgres@dang-db data]$$PGHOME/bin/pg_basebackup -D /postgres/pgsql/pg_slave/data -Fp -Xs -R -v -P --tablespace-mapping=/postgres/pgsql/tbs/tbs_test=/postgres/pgsql/pg_slave/tbs -p 5532 -U repuser
transaction log start point: 0/B2000028 on timeline 1
pg_basebackup: starting background WAL receiver
565639/565639 kB (100%), 2/2 tablespaces
transaction log end point: 0/B20000C0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
其中 -D 指定了备库的数据目录
-R 指定了在备份完成后生成recove.conf
--tablespace-mapping 指定了主备表空间的映射
设置备库postgresql.conf
hot_standby = on
设置备库recovery.conf
[postgres@dang-db data]$more recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=repuser port=5532 sslmode=disable sslcompression=1'
启动备库
export PGHOME=/postgres/pgsql/9.5.3/
export PGDATA=/postgres/pgsql/pg_slave/data
export PGPORT=5535
[postgres@dang-db data]$$PGHOME/bin/pg_ctl start
server starting
[postgres@dang-db data]$LOG: pgaudit extension initialized
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
[postgres@dang-db data]$
查看备库进程:
[postgres@dang-db data]$ps -ef| grep receiver
postgres 29496 29490 0 15:19 ? 00:00:00 postgres: wal receiver process streaming 0/B7000140
查看主库进程:
[postgres@dang-db data]$ps -ef| grep sender
postgres 29497 28196 0 15:19 ? 00:00:00 postgres: wal sender process repuser [local] streaming 0/B7000140
测试
主库上创建测试数据
postgres@postgres:5532 # \c testdb user01
You are now connected to database "testdb" as user "user01".
user01@testdb:5532 > create table test_rep ( id serial primary key);
CREATE TABLE
user01@testdb:5532 > insert into test_rep select generate_series(1,10);
INSERT 0 10
user01@testdb:5532 > select * from test_rep;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
备库上查询测试数据:
postgres@postgres:5535 # show hot_standby;
hot_standby
-------------
on
(1 row)
postgres@postgres:5535 #
postgres@postgres:5535 # select * from test_rep;
ERROR: relation "test_rep" does not exist
LINE 1: select * from test_rep;
^
postgres@postgres:5535 # \c testdb user01
You are now connected to database "testdb" as user "user01".
user01@testdb:5535 > select * from test_rep;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
同步还是异步?
主库的参数synchronous_standby_names来决定哪个备节点的流复制为同步方式。
也可以通过视图pg_stat_replication中的sync_state字段查看流复制为同步还是异步。
postgres@testdb:5532 # show synchronous_standby_names;
synchronous_standby_names
---------------------------
(1 row)
postgres@testdb:5532 # select pid,application_name,client_addr,sync_state from pg_stat_replication;
pid | application_name | client_addr | sync_state
-------+------------------+-------------+------------
16627 | walreceiver | | async
(1 row)
当前synchronous_standby_names的value为空,所以为异步方式。
修改为同步方式:
修改主库postgres.conf:
synchronous_standby_names='slave0'
修改备库recovery.conf:
primary_conninfo = 'user=repuser port=5532 sslmode=disable sslcompression=1 application_name=slave0'
主库查询:
postgres@postgres:5532 # select pid,application_name,client_addr,sync_state from pg_stat_replication;
pid | application_name | client_addr | sync_state
-------+------------------+-------------+------------
27952 | slave0 | | sync
(1 row)
同步方式下,主库的事务xlog必须成功写到备库之后,事务才能成功提交。如果在主库事务提交之前,备库crash,在主库的所有事务都会hang,直到备库恢复。
在主库上执行一个insert,不提交:
user01@testdb:5532 > begin;
BEGIN
user01@testdb:5532 >
user01@testdb:5532 > insert into test_rep values(14);
INSERT 0 1
备库stop:
[postgres@dang-db data]$$PGHOME/bin/pg_ctl stop -m fast -p 5535
waiting for server to shut down.... done
server stopped
[postgres@dang-db data]
这时主库commit hang住,直到备库重新启动后才恢复:
user01@testdb:5532 > commit;
COMMIT
sync_state有三种状态:
sync:同步
async:异步
potential:可升级到同步状态。
如果synchronous_standby_names中有多个值,则第一个为sync,其他的为potential,不在synchronous_standby_names中的为async。
主库还是备库?
- 查看参数transaction_read_only 的值,off为主库,on为备库
主库:
postgres@postgres:5532 # show transaction_read_only;
transaction_read_only
-----------------------
off
(1 row)
备库:
postgres@postgres:5535 # show transaction_read_only;
transaction_read_only
-----------------------
on
(1 row)
- 查看进程,WAL sender 为主库,WAL receiver 为备库
主库:
[postgres@dang-db ~]$ps -ef | grep sender
postgres 11731 27874 0 09:05 ? 00:00:00 postgres: wal sender process repuser [local] streaming 0/BB0194F8
备库:
[postgres@dang-db ~]$ps -ef | grep receiver
postgres 11730 30879 0 09:05 ? 00:00:00 postgres: wal receiver process streaming 0/BB0194F8
- 查看参数hot_standby的值,off为主库,on为备库
主库:
postgres@postgres:5532 # show hot_standby;
hot_standby
-------------
off
(1 row)
备库:
postgres@postgres:5535 # show hot_standby;
hot_standby
-------------
on
(1 row)
- 查看 PGDATA下是否存在recovery.conf文件,有则为备库,无则为主库
主库:
[postgres@dang-db ~]$ll $PGDATA | grep recov
[postgres@dang-db ~]$
备库:
[postgres@dang-db ~]$ll $PGDATA | grep recov
-rw-rw-r-- 1 postgres postgres 121 Aug 24 17:44 recovery.conf
[postgres@dang-db ~]$
- 根据pg_controldata的输出中Database cluster state来判断,in production为主库, in archive recovery 为备库
主库:
[postgres@dang-db ~]$$PGHOME/bin/pg_controldata | grep "Database cluster state"
Database cluster state: in production
备库:
[postgres@dang-db ~]$$PGHOME/bin/pg_controldata | grep "Database cluster state"
Database cluster state: in archive recovery
- 自带的函数pg_is_in_recovery ,true为备库,false为主库
主库:
postgres@postgres:5532 # select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
postgres@postgres:5535 # select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
参考链接:
- http://francs3.blog.163.com/blog/static/4057672720136210240967/
- https://wiki.postgresql.org/wiki/Streaming_Replication
- http://www.philo.top/2016/03/24/PostgresqlReplication/
- http://www.ithao123.cn/content-134164.html