Update production version of postgres 😬

[cdrini]

Current State

Postgres is currently at 9.3 which is ancient. This has performance ramifications, support ramifications, and complicated moving off trusty on our remaining legacy machines.

See also (internal):
https://docs.google.com/document/d/1ZOFi8EIOUTsFJwz9xpee9RG7jael1qX_J4E1CZbGiBc/edit?tab=t.0

Next Steps

See breakdown here: https://docs.google.com/document/d/1ZOFi8EIOUTsFJwz9xpee9RG7jael1qX_J4E1CZbGiBc/edit

• Test 16.2 locally using built in db subset Try updating local environment to postgres 13 or 16 #5686
  • Attempt to upgrade the postgres version and catalog what breaks
• Provision ol-db0 and ol-backup1
  • Provision ol-db0 #9153
  • Provision ol-backup1 #9154
  • Import full psql dump from ol-db1 into ol-db0 #9155

Questions

• How do we keep ol-db0 in sync

Step 2: Test Prod Data

• Option 1: Test with psql dump + reload
  • Create ol-db3 with latest Ubuntu + postgres, and try loading a backup in it
  • Try running an open library off ol-db3 (NOT PUBLIC) ensure everything works; connect to mek.openlibrary.org
• Option 2: Clone ol-db1 to new server + update ubuntu/postgres
• Option 3: Create new server with latest ubuntu/postgres + copy filesystem

Step 3: Prod Migration

• Option 1:
  • Turn off open library
  • Dump prod DB
  • Load DB into ol-db3
  • Set-up ol-db4 to replicate ol-db3
  • Connect web heads to ol-db3
  • Turn OL back on
• Option 2: Update ubuntu/postgres on prod server on both db servers
• Option 3: Same as testing step

[jimman2003]

can we get the psql dump? currently expementing with postgres

[cdrini]

That full psql dump contains private user data, so that can't be made public I'm afraid.

[cclauss]

https://www.postgresql.org Current version is v15.2.