https://homeproj.cesnet.cz/https://homeproj.cesnet.cz/httpauth-login/favicon.ico?16194486082019-11-01T13:36:14ZHomeproj: Redmine for CESNETMentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=232662019-11-01T13:36:14ZRadko Krkoškrkos@cesnet.cz
<ul></ul><p>The steps to migrate are as follows:</p>
<pre>
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
</pre> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=232672019-11-01T13:37:23ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>% Done</strong> changed from <i>0</i> to <i>90</i></li></ul><p>The upgrade was successfully performed at all three of dev, alt and hub. It should also be documented for benefit of external installations.</p> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=232832019-11-04T15:55:55ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>% Done</strong> changed from <i>90</i> to <i>100</i></li></ul><p>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.</p>
<p>This issue can be closed now, there is no other work to do regarding this...I hope.</p> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=233912019-11-11T15:04:28ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>Closed</i> to <i>Resolved</i></li><li><strong>To be discussed</strong> changed from <i>No</i> to <i>Yes</i></li></ul><p>What about performance gains after the upgrade. Are there any measured?</p> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=234332019-11-12T08:36:00ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>Resolved</i> to <i>Feedback</i></li></ul> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=234872019-11-13T10:23:44ZJan Machjan.mach@cesnet.cz
<ul></ul><strong>Things to discuss:</strong>
<ol>
<li>What are the actual performance impacts of the upgrade.</li>
</ol> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=235042019-11-14T10:04:37ZPavel Káchaph@cesnet.cz
<ul><li><strong>To be discussed</strong> changed from <i>Yes</i> to <i>No</i></li></ul> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=235062019-11-14T12:33:40ZPavel Káchaph@cesnet.cz
<ul></ul><p>Radko, could you please add <em>SHORT</em> conclusion/observation?</p> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=235082019-11-14T17:28:39ZRadko Krkoškrkos@cesnet.cz
<ul></ul><p>TLDR conclusion:<br />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 <code><ip> && ANY({source,target}_ip)</code> more efficient (as it should per documentation).</p> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=235092019-11-14T17:29:56ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>Feedback</i> to <i>Resolved</i></li></ul><p>TLDR conclusion:<br />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 <code><ip> && ANY({source,target}_ip)</code> more efficient (as it should per documentation).</p> Mentat - Config #6003: Upgrade PostgreSQL to v12.xhttps://homeproj.cesnet.cz/issues/6003?journal_id=235122019-11-14T18:10:36ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>Resolved</i> to <i>Closed</i></li></ul>