os: centos 7.4
postgresql: 10.4
postgresql 10 开始内置了逻辑复制这一强大的功能，之前都是利用extenstion的方式实现逻辑复制，或者是物理复制，slave端都是 readonly操作。使用逻辑复制后，可以在再多台机器(应用)之间同步数据，而各自都保持读写状态，想想都开心。
该功能主要是通过 发布者 和 订阅者 来完成。
事先在 nodea、nodeb上安装好 postgresql 10.4，wal_level 参数设置为 logical
$ psql
psql (10.4)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
postgres=# x
Expanded display is on.
postgres=#
postgres=#
postgres=# select * from pg_settings where name like '%wal_level%';
-[ RECORD 1 ]---+-------------------------------------------------
name | wal_level
setting | logical
unit |
category | Write-Ahead Log / Settings
short_desc | Set the level of information written to the WAL.
extra_desc |
context | postmaster
vartype | enum
source | configuration file
min_val |
max_val |
enumvals | {minimal,replica,logical}
boot_val | replica
reset_val | logical
sourcefile | /var/lib/pgsql/10/data/postgresql.conf
sourceline | 180
pending_restart | f
现在通过 logical replication 实现如下需求
源库： nodea 上 postgresql 的peiybdb数据库下的 public.tmp_t0
目标库：nodeb 上 postgresql 的peiybdb2数据库下的 public.tmp_t0
源库创建同步用户
postgres=# create user repuser replication login connection limit 20 encrypted password 'repuserrepuser';
CREATE ROLE
postgres=# du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
peiyb | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repuser | Replication +| {}
| 20 connections |
添加pg_hba.conf条目
$ vi pg_hba.conf
host replication repuser 192.168.56.0/24 md5
$ psql -c "select pg_reload_conf();"
pg_reload_conf
----------------
t
(1 row)
源库创建测试表 public.tmp_t0
$ psql -h 192.168.56.101 -U peiyb -W peiybdb
Password for user peiyb:
psql (10.4)
Type "help" for help.
peiybdb=>
peiybdb=> create table tmp_t0(c1 varchar(100),c2 varchar(100));
CREATE TABLE
peiybdb=> insert into tmp_t0(c1,c2)values('1','11'),('2','22');
INSERT 0 2
peiybdb=> select * from tmp_t0;
c1 | c2
----+----
1 | 11
2 | 22
(2 rows)
源库创建发布者 publication
peiyb 用户登录 postgresql
peiybdb=> create publication pub1 for table tmp_t0;
CREATE PUBLICATION
peiybdb=> select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
pub1 | 16384 | f | t | t | t
(1 row)postgres用户登录 postgresql
peiybdb=> grant connect on database peiybdb to repuser;
GRANT
peiybdb=> grant usage on schema public to repuser;
GRANT
peiybdb=> grant select on public.tmp_t0 to repuser;
GRANT目标库创建对应的表 public.tmp_t0
peiybdb2=> create table tmp_t0(c1 varchar(100),c2 varchar(100));
CREATE TABLE
peiybdb2=> d
List of relations
Schema | Name | Type | Owner
--------+--------+-------+--------
public | tmp_t0 | table | peiyb2
(1 row)
peiybdb2=> select * from tmp_t0;
c1 | c2
----+----
(0 rows)
目标库创建订阅者 subscription
peiybdb2=> create subscription sub1 connection 'host=192.168.56.101 port=5432 dbname=peiybdb user=repuser password=repuserrepuser' publication pub1;
ERROR: must be superuser to create subscriptions
peiybdb2=> select * from pg_subscription;
ERROR: permission denied for relation pg_subscription悲催了，以peiyb用户登录后，执行创建命令提示权限不够，必须以 superuser 角色创建 subscriptions。
困惑了，为什么创建 publication 权限就可以不是 superuser ？
下面是以 postgres用户登录postgresql
peiybdb2=# create subscription sub1 connection 'host=192.168.56.101 port=5432 dbname=peiybdb user=repuser password=repuserrepuser' publication pub1;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
peiybdb2=# x
Expanded display is on.
peiybdb2=# select * from pg_subscription;
-[ RECORD 1 ]---+----------------------------------------------------------------------------------
subdbid | 16385
subname | sub1
subowner | 10
subenabled | t
subconninfo | host=192.168.56.101 port=5432 dbname=peiybdb user=repuser password=repuserrepuser
subslotname | sub1
subsynccommit | off
subpublications | {pub1}
密码明文保存，怪不得只能以 superuser 来操作
目标库验证数据同步
peiybdb2=> select * from public.tmp_t0;
c1 | c2
----+----
1 | 11
2 | 22
(2 rows)源库插入数据
peiybdb=> insert into tmp_t0(c1,c2) values('3','33');
INSERT 0 1
peiybdb=> select * from tmp_t0;
c1 | c2
----+----
1 | 11
2 | 22
3 | 33
(3 rows)目标库验证数据
peiybdb2=# select * from tmp_t0;
c1 | c2
----+----
1 | 11
2 | 22
3 | 33
(3 rows)源库的进程
# ps -ef|grep -i postgres |grep -v grep
postgres 2706 1 0 03:54 ? 00:00:00 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data/
postgres 2709 2706 0 03:54 ? 00:00:00 postgres: logger process
postgres 2711 2706 0 03:54 ? 00:00:00 postgres: checkpointer process
postgres 2712 2706 0 03:54 ? 00:00:00 postgres: writer process
postgres 2713 2706 0 03:54 ? 00:00:00 postgres: wal writer process
postgres 2714 2706 0 03:54 ? 00:00:00 postgres: autovacuum launcher process
postgres 2715 2706 0 03:54 ? 00:00:00 postgres: stats collector process
postgres 2716 2706 0 03:54 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 3289 2789 0 04:17 pts/1 00:00:00 psql -h 192.168.56.101 -U peiyb -W peiybdb
postgres 3290 2706 0 04:17 ? 00:00:00 postgres: peiyb peiybdb 192.168.56.101(42430) idle
postgres 3591 2706 0 04:34 ? 00:00:00 postgres: wal sender process repuser 192.168.56.102(44504) idle
postgres 3774 3200 0 04:40 pts/2 00:00:00 psql
postgres 3776 2706 0 04:40 ? 00:00:00 postgres: postgres peiybdb [local] idle
postgres=# select * from pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+---------+------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+---------------------------------+---------------------
| | 2716 | 10 | postgres | | | | | 2018-06-13 03:54:06.874782+08 | | | | Activity | LogicalLauncherMain | | | | | background worker
| | 2714 | | | | | | | 2018-06-13 03:54:06.888134+08 | | | | Activity | AutoVacuumMain | | | | | autovacuum launcher
16385 | peiybdb | 3290 | 16384 | peiyb | psql | 192.168.56.101 | | 42430 | 2018-06-13 04:17:30.186683+08 | | 2018-06-13 04:48:36.671867+08 | 2018-06-13 04:48:36.672247+08 | Client | ClientRead | idle | | | select * from tmp_t0; | client backend
16385 | peiybdb | 3591 | 16386 | repuser | sub1 | 192.168.56.102 | | 44504 | 2018-06-13 04:34:52.400354+08 | | | 2018-06-13 04:34:52.403515+08 | Client | WalSenderWaitForWAL | active | | | | walsender
16385 | peiybdb | 3776 | 10 | postgres | psql | | | -1 | 2018-06-13 04:40:12.80178+08 | 2018-06-13 05:06:48.717854+08 | 2018-06-13 05:06:48.717854+08 | 2018-06-13 05:06:48.71786+08 | | | active | | 567 | select * from pg_stat_activity; | client backend
| | 2712 | | | | | | | 2018-06-13 03:54:06.875644+08 | | | | Activity | BgWriterHibernate | | | | | background writer
| | 2711 | | | | | | | 2018-06-13 03:54:06.889611+08 | | | | Activity | CheckpointerMain | | | | | checkpointer
| | 2713 | | | | | | | 2018-06-13 03:54:06.876317+08 | | | | Activity | WalWriterMain | | | | | walwriter
(8 rows)目标库的进程
# ps -ef|grep -i postgres |grep -v grep
postgres 967 31873 0 16:54 pts/0 00:00:00 psql
postgres 968 31760 0 16:54 ? 00:00:00 postgres: postgres postgres [local] idle
postgres 31760 1 0 15:39 ? 00:00:00 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data/
postgres 31763 31760 0 15:39 ? 00:00:00 postgres: logger process
postgres 31765 31760 0 15:39 ? 00:00:00 postgres: checkpointer process
postgres 31766 31760 0 15:39 ? 00:00:00 postgres: writer process
postgres 31767 31760 0 15:39 ? 00:00:00 postgres: wal writer process
postgres 31768 31760 0 15:39 ? 00:00:00 postgres: autovacuum launcher process
postgres 31769 31760 0 15:39 ? 00:00:00 postgres: stats collector process
postgres 31770 31760 0 15:39 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 32407 25440 0 16:09 pts/1 00:00:00 psql -h 192.168.56.102 -U peiyb2 -W peiybdb2
postgres 32409 31760 0 16:09 ? 00:00:00 postgres: peiyb2 peiybdb2 192.168.56.102(41094) idle
postgres 32573 31760 0 16:19 ? 00:00:00 postgres: bgworker: logical replication worker for subscription 16391
postgres=# select * from pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type
-------+----------+-------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+---------------------------------+---------------------
| | 31770 | 10 | postgres | | | | | 2018-06-13 15:39:07.581296+08 | | | | Activity | LogicalLauncherMain | | | | | background worker
| | 31768 | | | | | | | 2018-06-13 15:39:07.601239+08 | | | | Activity | AutoVacuumMain | | | | | autovacuum launcher
16385 | peiybdb2 | 32573 | 10 | postgres | | | | | 2018-06-13 16:19:39.131276+08 | | | 2018-06-13 16:33:18.310017+08 | Activity | LogicalApplyMain | idle | | | | background worker
16385 | peiybdb2 | 32409 | 16384 | peiyb2 | psql | 192.168.56.102 | | 41094 | 2018-06-13 16:09:11.123253+08 | | 2018-06-13 16:30:13.810469+08 | 2018-06-13 16:30:13.811226+08 | Client | ClientRead | idle | | | select * from pg_subscription; | client backend
13806 | postgres | 968 | 10 | postgres | psql | | | -1 | 2018-06-13 16:54:41.854388+08 | 2018-06-13 16:54:53.613457+08 | 2018-06-13 16:54:53.613457+08 | 2018-06-13 16:54:53.613474+08 | | | active | | 631 | select * from pg_stat_activity; | client backend
| | 31766 | | | | | | | 2018-06-13 15:39:07.58213+08 | | | | Activity | BgWriterHibernate | | | | | background writer
| | 31765 | | | | | | | 2018-06-13 15:39:07.602236+08 | | | | Activity | CheckpointerMain | | | | | checkpointer
| | 31767 | | | | | | | 2018-06-13 15:39:07.582871+08 | | | | Activity | WalWriterMain | | | | | walwriter
(8 rows)源库的log文件
COPY public.tmp_t0 TO STDOUT 目标库的log
"logical replication table synchronization worker for subscription ""sub1"", table ""tmp_t0"" has started",,,,,,,,,""
上面只是简单的使用 postgresql 10 logical replication 的 publication、subscription。
有空仔细看看官方文档再总结下。
参考：
https://www.postgresql.org/docs/10/static/logical-replication.html