Upgrade PostgreSQL from 9.5 to 14+

[jnm]

After a first reading of the release notes for the major versions:

• https://www.postgresql.org/docs/release/9.6.0/
• https://www.postgresql.org/docs/release/10.0/
• https://www.postgresql.org/docs/release/11.0/
• https://www.postgresql.org/docs/release/12.0/
• https://www.postgresql.org/docs/release/13.0/
• https://www.postgresql.org/docs/release/14.0/

…I think these may require our attention:

9.6

Merge the archive and hot_standby values of the wal_level configuration parameter into a single new value replica (Peter Eisentraut)

10

Hash indexes must be rebuilt after pg_upgrade-ing from any previous major PostgreSQL version (Mithun Cy, Robert Haas, Amit Kapila)

Major hash index improvements necessitated this requirement. pg_upgrade will create a script to assist with this.

ℹ️ We don't actually use hash indexes.

Rename write-ahead log directory pg_xlog to pg_wal, and rename transaction status directory pg_clog to pg_xact (Michael Paquier)

Users have occasionally thought that these directories contained only inessential log files, and proceeded to remove write-ahead log files or transaction status files manually, causing irrecoverable data loss. These name changes are intended to discourage such errors in future.

Rename SQL functions, tools, and options that reference “xlog” to “wal” (Robert Haas)

For example, pg_switch_xlog() becomes pg_switch_wal(), pg_receivexlog becomes pg_receivewal, and --xlogdir becomes --waldir. This is for consistency with the change of the pg_xlog directory name; in general, the “xlog” terminology is no longer used in any user-facing places.

13

Rename configuration parameter wal_keep_segments to wal_keep_size (Fujii Masao)

[noliveleger]

🍾

[jnm]

maybe have a look at https://pgbackrest.org/

[jnm]

Resources from past upgrades: kobotoolbox/kobo-docker#259, kobotoolbox/kobo-docker#265

[jnm]

quick wip scratch pad

source


kobotoolbox=# \c kobocat
You are now connected to database "kobocat" as user "kobo".
kobocat=# create extension pglogical;
CREATE EXTENSION
kobocat=# select pglogical.create_node(
node_name := 'kobocat_db_provider',
dsn := 'host=localhost port=5432 dbname=kobocat'
);
 create_node 
-------------
  3159100384
(1 row)

kobocat=# select pglogical.create_replication_set('kobocat_db_set');
 create_replication_set 
------------------------
             3979314219
(1 row)

kobocat=# select pglogical.replication_set_add_table('kobocat_db_set', 'logger_instance');
 replication_set_add_table 
---------------------------
 t
(1 row)

kobocat=# select pglogical.replication_set_add_sequence('kobocat_db_set', 'logger_instance_id_seq', true);
 replication_set_add_sequence 
------------------------------
 t
(1 row)

destination

postgres=# alter system set wal_level to 'logical';
alter system set max_worker_processes to 10;
alter system set max_replication_slots to 10;
alter system set max_wal_senders to 10;
alter system set shared_preload_libraries to 'pglogical';
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
postgres=# 

%%%%%%%


kobocat=# alter table logger_instance drop constraint logger__survey_type_id_5a58f1cfec1771cd_fk_logger_surveytype_id;
ALTER TABLE
kobocat=# alter table logger_instance drop constraint logger_instance_user_id_3a3b162ef6bedc0e_fk_auth_user_id;
ALTER TABLE
kobocat=# alter table logger_instance drop constraint logger_instance_xform_id_546567e950972f94_fk_logger_xform_id;
ALTER TABLE

%%%%%%%

select pglogical.create_node(
    node_name := 'kobocat_db_subscriber',
    dsn := 'host=localhost port=5432 dbname=kobocat user=postgres password=zomg'
);
select pglogical.create_subscription(
    subscription_name := 'kobocat_db_subscription',
    replication_sets := '{kobocat_db_set}',
    provider_dsn := 'host=old-server port=5432 dbname=kobocat user=kobo password=zomg'
);


kobocat=# select * from pglogical.local_sync_status ;
 sync_kind | sync_subid | sync_nspname | sync_relname | sync_status | sync_statuslsn 
-----------+------------+--------------+--------------+-------------+----------------
 d         |  725963785 |              |              | d           | 0/0
(1 row)

https://github.com/2ndQuadrant/pglogical/blob/aef974d456254080bb51b2a12920be37a271982e/pglogical_sync.h#L43-L51

kobocat=# SELECT subscription_name, status FROM pglogical.show_subscription_status();
    subscription_name    |    status    
-------------------------+--------------
 kobocat_db_subscription | initializing
(1 row)

[jnm]

to dos:

1. can we actually authenticate from an external server
2. have to get the sequence reset SQL for every app ./manage.py sqlsequencereset

   call_command('sqlsequencereset', *(app.label for app in apps.get_app_configs()), no_color=True)

   thanks @noliveleger 😄
3. automatic resizing script; commands needed:

   # after resizing EBS volume
   sudo pvresize /dev/nvme1n1
   sudo lvextend -l +100%FREE /dev/ocha-databases/postgresql
   sudo xfs_growfs /dev/ocha-databases/postgresql
   

4. replication to secondary (watch out for WAL-related changes?)
5. backups: https://chat.kobotoolbox.org/#narrow/stream/5-Kobo-SysAdmin/topic/Backups.20with.20borg.20and.20friends
6. check on instructions for smaller installs Upgrade PostgreSQL to v14.2, MongoDB to v5 and Redis to v6.2  kobo-docker#335

plan for switchover:

1. schedule a 30-minute outage
2. take down the web apps (put up maintenance page)
3. wait for replication to settle
4. stop old postgres server
5. update primary.postgres.koboroute.internal to point to new postgres server
6. test communication between front-end instances and new postgres server
7. run sequence reset sql commands
8. [try pg_upgrade on the secondary? figure out if it makes sense to attempt this and preserve replication]
9. upgrade mongo and redis (see below)
10. bring the web apps back up (remove maintenance page)

upgrading mongo and redis:

1. mongo: start at current 3.4 and go through sequence of 3.6, 4.0, 4.2, 4.4 and 5.0. restart every time, and after each restart, run db.adminCommand( { setFeatureCompatibilityVersion: "[new version]" } ).
2. go through upgrades using docker on old 20.04 server
3. shut down old server and attach ebs volume to new 22.04 server
4. redis: just change the version and restart; it reads everything from the old version's rdb file without issue. once it's done, though, you cannot go back to the old version.
5. must copy rdb files from old 20.04 to new 22.04 server
6. ⚠️ is it easy to run two redis servers without docker containers?
7. close kobotoolbox/tasks#420

don't forget:

1. close Update container images kobo-docker#270
2. later, after we upgrade mongo and move it to the new ec2 instance, we should use the .local.dns.conf and .public.dns.conf files to set DNS automatically