Config #6003
closedUpgrade PostgreSQL to v12.x
Added by Radko Krkoš almost 5 years ago. Updated almost 5 years ago.
100%
Description
The changes in postgres v12 should improve performance for some of our use-cases.
Updated by Radko Krkoš almost 5 years ago
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
Updated by Radko Krkoš almost 5 years ago
- % Done changed from 0 to 90
The upgrade was successfully performed at all three of dev, alt and hub. It should also be documented for benefit of external installations.
Updated by Jan Mach almost 5 years ago
- Status changed from New to Closed
- % Done changed from 90 to 100
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.
Updated by Jan Mach almost 5 years ago
- Status changed from Closed to Resolved
- To be discussed changed from No to Yes
What about performance gains after the upgrade. Are there any measured?
Updated by Jan Mach almost 5 years ago
- What are the actual performance impacts of the upgrade.
Updated by Pavel Kácha almost 5 years ago
- To be discussed changed from Yes to No
Updated by Pavel Kácha almost 5 years ago
Radko, could you please add SHORT conclusion/observation?
Updated by Radko Krkoš almost 5 years ago
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).
Updated by Radko Krkoš almost 5 years ago
- Status changed from Feedback to Resolved
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).