PostgreSQL 逻辑复制 配置操作
####PostgreSQLLogicalReplication(逻辑复制)####
LogicalReplication(逻辑复制)为PostgreSQL自己提供的一种方案,主要是通过一方发布，一方订阅，来实现彼此共同关注的一种技术。
服务器|功能
|10.10.56.16|发布者P1_pub表| |10.10.56.17|发布者P2_pub表| |10.10.56.19|订阅者P_sub表|
背景
一些数据根据业务拆分成一部分P1表、一部分P2表或者多个，A库操作P1表，B库操作P2表
现需要可以查询整个P表，即需要一个p表的汇总库数据需求，所以通过逻辑复制来实现。
PostgreSQL安装环境可见之前的搭建环境教程
分别在3台服务器pg_hba.conf配置文件新增
hostallall10.10.56.0/0md5 hostreplicationall10.10.56.0/0trust
允许在10.10.56.0同一网段的服务器所有用户进行复制、连接等操作
分别在16、17、19服务器启动服务，连接数据库执行sql,检查单个数据库是否为单实例主库
pocdb=#selectpg_is_in_recovery(); pg_is_in_recovery ------------------- f (1row)
如上查询是否为备库，f为表示false,即为主库，反之t为从库
分别在16、17、19单实例数据上创建数据库、用户、分配权限
postgres@clw-db3:/home/postgres>/opt/pgsql-10/bin/psql-p5432postgres psql(10.3) Type"help"forhelp. postgres=#createdatabasepocdb; CREATEDATABASE pocdb=#CREATEUSERl_replPASSWORD'123456'REPLICATION; CREATEROLE
在16、17服务器上分别创建p表(即表示各自业务的表),并给逻辑复制的用户l_repl分配权限。
pocdb=#CREATETABLEp(idbigintprimarykey,ivalint); CREATETABLE pocdb=#GRANTALLONpTOl_repl; pocdb=# pocdb=# pocdb=# pocdb=# pocdb=#\d+p Table"public.p" Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description --------+---------+-----------+----------+---------+---------+--------------+------------- id|bigint||notnull||plain|| ival|integer||||plain|| Indexes: "p_pkey"PRIMARYKEY,btree(id)
在16、17服务器上分别创建发布者,即把自己的p表发布出去,使订阅者可以订阅
pocdb=#CREATEPUBLICATIONp_pubFORTABLEpWITH(publish='insert,update,delete'); CREATEPUBLICATION
在19上创建订阅者,即分别订阅16、17服务器各自发布的表
CREATESUBSCRIPTIONp1_subCONNECTION'host=10.10.56.16port=5432user=l_repldbname=pocdbpassword=123456'PUBLICATIONp_pub; CREATESUBSCRIPTIONp2_subCONNECTION'host=10.10.56.17port=5432user=l_repldbname=pocdbpassword=123456'PUBLICATIONp_pub;
16服务器插入奇数id数据
pocdb=#INSERTINTOp(id,ival)VALUES(1,1); INSERT01 pocdb=# pocdb=#select*fromp; id|ival ----+------ 1|1 (1row)
17服务器插入偶数id数据
pocdb=#INSERTINTOp(id,ival)VALUES(2,2); INSERT01 pocdb=# pocdb=#
注意
在16和17服务器插入数据时，必须主键不能冲突，否则会出错，在实际业务中，插入的数据主键永远不会冲突
此处我们在16服务器插入主键为奇数的数据，17为偶数的数据
此时19服务器查看P表汇总的数据
pocdb=#select*fromp; id|ival ----+------ 1|1 2|2 (2rows)
发现数据已经复制过来，达到我们需要的需求
创建R表
背景
在实际业务需求中，我们可能会有一些数据字典之类的公共表，即现有A、B、C独立的数据库，但是他们都需要共同的字典表R表,且R表的入口只能有一个，比如只能在A库进行写入，其他库则需要同步该表，故通过逻辑复制来实现。
pocdb=#CREATEPUBLICATIONr_pubFORTABLErWITH(publish='insert,update,delete,TRUNCATE'); CREATEPUBLICATION pocdb=#
逻辑复制不支持TRUNCATE级联删除表数据
R表的逻辑复制
在16、17、19分别创建R表
CREATETABLER(idbigint,ageint);
配置文件上述已经配置，同上，16发布R表，17与19分别订阅16服务器的R表，即可实现上述业务。
补充：PostgreSQL逻辑复制压测方案
逻辑复制延迟压测
本次压力测试过程基于以上
56.16–>56.19监控延迟脚本
创建脚本query_logical_lag.sh，并分配权限
#!/bin/bash /opt/pgsql-10/bin/psqlpocdb<56.17–>56.19延迟监测脚本
创建脚本query_logical_lag2.sh
#!/bin/bash /opt/pgsql-10/bin/psqlpocdb<分配权限，不然无法执行脚本
postgres@clw-db2:~>chmod+xquery_logical_lag2.sh postgres@clw-db2:~>ls-l total4 -rwxr-xr-x1postgrespostgres218May816:49query_logical_lag2.sh执行监控延迟
在16服务器执行：
clw_db1 postgres@clw-db1:~>foriin{1..100000} >do >/home/postgres/query_logical_lag.sh>>/home/postgres/query_logical_lag1 >sleep5 >done表示：执行query_logical_lag.sh脚本100000次，每执行一次等待5s，把查询结果记录到
query_logical_lag1文件
在17服务器执行
clw_db2 crontab-e foriin{1..10000000} do /home/postgres/query_logical_lag.sh>>/home/postgres/query_logical_lag2 sleep5 done创建sequence，使用sequence保证不同节点插入的数值是奇数或偶数
56.16
createsequencep_seq1incrementby2minvalue1maxvalue100000000000000cache50nocycle;----cache是否需要调大56.17服务器创建序列
createsequencep_seq1incrementby2minvalue2maxvalue100000000000000cache50nocycle;16、17服务器分别创建压测脚本bench_script_for_insert.sql
\sleep500ms \setivalrandom(1,500000000) INSERTINTOp(id,ival)VALUES(nextval('p_seq1'),:ival);16、17分别执行pgbench压测命令
/opt/pgsql-10/bin/pgbench-c150-j120-T600-f/pgdata/10/poc/scripts/bench_script_for_insert.sqlpocdbnmon监控cpu、内存、网络
chmod+x/home/super/pgsoft/nmon_x86_64_sles11 /home/super/pgsoft/nmon_x86_64_sles11-f-c120-s10注：
-f将结果存储在当前目录的文件中，以nmon结尾，自动生成
-c总共统计120次
-s每隔10s统计一次
chmod+x/home/pgsoft/nmon_x86_64_sles11 chmod+x/home/pgsoft/nmon_x86_64_sles11 /home/pgsoft/nmon_x86_64_sles11-f-c120-s10 /home/super/pgsoft/nmon_x86_64_sles11-f-c120-s10以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。