Mentat

The steps for upgrade are as follows:

1. Stop and disable Mentat

$ sudo systemctl stop warden_filer_cesnet_receiver.service
$ sudo systemctl disable warden_filer_cesnet_receiver.service
$ sudo mentat-controller.py --command stop
$ sudo mentat-controller.py --command disable

2. Stop Apache

$ sudo systemctl stop apache2
$ sudo systemctl disable apache2

3. psql mentat_events

DROP INDEX events_detecttime_idx;
DROP INDEX events_combined_idx;
DROP INDEX events_cesnet_storagetime_idx;
DROP INDEX events_cesnet_eventseverity_idx;
ALTER TABLE events_json DROP CONSTRAINT events_json_id_fkey;
ALTER TABLE events_json DROP CONSTRAINT events_json_pkey;
ALTER TABLE events DROP CONSTRAINT events_pkey;
VACUUM FREEZE;
CHECKPOINT;

4. Stop postgresql

$ sudo systemctl stop postgresql

5. Install PostgreSQL 13

$ sudo apt-get update
$ sudo apt-get install postgresql-13 postgresql-13-ip4r postgresql-server-dev-13 postgresql-client-13

6. Back up the default PostgreSQL v13 configuration file

$ cp /etc/postgresql/13/main/postgresql.conf ~/postgresql_13_default.conf

7. Migration

$ sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
13  main    5433 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

$ sudo systemctl stop postgresql

$ sudo pg_dropcluster 13 main

$ sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

# This will require *temporarily* setting wal_level to 'logical' (in postgresql.conf) - it is set to 'minimal' if you followed configuration advice from docs
# Alternatively one can ommit the --link parameter, but that requires free space for a 1:1 copy and of course also takes much longer
$ sudo pg_upgradecluster --method=upgrade --link 12 main

$ sudo pg_dropcluster 12 main

8. Remove PostgreSQL 12 and previous

$ sudo apt-get remove --purge postgresql-12 postgresql-client-12 postgresql-server-dev-12 postgresql-12-ip4r postgresql-12 postgresql-client-11 postgresql-server-dev-11 postgresql-11-ip4r postgresql-10 postgresql-9.4 postgresql-9.5 postgresql-9.6

9. Update the configuration file

$ sudo vimdiff /etc/postgresql/13/main/postgresql.conf ~/postgresql_13_default.conf

Change the following options in /etc/postgresql/13/main/postgresql.conf:

autovacuum_vacuum_insert_threshold = -1

Change the setting for wal_level back if it was changed in step 7.

10a. Reboot the system

$ sudo reboot

10b. Start PostgreSQL

$ sudo systemctl start postgresql

11. Indices

ANALYZE;
REINDEX DATABASE mentat_events;
ALTER TABLE events ADD PRIMARY KEY (id);
ALTER TABLE events_json ADD PRIMARY KEY (id);
ALTER TABLE events_json ADD FOREIGN KEY (id) REFERENCES events(id) ON DELETE CASCADE;
CREATE INDEX IF NOT EXISTS events_detecttime_idx ON events USING BTREE (detecttime);
CREATE INDEX IF NOT EXISTS events_cesnet_storagetime_idx ON events USING BTREE (cesnet_storagetime);
CREATE INDEX IF NOT EXISTS events_cesnet_eventseverity_idx ON events USING BTREE (cesnet_eventseverity) WHERE cesnet_eventseverity IS NOT NULL;
CREATE INDEX IF NOT EXISTS events_combined_idx ON events USING GIN (category, node_name, protocol, source_port, target_port, source_type, target_type, node_type, cesnet_resolvedabuses, cesnet_inspectionerrors);
CREATE INDEX IF NOT EXISTS events_ip_aggr_idx ON events USING GIST (source_ip_aggr_ip4, target_ip_aggr_ip4, source_ip_aggr_ip6, target_ip_aggr_ip6);
CHECKPOINT;

12. Start Mentat & Apache

$ sudo mentat-controller.py --command enable
$ sudo mentat-controller.py --command start
$ sudo systemctl enable apache2
$ sudo systemctl start apache2
$ sudo systemctl enable warden_filer_cesnet_receiver.service
$ sudo systemctl start warden_filer_cesnet_receiver.service