pg_upgrade, Advanced Techniques

Robert Bernier


Senior PostgreSQL Consultant

16:00 (UTC+3) , english

When it comes to performing postgres upgrades between major versions the command line utility pg_upgrade is the most popular method used today. But as with all things wonderful, there are caveats. One of the more critical issues is what to do when there's a failure. The purpose of this talk is to present those little covered "tricks" of the trade that allows one to improve and enhance the upgrade experience.

We'll start out by reviewing its basic mode operation. We'll then learn what makes it so darn fast when upgrading multi-terabyte datacluster in a matter of minutes. Finally, you will be introduced to those feared failures and how to deal with them with confidence and certainty.

Herein is a summary of the topics in this talk:

  • How pg_upgrade works: the big picture
  • About pgupgrade (comand line invocation)

    • arguments and options

  • The steps performing an upgrade
  • About the replication ROLE

    • with replication
    • with login

  • Upgrade options: COPY vs Hard Link
  • Post-upgrade

    • about performance
    • aboutanalyze
    • repack
    • reindexing

  • When something goes wrong at the point of no return (or is it?)
  • Upgrading the REPLICA
    • default method: pg_basebackup
    • the cool method:
      • - leveraging rsync
      • caveat; about vacuum wraparound