Database Upgrade

• In advance:
  • Install new postgres packages on each machine
• Take site offline
• Stop chef on all database servers
• Let osmdbt on master capture logs for most recent changes
• Disable osmdbt replication on planet server with osmdbt-disable-replication -c /etc/replication/osmdbt-config.yaml
• Stop postgres on master
• Stop postgres on slaves
• Run sudo /usr/lib/postgresql/<old-version>/bin/pg_controldata -D /var/lib/postgresql/<old-version>/main on each machine
  • Check "Latest checkpoint location" values match
• Ugrade master
  • sudo pg_upgradecluster -v <new-version> -m upgrade -k <old-version> main
• Sync tablespaces to slaves
  • cd /etc/postgresql
  • sudo rsync --verbose --archive --delete <old-version> <new-version> root@slave:/etc/postgresql
  • cd /var/lib/postgresql
  • sudo rsync --verbose --archive --delete --hard-links --size-only --no-inc-recursive <old-version> <new-version> root@slave:/var/lib/postgresql
  • cd /store/postgresql
  • sudo rsync --verbose --archive --delete --hard-links --size-only --no-inc-recursive --copy-dirlinks --keep-dirlinks openstreetmap root@slave:/store/postgresql
  • sudo rsync --verbose --archive --delete --hard-links --size-only --no-inc-recursive --copy-dirlinks --keep-dirlinks ssd root@slave:/store/postgresql
• Update cookbooks/db/attributes/default.rb in chef to new version
• Run chef on master, wait for run to complete and check postgres is up and working
• Run chef on slaves, wait for run to complete and check postgres is up and working
• Drop old cluster sudo pg_dropcluster <old-version> main on each machine
• Analyse database
  • vacuumdb --all --verbose --analyze-in-stages --jobs=20
• Reindex users
  • reindexdb --table=users openstreetmap
• Enable osmdbt replication on planet server with osmdbt-enable-replication -c /etc/replication/osmdbt-config.yaml
• Bring site online