pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18683: A publication must be created *before* a logical rep slot in order to be used with that slot?
3+ messages / 3 participants
[nested] [flat]
* BUG #18683: A publication must be created *before* a logical rep slot in order to be used with that slot?
@ 2024-11-01 18:09 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2024-11-01 18:09 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: accomazz@gmail.com
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
```
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18683: A publication must be created *before* a logical rep slot in order to be used with that slot?
@ 2024-11-04 09:56 Peter Eisentraut <peter@eisentraut.org>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Peter Eisentraut @ 2024-11-04 09:56 UTC (permalink / raw)
To: accomazz@gmail.com; pgsql-bugs@lists.postgresql.org
On 01.11.24 19:09, PG Bug reporting form wrote:
> 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
Do the docs anywhere suggest you can do this? The normal way to set up
logical replication is to run CREATE SUBSCRIPTION, which makes the slot
for you. And CREATE SUBSCRIPTION requires specifying a publication, so
it already has to exist beforehand.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18683: A publication must be created *before* a logical rep slot in order to be used with that slot?
@ 2024-11-06 10:32 Amit Kapila <amit.kapila16@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Amit Kapila @ 2024-11-06 10:32 UTC (permalink / raw)
To: accomazz@gmail.com; pgsql-bugs@lists.postgresql.org
On Sat, Nov 2, 2024 at 12:26 AM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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
> ```
This is a known behavior and I have seen such reports earlier. There
is a discussion of a related issue in the email [1] with a proposed
patch to solve it. I don't think the proposed solution can help with
getting the data generated before the creation of publication but one
can avoid getting repeated ERRORs. You can give it a try and share
your feedback on that thread [1], if possible.
[1] - https://www.postgresql.org/message-id/CALDaNm0-n8FGAorM%2BbTxkzn%2BAOUyx5%3DL_XmnvOP6T24%2B-NcBKg%40...
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-06 10:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-01 18:09 BUG #18683: A publication must be created *before* a logical rep slot in order to be used with that slot? PG Bug reporting form <noreply@postgresql.org>
2024-11-04 09:56 ` Peter Eisentraut <peter@eisentraut.org>
2024-11-06 10:32 ` Amit Kapila <amit.kapila16@gmail.com>
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