Logical Replication is not working

[darshanp351]

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

[tjmoore4]

Hi @darshanp351. Crunchy Postgres for Kubernetes officially supports major Postgres upgrades using the method described in our documentation. And looking at your issue, it appears as though you have a Patroni cluster that is not being managed by Crunchy Postgres for Kubernetes. As a result, your question is out of scope for the maintainers of this repository. I recommend asking about the Crunchy Discord server instead, where we have various other channels (e.g. a postgres-questions channel) that might be more suitable for your question.