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

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
```

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.

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%40mail.gmail.com

--
With Regards,
Amit Kapila.