Postgres的发布与订阅

最新推荐文章于 2025-04-02 10:58:46 发布

文档搬运工

最新推荐文章于 2025-04-02 10:58:46 发布

阅读量3k

点赞数 1

分类专栏： Postgres SQL

本文链接：https://blog.csdn.net/xxzhaobb/article/details/118704383

版权

Postgres SQL 专栏收录该内容

39 篇文章

订阅专栏

本文介绍了在PostgreSQL中如何设置逻辑复制，包括创建发布端和订阅端，以及处理同步问题。在发布端创建表和发布，订阅端进行订阅配置，并通过实例展示了数据的插入和同步。然而，TRUNCATE操作不会被同步，需要通过触发器变相实现。文章还提及了删除订阅事务槽的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 发布与订阅（个人感觉类似于Oracle的stream，和sqlserver的发布与订阅）

参考文档：http://www.postgres.cn/docs/11/logical-replication.html

发布端： 192.168.2.74 PG10.16 pub_t1 ;
订阅端： 192.168.2.80 PG10.15 pub_t1 ;

-- 创建一个数据库，作为订阅端

mydb=# create database repdb;
CREATE DATABASE
mydb=#

-- 创建发布端

create table pub_t1(id int primary key,name text,crt_time timestamp);
create publication test_pub1 for table pub_t1;

select * from pg_publication;

mydb=# create table pub_t1(id int primary key,name text,crt_time timestamp);
CREATE TABLE
mydb=# create publication test_pub1 for table pub_t1;
CREATE PUBLICATION
mydb=# 
mydb=# select * from pg_publication;
  pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete 
-----------+----------+--------------+-----------+-----------+-----------
 test_pub1 |       10 | f            | t         | t         | t
(1 row)

mydb=#

-- 配置订阅端（订阅段没有开启归档，仅仅设置了wal_level = logical）

create table pub_t1(id int primary key,name text,crt_time timestamp);

create subscription test_sub1 connection 'hostaddr=192.168.2.74 port=5432 password=oracle user=postgres dbname=mydb' publication test_pub1  with (enabled, create_slot, slot_name='sub1_from_pub1t1')

select * from pg_subscription ;

repdb=# create subscription test_sub1 connection 'hostaddr=192.168.2.74 port=5432 password=oracle user=postgres dbname=mydb' publication test_pub1  with (enabled, create_slot, slot_name='sub1_from_pub1t1')
repdb-# ;
NOTICE:  created replication slot "sub1_from_pub1t1" on publisher
CREATE SUBSCRIPTION


repdb=# \x
Expanded display is on.
repdb=# select * from pg_subscription ;
-[ RECORD 1 ]---+--------------------------------------------------------------------------
subdbid         | 16393
subname         | test_sub1
subowner        | 10
subenabled      | t
subconninfo     | hostaddr=192.168.2.74 port=5432 password=oracle user=postgres dbname=mydb
subslotname     | sub1_from_pub1t1
subsynccommit   | off
subpublications | {test_pub1}

repdb=#

发布端和订阅段的表名称要一样

log中，创建订阅的时候，一直卡这里 (主要原因是，在同一台机器上的pg会出现这个问题，这个在pgadmin上有说明)

            WITH (connect = true, enabled = true, copy_data = true, create_slot = false, synchronous_commit = 'off');
2021-07-13 13:48:30.039 CST [112030] ERROR:  relation "public.pub_t1" does not exist
2021-07-13 13:48:30.039 CST [112030] STATEMENT:  create subscription test_sub1 connection 'host=192.168.2.74 port=5432 user=postgres password=oracle dbname=mydb' publication test_pub1;
2021-07-13 13:48:48.764 CST [113598] LOG:  logical decoding found initial starting point at 4/3E0342E0
2021-07-13 13:48:48.764 CST [113598] DETAIL:  Waiting for transactions (approximately 1) older than 16276 to end.

-- 插入测试数据

insert into pub_t1 values(1,'aa',now());
select * from pub_t1;
select * from pub_t1;

mydb=# select * from pub_t1;
 id | name |          crt_time          
----+------+----------------------------
  1 | aa   | 2021-07-13 16:30:56.141042
(1 row)

mydb=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

mydb=# 

repdb=# select * from pub_t1;
 id | name |          crt_time          
----+------+----------------------------
  1 | aa   | 2021-07-13 16:30:56.141042
(1 row)

repdb=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)

repdb=#

-- truncate 是否会同步过去，不会同步过去，在发布的选项中，只有insert，update，delete 。
变相实现方法，同步触发器，通过触发器在订阅段执行truncate（测试过程略）。

mydb=# truncate table pub_t1;
TRUNCATE TABLE
mydb=# select * from pub_t1;
 id | name | crt_time 
----+------+----------
(0 rows)

mydb=# 
repdb=# select * from pub_t1;
-[ RECORD 1 ]------------------------
id       | 1
name     | aa
crt_time | 2021-07-13 16:30:56.141042

repdb=#

-- other
删除事务槽

mydb=# \c repdb
You are now connected to database "repdb" as user "postgres".
repdb=# alter subscription test_sub1 set (slot_name=none);
ERROR:  cannot set slot_name = NONE for enabled subscription
repdb=# alter subscription test_sub1 disable;
ALTER SUBSCRIPTION
repdb=# alter subscription test_sub1 set (slot_name=none);
ALTER SUBSCRIPTION
repdb=# alter subscription test_sub2 disable;
ALTER SUBSCRIPTION
repdb=# alter subscription test_sub2 set (slot_name=none);
ALTER SUBSCRIPTION
repdb=# drop subscription test_sub1;
DROP SUBSCRIPTION
repdb=# drop subscription test_sub2;
DROP SUBSCRIPTION
repdb=#

END