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.
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
Install postgresql-common if it not already present:
$ sudo apt install postgresql-common
Add PostgreSQL APT repositories to your computer:
$ sudo sh /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Install desired version:
$ sudo apt-get install postgresql-14
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.
Stop the new cluster and drop it:
$ sudo pg_dropcluster 14 main --stop
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 (...)
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.)
Test if FrontStage is working.
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.
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.
Open
postgresql.conf
as postgres user.sudo -u postgres nano /etc/postgresql/12/main/postgresql.conf
Change the value of
shared_buffers
to 25% RAM but not more than 20 GB. For example,shared_buffers = 3GB
.Change or keep the
max_connections = 100
, which is usually sufficient.Change the value of
work_mem
to0.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
.Restart the PostgreSQL and test if it is running.