PostgreSQL Database Migration

Introduction to PostgreSQL Databases

Uyuni 3 uses PostgreSQL 9.4 by default. SUSE Linux Enterprise Server 12 SP3 uses PostgreSQL 9.6 by default.

When you installed Uyuni Server 3.1 or earlier on SUSE Linux Enterprise Server 12 SP2 or earlier, it was based on a PostgreSQL 9.4 database, which you can upgrade to PostgreSQL 9.6. This chapter provides information on managing your PostgreSQL database, and provides instructions for upgrading your database to PostgreSQL 9.6. Uyuni 3.1 on SUSE Linux Enterprise Server 12 SP3 and Uyuni 3.2 use PostgreSQL 9.6 by default. If you use one of those you can skip the following sections.

PostgreSQL stores data at /var/lib/pgsql/data/, and logs to /var/lib/pgsql/data/pg_xlog/.

Prepare to Upgrade PostgreSQL

Before you begin upgrading you will need to check your existing system is ready for the upgrade, and prepare a database backup.

Procedure: Preparing to Upgrade PostgreSQL
  1. Check the active PostgreSQL version:

    suse-manager-example-srv:~ # psql --version
    psql (PostgreSQL) 9.x.y

    If you are using PostgreSQL 9.4, you can update to PostgreSQL 9.6.

  2. Check your system is fully updated:

    suma-test-srv:~ # rpm -q smdba
    smdba-1.5.8-0.2.3.1.x86_64

    PostgreSQL 9.6 requires smdba version 1.5.8 or higher.

  3. Perform a database backup before you begin. For more information on backing up, see administration:backup-restore.adoc.

Always create a database backup before performing a migration

Upgrade PostgreSQL

PostgreSQL upgrades can be performed in two ways: a regular upgrade, or a fast upgrade. A regular upgrade will create a complete copy of the database, so you will need double the existing database size of space available. Regular upgrades can take a considerable amount of time, depending on the size of the database and the speed of the storage system. Regular upgrades also require database downtime, your database will not be accessible while the upgrade is running.

A fast upgrade only takes a few minutes, and uses no additional disk space. However, fast upgrades are more likely to fail, and if this occurs you will need to restore the database from backup.

Procedure: Performing a Regular PostgreSQL Upgrade
  1. Perform a database backup before you begin. For more information on backing up, see administration:backup-restore.adoc.

  2. Begin the upgrade:

    /usr/lib/susemanager/bin/pg-migrate.sh

    The pg-migrate.sh script performs these operations:

    • Stop spacewalk services

    • Shut down the running database

    • Check if PostgreSQL 9.6 is installed and install it if not already present

    • Switch from PostgreSQL 9.4 to PostgreSQL 9.6 as the new default

    • Initiate the database migration

    • Create a PostgreSQL configuration file tuned for use by Uyuni

    • Start the database and spacewalk services

      If the upgrade fails, the migration script will attempt to restore the database to its original state.

  3. After the upgrade has successfully completed, you can safely delete the old database directory which will have been renamed to /var/lib/pgsql/data-pg94 to reclaim lost disk space.

Procedure: Performing a Fast PostgreSQL Upgrade
  1. Perform a database backup before you begin. For more information on backing up, see administration:backup-restore.adoc.

  2. Begin the upgrade:

    /usr/lib/susemanager/bin/pg-migrate.sh fast