Set up PostgreSQL Logical Replication Between Databases on the Same Server Cluster

One case Grok beats ChatGPT O1 reasoning model on PostgreSQL problem

The problem

I was trying to setup a logical replication in two databases in one server cluster (The same PG instance). Since I have set up many replications between different servers, I thought this would be a pretty easy task. However, the result does not allow me to consider it an easy task.

I used a script like this to create a subscription together with a slot created on the publisher side:

CREATE SUBSCRIPTION 
  mysub 
CONNECTION 
  'host=localhost port=5432 dbname=source_db user=myuser password=mypassword' 
PUBLICATION 
  mypub 
WITH (slot_name = 'myslot', copy_data = true);

The above script will create a slot automatically in the publisher side and start copying data over, it works every time.

However, the replication subscription creation lasts for more than 2 hours and still does not give a clear response, it looks like freeze.

What is going on?

Then I checked the publisher’s slot on the publisher side using the following script.

SELECT *
FROM pg_replication_slots;

Yes, the target slot is created. How about the replication status?

SELECT 
    pid
    , state
    , sent_lsn
    …