PostgreSQL installation for FrontStage

Upgrade PostgreSQL version

From time to time, you might want to upgrade the PostgreSQL major version. For example, you want to upgrade Version 12 to 14.

  1. You have healthy 12:

    $ 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

  2. Backup the entire cluster.

  3. Install postgresql-common if it not already present:

    $ sudo apt install postgresql-common

  4. Add PostgreSQL APT repositories to your computer:

    $ sudo sh /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

  5. Install desired version:

    $ sudo apt-get install postgresql-14

  6. Ensure that you have the old and the new cluster and both are up and running:

    $ 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
14  main    5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log

    There already is a cluster “main” for Version 14 (since this is created by default during package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 12/main when 14/main also exists. The recommended procedure is to remove the 14 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

  7. Stop the new cluster and drop it:

    $ sudo pg_dropcluster 14 main --stop

  8. Update the old cluster to the new version:

    $ sudo pg_upgradecluster 12 main

    The command produces a lot of text but look at the end of output whether it was sucessfully completed:

    (...)
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Fixing hardcoded library paths for stored procedures...
Upgrading database FrontStage2...
Analyzing database FrontStage2...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database FrontStage3...
Analyzing database FrontStage3...
Fixing hardcoded library paths for stored procedures...
Upgrading database FrontStage...
Analyzing database FrontStage...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Starting upgraded cluster on port 5432...

Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 12 main

(...)

  9. By now, the old one should be down, while the new one should be online:

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

    (Please note that the default 5432 port now belongs to new the one.)

  10. Test if FrontStage is working.

  11. If no problems occured:

    • Remove the old cluster:

      $ sudo pg_dropcluster 12 main

    • (optionally) Remove the old cluster backup file.

    • (optionally) Remove your old APT packages:

      $ sudo apt-get purge postgresql-12 postgresql-client-12

Improve performance

Default PostgreSQL performance settings are underestimated for most today powerful machines. We strongly recommend to tweak the shared_buffers, work_mem, and max_connections parameters.

The following performance tuning example assumes the server runs no other app except for PostgreSQL.

  1. Determine the installed RAM:

    $ free -h
              total        used        free      shared  buff/cache   available
Mem:           11Gi       7.9Gi       1.0Gi        49Mi       2.8Gi       3.4Gi
Swap:            0B          0B          0B

    For example, 12 GB.

  2. Open postgresql.conf as postgres user.

    sudo -u postgres nano /etc/postgresql/12/main/postgresql.conf

  3. Change the value of shared_buffers to 25% RAM but not more than 20 GB. For example, shared_buffers = 3GB.

  4. Change or keep the max_connections = 100, which is usually sufficient.

  5. Change the value of work_mem to 0.25 * (total RAM  - shared_buffers) / max_connections / 2.

    work_mem = 0.25 * (12 - 3) / 100 / 2
work_mem = 0.00125

    For example, round and set work_mem = 11MB.

  6. Restart the PostgreSQL and test if it is running.