Logical Replication is not working.

[Tekchanddagar]

Hello Team, We are trying to upgrade our postgresql version from PSQL 14 to PSQL 16 using logical replication. Below is our setup details and steps we are using:

1. Currently we have 2 node patroni cluster with postgresql 14. Its working fine. And currently wal_level=replica
2. We have set up new 2 node patroni cluster with postgresql 16 e.g psql-node-1 (Role Master, wal_level=logical) and psql-node-2 (Role replica and wal_level=replica).
3. Now we set the wal_level=logical on postgresql-14 master node and restart the patroni service and we have checked wal_level is changed from replica to logical.
4. We created PUBLICATION on psql-14 master node.
5. We created a user for replication and provided all the required permission to this user i.e

   GRANT ALL PRIVILEGES ON DATABASE db_name TO repuser; 
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO repuser;
   GRANT USAGE ON SCHEMA public TO repuser;

6. Now we created SUBSCRIPTION on the postgresql-16 master node.

   CREATE SUBSCRIPTION my_sub CONNECTION 'host=192.168.14.12 dbname=db_name user=repuser password=example123' PUBLICATION my_pub;

   Once we created subscription we can see data replication is start on postgresql-16 master node. But replication never reached in complete state and DB size on postgresql-16 become large then the actual DB size on postgresql-14 cluster. We have chekced logs also but we are not seeing any error.

We also checked the restriction doc of postgresql https://www.postgresql.org/docs/14/logical-replication-restrictions.html.

Our postgresql-14 database has sequnce. But its should not be reason of failure because we can copy the sequence later from psql-14 to psql-16 and then we can start sending traffic on psql-16.

Can you please help us here or point us what mistake we are doing here?

Thank You

[porsager]

Hi @Tekchanddagar .. I think you meant to post this to something directly related to PostgreSQL itself. This is a client driver for Javascript