Task #6610
closedUpgrade PostgreSQL to v13
Added by Radko Krkoš almost 4 years ago. Updated over 3 years ago.
100%
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
Updated by Radko Krkoš almost 4 years 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
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
Updated by Radko Krkoš almost 4 years ago
- Related to Bug #6211: Missing indices on aggregated columns added
Updated by Radko Krkoš almost 4 years ago
- To be discussed changed from Yes to No
Updated by Pavel Kácha almost 4 years ago
- To be discussed changed from No to Yes
Updated by Pavel Kácha almost 4 years ago
- Status changed from New to Closed
Succesfully done, added to docs.
Updated by Radko Krkoš over 3 years 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.