Remote postgres : replication slot and publication exists but the script fail

[deft01]

Hi,

Im getting this behavior :

In [12]: import os
    ...: import pypgoutput
    ...:
    ...: HOST = "172.41.0.2"
    ...: PORT = 5432
    ...: DATABASE_NAME = "redsystem_db"
    ...: USER = "postgres"
    ...: PASSWORD = "lol"
    ...:
    ...:
    ...: cdc_reader = pypgoutput.LogicalReplicationReader(
    ...:                 publication_name="test_pub",
    ...:                 slot_name="test_slot",
    ...:                 host=HOST,
    ...:                 database=DATABASE_NAME,
    ...:                 port=PORT,
    ...:                 user=USER,
    ...:                 password=PASSWORD,
    ...:             )
    ...: for message in cdc_reader:
    ...:     print(message.json(indent=2))
    ...:
    ...: cdc_reader.stop()
Error consuming stream from slot: 'test_slot'. publication "test_pub" does not exist
CONTEXT:  slot "test_slot", output plugin "pgoutput", in the change callback, associated LSN 0/173BD10

But in postgres I can attest the slot and the publication exist.

redsystem_db=# CREATE PUBLICATION test_pub FOR ALL TABLES;
ERROR:  publication "test_pub" already exists

slot_name |  plugin  | slot_type | datoid |   database   | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | s
----------+----------+-----------+--------+--------------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+--
test_slot | pgoutput | logical   |  24576 | redsystem_db | f         | f      |            |      |          494 | 0/173BC70   | 0/173BCA8           | reserved   |```

I'm using this inside a docker-compose network. The database is on another machine (cf the ip in my config). They can ping eachothers etc.

BUT your script is working well on my main machine with all the things locally (database + script).

[deft01]

I have the same version of postgres in docker and localhost.

[deft01]

I'm investigating the error. I think the problem comes from psycopg2 outputing this unintended error when starting the replication slot :

In [8]: cur.start_replication(slot_name='test_slot', decode=False, options=options)                                 │
---------------------------------------------------------------------------                                         │
ObjectNotInPrerequisiteState              Traceback (most recent call last)                                         │
<ipython-input-8-cf245c06a3be> in <module>                                                                          │
----> 1 cur.start_replication(slot_name='test_slot', decode=False, options=options)                                 │
                                                                                                                    │
/usr/local/lib/python3.9/dist-packages/psycopg2/extras.py in start_replication(self, slot_name, slot_type, start_lsn│
, timeline, options, decode, status_interval)                                                                       │
    618             command += ")"                                                                                  │
    619                                                                                                             │
--> 620         self.start_replication_expert(                                                                      │
    621             command, decode=decode, status_interval=status_interval)                                        │
    622                                                                                                             │
                                                                                                                    │
ObjectNotInPrerequisiteState: replication slot "test_slot" was not created in this database                         │

Do you have any idea how to fix it ? The error isn't meaningfull imo...

[dgea005]

Hey,

Thanks for opening the issue :-)

I don't have much time to look at this at the moment and I'm also not sure how to reproduce it.

I'd suggest testing if you can manually get the changes against the remote host using a client such as psql

These links could be useful

• https://medium.com/@film42/getting-postgres-logical-replication-changes-using-pgoutput-plugin-b752e57bfd58
• https://www.postgresql.org/docs/current/logicaldecoding-example.html
• https://www.postgresql.org/docs/14/functions-admin.html#FUNCTIONS-REPLICATION

There is also some configuration required on the DB https://www.postgresql.org/docs/current/runtime-config-wal.html
I.e., the wal_level configuration should be set to logical

[Howe829]

I've solved this problem by using the following sqls:
CREATE PUBLICATION test_pub FOR ALL TABLES;
select pg_create_logical_replication_slot('test_slot', 'pgoutput');

Hope it will help you.