search string side menu

Updating standalone PostgreSQL 12 and lower to version 18

When updating PostgreSQL to a major version, the utility pg_upgrade is used. It allows updating the data stored in PostgreSQL data files to a later version of PostgreSQL.

The update of standalone PostgreSQL consists of six steps:

  1. Database backup.
  2. Installation of PostgreSQL 18.
  3. Updating data in the database directory.
  4. Configuration update.
  5. Post-installation check.

Step 1. Database backup

Before performing any actions related to the update, create a backup of the data and PostgreSQL configuration. Read more about how to do this in Backup and recover databases.

начало внимание

If there are third-party databases on the PostgreSQL server, their backups also need to be created.

конец внимание

Backup of the PostgreSQL database without using the elma365-backupper utility

 
You can create a backup of the elma365 database using the pg_dump:

 

sudo -u postgres pg_dump elma365 -Fc > path/to/elma365.bak 

Step 2. Installation of PostgreSQL 18

Use the package manager of your operating system to install PostgreSQL 18 on the server where version 12 or lower is installed:

sudo apt install postgresql-18

Step 3. Updating data in the database directory

  1. Stop PostgreSQL to keep its data inaccessible during the update. In this example version 12 is updated to version 18:

sudo systemctl stop postgresql@12 postgresql@18

  1. Ensure that both PostgreSQL 12 or lower and PostgreSQL 18 clusters are stopped:

pg_lsclusters

  1. On the server, perform a check for database configuration compatibility:

sudo -iu postgres /usr/lib/postgresql/18/bin/pg_upgrade \
-o "-c config_file=/etc/postgresql/12/main/postgresql.conf" \
-O "-c config_file=/etc/postgresql/18/main/postgresql.conf" \
--old-datadir=/var/lib/postgresql/12/main/ \
--new-datadir=/var/lib/postgresql/18/main/ \
--old-bindir=/usr/lib/postgresql/12/bin \
--new-bindir=/usr/lib/postgresql/18/bin --check

Where:

  • /usr/lib/postgresql/12/bin and /usr/lib/postgresql/18/bin are the paths to the binary files of PostgreSQL versions 12 and 18.
  • /var/lib/postgresql/12/main and /var/lib/postgresql/18/main are the paths to the data directories.
  1. On the server, perform the transfer of data from PostgreSQL 12 or lower to PostgreSQL 18:

sudo -iu postgres /usr/lib/postgresql/18/bin/pg_upgrade \
-o "-c config_file=/etc/postgresql/12/main/postgresql.conf" \
-O "-c config_file=/etc/postgresql/18/main/postgresql.conf" \
--old-datadir=/var/lib/postgresql/12/main/ \
--new-datadir=/var/lib/postgresql/18/main/ \
--old-bindir=/usr/lib/postgresql/12/bin \
--new-bindir=/usr/lib/postgresql/18/bin

Where:

  • /usr/lib/postgresql/12/bin and /usr/lib/postgresql/18/bin are the paths to the binary files of PostgreSQL versions 12 and 18.
  • /var/lib/postgresql/12/main and /var/lib/postgresql/18/main are the paths to the data directories.

Step 4. Configuration update

  1. Update the PostgreSQL 18 configuration settings according to the configuration used in PostgreSQL 12 or lower:

sudo nano /etc/postgresql/18/main/postgresql.conf
sudo nano /etc/postgresql/18/main/pg_hba.conf

  1. Run PostgreSQL 18:

sudo systemctl start postgresql@18

Step 5. Post-installation check

  1. Check PostgreSQL database status:

sudo systemctl status postgresql

  1. Ensure that PostgreSQL server is ready to receive connections from clients:

sudo pg_isready

discontinued-postgresql-support.html upgrading-postgresql-cluster-10-to-15.html