Re: Logical replication troubles

Thank you for that clarification. It helps me understand how things work a
lot better.
I know this might be a bit off topic, but my challenge here is that we are
using Patroni (by using Zalando's postgres-operator for Kubernetes), and
any replication slot not created by Patroni, seems to be removed, whenever
the master pod restarts. We therefore specify in the Patroni config, that a
permanent replication slot should be created for our usage to do logical
replication of some select tables, to our data warehouse. That means that
the replication slot is created as soon as the database is ready to use,
which is also before any tables, data or publications are created. Can you
give me a hint as to what the correct way to set this up would be?
Or do I need to try contacting the Patroni devs instead?

On Fri, 22 May 2020 at 11:00, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 2020-05-20 17:16, Anders Bøgh Bruun wrote:
> > [67] LOG: logical replication apply worker for subscription
> > "widgets_sub" has started
> > [67] DEBUG: connecting to publisher using connection string
> > "dbname=testdb host=master port=5432 user=repuser password=abc123"
> > [67] ERROR: could not receive data from WAL stream: ERROR: publication
> > "widgets_pub" does not exist
> > CONTEXT: slot "my_slot", output plugin "pgoutput", in the change
> > callback, associated LSN 0/1674958
> > [1] DEBUG: unregistering background worker "logical replication worker
> > for subscription 16396"
> > [1] LOG: background worker "logical replication worker" (PID 67) exited
> > with exit code 1
> >
> > I can verify that the publication called widgets_pub does exist, and I
> > am not seeing any errors on the sending side.
> >
> > The SQL-file named "works" just has the creation of the replication slot
> > moved down to after I insert some data into the table I want to
> > replicate. And that works as expected.
>
> You need to create the publication before the replication slot. The
> walsender's view of the world moves along with the WAL it is
> decoding/sending. So when the subscription worker connects, it
> initially sees a state as of the creation of the replication slot, when
> the publication did not exist yet.
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

--
Anders Bøgh Bruun

Infrastructure Architect

CellPoint digital
cellpointdigital.com
*WE MAKE TRAVEL EASIER™*

M: +45 31 14 87 41
E: anders(at)cellpointdigital(dot)com

Chicago | *Copenhagen* | Dubai | London | Miami | Pune | Singapore