Project

General

Profile

Actions

Config #6003

closed

Upgrade PostgreSQL to v12.x

Added by Radko Krkoš over 2 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Installation
Target version:
Start date:
11/01/2019
Due date:
% Done:

100%

Estimated time:
To be discussed:
No

Description

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

Actions #1

Updated by Radko Krkoš over 2 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
Actions #2

Updated by Radko Krkoš over 2 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.

Actions #3

Updated by Jan Mach over 2 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.

Actions #4

Updated by Jan Mach over 2 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?

Actions #5

Updated by Jan Mach over 2 years ago

  • Status changed from Resolved to Feedback
Actions #6

Updated by Jan Mach over 2 years ago

Things to discuss:
  1. What are the actual performance impacts of the upgrade.
Actions #7

Updated by Pavel Kácha over 2 years ago

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

Updated by Pavel Kácha over 2 years ago

Radko, could you please add SHORT conclusion/observation?

Actions #9

Updated by Radko Krkoš over 2 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).

Actions #10

Updated by Radko Krkoš over 2 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).

Actions #11

Updated by Jan Mach over 2 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF