search string side menu

ELMA365 On-Premises > Prepare infrastructure > Databases > Discontinued PostgreSQL support / Updating standalone PostgreSQL 10 in ELMA365

Updating standalone PostgreSQL 10 in ELMA365

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 15.
  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 15

Use the package manager of your operating system to install PostgreSQL 15 on the server where version 10 is installed:

sudo apt install postgresql-15

Step 3. Updating data in the database directory

  1. Stop PostgreSQL to keep its data inaccessible during the update:

sudo systemctl stop postgresql@10 postgresql@15

  1. Ensure that both PostgreSQL 10 and PostgreSQL 15 clusters are stopped:

pg_lsclusters

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

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

where:

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

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

where:

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

Step 4. Configuration update

  1. Update the PostgreSQL 15 configuration settings according to the configuration used in PostgreSQL 10:

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

  1. Run PostgreSQL 15:

sudo systemctl start postgresql@15

Step 5. Post-installation check

  1. Check PostgreSQL database status:

sudo systemctl status postgresql

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

sudo pg_isready

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

Found a typo? Highlight the text, press ctrl + enter and notify us