On Thu, Mar 11, 2021 at 03:53:07PM -0600, tsuraan wrote: > > Did you see the pg_upgrade instructions on upgrading standby servers? > > Why are you not using that? > > Specifically referring to [0], the part I can't do (and that I Yes, that is what I was referring to. > unfortunately left out of my initial email) is right at the beginning, > "Do not start any servers yet". The master needs to get running again > as soon as it's upgraded, and the standby systems can't stop the > master for their upgrades. I believe what I've described is > essentially the documented approach, but with a running master and > then wrapping the rsync command in pg_start_backup/pg_stop_backup > calls, but is it still a correct way to upgrade? From the > pg_start_backup description on the binary replication tutorial [1], it > looks like it should be, but I'd like to know if I'm missing anything. Those documented instructions are very specific about what is possible, e.g.: What this does is to record the links created by pg_upgrade's link mode that connect files in the old and new clusters on the primary server. It then finds matching files in the standby's old cluster and creates links for them in the standby's new cluster. Files that were not linked on the primary are copied from the primary to the standby. (They are usually small.) This provides rapid standby upgrades. Unfortunately, rsync needlessly copies files associated with temporary and unlogged tables because these files don't normally exist on standby servers. Anything that doesn't match this might lead to disaster. Specifically, you can't use the simplistic rsync arguments listed there since they asssume we don't need to check if the files are the same. When doing this on a running primary, you effectively will be copying all of the system tables over, which is fine, but for the user heap/index files, it will not be able to use links because they can change on a running system, so you really are doing a streaming backup, and you just happen to have a cluster there. I think will just end up copying all the heap/index files into the empty new cluster directory, so you might as well just copy the cluster directory in a more simple way. You really need to backward and look at the file copy requirements for pg_backup() and see what is possible. We can discuss what is possible, but I can't think of any shortcuts given your requirements. I wonder if you just need to use logical replication here. -- Bruce Momjian <bruce@xxxxxxxxxx> https://momjian.us EDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
