databasepostgresqlreplicationlogical-replication

PostgreSQL logical replication hangs on CREATE SUBSCRIPTION

I have a problem with PostgreSQL logical replication, version 15. (I also tested on v10 and v12, but had the same problem). It needs replication for testing, so the source and target databases are on the same server.

In the configuration files I have set:

postgresql.conf:

wal_level = logical  # if I have "replica", I can't subscribe

pg_hba.conf:

local replication all
host replication all 127.0.0.1/32 trust
host replication all ::1/128               trust

In the source database I do:

CREATE PUBLICATION repl_name
   FOR TABLE table1, table2, table3, ...;

In pgAdmin at this stage I have:

CREATE PUBLICATION
Query returned successfully in 59 msec.

However, in the logs I have a message for each table:

2023-05-17 17:02:07.537 CEST [25356] ERROR: role "backup_user" does not exist
2023-05-17 17:02:07.537 CEST [25356] STATEMENT:  GRANT SELECT ON SEQUENCE public.table1 TO backup_user;

This may be related to the fact that on my computer it is restoring the backup from the client where this replication works.

But I don't know if it bothers you, because after typing:

select * from pg_catalog.pg_publication;

I can see my publications:

"20438"   "stack_repl"    "10"   false   true    true   true   true   false

But I don't see it here:

select * from pg_stat_replication;

I think it may have to do with the fact that there is no subscription yet.

The real problem occurs when I want to make a subscription. First, it restores the database with tables like table1, table2, table3 etc. Naturally, the tables are empty.

I enter the command:

CREATE SUBSCRIPTION sub_test
   CONNECTION 'dbname=dbname host=localhost port=5432 user=postgres password=12345'
   PUBLICATION repl_name;

And pgAdmin spins endlessly. Even when I left the computer for the weekend, it couldn't finish.

It shows up in the logs:

2023-05-17 17:22:24.178 CEST [25376] LOG:  logical decoding found initial starting point at 0/A0D6F338
2023-05-17 17:22:24.178 CEST [25376] DETAIL:  Waiting for transactions (approximately 1) older than 3712 to end.
2023-05-17 17:22:24.178 CEST [25376] STATEMENT:  CREATE_REPLICATION_SLOT "sub_test" LOGICAL pgoutput (SNAPSHOT 'nothing')

I assure you that I have been struggling with this problem for a long time and I need support / hints on what I can do to make the data start replicating.

Solution

  • You are probably setting up logical replication between two databases in the same database cluster. That makes CREATE SUBSCRIPTION hang forever, as the documentation describes:

    Creating a subscription that connects to the same database cluster (for example, to replicate between databases in the same cluster or to replicate within the same database) will only succeed if the replication slot is not created as part of the same command. Otherwise, the CREATE SUBSCRIPTION call will hang. To make this work, create the replication slot separately (using the function pg_create_logical_replication_slot with the plugin name pgoutput) and create the subscription using the parameter create_slot = false. This is an implementation restriction that might be lifted in a future release.

    So this is the route to success: