Обсуждение: table not found on publisher
Hello All,
It is a bit specific logical replication setup where we try to replicate
a partitioned table (pg14.1) to a non-partitioned one (pg11.14)
After establishing everything the subscriber fails on the initial copy with
ERROR: table "public.tab01" not found on publisher
If the subscription is created with (copy_data=false) changes are propagated okay.
So I'm puzzled. Any comments ?
Thanks a lot
Rado
Here is the example:
SOURCE:
test[14.1]=# CREATE TABLE tab01 (id int PRIMARY KEY) PARTITION BY RANGE(id);
CREATE TABLE
test[14.1]=# CREATE TABLE tab01_10 PARTITION OF tab01 FOR VALUES FROM (0) TO (10);
CREATE TABLE
test[14.1]=# CREATE TABLE tab01_20 PARTITION OF tab01 FOR VALUES FROM (10) TO (20);
CREATE TABLE
test[14.1]=# insert into tab01 values (generate_series(1,15));
INSERT 0 15
test[14.1]=# CREATE PUBLICATION pub01 FOR TABLE public.tab01 WITH (publish_via_partition_root = true);
CREATE PUBLICATION
test[14.1]=#
CREATE TABLE
test[14.1]=# CREATE TABLE tab01_10 PARTITION OF tab01 FOR VALUES FROM (0) TO (10);
CREATE TABLE
test[14.1]=# CREATE TABLE tab01_20 PARTITION OF tab01 FOR VALUES FROM (10) TO (20);
CREATE TABLE
test[14.1]=# insert into tab01 values (generate_series(1,15));
INSERT 0 15
test[14.1]=# CREATE PUBLICATION pub01 FOR TABLE public.tab01 WITH (publish_via_partition_root = true);
CREATE PUBLICATION
test[14.1]=#
TARGET:
test[11.14]=# CREATE TABLE tab01 (id int PRIMARY KEY);
CREATE TABLE
CREATE TABLE
test[11.14]=# create subscription sub01
connection 'host=/var/run/postgresql port=5435 dbname=test user=postgres'
publication pub01 ;
NOTICE: created replication slot "sub01" on publisher
CREATE SUBSCRIPTION
test[11.14]=#
publication pub01 ;
NOTICE: created replication slot "sub01" on publisher
CREATE SUBSCRIPTION
test[11.14]=#
Just an update.
If the target is upgraded to pg13 then the initial copy goes fine.
Unfortunately it is a 40T db in a sunset, so we'd rather won't upgrade.
On Fri, Feb 11, 2022 at 4:50 PM Radoslav Nedyalkov <rnedyalkov@gmail.com> wrote:
Hello All,It is a bit specific logical replication setup where we try to replicatea partitioned table (pg14.1) to a non-partitioned one (pg11.14)After establishing everything the subscriber fails on the initial copy withERROR: table "public.tab01" not found on publisherIf the subscription is created with (copy_data=false) changes are propagated okay.So I'm puzzled. Any comments ?Thanks a lotRadoHere is the example:SOURCE:test[14.1]=# CREATE TABLE tab01 (id int PRIMARY KEY) PARTITION BY RANGE(id);
CREATE TABLE
test[14.1]=# CREATE TABLE tab01_10 PARTITION OF tab01 FOR VALUES FROM (0) TO (10);
CREATE TABLE
test[14.1]=# CREATE TABLE tab01_20 PARTITION OF tab01 FOR VALUES FROM (10) TO (20);
CREATE TABLE
test[14.1]=# insert into tab01 values (generate_series(1,15));
INSERT 0 15
test[14.1]=# CREATE PUBLICATION pub01 FOR TABLE public.tab01 WITH (publish_via_partition_root = true);
CREATE PUBLICATION
test[14.1]=#TARGET:test[11.14]=# CREATE TABLE tab01 (id int PRIMARY KEY);
CREATE TABLEtest[11.14]=# create subscription sub01connection 'host=/var/run/postgresql port=5435 dbname=test user=postgres'
publication pub01 ;
NOTICE: created replication slot "sub01" on publisher
CREATE SUBSCRIPTION
test[11.14]=#
Radoslav Nedyalkov <rnedyalkov@gmail.com> writes:
> If the target is upgraded to pg13 then the initial copy goes fine.
> Unfortunately it is a 40T db in a sunset, so we'd rather won't upgrade.
Yeah, after looking at the code, pre-v13 versions simply don't know
how to do initial sync from anything except a plain table. The changes
involved in that were significant enough that nobody is going to be
on board with back-patching them.
The wording of this error message is pretty confusing though.
I think it might be reasonable to change the back branches so
that they report something like "can't sync from a non-table"
rather than claiming the object doesn't exist at all.
regards, tom lane