案头见蠹鱼,犹胜凡俦侣。这篇文章主要讲述HGDB企业版V6逻辑复制搭建相关的知识,希望能为你提供帮助。
目录
环境
文档用途
详细信息
环境
系统平台:
Linux x86-64 Red Hat Enterprise Linux 7
版本:
6.0
【HGDB企业版V6逻辑复制搭建】文档用途
本文只要用于描述HGDB企业版V6逻辑复制在redhat7环境下的搭建过程。
详细信息
一、环境介绍
数据库版本:HGDB企业版V6.0.1
操作系统版本:Redhat7.x
服务器IP地址:192.168.230.51(发布端)
192.168.230.52(订阅端)
二、逻辑复制搭建1、发布端修改数据库运行参数
highgo=# alter system set listen_addresses=\'*\';
highgo=# alter system set wal_level=\'logical\'; highgo=# alter system set max_replication_slots=30; highgo=# alter system set max_wal_senders=40; highgo=# alter system set max_logical_replication_workers=40; highgo=# alter system set max_sync_workers_per_subscription=10; highgo=# alter user postgres with password\'postgres\'; |
host
all
all
0.0.0.0/0
md5 host replication all 0.0.0.0/0 md5 |
pg_ctl start |
highgo=# create table test1(id int primary key,txt text);
highgo=# insert into test1 values(1,\'a\'); highgo=# insert into test1 values(2,\'b\'); highgo=# create table test2(id int primary key,txt text); highgo=# insert into test2 values(1,\'a\'); highgo=# insert into test2 values(2,\'b\'); |
highgo=# create user logicalrep replication login encrypted password \'Logical??@123??\';
|
highgo=# grant usage on schema public to logicalrep;
highgo=# grant select on all tables in schema public to logicalrep; highgo=# alter default privileges in schema public grant select on tables to logicalrep ; |
highgo=# create publication pub1 for table public.test1,public.test2;
highgo=# select * from pg_publication; #查询发布详情 |
highgo=# alter system set listen_addresses=\'*\';
highgo=# alter system set max_replication_slots=30; highgo=# alter system set max_wal_senders=40; highgo=# alter system set max_logical_replication_workers=40; highgo=# alter system set max_sync_workers_per_subscription=10; |
pg_dump -h 192.168.80.251 -d postgres -U postgres -s -t test1 -t test2 -f createtable.sql psql -f createtable.sql |
highgo=# create subscription sub1 connection \'host=192.168.230.51 port=5866 dbname=highgo user=logicalrep password=Logical??@123??\' publication pub1;
|
highgo=# select * from test1;
id | txt ----+----- 1 | a 2 | b (2 rows) highgo=# select * from test2; id | txt ----+----- 1 | a 2 | b (2 rows) |
##发布端新插入数据 highgo=# insert into test1 values(3,\'c\'); INSERT 0 1 highgo=# select * from test1; id | txt ----+----- 1 | a 2 | b 3 | c (3 rows) ##订阅端查询数据是否同步 highgo=# select * from test1; id | txt ----+----- 1 | a 2 | b 3 | c (3 rows) |
三、逻辑复制相关查询1、发布端
select
*
from
pg_publication;
##查询数据库内发布信息 select * from pg_stat_replication; ##查询流复制相关信息 select * from pg_publication_tables; ##查询已经发布的表的信息 |
select * from pg_subscription;
##查询订阅信息 select * from pg_subscription_rel; ##查询每张表的同步状态 select * from pg_replication_origin_status; |
四、逻辑复制注意事项不支持DDL复制(ALTER TABLE/CREATE TABLE)
不支持TEMPRORARY表和UNLOGGED表复制
不支持Sequences复制( serial/bigserial/identity)
不支持TRUNCATE操作复制
不支持大对象复制
不支持视图、物化视图、外部表复制被复制的表上最好有主键约束;如果没有,必须执行:ALTER TABLE reptest REPLICA IDENTITY FULL;
注:订阅端的复制表是可修改的,复制表一旦修改,发布者和订阅者会数据不一致,进而打破复制。
推荐阅读
- VMware Tanzu社区版初体验
- ACL理论及配置
- 5款国产开源低代码开发平台推荐
- 链路聚合
- C++类和对象--多态
- 自研python常用工具库(prestool)
- Python实现解千千音乐网站中的JS加密算法
- 小码匠数据科学之旅-猿门又多一枚女生
- Flutter的安装与设置(第一节)