PostgreSQL 逻辑复制 配置操作
#### PostgreSQL Logical Replication (逻辑复制) ####
Logical Replication (逻辑复制) 为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 配置文件新增
host all all 10.10.56.0/0 md5 host replication all 10.10.56.0/0 trust
允许在 10.10.56.0 同一网段的服务器所有用户 进行复制、连接等操作
分别在 16、17、19 服务器启动服务,连接数据库执行sql,检查单个数据库是否为单实例主库
pocdb=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
如上查询是否为备库 ,f 为表示false,即为主库,反之 t 为从库
分别在 16、17、19 单实例数据上创建数据库、用户、分配权限
postgres@clw-db3:/home/postgres> /opt/pgsql-10/bin/psql -p 5432 postgres psql (10.3) Type "help" for help. postgres=# create database pocdb; CREATE DATABASE pocdb=# CREATE USER l_repl PASSWORD '123456' REPLICATION; CREATE ROLE
在 16、17 服务器上分别创建 p 表(即表示各自业务的表),并给逻辑复制的用户 l_repl 分配权限。
pocdb=# CREATE TABLE p(id bigint primary key,ival int); CREATE TABLE pocdb=# GRANT ALL ON p TO l_repl; pocdb=# pocdb=# pocdb=# pocdb=# pocdb=# \d+ p Table "public.p" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | bigint | | not null | | plain | | ival | integer | | | | plain | | Indexes: "p_pkey" PRIMARY KEY, btree (id)
在 16、17 服务器上分别创建发布者,即把自己的 p 表发布出去,使 订阅者 可以订阅
pocdb=# CREATE PUBLICATION p_pub FOR TABLE p WITH (publish = 'insert,update,delete'); CREATE PUBLICATION
在19上创建订阅者,即分别订阅 16、17 服务器各自发布的表
CREATE SUBSCRIPTION p1_sub CONNECTION 'host=10.10.56.16 port=5432 user=l_repl dbname=pocdb password=123456' PUBLICATION p_pub; CREATE SUBSCRIPTION p2_sub CONNECTION 'host=10.10.56.17 port=5432 user=l_repl dbname=pocdb password=123456' PUBLICATION p_pub;
16 服务器插入奇数id 数据
pocdb=# INSERT INTO p (id,ival) VALUES (1,1); INSERT 0 1 pocdb=# pocdb=# select * from p; id | ival ----+------ 1 | 1 (1 row)
17 服务器插入偶数id数据
pocdb=# INSERT INTO p (id,ival) VALUES (2,2); INSERT 0 1 pocdb=# pocdb=#
注 意
在16和17服务器插入数据时,必须主键不能冲突,否则会出错,在实际业务中,插入的数据主键永远不会冲突
此处我们在16服务器插入主键为 奇数 的数据,17为 偶数 的数据
此时19服务器查看P表汇总的数据
pocdb=# select * from p; id | ival ----+------ 1 | 1 2 | 2 (2 rows)
发现数据已经复制过来,达到我们需要的需求
创建R表
背 景
在实际业务需求中,我们可能会有一些数据字典之类的公共表,即现有A、B、C 独立的数据库,但是他们都需要共同的字典表 R 表,且R表的入口只能有一个,比如只能在A库进行写入,其他库则需要同步该表,故通过逻辑复制来实现。
pocdb=# CREATE PUBLICATION r_pub FOR TABLE r WITH (publish = 'insert,update,delete,TRUNCATE'); CREATE PUBLICATION pocdb=#
逻辑复制不支持 TRUNCATE 级联删除表数据
R 表的逻辑复制
在16、17、19 分别创建 R表
CREATE TABLE R (id bigint ,age int);
配置文件上述已经配置,同上,16 发布 R 表,17 与 19 分别 订阅16服务器的R表,即可实现上述业务。
补充:PostgreSQL逻辑复制压测方案
逻辑复制延迟压测
本次压力测试过程基于以上
56.16 –> 56.19 监控延迟脚本
创建脚本 query_logical_lag.sh,并分配权限
#!/bin/bash /opt/pgsql-10/bin/psql pocdb<<EOF select now(); select client_addr, application_name, write_lag, flush_lag, replay_lag from pg_stat_replication where usename='l_repl' and application_name='p1_sub'; \q EOF
postgres@clw-db2:~> chmod +x query_logical_lag2.sh postgres@clw-db2:~> ls -l total 4 -rwxr-xr-x 1 postgres postgres 218 May 8 16:49 query_logical_lag2.sh
56.17 –> 56.19 延迟监测脚本
创建脚本 query_logical_lag2.sh
#!/bin/bash /opt/pgsql-10/bin/psql pocdb<<EOF select now(); select client_addr, application_name, write_lag, flush_lag, replay_lag from pg_stat_replication where usename='l_repl' and application_name='p2_sub'; \q EOF
分配权限,不然无法执行脚本
postgres@clw-db2:~> chmod +x query_logical_lag2.sh postgres@clw-db2:~> ls -l total 4 -rwxr-xr-x 1 postgres postgres 218 May 8 16:49 query_logical_lag2.sh
执行监控延迟
在16服务器执行:
clw_db1 postgres@clw-db1:~> for i in {1..100000} > do > /home/postgres/query_logical_lag.sh /home/postgres/query_logical_lag1 > sleep 5 > done
表示:执行query_logical_lag.sh脚本 100000次,每执行一次等待5s,把查询结果记录到
query_logical_lag1文件
在17服务器执行
clw_db2 crontab -e for i in {1..10000000} do /home/postgres/query_logical_lag.sh /home/postgres/query_logical_lag2 sleep 5 done
创建sequence,使用sequence保证不同节点插入的数值是奇数或偶数
56.16
create sequence p_seq1 increment by 2 minvalue 1 maxvalue 100000000000000 cache 50 no cycle; ----cache是否需要调大
56.17 服务器创建序列
create sequence p_seq1 increment by 2 minvalue 2 maxvalue 100000000000000 cache 50 no cycle;
16、17 服务器分别创建压测脚本 bench_script_for_insert.sql
\sleep 500ms \set ival random(1, 500000000) INSERT INTO p(id, ival) VALUES (nextval('p_seq1'),:ival);
16、17分别执行 pgbench 压测命令
/opt/pgsql-10/bin/pgbench -c 150 -j 120 -T 600 -f /pgdata/10/poc/scripts/bench_script_for_insert.sql pocdb
nmon监控cpu、内存、网络
chmod +x /home/super/pgsoft/nmon_x86_64_sles11 /home/super/pgsoft/nmon_x86_64_sles11 -f -c 120 -s 10
注:
-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 -c 120 -s 10 /home/super/pgsoft/nmon_x86_64_sles11 -f -c 120 -s 10
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。
下一篇:postgresql 实现获取所有表名,字段名,字段类型,注释