`Error: start replication: read last position: no rows in result set` during `pgstream run`

[Qrtn]

Hey there, I'm getting this error when trying to start pgstream:

using config file: pg2webhook.env
2024-09-04T17:25:52.166675-07:00 INF logger.go:37 > running subscription server...
2024-09-04T17:25:52.166861-07:00 INF logger.go:37 > running postgres listener...
2024-09-04T17:25:52.167807-07:00 INF logger.go:37 > subscription server listening on: :9900... service=webhook_subscription_server

   ____    __
  / __/___/ /  ___
 / _// __/ _ \/ _ \
/___/\__/_//_/\___/ v4.12.0
High performance, minimalist Go web framework
https://echo.labstack.com
____________________________________O/_______
                                    O\
2024-09-04T17:25:52.166866-07:00 INF logger.go:37 > running webhook notifier...
⇨ http server started on [::]:9900
2024-09-04T17:25:52.188372-07:00 INF logger.go:37 > replication handler: identifySystem success db_name=pgstream position=5351318109128 service=postgres_replication_handler slot_name=pgstream_pgstream_slot system_id=7241802899958812686 timeline=1
Error: start replication: read last position: no rows in result set

Before this, I ran pgstream init and created a pgstream database and schema for PGSTREAM_POSTGRES_LISTENER_URL and PGSTREAM_WEBHOOK_SUBSCRIPTION_STORE_URL.

Any idea why this might be? Happy to provide any additional info you need!

[eminano]

Hi @Qrtn ! This error usually happens when the replication handler can't find a replication slot in the configured database. Is is possible that the init was run against a different postgres URL than the one configured for the listener (PGSTREAM_POSTGRES_LISTENER_URL)? If no --pgurl flag is provided the init command will default to using postgres://postgres:postgres@localhost?sslmode=disable.

If that's not the case, could you confirm that the replication slot exists in your postgres database? You can check by running the following query:

select * from pg_replication_slots;

You should see something along these lines in the response:

+------------------------+----------+-----------+--------+----------+-----------+--------+------------+--------+--------------+-------------+---------------------+------------+---------------+-----------+
| slot_name              | plugin   | slot_type | datoid | database | temporary | active | active_pid | xmin   | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase |
|------------------------+----------+-----------+--------+----------+-----------+--------+------------+--------+--------------+-------------+---------------------+------------+---------------+-----------|
| pgstream_postgres_slot | wal2json | logical   | 5      | postgres | False     | False  | <null>     | <null> | 812          | 0/164BE28   | 0/164BE60           | reserved   | <null>        | False     |
+------------------------+----------+-----------+--------+----------+-----------+--------+------------+--------+--------------+-------------+---------------------+------------+---------------+-----------+

Let me know if this helps!

[Qrtn]

ahh @eminano you were right

is is possible that the init was run against a different postgres URL than the one configured for the listener (PGSTREAM_POSTGRES_LISTENER_URL)?

i accidentally re-specified my PGSTREAM_WEBHOOK_SUBSCRIPTION_STORE_URL as PGSTREAM_POSTGRES_LISTENER_URL 😅

thanks very much for your help!