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

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