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 配置文件新增

1 2	`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 表发布出去,使订阅者可以订阅

1 2	`pocdb=#` `CREATE` `PUBLICATION p_pub` `FOR` `TABLE` `p` `WITH` `(publish =` `'insert,update,delete');` `CREATE` `PUBLICATION`

在19上创建订阅者,即分别订阅 16、17 服务器各自发布的表

1 2	`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表

1	`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

1	`create` `sequence` `p_seq1 increment` `by` `2 minvalue 1 maxvalue 100000000000000 cache 50` `no` `cycle;` `----cache是否需要调大`

56.17 服务器创建序列

1	`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 压测命令

1	`/opt/pgsql-10/bin/pgbench -c 150 -j 120 -T 600 -f /pgdata/10/poc/scripts/bench_script_for_insert.sql pocdb`

nmon监控cpu、内存、网络

1 2	`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