Upgrading the PostgreSQL Database

Upgrading PostgreSQL to a new version is not necessary for most use cases (FACT should work just fine even if you use an older version). If you want to do so regardless, there are quite a few steps that you need to follow. It is not possible to do an in-place upgrade and keep using the existing database entries. Instead, you need to install the new version parallel to the old version and transfer the data from the old version to the new one.

1. Install the new PostgreSQL version

sudo apt install -y postgresql-common
# if you are not using ubuntu, replace $(lsb_release -cs) with the correct 
# release (you may need to check /etc/os-release)
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y $(lsb_release -cs)
sudo apt install -y postgresql-17

2. Check on which port the new version runs

Check on which port the new version of postgres runs (it's usually one higher than the last one, so if your old postgres database uses port 5432, the new version will probably use port 5433).

You can also use netstat or ss to check on which port postgres runs:

sudo netstat -tulpn | grep -i postgres

3. Transfer configuration and data

The contents of the old database (including data, roles, tables, etc.) can be transferred directly from one version of postgres to the other with this command:

sudo -u postgres pg_dumpall -p 5432 | sudo -u postgres psql -p 5433

where 5432 is the port of your old version and 5433 the one of the new version (edit if necessary).

4. Update configuration and check that the migration was successful

During the installation of FACT, the configuration of postgres is changed. First, we need to change the configuration of FACT that it uses the new database: Edit src/config/fact-core-config.toml and set the port (under [common.postgres]) to the new one. Since the new version has new config files, we need to rerun the installation to update them:

python src/install.py -D

Now start FACT and check that all the data is there.

5. Uninstall old postgres version (optional)

Once you are sure that all the data was migrated successfully, you can uninstall the old postgres version. This is optional, though.

sudo service postgresql@14-main stop
sudo apt remove -y postgresql-14

For more information, please refer to the official PostgreSQL docs.

Optionally, you can now also set the port of your new postgres version back to the standard port 5432 by editing the file postgresql.conf:

sudo nano /etc/postgresql/17/main/postgresql.conf 
# find the line with `PORT = ...` and change it to 5432
# then restart postgres
sudo service postgresql restart