How to upgrade a PostgreSQL database - pg_upgrade

How to upgrade a PostgreSQL database - pg_upgrade

Colleagues hello to all.

In today's article, we'll talk about how to upgrade a PostgreSQL database cluster to the latest version using the pg_upgrade utility. We will update from version 13 to version 14.

Sooner or later you will run into the problem of updating your PostgreSQL database to the latest version. Upgrading a database is a very important and in-demand task in the life of a DBA, because PostgreSQL             

There are two ways to update the database.

The first way is to first backup the entire database using the pg_dump or pg_dumpall utility, then delete the old database and deploy a new one instead of the old one, and only then use the same utilities pg_dump or pg_dumpall to restore the backup already in new DBMS PostgreSQL. This method is great if your database size is not very large in size, otherwise if your database size exceeds 100 gigabytes, then updates will take a very long time.

The second way is to use the  pg_upgrade utility. The pg_upgrade utility allows you to upgrade the database without creating backups and restoring these copies using the pg_dump or pg_dumpall utilities. pg_upgrade during the upgrade process transfers data from the old cluster to the new one by simply copying the data. pg_upgrade checks before upgrades that the old and the new cluster were binary compatible, the utility checks for compatible compile-time settings, including 32/64-bit binaries. Using  pg_upgrade gives us a big plus in that upgrades are very fast. We were updating a 3 terabyte database and the update took about 10 minutes.

 

Article content:

  1. Prerequisites.
  2. Directory locations.
  3. Environment variable files.
  4. Building and initializing a new cluster.
  5. Compatibility check.
  6. Stopping the old cluster.
  7. Updating the cluster with a simple method.
  8. Launching a new cluster.
  9. Summary

 

Utility syntax:

pg_upgrade -b old_pghome_bin -B new_pghome_bin -d old_pgdata -D new_pgdata [--options]

 

1. Prerequisites.

To make it clear what this article is about, I highly recommend that you look at the article on how we install PostgreSQL DBMS on Centos 8.

Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR


 

2. Directory locations.

Each of the DBMS will be stored in separate directories.

DBMS version 13 will be stored in the /app/postgresql/13 directory and will contain three more directories. The first directory is called  log, it is responsible for storing all the event logs associated with the database. The second directory is called  pgdata, it is responsible for storing all the database cluster files. The third directory is called  pghome, it contains all the database management utilities, all possible extensions and various libraries.

/app/postgresql/13
/app/postgresql/13/log
/app/postgresql/13/pgdata
/app/postgresql/13/pghome

postgresql upgrade

 

DBMS version 14 will be stored in the  /app/postgresql/14 directory and will contain the same three directories with the same names.

/app/postgresql/14
/app/postgresql/14/log
/app/postgresql/14/pgdata
/app/postgresql/14/pghome

postgresql upgrade


 

3. Environment variable files.

In the home directory of the postgres account, we must create two environment variable files. For each database, we will have a separate file with specific variables that are needed for the normal operation of the database itself. The files will be named  pgsql_13.env and  pgsql_14.env. The pgsql_13.env file will refer to database version 13, and the pgsql_14.env file will refer to database version 14. The contents of each of the files will be shown below.

postgresql upgrade

File pgsql_13.env

export PGUSER=postgres
export PGPORT=5432
export PGPASSWORD='Qwerty123!'
export PGHOME=/app/postgresql/13/pghome
export LD_LIBRARY_PATH=/app/postgresql/13/pghome/lib
export PGDATA=/app/postgresql/13/pgdata
export PATH=$PGHOME/bin:$PGDATA:$PATH

 

File pgsql_14.env

export PGUSER=postgres
export PGPORT=5432
export PGPASSWORD='Qwerty123!'
export PGHOME=/app/postgresql/14/pghome
export LD_LIBRARY_PATH=/app/postgresql/14/pghome/lib
export PGDATA=/app/postgresql/14/pgdata
export PATH=$PGHOME/bin:$PGDATA:$PATH

 

Variable notation:

  • PGUSER - Main account from under which authorization in the melon database occurs.
  • PGPORT - The port on which the database is running.
  • PGPASSWORD - Password for the  account under which authorization in the melon database takes place.
  • PGHOME - Path to the directory in which all utilities that manage the database are stored.
  • LD_LIBRARY_PATH - Path to libraries used by the database.
  • PGDATA - Path to the directory where all database cluster files are stored.
  • PATH - Redefine the main variable in Linux by adding variables related to the PostgreSQL DBMS.

 

4. Building and initializing a new cluster.

Before using the  pg_upgrade utility, we need to build and initialize a new database cluster already version 14, because the  pg_upgrade utility will connect to the old and at the same time to the new cluster before updates and check them for compatibility. If pg_upgrade finds at least one incompatibility, then the cluster will not be upgraded.

In this article I will not show how to build a new cluster, because you can see it in my last article as Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR.

Once you've built and initialized the new cluster, the next step is to copy the two configuration files from the old cluster and transfer them to the new cluster. The config files are pg_hba.conf and postgresql.conf, you need to copy them because the pg_upgrade utility will only transfer the data itself, and will not transfer the configuration files.

Designations for configuration files.

  • pg_hba.conf - Responsible for database authorization.
  • postgresql.conf - The main config file that is responsible for the entire database.

 

5. Compatibility check.

In the end, we're slowly getting to use the pg_upgrade utility. The most important thing we need to know is that the utility needs to be run from the new cluster, and not from the old one. In this case, we will run the utility from the directory where we have deployed a version 14 database cluster.

Before starting the update, we need to check the compatibility of the two clusters. You can check for compatibility even if we have an old database cluster running.

In order to check the compatibility of two clusters, we need to run the pg_upgrade utility with the following parameters:

$. /app/postgresql/14/pghome/bin/pg_upgrade -b /app/postgresql/13/pghome/bin -B /app/postgresql/14/pghome/bin -d /app /postgresql/13/pgdata -D /app/postgresql/14/pgdata --check

Parameter descriptions:

  • -b - Path to the directory with executable files of the old version of PostgreSQL.
  • -B - Path to the directory with executable files of the new PostgreSQL version.
  • -d - Path to the directory where all files of the old database cluster are stored.
  • -D - Path to the directory where all files of the new database cluster are stored.
  • --check - check clusters for compatibility with each other, will not change any data.

 

As a result of the check, you should receive the following response:

Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

*Clusters are compatible*

postgresql upgrade

If you get at least one error as a result, then you will not be able to update the cluster until you fix this error.


 

6. Stopping the old cluster.

It is imperative that both the old and the new database cluster be stopped before starting the upgrade. In order to stop the database, you must use the command:

$. /app/postgresql/13/pghome/bin/pg_ctl stop


 

7. Updating the cluster with a simple method.

Now we are going to upgrade the database cluster itself. In this example, we will use the update method which simply copies the data from the old cluster to the new one, this method is the default method. The advantage of this approach is that it allows you to save the working state of the old cluster when starting a new one. For example, we updated the cluster and started it, but still something went wrong, and we can stop the new cluster and restart the old cluster.

To start a regular update, run the command:

$. /app/postgresql/14/pghome/bin/pg_upgrade -b /app/postgresql/13/pghome/bin -B /app/postgresql/14/pghome/bin -d /app /postgresql/13/pgdata -D /app/postgresql/14/pgdata

You should get this response as a result of the update:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting oldest XID for new cluster                          ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to delete old cluster                       ok
Checking for extension updates                              ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /app/postgresql/14/pghome/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

After the update is completed, in the directory from which you ran the cluster update command, a file will appear called delete_old_cluster.sh. This file will contain only one command that will delete the old cluster for you. You can either delete the old cluster yourself or execute this file, but if you want, you can not delete the old cluster, but delete it a little later if you want.


 

8. Launching a new cluster.

Once you've successfully upgraded the database cluster, we can now start it up. In order to start the cluster, we can use the command:

$. /app/postgresql/14/pghome/bin/pg_ctl start

postgresql upgrade

As you can see, as a result, our cluster successfully started and the version of our cluster is now 14.0 instead of 13.0


 

9. Results.

Colleagues, as a result, today we have successfully considered how to update our database cluster in a simple way. Of course, in addition to a simple update, you can still use methods such as updates using hard links and the clone method, but these methods have one disadvantage. The disadvantages are that after the upgrade, the old database cluster becomes unusable, and in order to start it back, you need to make changes to the data files of the old cluster.


 

Thank you all, I hope that my article helped you in some way.

SIMILAR ARTICLES

PostgreSQL - How to determine which files on disk correspond to tables in a database
PostgreSQL - How to determine which files on disk correspond to tables in a database
POSTGRESQL
Read more
PostgreSQL - How to find out the size of tables in a database
PostgreSQL - How to find out the size of tables in a database
POSTGRESQL
Read more
PostgreSQL - How to check user privileges
PostgreSQL - How to check user privileges
POSTGRESQL
Read more