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

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';

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