Install newer (or latest) PostgreSQL on TurnKey

TurnKey Hub user Vishal recently asked about installing PostgreSQL v14+ on TurnKey. He noted that he was interested in Postgres 14 or higher because of the new IVM extension (incrementally updating materialized views). I started answering his support request, but then thought I might as well post this on the blog as perhaps it provides value for others?!

Firstly, some context and background...

Installing software

Where possible, we recommend that TurnKey users just use the packages provided by the Debian (or TurnKey) official apt repos. The rationale for that is that then automated security updates are relatively safe. It allows us to ship with automated security updates enabled and the risk of things going badly are relatively low. That means that your system will remain relatively secure (reboots are still required for kernel and other low level components - but otherwise security updates are auto applied nightly).

However sometimes there are features in a newer version which you really need - or perhaps you just want to test them out. In that scenario, there are often a range of install options.

Third party apt repos

When available, one of the easiest, and likely best is to use a third party apt repository. Always be careful when installing software from third parties. I would recommend that you DO NOT ever install packages from random apt repos that you may find, unless you trust the provider, not just that they won't do anything malicious, but also that they will act in a responsible way and ensure the security of the repository is not compromised (e.g. so no malicious actor can get write access). That is even more so on a production server. As apt needs to run as root, a malicious package can do untold amounts of damage to your system and data.

Install PostgreSQL from official 3rd party repo

PostgreSQL is a powerful SQL database that has many competitive features when compared with other popular SQL databases. Debian (and therefore TurnKey) only provides or PostgreSQL version 13 (TKL v17.x - Debian 11/Bullseye) or PostgreSQL version 11 (TKL v16.x - Debian 10/Buster).

However, PostgreSQL provide their own Debian apt repo so it's possible to install much newer versions from an official and reputable source. Seeing as they provide instructions, you could follow their documentation to install a newer version.

Having said that, their suggested config is not really "best practice" (adds key to shared "trusted" keyring, and no package pinning) plus unless you've explicitly installed 'sudo', it likely won't work anyway (very few TurnKey builds include sudo by default). So I'll provide instructions on how I would do it.

The instructions below will assume that you are running as root. If not, you can get a root shell from a sudo user like this:

sudo su -

When you are finished this process and no longer need root access, you can exit the root shell with:

exit

Pinning packages from the Postgres repo

Firstly I suggest "apt pinning" all packages from the PostgreSQL apt repo. Pinning is a process which configures package availability and priority of which package(s) will install first if/when there is a conflict. The way it's generally used is to make installing unintended packages difficult whilst allowing the easy installation of the desired packages. This stops the risk of core OS packages being unintentionally replaced by packages from a third party repo.

The following will pin all packages from the Postgres apt repo with a value of '10'. That essentially makes them uninstallable (unless you explicitly install them) - so no risk of accidentally installing non-postgresql related packages that might exist in the repo - now or in the future. The specific packages that we do want to install are pinned with a value of '550'. FYI Debian default repos are set at '500' - so even if the exact same package is available in Debian, it will prefer the version from the Postgresql apt repo.

cat > /etc/apt/preferences.d/pgdg.pref <<EOF
Package: *
Pin: origin apt.postgresql.org
Pin-Priority: 10

Package: pgdg-keyring
Pin: origin apt.postgresql.org
Pin-Priority: 550

Package: postgresql*
Pin: origin apt.postgresql.org
Pin-Priority: 550

Package: libpq5
Pin: origin apt.postgresql.org
Pin-Priority: 550
EOF

Please be aware that in future, you may need to update the pinning to install a newer version. E.g. say if in a new (hypothetical future) postgresql update depends on a newer and renamed 'libpq5' dependency package (e.g. renamed to 'libpg6'), then you won't be able to upgrade postgresql until you update the pinning so that it will allow installation of the new 'libpg6' package.

Configure PosgreSQL apt repo and download key

Next configure the PostgreSQL apt repo sources list:

cat > /etc/apt/sources.list.d/pgdg.list <<EOF
deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main
EOF

And download the signing key and put in the desired place. Note that the key will need to be updated (via a similar command) when PostgreSQL rotate their keys next. It should simply be a case of re-running this command, but with the URL of the new key.

get -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg -o /usr/share/keyrings/pgdg.gpg --dearmor

Finally install Postgres

Now it's a simple case of updating the apt package info cache:

apt update

And installing the desired version:

apt install -y postgresql-VERSION

Where VERSION is the major version you want. E.g. to install v14:

apt install -y postgresql-14

Please note that it is also possible to just install the latest available version (currently v15). Regardless, that is NOT recommended, especially on a production server. The rationale for that is that it may cause an unexpected major version upgrade in the future. Instead it is recommended that you just install a specific version (as noted above - currently either 'postgresql-14' or 'postgresql-15').

Regardless, if you want to instal lthe latest (e.g. on a dev server), you can do that like this (note no version specified):

apt install -y postgresql

Last words

I have tested these instructions and they appear to work fine, however I did not carefully test migration/upgrade with an existing PostgreSQL DB. So whilst I have no reason to suspect that it will cause issues, I don't know for sure. I am unclear on how well it might deal with updating an existing DB. It's also possible that the upstream packages (i.e. the ones direct from PostgreSQL) install things in different places. So there is a possibility that it may not even "see" existing PostgreSQL DBs.

As such, I would NOT recommend trying this on a production server without first doing some more testing. I would recommend testing on a staging or development server first. When it does come time to do it on your production server, please ensure that you have both a current (tested) backup and ideally a full system snapshot too (how easy that is will depend on where you have it running - most VM environments provide a "snapshot" functionality; if installed to bare metal, try something like Clonezilla).

It may even be better to set this up on a new server, then migrate your existing data across?

Keeping PostgreSQL up to date

Please note that after following these instructions, you will no longer get automated security updates to PostgreSQL packages (only Debian security and TurnKey security repo packages will auto install). So you will need to manually apply updates as they become available. It's perhaps also worth noting that there are no security specific releases of PostgreSQL, so to ensure that you are using the most secure version, you'll need to update regardless or manually check yourself whether or not an update includes security fixes.

To check for newer versions:

apt update
apt list --upgradeable | grep "^postgresql*"

Please ignore the warning (about apt not having a stable CLI interface). Then re-run the relevant 'apt install' command that you used for the initial install. E.g. (to update PostgreSQL v14):

apt install -y postgresql-14

Alternatively, you could just run an 'apt update' followed by the apt install line above. If there are no postgresql updates, it will simply say something like "posgresql-14 is already at the latest version".

As per always I highly recommend ensuring that you have a current working backup prior to updating versions and I recommend testing on a staging/dev server before rolling out on production.

Let us know how you go

Hopefully that gets you going with a newer version of Postgres than what is supplied by Debian. Please share any feedback you have, be it announcing success, or reporting issues and seeking assistance if something goes wrong.