Re: BUG #18683: A publication must be created *before* a logical rep slot in order to be used with that slot?

On Sat, Nov 2, 2024 at 12:26 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 18683
> Logged by: Anthony Accomazzo
> Email address: accomazz(at)gmail(dot)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(at)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%40mail.gmail.com

--
With Regards,
Amit Kapila.