个人中心
gpt4 book ai didi

PostgreSQL 逻辑复制 配置操作

转载 作者：qq735679552 更新时间：2022-09-27 22:32:09 27 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章PostgreSQL 逻辑复制 配置操作由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

#### PostgreSQL Logical Replication (逻辑复制) #### 。

Logical Replication (逻辑复制) 为PostgreSQL自己提供的一种方案,主要是通过一方 发布，一方订阅，来实现彼此共同关注的一种技术.

服务器 | 功能 。

?
1
2
3
| 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,检查单个数据库是否为单实例主库 。

?
1
2
3
4
5
pocdb=# select pg_is_in_recovery();
  pg_is_in_recovery
-------------------
  f
(1 row)

如上查询是否为备库 ，f 为表示false,即为主库，反之 t 为从库 。

分别在 16、17、19 单实例数据上创建数据库、用户、分配权限 。

?
1
2
3
4
5
6
7
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 分配权限.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 数据 。

?
1
2
3
4
5
6
7
8
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数据 。

?
1
2
3
4
pocdb=# INSERT INTO p (id,ival) VALUES (2,2);
INSERT 0 1
pocdb=#
pocdb=#

注 意 。

在16和17服务器插入数据时，必须主键不能冲突，否则会出错，在实际业务中，插入的数据主键永远不会冲突 。

此处我们在16服务器插入主键为 奇数 的数据，17为 偶数 的数据 。

此时19服务器查看P表汇总的数据 。

?
1
2
3
4
5
6
pocdb=# select * from p;
  id | ival
----+------
  1 |  1
  2 |  2
(2 rows )

发现数据已经复制过来，达到我们需要的需求 。

创建R表

  。

背 景 。

在实际业务需求中，我们可能会有一些数据字典之类的公共表，即现有A、B、C 独立的数据库，但是他们都需要共同的字典表 R 表,且R表的入口只能有一个，比如只能在A库进行写入，其他库则需要同步该表，故通过逻辑复制来实现.

?
1
2
3
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，并分配权限 。

?
1
2
3
4
5
6
#!/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
?
1
2
3
4
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 。

?
1
2
3
4
5
6
#!/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

分配权限，不然无法执行脚本 。

?
1
2
3
4
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服务器执行:

?
1
2
3
4
5
6
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服务器执行 。

?
1
2
3
4
5
6
7
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 。

?
1
2
3
\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统计一次 。

?
1
2
3
4
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

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我。如有错误或未考虑完全的地方，望不吝赐教.

原文链接：https://blog.csdn.net/yaoqiancuo3276/article/details/80376390 。

最后此篇关于PostgreSQL 逻辑复制 配置操作的文章就讲到这里了,如果你想了解更多关于PostgreSQL 逻辑复制 配置操作的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

27 4 0
文章推荐： PostgresSql 多表关联删除语句的操作
文章推荐： 2021超级幸福的qq情侣签名 漫长时光我只想念你
文章推荐： 女生喜欢的两个字网名2020最新版设计 新鲜
文章推荐： 好听意境的qq签名大全 触碰感情的深处读懂了幸福
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com