4.7. Migrating to a RHEL 9 version of PostgreSQL

PDF

Red Hat Enterprise Linux 8 provides PostgreSQL in multiple module streams: PostgreSQL 10 (the default postgresql stream), PostgreSQL 9.6, PostgreSQL 12, PostgreSQL 13, and PostgreSQL 15. In RHEL 9, PostgreSQL 13 and PostgreSQL 15 are available.

On RHEL, you can use two PostgreSQL migration paths for the database files:

The fast upgrade method is quicker than the dump and restore process. However, in certain cases, the fast upgrade does not work, and you can only use the dump and restore process, for example in case of cross-architecture upgrades.

As a prerequisite for migration to a later version of PostgreSQL, back up all your PostgreSQL databases.

Dumping the databases and performing backup of the SQL files is required for the dump and restore process and recommended for the fast upgrade method.

Before migrating to a later version of PostgreSQL, see the upstream compatibility notes for the version of PostgreSQL to which you want to migrate, as well as for all skipped PostgreSQL versions between the one you are migrating from and the target version.

4.7.1. Notable differences between PostgreSQL 13 and PostgreSQL 15

PostgreSQL 15 introduced the following backwards incompatible changes.

Default permissions of the public schema

The default permissions of the public schema have been modified in PostgreSQL 15. Newly created users need to grant permission explicitly by using the GRANT ALL ON SCHEMA public TO myuser; command.

The following example works in PostgreSQL 13 and earlier:

postgres=# CREATE USER mydbuser;
postgres=# \c postgres mydbuser
postgres=$ CREATE TABLE mytable (id int);

The following example works in PostgreSQL 15:

postgres=# CREATE USER mydbuser;
postgres=# GRANT ALL ON SCHEMA public TO mydbuser;
postgres=# \c postgres mydbuser
postgres=$ CREATE TABLE mytable (id int);

Note

Ensure that the mydbuser acces is configured appropriately in the pg_hba.conf file. See Creating PostgreSQL users for more information.

PQsendQuery() no longer supported in pipeline mode

Since PostgreSQL 15, the libpq PQsendQuery() function is no longer supported in pipeline mode. Modify affected applications to use the PQsendQueryParams() function instead.

4.7.2. Fast upgrade using the pg_upgrade utility

As a system administrator, you can upgrade to the most recent version of PostgreSQL by using the fast upgrade method. To perform a fast upgrade, copy binary data files to the /var/lib/pgsql/data/ directory and use the pg_upgrade utility.

You can use this method for migrating data:

From the RHEL 8 version of PostgreSQL 12 or later to the equal or later version of PostgreSQL in RHEL 9
From the RHEL 9 version of PostgreSQL 13 to the RHEL 9 version of PostgreSQL 15

The following procedure describes migration from the RHEL 8 version of PostgreSQL 12 to the RHEL 9 version of PostgreSQL 13 using the fast upgrade method. For migration from postgresql streams other than 12, use one of the following approaches:

Update your PostgreSQL server to version 12 on RHEL 8 and then use the pg_upgrade utility to perform the fast upgrade to RHEL 9 version of PostgreSQL 13.
Use the dump and restore upgrade directly between any RHEL 8 version of PostgreSQL and an equal or later PostgreSQL version in RHEL 9.

Conditions préalables

Before performing the upgrade, back up all your data stored in the PostgreSQL databases. By default, all data is stored in the /var/lib/pgsql/data/ directory on both the RHEL 8 and RHEL 9 systems.

Procédure

On the RHEL 9 system, install the postgresql-server and postgresql-upgrade packages:
```
# dnf install postgresql-server postgresql-upgrade
```
Optionally, if you used any PostgreSQL server modules on RHEL 8, install them also on the RHEL 9 system in two versions, compiled both against PostgreSQL 12 (installed as the postgresql-upgrade package) and the target version of PostgreSQL 13 (installed as the postgresql-server package). If you need to compile a third-party PostgreSQL server module, build it both against the postgresql-devel and postgresql-upgrade-devel packages.
Check the following items:
- Basic configuration: On the RHEL 9 system, check whether your server uses the default /var/lib/pgsql/data directory and the database is correctly initialized and enabled. In addition, the data files must be stored in the same path as mentioned in the /usr/lib/systemd/system/postgresql.service file.
- PostgreSQL servers: Your system can run multiple PostgreSQL servers. Ensure that the data directories for all these servers are handled independently.
- PostgreSQL server modules: Ensure that the PostgreSQL server modules that you used on RHEL 8 are installed on your RHEL 9 system as well. Note that plug-ins are installed in the /usr/lib64/pgsql/ directory.
Ensure that the postgresql service is not running on either of the source and target systems at the time of copying data.
```
# systemctl stop postgresql.service
```
Copy the database files from the source location to the /var/lib/pgsql/data/ directory on the RHEL 9 system.
Perform the upgrade process by running the following command as the PostgreSQL user:
```
# postgresql-setup --upgrade
```
This launches the pg_upgrade process in the background.
In case of failure, postgresql-setup provides an informative error message.
Copy the prior configuration from /var/lib/pgsql/data-old to the new cluster.
Note that the fast upgrade does not reuse the prior configuration in the newer data stack and the configuration is generated from scratch. If you want to combine the old and new configurations manually, use the *.conf files in the data directories.
Start the new PostgreSQL server:
```
# systemctl start postgresql.service
```

Analyze the new database cluster.

For PostgreSQL 13:

su postgres -c '~/analyze_new_cluster.sh'

For PostgreSQL 15:

su postgres -c 'vacuumdb --all --analyze-in-stages'

If you want the new PostgreSQL server to be automatically started on boot, run:
```
# systemctl enable postgresql.service
```

4.7.3. Dump and restore upgrade

When using the dump and restore upgrade, you must dump all databases contents into an SQL file dump file. Note that the dump and restore upgrade is slower than the fast upgrade method and it may require some manual fixing in the generated SQL file.

You can use this method for migrating data from any RHEL 8 version of PostgreSQL to any equal or later version of PostgreSQL in RHEL 9.

On RHEL 8 and RHEL 9 systems, PostgreSQL data is stored in the /var/lib/pgsql/data/ directory by default.

To perform the dump and restore upgrade, change the user to root.

The following procedure describes migration from the RHEL 8 default version of Postgreql 10 to the RHEL 9 version of PostgreSQL 13.

Procédure

On your RHEL 8 system, start the PostgreSQL 10 server:
```
# systemctl start postgresql.service
```
On the RHEL 8 system, dump all databases contents into the pgdump_file.sql file:
```
su - postgres -c "pg_dumpall > ~/pgdump_file.sql"
```
Ensure that the databases were dumped correctly:
```
su - postgres -c 'less "$HOME/pgdump_file.sql"'
```
As a result, the path to the dumped sql file is displayed: /var/lib/pgsql/pgdump_file.sql.
On the RHEL 9 system, install the postgresql-server package:
```
# dnf install postgresql-server
```
Optionally, if you used any PostgreSQL server modules on RHEL 8, install them also on the RHEL 9 system. If you need to compile a third-party PostgreSQL server module, build it against the postgresql-devel package.
On the RHEL 9 system, initialize the data directory for the new PostgreSQL server:
```
# postgresql-setup --initdb
```
On the RHEL 9 system, copy the pgdump_file.sql into the PostgreSQL home directory, and check that the file was copied correctly:
```
su - postgres -c 'test -e "$HOME/pgdump_file.sql" && echo exists'
```
Copy the configuration files from the RHEL 8 system:
```
su - postgres -c 'ls -1 $PGDATA/.conf'*
```
The configuration files to be copied are:
- /var/lib/pgsql/data/pg_hba.conf
- /var/lib/pgsql/data/pg_ident.conf
- /var/lib/pgsql/data/postgresql.conf
On the RHEL 9 system, start the new PostgreSQL server:
```
# systemctl start postgresql.service
```
On the RHEL 9 system, import data from the dumped sql file:
```
su - postgres -c 'psql -f ~/pgdump_file.sql postgres'
```

4.7. Migrating to a RHEL 9 version of PostgreSQL

4.7.1. Notable differences between PostgreSQL 13 and PostgreSQL 15

4.7.2. Fast upgrade using the pg_upgrade utility

4.7.3. Dump and restore upgrade

Apprendre

Essayez, achetez et vendez

Communautés

À propos de la documentation Red Hat

Rendre l’open source plus inclusif

À propos de Red Hat

Red Hat legal and privacy links

Red Hat legal and privacy links