ERROR: cannot copy from partitioned table

[ZiBiS]

PG 13.2, Pglogical: 2.3.3 I have three servers: old_master (A) with non partitioned big table, new_master (B) with partitioned table ready to replicate data from A, and next replica with partitioned table (C), to replicate data from B. B and C are two-level partitioned.

I created pglogical replication from partitioned table on B to partitioned table on C, replication starts, no errors. When stared replication from A to B, all records copied to partitioned table on B, but no data on C. I've got following errors when tried to resynchronize partitioned table on C:

STATEMENT:  COPY "public"."my_partitioned_table"(...) TO stdout
 unexpected EOF on client connection with an open transaction
logical decoding found consistent point at 0/7F32EB8
DETAIL:  There are no running transactions.
LOG:  exported logical decoding snapshot: "00000008-0000031E-1" with 0 transaction IDs
ERROR:  cannot copy from partitioned table "my_partitioned_table"
HINT:  Try the COPY (SELECT ...) TO variant.

Replication A to B is temporary to move data to partioned table and will be removed after data migration. Any idea how to get such replications working?

[petere]

Replicating from non-partitioned to partitioned tables is not supported.

[ZiBiS]

Really? it looks completely the opposite. Replication from non partitioned to partitioned works wihout problems. I dropped subscription from B to A, but replication from B to C still not working. Also setting the following parameters did not help:

pglogical.conflict_resolution = false pglogical.use_spi = true

https://www.2ndquadrant.com/en/blog/pg-phriday-pglogical-postgres-10-partitions/

B and C are two-level partitioned and maybe that's the source of the problem...

[ZiBiS]

https://www.2ndquadrant.com/en/resources/pglogical/ ... Different partitioning configurations can be defined on the subscriber, including replication of a non-partitioned table to a partitioned one, and vice versa. Partitioning support is elastic, allowing to add and remove partitions transparently ...

[DmitryFomin1]

I've got the same issue, non-partitioned table to partitioned table replication works perfectly, but partitioned -> partitioned does not
postgresql v 13.5 pglogical v 2.4.0

tried these settings as well - no luck pglogical.conflict_resolution = error pglogical.use_spi = true

[NikolayS]

Hi @petere! Could you double-check this, please, if possible?

Indeed, looks like:

• from non-partitioned to partitioned – works well,
• from partitioned to partitioned doesn't work.

[DmitryFomin1]

test case: non-partitioned table on provider and partitioned table on subscriber (works):

on provider side

Create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;

create table prt_test (
    id             serial,
    username       text not null,
    password       text,
    created_on     timestamptz not null,
    last_logged_on timestamptz not null,
    CONSTRAINT prt_test_pk PRIMARY KEY (id, created_on)
);

insert into prt_test (username, password, created_on, last_logged_on)
    select
        random_string( (random() * 4 + 5)::int4),
        random_string( 20 ),
        now() - '1 years'::interval * random(),
        now() - '1 years'::interval * random()
    from
        generate_series(1, 10000);

pglogical=# select count(*) from prt_test;
 count
-------
 10000
(1 row)

SELECT pglogical.create_replication_set(
    set_name := 'prt_test_set',
    replicate_insert := TRUE, replicate_update := TRUE,
    replicate_delete := TRUE, replicate_truncate := TRUE
);

SELECT pglogical.replication_set_add_table(
    set_name := 'prt_test_set', relation := 'prt_test', 
    synchronize_data := TRUE
);

on subscriber side

create table prt_test (
    id             serial,
    username       text not null,
    password       text,
    created_on     timestamptz not null,
    last_logged_on timestamptz not null
)PARTITION BY RANGE (created_on);

CREATE TABLE prt_test_202010 PARTITION OF prt_test
    FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE prt_test_202011 PARTITION OF prt_test
    FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE prt_test_202012 PARTITION OF prt_test
    FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');
CREATE TABLE prt_test_202101 PARTITION OF prt_test
    FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE prt_test_202102 PARTITION OF prt_test
    FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE prt_test_202103 PARTITION OF prt_test
    FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE prt_test_202104 PARTITION OF prt_test
    FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE prt_test_202105 PARTITION OF prt_test
    FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE prt_test_202106 PARTITION OF prt_test
    FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE prt_test_202107 PARTITION OF prt_test
    FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE prt_test_202108 PARTITION OF prt_test
    FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE prt_test_202109 PARTITION OF prt_test
    FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE prt_test_202110 PARTITION OF prt_test
    FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE prt_test_202111 PARTITION OF prt_test
    FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');

SELECT pglogical.create_subscription(
    subscription_name := 'prt_test_sbscrp',
    replication_sets := array['prt_test_set'],
    provider_dsn := 'host=pg-master port=6666 dbname=pglogical user=postgres password=welcome1'
);

pglogical=# select count(*) from prt_test;
 count
-------
 10000
(1 row)

[DmitryFomin1]

test case: partitioned table on provider and partitioned table on subscriber with different partitioning schema (does not work): on provider side

Create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;

SELECT pglogical.drop_replication_set('prt_test_set');
DROP TABLE prt_test;

create table prt_test (
    id             serial,
    username       text not null,
    password       text,
    created_on     timestamptz not null,
    last_logged_on timestamptz not null,
    CONSTRAINT prt_test_pk PRIMARY KEY (id, created_on)
) PARTITION BY RANGE (created_on);

CREATE TABLE prt_test_2018 PARTITION OF prt_test
    FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
CREATE TABLE prt_test_2020 PARTITION OF prt_test
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE prt_test_2021 PARTITION OF prt_test
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
CREATE TABLE prt_test_2019 PARTITION OF prt_test
    FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');

insert into prt_test (username, password, created_on, last_logged_on)
    select
        random_string( (random() * 4 + 5)::int4),
        random_string( 20 ),
        now() - '1 years'::interval * random(),
        now() - '1 years'::interval * random()
    from
        generate_series(1, 10000);

pglogical=# select count(*) from prt_test;
 count
-------
 10000
(1 row)

SELECT pglogical.create_replication_set(
    set_name := 'prt_test_set',
    replicate_insert := TRUE, replicate_update := TRUE,
    replicate_delete := TRUE, replicate_truncate := TRUE
);

SELECT pglogical.replication_set_add_table(
    set_name := 'prt_test_set', relation := 'prt_test', 
    synchronize_data := TRUE
);

on subscriber side

SELECT pglogical.drop_subscription(
    subscription_name := 'prt_test_sbscrp'
);

DROP TABLE prt_test;

create table prt_test (
    id             serial,
    username       text not null,
    password       text,
    created_on     timestamptz not null,
    last_logged_on timestamptz not null
)PARTITION BY RANGE (created_on);

CREATE TABLE prt_test_202010 PARTITION OF prt_test
    FOR VALUES FROM ('2020-10-01') TO ('2020-11-01');
CREATE TABLE prt_test_202011 PARTITION OF prt_test
    FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
CREATE TABLE prt_test_202012 PARTITION OF prt_test
    FOR VALUES FROM ('2020-12-01') TO ('2021-01-01');
CREATE TABLE prt_test_202101 PARTITION OF prt_test
    FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');
CREATE TABLE prt_test_202102 PARTITION OF prt_test
    FOR VALUES FROM ('2021-02-01') TO ('2021-03-01');
CREATE TABLE prt_test_202103 PARTITION OF prt_test
    FOR VALUES FROM ('2021-03-01') TO ('2021-04-01');
CREATE TABLE prt_test_202104 PARTITION OF prt_test
    FOR VALUES FROM ('2021-04-01') TO ('2021-05-01');
CREATE TABLE prt_test_202105 PARTITION OF prt_test
    FOR VALUES FROM ('2021-05-01') TO ('2021-06-01');
CREATE TABLE prt_test_202106 PARTITION OF prt_test
    FOR VALUES FROM ('2021-06-01') TO ('2021-07-01');
CREATE TABLE prt_test_202107 PARTITION OF prt_test
    FOR VALUES FROM ('2021-07-01') TO ('2021-08-01');
CREATE TABLE prt_test_202108 PARTITION OF prt_test
    FOR VALUES FROM ('2021-08-01') TO ('2021-09-01');
CREATE TABLE prt_test_202109 PARTITION OF prt_test
    FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');
CREATE TABLE prt_test_202110 PARTITION OF prt_test
    FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE prt_test_202111 PARTITION OF prt_test
    FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');

SELECT pglogical.create_subscription(
    subscription_name := 'prt_test_sbscrp',
    replication_sets := array['prt_test_set'],
    provider_dsn := 'host=pg-master port=6666 dbname=pglogical user=postgres password=welcome1'
);

pglogical=# select count(*) from prt_test;
 count
-------
     0
(1 row)

on provider in postgresql.log

2021-11-30 16:23:40.478 UTC [231106] postgres@pglogical LOG:  logical decoding found consistent point at 1/4A0A1BB8
2021-11-30 16:23:40.478 UTC [231106] postgres@pglogical DETAIL:  There are no running transactions.
2021-11-30 16:23:40.478 UTC [231106] postgres@pglogical STATEMENT:  CREATE_REPLICATION_SLOT "pgl_pglogical_master_prt_test_sbscrp" LOGICAL pglogical_output
2021-11-30 16:23:40.478 UTC [231106] postgres@pglogical LOG:  exported logical decoding snapshot: "00000009-00003A61-1" with 0 transaction IDs
2021-11-30 16:23:40.478 UTC [231106] postgres@pglogical STATEMENT:  CREATE_REPLICATION_SLOT "pgl_pglogical_master_prt_test_sbscrp" LOGICAL pglogical_output
2021-11-30 16:23:40.510 UTC [231108] postgres@pglogical LOG:  starting logical decoding for slot "pgl_pglogical_master_prt_test_sbscrp"
2021-11-30 16:23:40.510 UTC [231108] postgres@pglogical DETAIL:  Streaming transactions committing after 1/4A0A1BF0, reading WAL from 1/4A0A1BB8.
2021-11-30 16:23:40.510 UTC [231108] postgres@pglogical STATEMENT:  START_REPLICATION SLOT "pgl_pglogical_master_prt_test_sbscrp" LOGICAL 1/4A0A1BF0 (expected_encoding 'UTF8', min_proto_version '1', max_proto_version '1', startup_params_format '1', "binary.want_internal_basetypes" '1', "binary.want_binary_basetypes" '1', "binary.basetypes_major_version" '1300', "binary.sizeof_datum" '8', "binary.sizeof_int" '4', "binary.sizeof_long" '8', "binary.bigendian" '0', "binary.float4_byval" '0', "binary.float8_byval" '1', "binary.integer_datetimes" '0', "hooks.setup_function" 'pglogical.pglogical_hooks_setup', "pglogical.forward_origins" '"all"', "pglogical.replication_set_names" 'prt_test_set', "relmeta_cache_size" '-1', pg_version '130004', pglogical_version '2.4.0', pglogical_version_num '20400', pglogical_apply_pid '217081')
2021-11-30 16:23:40.510 UTC [231108] postgres@pglogical LOG:  logical decoding found consistent point at 1/4A0A1BB8
2021-11-30 16:23:40.510 UTC [231108] postgres@pglogical DETAIL:  There are no running transactions.
2021-11-30 16:23:40.510 UTC [231108] postgres@pglogical STATEMENT:  START_REPLICATION SLOT "pgl_pglogical_master_prt_test_sbscrp" LOGICAL 1/4A0A1BF0 (expected_encoding 'UTF8', min_proto_version '1', max_proto_version '1', startup_params_format '1', "binary.want_internal_basetypes" '1', "binary.want_binary_basetypes" '1', "binary.basetypes_major_version" '1300', "binary.sizeof_datum" '8', "binary.sizeof_int" '4', "binary.sizeof_long" '8', "binary.bigendian" '0', "binary.float4_byval" '0', "binary.float8_byval" '1', "binary.integer_datetimes" '0', "hooks.setup_function" 'pglogical.pglogical_hooks_setup', "pglogical.forward_origins" '"all"', "pglogical.replication_set_names" 'prt_test_set', "relmeta_cache_size" '-1', pg_version '130004', pglogical_version '2.4.0', pglogical_version_num '20400', pglogical_apply_pid '217081')

on subscriber in postgresql.log

2021-11-30 16:23:40.451 UTC [217080] [unknown]@postgres LOG:  manager worker [217080] at slot 2 generation 141 detaching cleanly
2021-11-30 16:23:40.452 UTC [217081] [unknown]@pglogical LOG:  starting apply for subscription prt_test_sbscrp
2021-11-30 16:23:40.456 UTC [217082] [unknown]@template1 LOG:  manager worker [217082] at slot 2 generation 142 detaching cleanly

manual resync on subscriber side

pglogical=# select pglogical.alter_subscription_resynchronize_table('prt_test_sbscrp', 'prt_test'::regclass);
 alter_subscription_resynchronize_table
----------------------------------------
 t
(1 row)

subscriber postgresql.log

2021-11-30 16:27:18.453 UTC [217469] [unknown]@pglogical LOG:  sync worker [217469] at slot 2 generation 212 exiting with error
2021-11-30 16:27:18.456 UTC [217470] postgres@pglogical LOG:  could not receive data from client: Connection reset by peer
2021-11-30 16:27:18.456 UTC [217470] postgres@pglogical LOG:  unexpected EOF on client connection with an open transaction
2021-11-30 16:27:18.456 UTC [168232] LOG:  background worker "pglogical sync                    prt_test 16384:1530081523" (PID 217469) exited with exit code 1
2021-11-30 16:27:18.459 UTC [217471] [unknown]@pglogical LOG:  starting sync of table public.prt_test for subs

[DmitryFomin1]

also tried partitioned table -> partitioned table with same partitioning schema and partitioned table -> regular heap table, same result

[StepBee]

Did anyone actually found a solution or a workaround for this issue? We are still facing the same issue and cant find a way around it.

Any hint is much appreciated

[vitabaks]

Hi @petere !

non-partitioned table to partitioned table replication works perfectly, but partitioned -> partitioned does not

Are there any plans to fix this problem? Thanks!

[petere]

There is no more major feature work planned on pglogical. To get this kind of functionality, use the logical replication built into PostgreSQL.

[vitabaks]

@petere thanks for the answer.