postgresql 11 的逻辑复制 logical replication 之二 alter table replica identity using

落日映苍穹つ 2022-12-11 01:23 305阅读 0赞

os: centos 7.4.1708  
db: postgresql 11.7

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.4.1708 (Core) 
    # su - postgres
    Last login: Sun Sep 27 13:38:50 CST 2020 on pts/2
    $ 
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
    (1 row)

# create publication #

peiybdb=# create table tmp_t0(
     c0 varchar(100),
     c1 varchar(100)
    );
    
    peiybdb=# \d+ tmp_t0
                                              Table "public.tmp_t0"
     Column |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
    --------+------------------------+-----------+----------+---------+----------+--------------+-------------
     c0     | character varying(100) |           |          |         | extended |              | 
     c1     | character varying(100) |           |          |         | extended |              |

peiybdb=# CREATE PUBLICATION test1 FOR TABLE tmp_t0 ;
    
    peiybdb=# select * from pg_publication;
     pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate 
    ---------+----------+--------------+-----------+-----------+-----------+-------------
     test1   |       10 | f            | t         | t         | t         | t
    (1 row)
    
    peiybdb=# select * from pg_publication_rel;
     prpubid | prrelid 
    ---------+---------
      106992 |  106989
    (1 row)
    
    peiybdb=# select * from pg_publication_tables ;
     pubname | schemaname | tablename 
    ---------+------------+-----------
     test1   | public     | tmp_t0
    (1 row)

# create subscription #

testdb=# create table tmp_t0(
     c0 varchar(100),
     c1 varchar(100)
    );
    
    testdb=# \d+ tmp_t0
                                              Table "public.tmp_t0"
     Column |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
    --------+------------------------+-----------+----------+---------+----------+--------------+-------------
     c0     | character varying(100) |           |          |         | extended |              | 
     c1     | character varying(100) |           |          |         | extended |              |

testdb=# CREATE SUBSCRIPTION test1  CONNECTION 'dbname=peiybdb host=nodepg11 user=repl password=xxoo' PUBLICATION test1;

testdb=# select * from pg_subscription;
     subdbid | subname | subowner | subenabled |                     subconninfo                      | subslotname | subsynccommit |subpublications 
    ---------+---------+----------+------------+------------------------------------------------------+-------------+---------------+-----------------
       32862 | test1   |       10 | t          | dbname=peiybdb host=nodepg11 user=repl password=xxoo | test1       | off           | {test1}
    (1 row)
    
    testdb=# select * from pg_subscription_rel;
     srsubid | srrelid | srsubstate | srsublsn 
    ---------+---------+------------+----------
      106988 |   32863 | d          | 
    (1 row)

# update、delete 报错 #

peiybdb=# insert into tmp_t0(c0,c1) values('1','1');
    INSERT 0 1
    peiybdb=# 
    peiybdb=# update tmp_t0 set c1='11' where c0='1';
    ERROR:  cannot update table "tmp_t0" because it does not have a replica identity and publishes updates
    HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
    peiybdb=# 
    peiybdb=# delete from tmp_t0 where c0='1';
    ERROR:  cannot delete from table "tmp_t0" because it does not have a replica identity and publishes deletes
    HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

insert 可以，但是 update，delete 报错了。

网上搜索后，发现是需要配置replica identity

peiybdb=# alter table tmp_t0 add primary key(c0);
    
    peiybdb=# alter table tmp_t0 replica identity using index tmp_t0_pkey;

peiybdb=# \d+ tmp_t0 
                                              Table "public.tmp_t0"
     Column |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
    --------+------------------------+-----------+----------+---------+----------+--------------+-------------
     c0     | character varying(100) |           | not null |         | extended |              | 
     c1     | character varying(100) |           |          |         | extended |              | 
    Indexes:
        "tmp_t0_pkey" PRIMARY KEY, btree (c0) REPLICA IDENTITY
    Publications:
        "test1"
    
    peiybdb=# update tmp_t0 set c1='11' where c0='1';
    
    peiybdb=# delete from tmp_t0 where c0='1';

所以，表要有主键，需要成为一种规范，一种标准。

发表评论取消回复

表情：

评论列表（有 0 条评论，305人围观）

还没有评论，来说两句吧...

相关阅读

相关 PostgreSQL Replication之第一章理解复制概念一

在本章中，将会介绍不同的复制概念，您会了解哪些类型的复制对哪一种实用场景是最合适的。在本章的最后，您将能够判断某个概念在各种情况下是否是可行的。我们在本章将介绍以下主题

本是古典何须时尚/ 2022年12月28日 08:18/ 0 赞/ 112 阅读

相关 postgresql 11 的逻辑复制 logical replication 之二 alter table replica identity using

os: centos 7.4.1708 db: postgresql 11.7 版本 cat /etc/centos-release CentOS

落日映苍穹つ/ 2022年12月11日 01:23/ 0 赞/ 306 阅读

相关 PostgreSQL 动态表复制（CREATE TABLE AS&CREATE TABLE LIKE）

前言项目中有表复制的需求，而且是动态复制，即在存储过程里根据参数数组的值循环复制n张结构（约束、索引等）等一致的一组表，PostgreSQL提供了两种语法来进行表复制，

曾经终败给现在/ 2022年11月17日 03:57/ 0 赞/ 334 阅读

相关 MongoDB 复制集(Replica Set)

> 复制集(replica Set)或者副本集是MongoDB的核心高可用特性之一，它基于主节点的oplog日志持续传送到辅助节点，并重放得以实现主从节点一致。再结合心跳机制，

向右看齐/ 2022年09月26日 15:53/ 0 赞/ 173 阅读

相关 PostgreSQL 动态表复制（CREATE TABLE...LIKE）

前言项目中有表复制的需求，而且是动态复制，即在存储过程里根据参数数组的值循环复制n张结构（约束、索引等）等一致的一组表，PostgreSQL提供了两种语法来进行表复制，

Dear 丶/ 2022年09月25日 12:27/ 0 赞/ 388 阅读

相关 ALTER TABLE语法--mysql

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] .

电玩女神/ 2022年09月19日 12:24/ 0 赞/ 181 阅读

相关 PG逻辑复制的REPLICA IDENTITY设置

前两天同事问了一个PG的错误，创建一张普通表，insert插入正常，但是执行update和delete时，提示这个错误， SQL 错误 [55000]: ERROR:

拼搏现实的明天。/ 2022年09月01日 10:57/ 0 赞/ 322 阅读

相关【Mysql】Alter table 使用

Alter table 可以对表进行相应的操作，包括：添加列，删除列，修改列，数据库重新命名添加列： ALTER TABLE tb_family_info ADD

心已赠人/ 2022年05月28日 00:29/ 0 赞/ 266 阅读

相关 MySQL Replication和Oracle logical standby的原理对比

转载于:[http://blog.csdn.net/linwaterbin/article/details/8229511][http_blog.csdn.net_linwat

电玩女神/ 2022年05月13日 12:50/ 0 赞/ 203 阅读

相关 MySQL Replication--TABLE_ID与行格式复制

BINLOG中的TABLE\_ID 在ROW格式的二进制中，事件信息中没有列的信息，需要通过Table\_Map将表名对于的表信息加载到cache中，然后根据事件信息中的列下

青旅半醒/ 2021年10月25日 14:16/ 0 赞/ 137 阅读