Enabling standby node Wal streaming without pg_basebackup

Question

So currently i'm planning to do a migration to RDS where i'm using a snapshot of our master and doing a pg_upgrade on a new machine(reasons being so that if the upgrade fails for whatever reason, we still have our old postgres instance around)

For good practice, i'd like to have a hot_standby as well. The problem is that pg_basebackup takes too long. I tried to be clever and took a snapshot of our standby instance(the original setup has a master and standby setup), did a pgupgrade and attempted to make them communicate to each other, but failed.

Is there a way to do what i'm trying to do, or is it impossible?

thank you

Answer 1

Running a major upgrade with pg_upgrade without building the standby from scratch is tricky.

Assuming that you use the --link option for speed, there is a way to speed up upgrading the standby, as described in the documentation. For that, you run rsync in such a way that the corresponding data files on the upgraded standby are hard linked to original standby. This should be about as fast as a pg_upgrade --link.

Your attempt fails because pg_upgrade changes the database identifier:

pg_controldata -D /data/dir | grep 'Database system identifier'
Database system identifier:           6979108840766301543

But primary and standby need to have the same identifier and need to be physically identical.