Project

General

Profile

Actions

Task #6610

closed

Upgrade PostgreSQL to v13

Added by Radko Krkoš 9 months ago. Updated 2 months ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Development - Database
Target version:
Start date:
10/30/2020
Due date:
% Done:

100%

Estimated time:
To be discussed:

Description

As always, there are performance benefits in running the latest database. For example, v13 should finally fix performance for the single IP search case.


Related issues

Related to Mentat - Bug #6211: Missing indices on aggregated columnsClosedPavel Kácha02/07/2020

Actions
Actions #1

Updated by Radko Krkoš 9 months ago

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
There can be no DB writes beyond this point as we are about to drop indices ensuring data integrity!

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

This is used later in step 9.
$ 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

This is the most laborous step, which I have found no way of automating. Also, rarely the options are just reordered, what complicates the merge process.
$ 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
Documented in #6444#note-6

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

10a. Reboot the system

OPTIONAL: This is a good time to reboot the machine if desired (kernel update, long runtime). Alternatively, just follow with 10b.
$ sudo reboot

10b. Start PostgreSQL

Only if 10a was skipped.
$ 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
Actions #2

Updated by Radko Krkoš 9 months ago

  • Related to Bug #6211: Missing indices on aggregated columns added
Actions #3

Updated by Radko Krkoš 9 months ago

  • To be discussed changed from Yes to No
Actions #4

Updated by Pavel Kácha 9 months ago

  • To be discussed changed from No to Yes
Actions #5

Updated by Pavel Kácha 9 months ago

  • Status changed from New to Closed

Succesfully done, added to docs.

Actions #6

Updated by Pavel Kácha 9 months ago

  • To be discussed deleted (Yes)
Actions #7

Updated by Radko Krkoš 2 months ago

  • Target version changed from Backlog to 2.8
  • % Done changed from 0 to 100

This was done during development of v2.8, with documentation changes. The users should use PostgreSQL v13 also, as it fixes #6211, so the upgrade is part of the release.

Actions

Also available in: Atom PDF