Skip to content

Draft: PostgreSQL Cluster Auto-Upgrade

Max Headroom requested to merge pg-auto-migrations into master

Adds the ability to automatically upgrade PostgreSQL nodes in a Patroni cluster across major versions with (ideally) minimal downtime.

Upgrade step-by-step:

  • be stopped
  • on startup, check highest pg version in cluster
    • TODO: fix setting CONSUL_HTTP_ADDR
  • if version matches our target, wait until the leader is that version
    • requires us to run a switchover/failover the first time around
  • if version is above target, crash
  • if version is below target, perform self-upgrade:
    • nuke data dir, initdb with target version
    • find the leader
    • TODO: find the leader's port number (patroni config?)
    • pause auto failover
    • prevent leader from stopping itself
    • for all databases on leader, dump schema and load into ours
    • temporarily switch cluster wal_level to logical
    • for all databases on leader, create replication slot for all tables, subscribe to each replication slot on ours
    • write down the current wal lsn on the leader
    • wait until we reach that lsn
    • switch the leader into read-only mode
    • sync the remaining data over, i.e. wait for replication
      • TODO: what if a ddl change happened between schema dump and read-only?
    • drop the publications and subscriptions
    • reset cluster's wal_level to previous value
    • set the cluster's sysid to our new sysid
      • TODO: is this desirable? it kills all other nodes, removing the leader
    • perform a switchover to us
      • TODO: should we do this ourselves? (currently, the second node to upgrade forces a failover on us)
    • we are now the leader
    • wait for the rest of the replicas to do the same procedure
    • cluster upgraded with minimal downtime!
Edited by Max Headroom

Merge request reports