On Thu, Aug 10, 2017 at 11:59 AM, Don Seiler <don@seiler.us> wrote:
> Afternoon,
>
> Just wondering if there's a best practice in regards to shutdown of primary
> and standbys, as I approach my first planned maintenance task as a newbie
> PostgreSQL DBA. I found one older post that suggested doing a fast-mode
> shutdown on primary first so that all transaction info can be cleanly pushed
> to standby, then shutdown the standby. Our big app group runs on 9.2.18 and
> is being upgraded to 9.2.22.
>
> Extending that scenario, if you had a cascading standby, would you go
> primary -> upstream standby -> downstream standby?
Set keep wal segments to something largish (1000 or so) well before
the upgrade etc. Make sure the volume holding pg_xlog can hold
1000*16MB of data. This ensures the streaming replicant can catch up
if some stuff happens before it's back up.
Best practices are to first eliminate client access to the db cluster
so there's no updates going on up to the last second and all that. A
great tool for this is pgbouncer, which can pause its connections
while you do the upgrade.
After that, it really doesn't matter the order, but yes, I generally
shut down the source, then the destination machines.