Upgrade PostgreSQL to v12.x

The changes in postgres v12 should improve performance for some of our use-cases.

assigned to @radko_krkos

By Radko Krkoš on 2024-12-26T14:06:49

added C-Inst Done 0% P4 S-New T-Config labels

changed milestone to %2.6

The steps to migrate are as follows:

1. Stop and disable Mentat
	$ sudo systemctl stop warden_filer_cesnet_receiver.service
	$ sudo mentat-controller.py --command stop
	$ sudo mentat-controller.py --command disable

2. Stop Apache
	$ sudo systemctl stop 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 DROP CONSTRAINT events_pkey;
	VACUUM FREEZE;
	CHECKPOINT;

4. Stop postgresql
	$ sudo systemctl stop postgresql

5. Install PostgreSQL 12
	$ sudo apt-get update
	$ sudo apt-get install postgresql-12 postgresql-12-ip4r postgresql-server-dev-12 postgresql-client-12

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

	$ sudo systemctl stop postgresql
	
	$ sudo pg_dropcluster 12 main

	$ sudo pg_lsclusters
	Ver Cluster Port Status Owner    Data directory              Log file
	11  main    5432 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-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 11 main

	$ sudo pg_dropcluster 11 main

7. Remove PostgreSQL 11
	$ sudo apt-get remove --purge postgresql-11 postgresql-client-11 postgresql-server-dev-11 postgresql-11-ip4r postgresql-9.4 postgresql-9.5 postgresql-9.6 postgresql-10

8. Start PostgreSQL
	$ sudo systemctl start postgresql

9. Indices
	REINDEX DATABASE mentat_events;
	ALTER TABLE events ADD PRIMARY KEY (id);
	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);
	CHECKPOINT;
	ANALYZE;

10. Start Mentat & Apache
	$ sudo mentat-controller.py --command enable
	$ sudo mentat-controller.py --command start
	$ sudo systemctl start apache2
	$ sudo systemctl start warden_filer_cesnet_receiver.service

By Radko Krkoš on 2019-11-01T13:36:14

The upgrade was successfully performed at all three of dev, alt and hub. It should also be documented for benefit of external installations.

By Radko Krkoš on 2019-11-01T13:37:23

added Done 90% label and removed Done 0% label

I have just added the documentation snippet regarding the PostgreSQL upgrade provided by Radko Krkoš to main project documentation. It will be available on Alchemist server after next build.

This issue can be closed now, there is no other work to do regarding this...I hope.

By Jan Mach on 2019-11-04T15:55:55

added Done 100% S-Closed labels and removed Done 90% S-New labels

What about performance gains after the upgrade. Are there any measured?

By Jan Mach on 2019-11-11T15:04:28

added S-Resolved label and removed S-Closed label

added S-Feedback label and removed S-Resolved label

Things to discuss:

1. What are the actual performance impacts of the upgrade.

By Jan Mach on 2019-11-13T10:23:44

Radko, could you please add SHORT conclusion/observation?

By Pavel Kácha on 2019-11-14T12:33:40

TLDR conclusion:
A speedup is observable in sequential scan queries (i.e. IP address search) of up to 10%. We attribute this to JIT compiler making the <ip> && ANY({source,target}_ip) more efficient (as it should per documentation).

By Radko Krkoš on 2019-11-14T17:28:39

TLDR conclusion:
A speedup is observable in sequential scan queries (i.e. IP address search) of up to 10%. We attribute this to JIT compiler making the <ip> && ANY({source,target}_ip) more efficient (as it should per documentation).

By Radko Krkoš on 2019-11-14T17:29:56

added S-Resolved label and removed S-Feedback label

added S-Closed label and removed S-Resolved label

closed