3.10. Migrating a PostgreSQL instance from a previous RHEL version to PostgreSQL on RHEL 10

Procedure

1. On the host with the existing PostgreSQL instance that you want to migrate:

   1. Export all databases to the /var/lib/pgsql/pgdump_file.sql file:

      # su - postgres -c "pg_dumpall > /var/lib/pgsql/pgdump_file.sql"

   2. Check the exported file:

      # su - postgres -c 'less "/var/lib/pgsql/pgdump_file.sql"'

   3. Copy the database dump that you created in an earlier step and the PostgreSQL configuration files to the RHEL 10 host, for example:

      # scp /var/lib/pgsql/pgdump_file.sql \
             /var/lib/pgsql/data/pg_hba.conf \
             /var/lib/pgsql/data/pg_ident.conf \
             /var/lib/pgsql/data/postgresql.conf \
             <user>@<rhel_10_host>:/tmp/

2. On the RHEL 10 host:

   1. Install the postgresql-server package:

      # dnf install postgresql-server

   2. Initialize the /var/lib/pgsql/data/ directory:

      # postgresql-setup --initdb

   3. Move the copied configuration files to the /var/lib/pgsql/data/ directory:

      # mv /tmp/pg_hba.conf \
           /tmp/pg_ident.conf \
           /tmp/postgresql.conf \
           /var/lib/pgsql/data/

   4. Ensure a correct ownership of the content in the /var/lib/pgsql/data/ directory:

      # chown -R postgres:postgres /var/lib/pgsql/data/

   5. Restore the SELinux context on /var/lib/pgsql/data/:

      # restorecon -Rv /var/lib/pgsql/data/

   6. Enable and start the postgresql service:

      # systemctl enable --now postgresql.service

   7. Import the data as the postgres user:

      # su - postgres -c 'psql -f /tmp/pgdump_file.sql postgres'

   8. Verify your databases and ensure that your applications that use the PostgreSQL server work as expected.

Procedure

1. On the host with the existing PostgreSQL instance that you want to migrate:

   1. Stop the postgresql service:

      # systemctl stop postgresql.service

   2. Change into the /var/lib/pgsql/ directory, and back up the data subdirectory:

      # cd /var/lib/pgsql/
      # tar -zcf ~/pgdata.bak.tar.gz data/

   3. Copy the ~/pgdata.bak.tar.gz archive to the RHEL 10 host, for example:

      # scp ~/pgdata.bak.tar.gz <user>@<rhel_10_host>:/tmp/

2. On the RHEL 10 host:

   1. Install the required packages:

      # dnf install postgresql-server postgresql-upgrade

      The postgresql-upgrade package provides a PostgreSQL 13 server which is required during the migration.

   2. If you use third party PostgreSQL server modules, build them against both the postgresql-devel and postgresql-upgrade-devel packages, and install them.

   3. Ensure that the postgresql service is stopped:

      # systemctl stop postgresql.service

   4. Change into the /var/lib/pgsql/ directory, and extract the backed up data directory from the previous host:

      # cd /var/lib/pgsql/
      # tar -zxf /tmp/pgdata.bak.tar.gz

   5. Optional: Remove the /tmp/pgdata.bak.tar.gz archive:

      # rm /tmp/pgdata.bak.tar.gz

   6. Perform the upgrade process:

      # postgresql-setup --upgrade

      The postgresql-setup shell script renames the /var/lib/pgsql/data/ directory to /var/lib/pgsql/data-old/ and uses the pg_upgrade utility to migrate the databases to a re-created /var/lib/pgsql/data/ directory.

      중요

      The pg_upgrade utility migrates only the databases and not the configuration files. After the migration, /var/lib/pgsql/data/ contains only the default .conf files. If you, previously, had custom configuration files, copy them from the /var/lib/pgsql/data-old/ directory and ensure that they are compatible with the new PostgreSQL version.

   7. Enable and start the postgresql service:

      # systemctl enable --now postgresql.service

   8. Clean up and analyze all databases:

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

   9. Verify your databases and ensure that your applications that use the PostgreSQL server work as expected.

   10. Optional: Remove the /var/lib/pgsql/data-old/ directory which contains the databases and configuration file from before the migration.

       # rm -r /var/lib/pgsql/data-old/

   11. Optional: Remove the postgresql-upgrade package:

       # dnf remove postgresql-upgrade