# Lesson 17: Administration / Server Upgrade

> Native Markdown source page: Administration / Server Upgrade

---

LLMS index: [llms.txt](/en/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references.
> **HTML source:** [dba2_17_admin_upgrade.html](https://edu.postgrespro.com/16/dba2-16-en/dba2_17_admin_upgrade.html)
> **Archive:** [DBA2-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA2-handouts-16-en.zip)
> **PDF pages:** 37

## Page 1: Administration / Server Upgrade {#source-page-001}

### Copyright

© Postgres Professional, 2016–2026 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
### Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials.
### Feedback

Please send your feedback, comments and suggestions to: edu@postgrespro.ru
### Disclaimer

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications.

## Page 2: Topics {#source-page-002}

- Version Numbering and General Considerations
- Minor Upgrade
- Major Upgrade

## Page 3: Server Versions {#source-page-003}

**Major versions (9.6, 10, 11, ...)**
  - community support for 5 years
  - functionality is added and modified
  - new version is not binary compatible with the previous one
  - upgrading always requires special procedures
- Minor releases (16.1, 16.2, ...)
  - bug and security fixes only
  - binary compatibility is guaranteed
  - in most cases replacing the executables is sufficient

### Speaker notes

PostgreSQL version number consists of two components: the major version number and the minor release number. Before version 10, the major version number consisted of two digits (9.5, 9.6); starting with version 10, it switched to a single digit (10, 11). The minor release number is the last digit after the dot (for example, 2 in 16.2).

Minor releases serve exclusively to fix bugs found in the major version. They maintain binary compatibility (on the same platform). Therefore, upgrading to the next minor release is straightforward and is recommended as soon as it becomes available.

A new major version introduces functional changes: some features are added, modified, or, less frequently, removed. In this case, binary compatibility is not maintained. PostgreSQL will refuse to work with a database cluster if you try to connect a newer version of the executables to an older database cluster.

Upgrading a major version requires special steps. In addition to upgrading the database server, you may also need to update your application. The main reasons for upgrading to a new version are the introduction of new features and the end of support for the old version (5 years after release). Due to the complexity of the process, it is common to skip several versions, for example, upgrading directly from version 13 to 16, etc.

<https://postgrespro.com/docs/postgresql/16/upgrading>

## Page 4: General Considerations {#source-page-004}

**Release Notes**
  - study the Migration section of all intermediate versions
- Testing the upgrade in a stage environment
  - identify potential issues in advance
  - automate the process to reduce downtime
- Fallback plan
  - in case issues arise during the upgrade of the production server

### Speaker notes

Regardless of the type of upgrade being performed, it is important to pay attention to several key points.

Always carefully review the Release Notes appendix of the documentation. Its Migration section covers all incompatibilities and non-standard steps required for the upgrade.

For example, if you are planning to upgrade from version 15.4 to 16.13 (the latest minor release of the chosen major version as of the preparation of the course materials), you need to review all the release notes for versions 15.4 through 15.17 (the latest minor release for version 15), and 16.0 through 16.13.

The upgrade should be tested in a stage environment equivalent to production, followed by application testing. It helps identify potential issues and refine and automate the upgrade procedure.

This significantly reduces the risk of issues when upgrading the production server, though it does not eliminate it entirely. Therefore, it is always necessary to have a (tested) fallback plan in case of upgrade failure. Such a plan could be, for example, a standby or a backup, depending on your acceptable downtime requirements.

## Page 5: Minor Upgrade {#source-page-005}

**Minor upgrade**
- Using physical standbys

## Page 6: Minor Upgrade {#source-page-006}

16.3
  - installing the new version packages (server, client, extensions)
  - restarting the server
  - updating extensions and performing additional steps if required
- 16.4
- + simplicity – forced service interruption

### Speaker notes

To upgrade the server to the next minor release, you need to do the following:

1. Install the new executables.

If PostgreSQL was installed from packages, install new versions of the packages (server, client, extensions). Some package managers, particularly apt in Ubuntu, automatically restart the server, which is usually undesirable.

If PostgreSQL was built from source code, you need to run make install.

2. Restart the server. After the restart, the server will be running the new version. Client service is interrupted for the duration of the restart.

3. After the restart, you may need to update extensions (ALTER EXTENSION ... UPDATE) and perform additional steps specified in the Release Notes. However, in most cases, it is not required.

## Page 7: apt install {#source-page-007}

**Upgrading with OS tools**
```text
sudo apt install postgresql-16
```
- May restart the server automatically
  - the needrestart utility

### Speaker notes

If PostgreSQL was installed from a package, the OS can upgrade its version automatically. In Ubuntu, this is done using the following command

sudo apt-get install --only-upgrade package-name

or

sudo apt install package-name

During such an upgrade, the server is usually restarted automatically, which can be inconvenient. This behavior can be configured. Details are provided in the documentation of the needrestart utility:

<https://github.com/liske/needrestart>

## Page 8: Reducing Downtime {#source-page-008}

**Server restart process**
  - new connections are prohibited
  - smart
  - waits for all sessions to complete
  - fast
  - forcibly terminates all sessions (by default)
  - a checkpoint is performed
- Checkpoint
  - manually before restarting the server
- PgBouncer
  - pauses during server restart
  - open transactions continue to execute, while all new ones are suspended

### Speaker notes

Service interruption can be reduced, and in some cases, eliminated entirely.

What happens during a server restart?

Firstly, PostgreSQL stops accepting new connections (clients receive a “shutdown in progress” error).

Secondly, depending on the shutdown mode, the server either waits for active sessions to complete or terminates them forcibly. The latter is implied by the management utility by default, but it is probably not the best choice for a production environment if the server primarily handles OLTP workloads (short queries).

Thirdly, a final shutdown checkpoint is performed to flush data from RAM buffers to disk (as discussed in the WAL module). When the buffer cache is large, this process can take significant time. Therefore, it might be advisable to first run a manual CHECKPOINT before restarting the server. In this case, the final checkpoint will still be performed, but much faster.

If PgBouncer (<https://pgbouncer.org)> is used, it makes sense to pause it during the restart. In this case (transaction mode is implied), active transactions continue executing while all new ones will be queued. Therefore, for an OLTP application, the restart appears as increased database response time.

## Page 9: Replication {#source-page-009}

primary standby
  - standby is
  - upgraded first
  - standby has caught up with the primary, switchover
  - primary
- standby
- + enables upgrades with zero or minimal service interruption

### Speaker notes

If the system uses physical replication and has a mechanism for seamless user switchover between servers, the upgrade procedure can be performed without service interruption.

To achieve this, the standby is upgraded first. During its restart, the primary handles the entire load. Since the versions are binary compatible, replication continues to work between the upgraded standby and the primary that has not been upgraded yet. In the vast majority of cases, replication also works in the opposite direction (from the upgraded server to the server running the older release), but backward compatibility is not guaranteed. That’s why it is safer to upgrade the standby first.

After standby restart, it catches up with the primary, the primary is stopped, and a switchover to the standby is performed. Upon starting the former primary, it connects to the new primary as a standby (this may require running the pg\_rewind utility; the entire procedure is covered in detail in the DBA3 course).

## Page 10: Minor Upgrade {#source-page-010}

Check the current PostgreSQL version:
```text
16=> SHOW server_version;
```
```text
          server_version
----------------------------------
 16.2 (Ubuntu 16.2-2.pgdg24.04+1)
(1 row)
```
There is a more recent version in the repository:
```sh
student$ sudo apt list -q postgresql-16
```
```sql
postgresql-16/noble-pgdg-archive,noble-pgdg 16.14-1.pgdg24.04+1 arm64 [upgradable from:
16.2-2.pgdg24.04+1]
```
Let’s upgrade to version 16.14 by updating the postgresql-16 package using the OS package manager:
```sh
student$ sudo apt install -y postgresql-16
```
```sql
The following additional packages will be installed:
  postgresql-client-16
Suggested packages:
  postgresql-doc-16
The following packages will be upgraded:
  postgresql-16 postgresql-client-16
Preconfiguring packages ...
2 upgraded, 0 newly installed, 0 to remove and 367 not upgraded.
Need to get 0 B/17.8 MB of archives.
After this operation, 884 kB of additional disk space will be used.
(Reading database ... 223372 files and directories currently installed.)
Preparing to unpack .../postgresql-client-16_16.14-1.pgdg24.04+1_arm64.deb ...
Unpacking postgresql-client-16 (16.14-1.pgdg24.04+1) over (16.2-2.pgdg24.04+1) ...
Preparing to unpack .../postgresql-16_16.14-1.pgdg24.04+1_arm64.deb ...
Unpacking postgresql-16 (16.14-1.pgdg24.04+1) over (16.2-2.pgdg24.04+1) ...
Setting up postgresql-client-16 (16.14-1.pgdg24.04+1) ...
Setting up postgresql-16 (16.14-1.pgdg24.04+1) ...
Processing triggers for postgresql-common (291.pgdg24.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
```
> [!NOTE]
> **Note**
> Note that the postgresql-client-16 package was also updated and the server has been restarted.
```sh
student$ tail -n 5 /var/log/postgresql/postgresql-16-main.log
```
```text
2026-06-11 17:36:25.409 MSK [41182] LOG:  starting PostgreSQL 16.14 (Ubuntu
16.14-1.pgdg24.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
2026-06-11 17:36:25.409 MSK [41182] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2026-06-11 17:36:25.410 MSK [41182] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2026-06-11 17:36:25.415 MSK [41185] LOG:  database system was shut down at 2026-06-11
17:36:21 MSK
2026-06-11 17:36:25.420 MSK [41182] LOG:  database system is ready to accept connections
```
Check the version number again:
```sh
student$ psql
```
```text
16=> SHOW server_version;
```
```text
           server_version
------------------------------------
 16.14 (Ubuntu 16.14-1.pgdg24.04+1)
(1 row)
```

## Page 11: Major Upgrade {#source-page-011}

**Logical backup with pg\\\_dumpall**
- The pg\_upgrade utility
- Logical replication

## Page 12: pg_dumpall {#source-page-012}

  - installing packages and initializing the cluster
  - disconnecting users (read-only mode)
  - logical backup (dump)
  - restoring from a backup (psql)
  - collecting statistics
  - startup
- + allows changing platform, bitness, database encoding, etc. – long upgrade time, requires significant disk space

### Speaker notes

The simplest way to upgrade to a major version is to create a logical backup and restore it on another server running the new version. (A logical backup is a set of SQL commands that recreate the database; see the DBA3 course for details).

Firstly, install the new version and initialize the database cluster (some package managers perform initialization automatically during installation).

If you are deploying the new server on the same machine, make sure the data directories do not overlap. Packages are typically built to meet this requirement (for example, /var/lib/postgresql/15/ and /var/lib/postgresql/16/). Modify the configuration files of the new cluster by applying settings from the running server.

You can create a backup while the server is running, but all users must be disconnected (only read‐only access is allowed), since changes made after the backup procedure starts will not be included.

After that, start the new server and restore the prepared backup. Then you need to collect statistics; without it, the optimizer cannot build efficient query plans.

Once you have completed all necessary checks, the old server directories (executables, PGDATA contents, and custom tablespaces) can be removed.

## Page 13: pg_dumpall {#source-page-013}

**Speeding up data migration**
```text
pg_dumpall --globals-only
```
```text
pg_dump --format=d --jobs=N
pg_restore --jobs=N
```
  - individually for each database
- Space optimization
```text
pg_dumpall | psql connection-parameters-to-new-cluster
```
- Statistics collection
  - $ vacuumdb --analyze-in-stages
  - dump\_stat extension
- Quick validation
```text
pg_dumpall --schema-only
```

### Speaker notes

Although pg\_dumpall creates a backup of the entire cluster, it always runs in a single-threaded mode. Restoration uses psql, so it is also single-threaded.

If hardware resources allow, you can save the cluster’s global objects using pg\_dumpall, and then back up each database individually in directory format using pg\_dump in parallel mode. In this case, recovery can also be performed in parallel using the pg\_restore utility. See the DBA3 course for details.

To optimize disk space, you can skip storing the dump file and simply pipe the output of pg\_dumpall directly into psql.

To collect at least some statistics as quickly as possible, you can do it in three stages. You can try to start using the database after the second stage. Another option is to use the dump\_stat extension:

<https://postgrespro.com/docs/postgrespro/16/dump-stat>

When testing the upgrade in a stage environment, you can first perform a schema-only migration (without data). This facilitates early detection of possible issues.

It is recommended to use the utilities from the new (already installed) version, as they may include improvements not available in the previous version.

## Page 14: PostgreSQL 15 Cluster {#source-page-014}

![Source slide page 14: PostgreSQL 15 Cluster](images/page-014.png){#source-figure-014 caption="Source slide page 14: PostgreSQL 15 Cluster"}

Stop the 16 main cluster to avoid accidental connections to it.
```sh
student$ sudo pg_ctlcluster 16 main stop
```
The data directory /var/lib/postgresql/15/prod contains a PostgreSQL 15 database cluster.
```sh
student$ psql -p 5433
```
```text
15=> SHOW server_version;
```
```text
           server_version
------------------------------------
 15.18 (Ubuntu 15.18-1.pgdg24.04+1)
(1 row)
```
Create a tablespace and a database.
```sh
student$ sudo rm -rf /var/lib/postgresql/ts_dir
```
```sh
student$ sudo mkdir /var/lib/postgresql/ts_dir
```
```sh
student$ sudo chown postgres: /var/lib/postgresql/ts_dir
```
```sql
15=> CREATE TABLESPACE ts LOCATION '/var/lib/postgresql/ts_dir';
```
```sql
CREATE TABLESPACE
```
```sql
15=> CREATE DATABASE admin_upgrade;
```
```sql
CREATE DATABASE
```
```sql
15=> \c admin_upgrade
```
```text
You are now connected to database "admin_upgrade" as user "student".
```
Create a table in the newly created tablespace:
```sql
15=> CREATE TABLE test(
  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  s text
) TABLESPACE ts;
```
```sql
CREATE TABLE
```
```sql
15=> INSERT INTO test(s) VALUES ('Hello from 15!');
```
```sql
INSERT 0 1
```
Install the pgaudit extension:
```sh
student$ sudo apt install -y postgresql-15-pgaudit
```
```sql
The following NEW packages will be installed:
  postgresql-15-pgaudit
0 upgraded, 1 newly installed, 0 to remove and 367 not upgraded.
Need to get 0 B/44.7 kB of archives.
After this operation, 139 kB of additional disk space will be used.
Selecting previously unselected package postgresql-15-pgaudit.
(Reading database ... 223374 files and directories currently installed.)
Preparing to unpack .../postgresql-15-pgaudit_1.7.1-2.pgdg24.04+1_arm64.deb ...
Unpacking postgresql-15-pgaudit (1.7.1-2.pgdg24.04+1) ...
Setting up postgresql-15-pgaudit (1.7.1-2.pgdg24.04+1) ...
Processing triggers for postgresql-common (291.pgdg24.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
```
```sql
15=> ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo pg_ctlcluster 15 prod restart
```
```sh
student$ psql -p 5433
```
```sql
15=> \c admin_upgrade
```
```text
You are now connected to database "admin_upgrade" as user "student".
```

## Page 15: Page 15 {#source-page-015}

```sql
15=> CREATE EXTENSION pgaudit;
```
```sql
CREATE EXTENSION
```
```text
15=> \dx pgaudit
```
```text
                 List of installed extensions
  Name   | Version | Schema |           Description
---------+---------+--------+---------------------------------
 pgaudit | 1.7.1   | public | provides auditing functionality
(1 row)
```
### The pg\\\_dumpall Utility
Create a logical backup of the entire cluster. The server must be running, but changes made after pg\_dumpall starts will not be included in the backup.
```sh
student$ pg_dumpall -p 5433 > ~/dump.sql
```
The backup that we created is a text file containing SQL commands. It includes commands for creating databases:
```sh
student$ grep 'CREATE DATABASE' ~/dump.sql | fold -sw 100
```
```sql
CREATE DATABASE admin_upgrade WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER
= libc
LOCALE = 'en_US.UTF-8';
CREATE DATABASE student WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER =
libc LOCALE =
'en_US.UTF-8';
```
And commands for creating tables:
```sh
student$ grep -A 3 'CREATE TABLE ' ~/dump.sql
```
```sql
CREATE TABLE public.test (
    id integer NOT NULL,
    s text
);
```
It also includes commands for creating cluster-wide objects such as roles and tablespaces, for example:
```sh
student$ grep 'CREATE TABLESPACE' ~/dump.sql
```
```sql
CREATE TABLESPACE ts OWNER student LOCATION '/var/lib/postgresql/ts_dir';
```
We will not restore the backup on the PostgreSQL 16 server; you will perform this step in the practice.

## Page 16: pg_upgrade {#source-page-016}

**Applicability requirements**
  - in the new version, only the format of internal system data changes
  - binary compatibility is maintained in all other aspects,
  - including type representations and data files
  - REG types are not used in the databases
  - upgrade from version 8.4 or later
  - upgrade to the version matching the utility

### Speaker notes

The second and most common upgrade method is using the pg\_upgrade utility, which can transform files of the cluster to a format compatible with the new major version.

<https://postgrespro.com/docs/postgresql/16/pgupgrade>

The utility can be used because major version upgrade typically changes the system catalog tables, while leaving data and index file formats unchanged. Therefore, only a small portion of the data needs to be modified, leaving the bulk of it untouched. The new cluster must be compatible with the old one in terms of bitness, encoding, and locale settings.

In some versions, the binary representation of data types changes, though this happens quite rarely. In such cases, the utility cannot perform the upgrade, but it will at least identify where the problematic type is used. This will help you resolve the issue manually before upgrading.

There are some additional limitations as well. For example, the utility cannot upgrade a cluster if any of the databases use REG types (except for regtype, regclass, and regrole).

When pg\_upgrade runs, it checks that its own version and the versions of all executables involved in the upgrade process match the version of the new server. The utility supports upgrading from any version starting with 8.4 to the version that corresponds to the utility itself. Therefore, pg\_upgrade version 16 can only upgrade an old server to version 16, not to version 15. Downgrading the server is not possible.

## Page 17: pg_upgrade {#source-page-017}

![Source slide page 17: pg_upgrade](images/page-017.png){#source-figure-017 caption="Source slide page 17: pg_upgrade"}

- + fast upgrade, typically no additional disk space required – limited applicability

### Speaker notes

First, let’s look at the general (simplified) sequence of steps for an upgrade using pg\_upgrade.

Install the new server and initialize (but do not start) the new cluster. This step is identical to the upgrading procedure using pg\_dumpall.

Then stop the old server and run the pg\_upgrade utility. Let’s outline what it does:

● The old server is started temporarily to create a logical backup of the cluster-wide objects and the schema of all databases. This is precisely the information for which binary compatibility is not maintained.

● The new server is started temporarily to perform necessary applicability checks and to restore the global objects and data schema from the backup.

● Data migration is performed: either by copying, hard linking, or cloning (for copy-on-write file systems).

Once the utility completes its work, the new cluster becomes an upgraded version of the old one. Finally, post-upgrade steps are performed, such as collecting statistics and updating extensions.

A huge advantage of pg\_upgrade is its capability to perform extremely fast upgrades of clusters of any size, with no need for additional disk space (when using hard links). However, this utility is not always applicable.

## Page 18: pg_upgrade {#source-page-018}

![Source slide page 18: pg_upgrade](images/page-018.png){#source-figure-018 caption="Source slide page 18: pg_upgrade"}


### Speaker notes

Let’s take a closer look at what happens to the file systems of the old and new clusters during an upgrade.

Step 1. Packages for the new version are installed, and the new cluster is initialized. The new server is shut down and contains no user objects — only the standard databases and the system catalog.

The old server continues to run.

Note that the new cluster must be initialized using the same locale as the old one.

## Page 19: PostgreSQL 16 Cluster {#source-page-019}

PostgreSQL 16 is already installed, and the cluster has been initialized in /var/lib/postgresql/16/prod directory.
Check that the server is running and using the same locale as PostgreSQL 15, then stop it.
```text
15=> \x \l template0
```
```text
Expanded display is on.
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | template0
Owner             | postgres
Encoding          | UTF8
Locale Provider   | libc
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
ICU Locale        |
ICU Rules         |
Access privileges | =c/postgres          +
                  | postgres=CTc/postgres
```
```sql
15=> \q
```
```sh
student$ sudo pg_ctlcluster 15 prod stop
```
```sh
student$ sudo pg_ctlcluster 16 prod start
```
```sh
student$ psql -p 5433 -U postgres
```
```text
16=> \x \l template0
```
```text
Expanded display is on.
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | template0
Owner             | postgres
Encoding          | UTF8
Locale Provider   | libc
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
ICU Locale        |
ICU Rules         |
Access privileges | =c/postgres          +
                  | postgres=CTc/postgres
```
Stop the server.
```sql
16=> \q
```
```sh
student$ sudo pg_ctlcluster 16 prod stop
```
### Checking Upgrade Feasibility
Before performing the actual upgrade, it is a good idea to run pg\_upgrade in check mode with the --check option. Since we plan to use hard links instead of copying data files, we also specify the --link option.
We also need to specify the paths to the executables and data directories for both the old and new versions. Note that the program operates under the OS user postgres, since it needs access to the data directories.
It is important that the versions of pg\_upgrade and other server utilities (pg\_controldata, initdb) match the versions of the client utilities they invoke (pg\_dump, pg\_dumpall, pg\_restore, psql, vacuumdb). Otherwise, an error will occur. Since we updated both the server and client packages to version 16.14, this requirement is satisfied:
```sh
postgres$ /usr/lib/postgresql/16/bin/pg_upgrade --version
```
```text
pg_upgrade (PostgreSQL) 16.14 (Ubuntu 16.14-1.pgdg24.04+1)
```
```sh
student$ pg_dump --version
```
```text
pg_dump (PostgreSQL) 16.14 (Ubuntu 16.14-1.pgdg24.04+1)
```
You should pay attention to the authentication settings. The utility needs local superuser access to both clusters since it starts and stops the servers during operation. We already have such access rights, but in general cases you may need to temporarily modify the pg\_hba.conf file. The pg\_upgrade utility also needs write access to create various temporary files in a dedicated working

## Page 20: Page 20 {#source-page-020}

directory pg\_upgrade\_output.d within the new cluster’s data directory. The servers are started sequentially on port 50432 to prevent accidental user connections; the port number can be specified explicitly if necessary.
```sh
postgres$ /usr/lib/postgresql/16/bin/pg_upgrade \
--check \
--link \
-b /usr/lib/postgresql/15/bin/ \
-B /usr/lib/postgresql/16/bin/ \
-d /etc/postgresql/15/prod \
-D /etc/postgresql/16/prod
```
```text
Finding the real data directory for the source cluster        ok
Finding the real data directory for the target cluster        ok
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 incompatible "aclitem" data type in user tables  ok
Checking for not-null constraint inconsistencies              ok
Checking for presence of required libraries                   fatal
```
```sql
Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
/var/lib/postgresql/16/prod/pg_upgrade_output.d/20260611T173636.792/loadable_libraries.txt
Failure, exiting
```
The utility has detected an issue: the new cluster is missing some libraries.
Let’s check the list:
```sh
student$ sudo cat
/var/lib/postgresql/16/prod/pg_upgrade_output.d/20260611T173636.792/loadable_libraries.txt
```
```text
could not load library "$libdir/pgaudit": ERROR:  could not access file
"$libdir/pgaudit": No such file or directory
In database: admin_upgrade
```
This is the library of the extension we installed, which is required by an index in the admin\_upgrade database. It must also be installed for the new version.
```sh
student$ sudo apt install -y postgresql-16-pgaudit
```
```sql
The following NEW packages will be installed:
  postgresql-16-pgaudit
0 upgraded, 1 newly installed, 0 to remove and 367 not upgraded.
Need to get 0 B/46.1 kB of archives.
After this operation, 140 kB of additional disk space will be used.
Selecting previously unselected package postgresql-16-pgaudit.
(Reading database ... 223385 files and directories currently installed.)
Preparing to unpack .../postgresql-16-pgaudit_16.1-2.pgdg24.04+1_arm64.deb ...
Unpacking postgresql-16-pgaudit (16.1-2.pgdg24.04+1) ...
Setting up postgresql-16-pgaudit (16.1-2.pgdg24.04+1) ...
Processing triggers for postgresql-common (291.pgdg24.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
  en_us
Removing obsolete dictionary files:
```
Don’t forget to configure the server to load the library into each backend process’s memory:
```sh
student$ sudo pg_ctlcluster 16 prod start
```
```sh
student$ psql -p 5433 -U postgres
```
```sql
16=> ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo pg_ctlcluster 16 prod stop
```
Check again.
```sh
postgres$ /usr/lib/postgresql/16/bin/pg_upgrade \
--check \
--link \
-b /usr/lib/postgresql/15/bin/ \
```

## Page 21: Page 21 {#source-page-021}

```text
-B /usr/lib/postgresql/16/bin/ \
-d /etc/postgresql/15/prod \
-D /etc/postgresql/16/prod
```
```text
Finding the real data directory for the source cluster        ok
Finding the real data directory for the target cluster        ok
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 incompatible "aclitem" data type in user tables  ok
Checking for not-null constraint inconsistencies              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
```
```text
*Clusters are compatible*
```
Now the clusters are compatible.

## Page 22: pg_upgrade {#source-page-022}

![Source slide page 22: pg_upgrade](images/page-022.png){#source-figure-022 caption="Source slide page 22: pg_upgrade"}


### Speaker notes

Step 2. The old server is stopped, and the pg\_upgrade utility is started. It creates a backup of the catalog and schemas for all databases using the pg\_dump and pg\_dumpall utilities in a special --binary-upgrade mode, which preserves file numbering (this mode is not intended for regular use and is not officially supported):

pg\_dumpall --globals-only --binary-upgrade pg\_dump --schema-only --binary-upgrade

After that these objects are restored to the new server using pg\_restore (these utilities are covered in detail in the DBA3 course). The directory names for databases and tablespaces, as well as the file names of the relations, remain unchanged after the upgrade.

All these steps are completed fairly quickly — the completion time does not depend on the amount of data in the cluster.

After this step, the system catalog of the new cluster will match that of the old cluster, except for changes in the data format (this is why a logical backup is used for it instead of a physical one). In fact, all databases and all objects within them will be created in the new cluster, but the system catalog will reference data files that do not exist yet.

The pg\_upgrade utility performs many other quite complex operations. It freezes all transactions in the new cluster (to avoid dependency on IDs of transactions that were used there), copies the transaction status (CLOG), copies information about multixacts, and sets the transaction ID counter to the value retrieved from the old cluster.

## Page 23: pg_upgrade {#source-page-023}

![Source slide page 23: pg_upgrade](images/page-023.png){#source-figure-023 caption="Source slide page 23: pg_upgrade"}


### Speaker notes

Step 3. The pg\_upgrade utility performs the user data migration.

At this point, the data files of the new cluster are either copied from the files of the old one or linked to them via hard links.

Note that if data files are copied, the old cluster remains completely untouched by the upgrade. This means you can return to it at any time if something goes wrong during the process.

However, when hard links are used, the data files are shared between the clusters: identical file names in both clusters point to the same filesystem inodes. Therefore, once the new cluster is started for the first time, the old cluster can no longer be started safely. This upgrade mode requires a backup plan.

Copy-on-write filesystems offer a third option: cloning (--clone). It combines the advantages of both previous approaches: fast data migration and the ability to start both clusters.

After the user data has been migrated, the utility’s final task is to set the OID counter to the value from the old cluster (this is done via the pg\_resetwal utility).

The administrator should update extensions, collect statistics, and set up routine backups for the upgraded cluster.

## Page 24: The Upgrade {#source-page-024}

Perform the upgrade using hard links.
```sh
postgres$ /usr/lib/postgresql/16/bin/pg_upgrade \
--link \
-b /usr/lib/postgresql/15/bin/ \
-B /usr/lib/postgresql/16/bin/ \
-d /etc/postgresql/15/prod \
-D /etc/postgresql/16/prod
```
```text
Finding the real data directory for the source cluster        ok
Finding the real data directory for the target cluster        ok
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 incompatible "aclitem" data type in user tables  ok
Checking for not-null constraint inconsistencies              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
```
```text
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
```
```sql
Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
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
Adding ".old" suffix to old global/pg_control                 ok
```
```sql
If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/postgresql/15/prod/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking 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                                notice
```
```sql
Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.
```
```sql
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
```

## Page 25: Page 25 {#source-page-025}

```text
    ./delete_old_cluster.sh
```
Before starting the server, you should transfer any configuration changes from the old server to the new one. We will skip this step since we are working with default settings.
Check the results.
```sh
student$ sudo pg_ctlcluster 16 prod start
```
```sh
student$ psql -p 5433
```
```text
16=> SHOW server_version_num;
```
```text
 server_version_num
--------------------
 160014
(1 row)
```
```sql
16=> \c admin_upgrade
```
```text
You are now connected to database "admin_upgrade" as user "student".
```
```sql
16=> SELECT * FROM test;
```
```sql
 id |       s
----+----------------
  1 | Hello from 15!
(1 row)
```
The upgrade has been successful: we have access to the contents of the old cluster.
### Tablespaces
Look at the tablespace files:
```sh
student$ sudo tree /var/lib/postgresql/ts_dir --inodes
```
```text
[ 417099]  /var/lib/postgresql/ts_dir
├── [ 417307]  PG_15_202209061
│   └── [ 443903]  16387
│       ├── [ 443905]  16389
│       ├── [ 443907]  16392
│       └── [ 443909]  16393
└── [ 445624]  PG_16_202307071
    └── [ 786068]  16387
        ├── [ 443905]  16389
        ├── [ 443907]  16392
        └── [ 443909]  16393
```
```text
5 directories, 6 files
```
A subdirectory is created within the tablespace directory for each version, preventing any file conflicts. As can be seen from the number in square brackets (inode), the files in the old and new version directories actually share the same content.
### Post-Upgrade Tasks
When we install a new PostgreSQL version, the shared libraries for installed extensions are replaced, but at the database object level, the extension version remains unchanged:
```text
16=> \dx pgaudit
```
```text
                 List of installed extensions
  Name   | Version | Schema |           Description
---------+---------+--------+---------------------------------
 pgaudit | 1.7.1   | public | provides auditing functionality
(1 row)
```
The utility detected this and generated a script to update extensions in all databases:
```sh
student$ sudo cat /var/lib/postgresql/update_extensions.sql
```
```sql
\connect admin_upgrade
ALTER EXTENSION "pgaudit" UPDATE;
```
Let’s run it.
```sh
student$ sudo psql -U postgres -p 5433 -f /var/lib/postgresql/update_extensions.sql
```

## Page 26: Page 26 {#source-page-026}

```sql
You are now connected to database "admin_upgrade" as user "postgres".
psql:/var/lib/postgresql/update_extensions.sql:2: ERROR:  extension "pgaudit" has no
update path from version "1.7.1" to version "16.1"
```
In our case, the extension developers did not provide an upgrade script, so we have to delete and reinstall the extension.
```sql
16=> DROP EXTENSION pgaudit;
```
```sql
DROP EXTENSION
```
```sql
16=> CREATE EXTENSION pgaudit;
```
```sql
CREATE EXTENSION
```
```text
16=> \dx pgaudit
```
```text
                 List of installed extensions
  Name   | Version | Schema |           Description
---------+---------+--------+---------------------------------
 pgaudit | 16.1    | public | provides auditing functionality
(1 row)
```
The pg\_upgrade utility generated another script to delete the old cluster files:
```sh
student$ sudo cat /var/lib/postgresql/delete_old_cluster.sh
```
```text
#!/bin/sh
```
```text
rm -rf '/var/lib/postgresql/15/prod'
rm -rf '/var/lib/postgresql/ts_dir/PG_15_202209061'
```
And it reminded us to gather statistics. The --analyze-in-stages option collects statistics in three stages, each time increasing accuracy, so the optimizer can start building plans as early as possible.
```sh
student$ /usr/lib/postgresql/16/bin/vacuumdb -p 5433 --all --analyze-in-stages
```
```text
vacuumdb: processing database "admin_upgrade": Generating minimal optimizer statistics (1
target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1
target)
vacuumdb: processing database "student": Generating minimal optimizer statistics (1
target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1
target)
vacuumdb: processing database "admin_upgrade": Generating medium optimizer statistics (10
targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10
targets)
vacuumdb: processing database "student": Generating medium optimizer statistics (10
targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10
targets)
vacuumdb: processing database "admin_upgrade": Generating default (full) optimizer
statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "student": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
```

## Page 27: pg_upgradecluster {#source-page-027}

**Wrapper**
  - pg\_dump + pg\_restore
  - -method=dump
  - pg\_upgradecluster
  - pg\_upgrade
  - -method=upgrade
  - old cluster is switched to a different port
  - configuration files are copied
  - pg\_dump: allows changing the locale
  - pg\_upgrade: file copying or hard links
- Not applicable to tablespaces

### Speaker notes

The pg\_upgradecluster wrapper in Ubuntu simplifies cluster upgrades in straightforward scenarios. By default, it uses the logical backup method, but it also supports upgrades via pg\_upgrade in both file copying and hard link modes.

The new cluster is configured to use the same port, while the old cluster is reconfigured to use a different (unused) port and manual startup. The utility copies the configuration files to the new cluster, so additional tuning of these files may be required afterward.

When upgrading via logical backup, you can change the locale or specific locale categories.

However, if the cluster contains custom tablespaces, the upgrade must be performed manually, as the utility does not support this configuration.

## Page 28: Physical Replication {#source-page-028}

**Standbys must be recreated**
  - upgrade is mandatory (to maintain binary compatibility)
  - pg\_upgrade cannot be used (due to non-deterministic behavior)
- Option 1 (pg\_basebackup)
  - deploy new standbys from a physical backup taken after upgrading the
  - primary server
  - reliable, but time-consuming
- Option 2 (rsync)
  - leverage the fact that data files remain unchanged
  - applicable only on Unix systems and only when using hard links
  - fast, but complex

### Speaker notes

When using physical replication, additional challenges arise. The case is that the primary and all its standbys must be upgraded simultaneously. Connecting a primary of one version to a standby of another version will most likely lead to data loss.

Another important point: a standby cannot be upgraded using pg\_upgrade. Such an upgrade does not provide a 100% guarantee that two identical instances will remain identical after the upgrade.

The standard approach is to discard the existing standbys and create new ones from scratch. To do this, you need to take a base backup of the primary and deploy a new standby from it (this procedure is covered in detail in the DBA3 course). This can be quite a time-consuming process.

However, if you are using a Unix-based OS and the primary was upgraded using hard links, you can significantly speed up the deployment of new standbys. This results in time saving comparable to that achieved by pg\_upgrade for the primary. To achieve this, you need to use the rsync utility correctly.

## Page 29: Physical Replication {#source-page-029}

![Source slide page 29: Physical Replication](images/page-029.png){#source-figure-029 caption="Source slide page 29: Physical Replication"}


### Speaker notes

The primary has been upgraded, but the new server has not been started yet. The standby is shut down after it has fully caught up with the primary (so the data files are identical).

The new server version is installed on the standby, but the cluster is not initialized yet.

## Page 30: Physical Replication {#source-page-030}

![Source slide page 30: Physical Replication](images/page-030.png){#source-figure-030 caption="Source slide page 30: Physical Replication"}


### Speaker notes

Next, rsync is run to copy both clusters from the primary to the standby in a single command.

In this mode, rsync recognizes that:

● The old and new clusters share common data files.

● The data files of the old cluster are identical on both the primary and standby (to avoid dependency on volume, only file sizes are compared not their contents).

● Therefore, there is no need to physically copy the data files to the standby; it is enough to create hard links.

As a result, rsync only needs to copy the files related to the system catalog, which are small in size.

This process is described in the pg\_upgrade documentation (<https://postgrespro.com/docs/postgresql/16/pgupgrade)>, including the exact commands to be executed. Unfortunately, the documentation does not explain why these specific commands are necessary. For a detailed understanding of this matter, check out the developer discussion:

<https://www.postgresql.org/message-id/flat/54C95E66.1000309@agliodbs.c> om

## Page 31: Logical Replication {#source-page-031}

  - creating the publication
  - creating the data schema and subscribing to the publication
  - data synchronization
  - switchover
- + enables upgrades with zero or minimal service interruption – complex setup, does not replicate the data schema and DDL commands, requires additional disk space, etc.

### Speaker notes

It is possible to perform a major version upgrade with zero downtime (or minimal downtime) by using logical replication.

To do this, you need to install and properly configure a new server instance. Publications are created on the old server for all changes across all databases. Subscriptions to these publications are created on the new server with data synchronization. Once the data is synchronized, switch the users to the new server, remove the subscriptions, and stop the old server.

Built‐in logical replication has been introduced in PostgreSQL 10. While this feature is under active development, it is still far from ideal: data schemas, DDL commands, and sequence data are not replicated. These limitations make this approach more complex and less attractive than using pg\_upgrade.

## Page 32: Takeaways {#source-page-032}

**Minor upgrade is simple replacement of executables**
  - physical replication can help avoid service interruption
- Major upgrade
  - logical backup (pg\_dumpall)
  - upgrade utility (pg\_upgrade)
  - logical replication
  - physical replication complicates the procedure
  - immediately create a physical backup after the upgrade

## Page 33: Practice {#source-page-033}

1. In PostgreSQL 15 cluster, create a user with password authentication.
- 2. As the new user, create a table with a column of uom type in the database.
- 3. Upgrade the cluster from version 15 to version 16 using a logical backup. During the upgrade, ensure that all data is placed in a separate tablespace.
- 4. Verify that the upgrade was successful: check server availability, user authentication, and table contents.

### Speaker notes

1. PostgreSQL 15 is already installed with a cluster initialized in the /var/postgresql/15/prod directory, and the server is running on port 5433. Local connections are trusted, and password authentication is configured for TCP/IP connections. The cluster has a single role called postgres with superuser privileges.

To initially connect to the cluster, use the following command:

psql -p 5433 -U postgres

We recommend stopping the main cluster to avoid accidental connecting to it during the practice.

When creating a new user, specify a password. Remember to update the authentication settings in pg\_hba.conf for the new user.

2. The uom data type is available in the extension of the same name (see the Managing Extensions lesson).

3. PostgreSQL 16 is already installed with a cluster initialized in /var/postgresql/16/prod, similar to the prod cluster for version 15.

To place the data in a separate tablespace, first create the tablespace and then edit the backup file to set it as the default tablespace for the database.

To preserve authentication settings, apply the same changes to the pg\_hba.conf file that were made in the old cluster.

## Page 34: 1. User and Authentication in Cluster 15 {#source-page-034}

Stop the main cluster to avoid accidental connections, and start the 15 prod cluster:
```sh
student$ sudo pg_ctlcluster 16 main stop
```
```sh
student$ sudo pg_ctlcluster 15 prod start
```
```sh
student$ pg_lsclusters
```
```text
Ver Cluster Port Status Owner    Data directory              Log file
15  prod    5433 online postgres /var/lib/postgresql/15/prod
/var/log/postgresql/postgresql-15-prod.log
16  main    5432 down   postgres /var/lib/postgresql/16/main
/var/log/postgresql/postgresql-16-main.log
16  prod    5433 down   postgres /var/lib/postgresql/16/prod
/var/log/postgresql/postgresql-16-prod.log
16  slow    5432 down   postgres /var/lib/postgresql/16/slow
/var/log/postgresql/postgresql-16-slow.log
```
```sh
student$ psql -p 5433 -U postgres
```
The dbuser user:
```sql
=> CREATE USER dbuser PASSWORD 'mypassword';
```
```sql
CREATE ROLE
```
Add a rule for the new user at the beginning of pg\_hba.conf:
```sh
student$ sudo sed -i '1s/^/local all dbuser scram-sha-256\n/' /etc/postgresql/15/prod/pg_hba.conf
```
```sql
=> SELECT pg_reload_conf();
```
```text
 pg_reload_conf
----------------
 t
(1 row)
```
Check the result:
```sh
student$ sudo egrep '^[^#]' /etc/postgresql/15/prod/pg_hba.conf
```
```text
local all dbuser scram-sha-256
local   all             all                                     trust
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
```
Add the password to .pgpass to avoid typing it manually (though when working through this practice task, it is better to skip this step, otherwise it is not clear whether password authentication works or if it is just the trusted local access).
```sh
student$ echo '*:*:*:dbuser:mypassword' > ~/.pgpass
```
```sh
student$ chmod 0600 ~/.pgpass
```
### 2. Table in Cluster 15
First, create a database:
```sql
=> CREATE DATABASE admin_upgrade;
```
```sql
CREATE DATABASE
```
```sql
=> \c admin_upgrade
```
```text
You are now connected to database "admin_upgrade" as user "postgres".
```
The uom data type is available in the uom extension. Install version 1.1:
```sh
student$ sudo make install -C /home/student/uom PG_CONFIG=/usr/lib/postgresql/15/bin/pg_config
```
```text
make: Entering directory '/home/student/uom'
/bin/mkdir -p '/usr/share/postgresql/15/extension'
/bin/mkdir -p '/usr/share/postgresql/15/extension'
/usr/bin/install -c -m 644 .//uom.control '/usr/share/postgresql/15/extension/'
/usr/bin/install -c -m 644 .//uom--1.0.sql .//uom--1.2.sql .//uom--1.0--1.1.sql
```

## Page 35: Page 35 {#source-page-035}

![Source slide page 35: Page 35](images/page-035.png){#source-figure-035 caption="Source slide page 35: Page 35"}

```text
.//uom--1.1--1.2.sql  '/usr/share/postgresql/15/extension/'
make: Leaving directory '/home/student/uom'
```
```sql
=> CREATE EXTENSION uom VERSION '1.1';
```
```sql
CREATE EXTENSION
```
Remember that to create a table, the user needs the CREATE privilege on the schema. The superuser must grant this privilege:
```sql
=> GRANT CREATE ON SCHEMA public TO dbuser;
```
```text
GRANT
```
Create a table in the dbuser session:
```sh
student$ psql -p 5433 -h localhost -U dbuser -d admin_upgrade
```
```sql
=> CREATE TABLE test(id serial, length uom);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO test(length) VALUES ((500,'km')::uom);
```
```sql
INSERT 0 1
```
```sql
=> INSERT INTO test(length) VALUES ((8,'mm')::uom);
```
```sql
INSERT 0 1
```
### 3. Logical Backup and Upgrading to Version 16
```sh
student$ /usr/lib/postgresql/15/bin/pg_dumpall -U postgres -p 5433 > ~/dump.sql
```
Add a default tablespace:
```sh
student$ grep 'CREATE DATABASE admin_upgrade' ~/dump.sql | fold -sw 100
```
```sql
CREATE DATABASE admin_upgrade WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER
= libc
LOCALE = 'en_US.UTF-8';
```
```sh
student$ sed -i 's/\(CREATE DATABASE admin_upgrade WITH\)/\1 TABLESPACE = ts/' ~/dump.sql
```
```sh
student$ grep 'CREATE DATABASE admin_upgrade' ~/dump.sql | fold -sw 100
```
```sql
CREATE DATABASE admin_upgrade WITH TABLESPACE = ts TEMPLATE = template0 ENCODING = 'UTF8'
LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
```
Let’s stop the server.
```sh
student$ sudo pg_ctlcluster 15 prod stop
```
We do not transfer the postgresql.conf settings, although in production environments that would be necessary.
Modify pg\_hba.conf the same way we did for version 15:
```sh
student$ sudo sed -i '1s/^/local all dbuser scram-sha-256\n/' /etc/postgresql/16/prod/pg_hba.conf
```
Create the directory for the tablespace.
```sh
student$ sudo rm -rf /var/lib/postgresql/ts_dir
```
```sh
student$ sudo mkdir /var/lib/postgresql/ts_dir
```
```sh
student$ sudo chown postgres: /var/lib/postgresql/ts_dir
```
Start the cluster 16 and create the tablespace as the superuser.
```sh
student$ sudo pg_ctlcluster 16 prod start
```
```sh
student$ psql -p 5433 -U postgres
```
```sql
=> CREATE TABLESPACE ts LOCATION '/var/lib/postgresql/ts_dir';
```
```sql
CREATE TABLESPACE
```
Install the extension:
```sh
student$ sudo make install -C /home/student/uom PG_CONFIG=/usr/lib/postgresql/16/bin/pg_config
```
```text
make: Entering directory '/home/student/uom'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//uom.control '/usr/share/postgresql/16/extension/'
```

## Page 36: Page 36 {#source-page-036}

```text
/usr/bin/install -c -m 644 .//uom--1.0.sql .//uom--1.2.sql .//uom--1.0--1.1.sql
.//uom--1.1--1.2.sql  '/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/uom'
```
Restore the cluster from the backup:
```sh
student$ sudo mv ~/dump.sql /var/lib/postgresql/
```
```text
=> \i /var/lib/postgresql/dump.sql
```
```sql
psql:/var/lib/postgresql/dump.sql:5: error: invalid command \restrict
SET
SET
SET
CREATE ROLE
ALTER ROLE
psql:/var/lib/postgresql/dump.sql:18: ERROR:  role "postgres" already exists
ALTER ROLE
psql:/var/lib/postgresql/dump.sql:32: error: invalid command \unrestrict
You are now connected to database "template1" as user "postgres".
psql:/var/lib/postgresql/dump.sql:48: error: invalid command \restrict
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```text
SET
SET
SET
SET
psql:/var/lib/postgresql/dump.sql:68: error: invalid command \unrestrict
psql:/var/lib/postgresql/dump.sql:78: error: invalid command \restrict
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```sql
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
psql:/var/lib/postgresql/dump.sql:103: error: invalid command \unrestrict
You are now connected to database "admin_upgrade" as user "postgres".
psql:/var/lib/postgresql/dump.sql:105: error: invalid command \restrict
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```sql
SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 2
```

## Page 37: Page 37 {#source-page-037}

```text
 setval
--------
      2
(1 row)
```
```text
GRANT
psql:/var/lib/postgresql/dump.sql:205: error: invalid command \unrestrict
You are now connected to database "postgres" as user "postgres".
psql:/var/lib/postgresql/dump.sql:217: error: invalid command \restrict
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```text
SET
SET
SET
SET
psql:/var/lib/postgresql/dump.sql:237: error: invalid command \unrestrict
```
You will get an error when creating the postgres role since it already exists; this is normal.
### 4. Post‐Upgrade Verification
```sh
student$ psql -p 5433 -h localhost -U dbuser -d admin_upgrade
```
```sql
=> \conninfo
```
```text
You are connected to database "admin_upgrade" as user "dbuser" on host "localhost"
(address "127.0.0.1") at port "5433".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
```
```text
=> SHOW server_version;
```
```text
          server_version
----------------------------------
 16.2 (Ubuntu 16.2-2.pgdg24.04+1)
(1 row)
```
```text
=> \dx uom
```
```text
                   List of installed extensions
 Name | Version | Schema |               Description
------+---------+--------+-----------------------------------------
 uom  | 1.2     | public | Units of Measurement. uom--1.0--1.1.sql
(1 row)
```
```sql
=> SELECT * from test;
```
```text
 id |  length
----+----------
  1 | (500,km)
  2 | (8,mm)
(2 rows)
```
```sql
=> SELECT pg_relation_filepath('test');
```
```text
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16384/PG_16_202307071/16386/16417
(1 row)
```