From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: accomazz@gmail.com
Subject: BUG #18683: A publication must be created *before* a logical rep slot in order to be used with that slot?
Date: Fri, 01 Nov 2024 18:09:35 +0000
Message-ID: <18683-a98f79c0673be358@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18683
Logged by: Anthony Accomazzo
Email address: accomazz@gmail.com
PostgreSQL version: 17.0
Operating system: MacOS 15.0.1 (Sequoia)
Description:
It seems that if you (1) create a logical replication slot (2) and activity
happens in your database then (3) you create a publication, the combination
of that slot + publication is not usable (you can't connect).
i.e. if there is any activity "in" a slot that predates the create statement
for a pub, the slot+pub combo will not work.
Is this known behavior? It's surprising just insofar as I'd hope either:
- the docs cover this
- the error message that one gets could be made clearer
Reproduction steps:
```
create table public.mytable (
id serial primary key,
inserted_at timestamp default now(),
updated_at timestamp default now()
);
create table public.mytable2 (
id serial primary key,
inserted_at timestamp default now(),
updated_at timestamp default now()
);
-- ensure clean start
drop publication test_pub;
select pg_drop_replication_slot('test_slot');
-- create the slot
select pg_create_logical_replication_slot('test_slot', 'pgoutput');
-- insert into any table
insert into mytable2 (id) values (default);
-- create a pub
create publication test_pub for table mytable;
-- cannot connect to slot
-- in shell:
$ pg_recvlogical -d
postgresql://postgres:postgres@localhost:5432/sequin_dev_rep --slot
test_slot --start -o proto_version=1 -o publication_names='test_pub' -f -
pg_recvlogical: error: unexpected termination of replication stream: ERROR:
publication "test_pub" does not exist
CONTEXT: slot "test_slot", output plugin "pgoutput", in the change
callback, associated LSN A/7D3CD9F0
pg_recvlogical: disconnected; waiting 5 seconds to try again
pg_recvlogical: error: unexpected termination of replication stream: ERROR:
publication "test_pub" does not exist
```
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, accomazz@gmail.com
Subject: Re: BUG #18683: A publication must be created *before* a logical rep slot in order to be used with that slot?
In-Reply-To: <18683-a98f79c0673be358@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