Upgrading Logical Replica of PostgreSQL

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.

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 Comment