pglogical
pglogical copied to clipboard
2ndQuadrant
ERROR: cannot copy from partitioned table
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?
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...
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 ...
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
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.
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)
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
also tried partitioned table -> partitioned table with same partitioning schema and partitioned table -> regular heap table, same result
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
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!
There is no more major feature work planned on pglogical. To get this kind of functionality, use the logical replication built into PostgreSQL.