pg_upgrade: the tricky and dangerous parts

Postgres FM

Nikolay (alone, again) reveals some issues that might hit those who perform major PostgreSQL upgrades with minimal downtime.

Links:
- "Upgrades" – PostgresFM episode 037: https://postgres.fm/episodes/upgrades
- recovery_target_lsn: https://postgresqlco.nf/doc/en/param/recovery_target_lsn/
- recovery_target_action: https://postgresqlco.nf/doc/en/param/recovery_target_action/
- pg_easy_replicate https://github.com/shayonj/pg_easy_replicate/
- HN discussion of this tool: https://news.ycombinator.com/item?id=36405761
- Waiting for Postgres 16: Logical decoding on standbys: https://pganalyze.com/blog/5mins-postgres-16-logical-decoding
- pg_upgrade and logical replication (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud
- allow upgrading publisher node (proposal to natively support running pg_upgrade on publisher, WIP): https://commitfest.postgresql.org/43/4273/
- ❓🤔 pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption? (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/CAM527d8heqkjG5VrvjU3Xjsqxg41ufUyabD9QZccdAxnpbRH-Q%40mail.gmail.com

~~~

What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!

If you would like to share this episode, here's a good link (and thank you!)

~~~

Postgres FM is brought to you by:

• Nikolay Samokhvalov, founder of Postgres.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork