Upgrading Logical Replica of PostgreSQL

All we need is an easy explanation of the problem, so here it is.

I am trying to upgrade a logical replica from PostgreSQL V10 to V13. We are using pglogical for the logical replication.

Though the upgrade process of the replica can be done, however the replication will not re-start and the subscriptions status will be down after the upgrade.

Is there a way for the logical replication to survive pg_upgrade and the replication re-starts after the upgrade?

If replication origin can be used in this scenario how can I use that to prevent it because after the upgrade in the logs I see that the origin cannot be found.

LOG: starting apply for subscription sample_subscription
ERROR: replication origin "pgl_sampledb_provider_sub" does not exist
LOG:  background worker "pglogical apply 16523:89982342" (PID 10178) exited with exit code 1

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

So I figured out the problem.

pg_upgrade will not migrate the pg_replication_origin data, hence I had to re-create the origins using pg_replication_origin_create and the replication was replicating again.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply