From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: zzzzz.graf@gmail.com
Subject: BUG #18203: Logical Replication initial sync failure
Date: Sat, 18 Nov 2023 01:25:52 +0000
Message-ID: <18203-df37fe354b626670@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18203
Logged by: Justin G
Email address: zzzzz.graf@gmail.com
PostgreSQL version: 15.2
Operating system: Ubuntu 20
Description:
We’ve found an edge case that breaks logical replication.
Procedure:
alter system set wal_level to 'logical' ;
create database source; --pg 11
create database destin; --pg 15
create table no_col(); -- pg 11
insert into no_col default values;
insert into no_col default values;
insert into no_col default values;
insert into no_col default values;
insert into no_col default values;
CREATE PUBLICATION no_col_pub for table no_col;
create table no_col(); --pg 15
CREATE SUBSCRIPTION no_col_sub --pg15
CONNECTION
'host=<hostname/ip>
port=5432
user=<set username>
dbname=source
password=<set the password>'
PUBLICATION no_col_pub
WITH (
CREATE_SLOT = TRUE,
ENABLED = TRUE,
COPY_DATA = TRUE
);
We now have a table with 5 null rows. This is a valid table, pg_dump will
dump it and restore it. count(*) will return 5.
The dump file produced by pg_dump version 15 generates this SQL
COPY public.no_col FROM stdin;
However, the copy command from PG 15 using logical replication includes (),
which normally has a list of columns between the parentheses.
COPY public.no_col () TO STDOUT
Logical replication worker returns with the following error:
2023-11-17 20:40:16.141 UTC [151084] ERROR: could not start initial
contents copy for table "public.no_col": ERROR: syntax error at or near
")"
LINE 1: COPY public.no_col () TO STDOUT
If the direction of replication is reversed from PG15 to PG14, the above
error is not thrown, and the 5 rows are copied over.
Version tested
Publishers PG 11.14, 14.2, 15.2
Subscriber PG 14.2, 15.2, , RDS 14.7
The error has been observed in 15.2 and in RDS PostgreSQL 14.7.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, zzzzz.graf@gmail.com
Subject: Re: BUG #18203: Logical Replication initial sync failure
In-Reply-To: <18203-df37fe354b626670@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox