Upgrading DMC DB on PostgreSQL 10 to PostgreSQL 13

Tip: To use PostgreSQL 12 or 13, upgrade to DMC 7.15 or later versions. Before upgrading DMC after the Java upgrade, put back the previous version of Java (11.0.8) to the /opt/datical/old_jre directory for the DMC upgrade process to be successful. After your DMC upgrade is complete, change the Java version to the one you want to use.

PostgreSQL 13 introduces security and performance enhancements that provide a safer, more stable, and more secure DMC experience than previous versions. Thus, the embedded database has been updated to PostgreSQL 13. It is also recommended that you upgrade existing DMC DB instances from PostgreSQL 10 to PostgreSQL 13.

Demo Mode

You can upgrade the existing DMC DB from PostgreSQL 10 to PostgreSQL 13 in demo mode:

During the installation of a new DMC version

  1. Run the installer to upgrade DMC by following the Demo mode installation methods documentation. You will be prompted to upgrade your DMC DB to PostgreSQL 13.

    2. Note: Do not delete your existing DMC installation. Run a new installation script and point to the existing working installation. However, if you have DMC running and want to do the second DMC installation into a new directory, you must stop the existing DMC processes first by using the sudo datical-control service stop all command.

  1. Enter Yes to allow the automated process to install a new database, update your configuration, and migrate your data.

After the installation of a new DMC version

If you have upgraded DMC and declined an automated PostgreSQL upgrade, you can still initiate it.

Run the sudo datical-control db migrate command to start the upgrade of PostgreSQL from version 10 to 13 and migrate your data. DMC will continue to function and Datical DB (Liquibase Enterprise) will report the data to DMC DB without any reconfiguration.

Production Mode

Choose one of the following ways to upgrade the existing DMC DB from PostgreSQL 10 to PostgreSQL 13 in production mode:

  • Upgrade for a shared PostgreSQL 13 cluster or host to migrate DMC DB to an external Postgres 13 cluster or host that already hosts other databases
  • Upgrade for a dedicated PostgreSQL 13 cluster or host to migrate DMC DB to an external Postgres 13 cluster or host that does not host any other databases
  • Upgrade for PostgreSQL 13 on AWS RDS

Upgrade for a Shared PostgreSQL 13 Cluster or Host

Use the pg_dump and psql utilities to migrate your database and the datical-control commands to update your configuration.

Note: The pg_dump utility backs up the PostgreSQL 10 database.

Prerequisites

Upgrading a shared PostgreSQL 10 cluster or host to a shared PostgreSQL 13 cluster or host

  1. Using the CLI, back up the data of the DMC database from the PostgreSQL 10 instance: 
    2. /usr/pgsql-10/bin/pg_dump datical --username=dmcuser --password > 10dump.sql

    Then enter your password. The command creates a 10dump.sql file in the current directory of the local machine.

  1. Stop all services by running the following command:
    2. datical-control service stop all
  1. Stop the service for PostgreSQL 10:
    2. systemctl stop postgresql-10.service
  1. Install PostgreSQL 13 in a different directory than PostgreSQL 10.
  1. Start PostgreSQL 13 if it is not running: 
    2. systemctl start postgresql-13
  1. Follow all steps mentioned in the DMC production mode documentation because you need a new database server.
  1. Restore the PostgreSQL 10 backup to PostgreSQL 13. Perform this step in the same directory you have run the pg_dump command so that the 10dump.sql file can be found.

    2. Note: If the destination database server is on another host, specify the --host parameter with the psql command.

    psql -d datical -f 10dump.sql --username=dmcuser --password
  1. Start all datical services: 
    2. datical-control service start keycloak
datical-control service start datical-service
datical-control service start proxy

  1. Confirm that the data has been copied to the PostgreSQL13 instance and DMC has a connection to PostgreSQL:

    2. psql --host=<HOSTNAME> --port=5432 --username=<DMC_USER> --password --dbname=<DB_NAME>
  1. Disable or delete the PostgreSQL 10 service.

Upgrade for a Dedicated PostgreSQL 13 Cluster or Host

If the destination cluster contains no existing databases, use the pg_upgrade and datical-control commands to update your configuration.

Prerequisites

Upgrading a dedicated PostgreSQL 10 cluster or host to a dedicated PostgreSQL 13 cluster or host

  1. Stop all services by running the following command: 
    2. datical-control service stop all
  1. Stop the service for PostgreSQL 10: 
    2. systemctl stop postgresql-10.service
  1. Install Postgres 13 in a different directory than PostgreSQL 10.
  1. Update the pg_hba.conf and postgresql.conf files as defined by steps 6–9 in the DMC production mode documentation.
  2. Start PostgreSQL 13 if it is not running: 
    3. systemctl start postgresql-13
  1. Drop any databases that may exist in the PostgreSQL 13 instance because the pg_upgrade command will fail if the destination database has any databases. If you have other databases in the destination cluster, back up and restore them after completing the migration. Alternatively, you can use the Upgrade for a Shared PostgreSQL 13 Cluster or Host instructions for pg_dump.
  1. Drop any users that may exist in the PostgreSQL 13 instance, except for the postgres user.
  1. Stop PostgreSQL 13: 
    2. systemctl stop postgresql-13
  1. Switch to the PostgreSQL user: 
    2. sudo su - postgres

The following steps require the pg_upgrade executable. You need to run it from the newer installation of PostgreSQL. Also, it is not recommended to use the --link option when calling pg_upgrade.

  1. In the /tmp directory or any other directory with the PostgreSQL files to which you have write access, run the following command: 
    2. ${pg13bin}/pg_upgrade --old-bindir ${pg10bin} 
--new-bindir ${pg13bin} --old-datadir ${pg10data.toString()} 
--new-datadir ${pg13data.toString()} --username postgres 
--old-options '-c config_file=$pg10data/postgresql.conf' 
--new-options '-c config_file=$pg13data/postgresql.conf

    Example: /usr/pgsql-13/bin/pg_upgrade --old-bindir /usr/pgsql-10/bin/ --new-bindir /usr/pgsql-13/bin/
    --old-datadir /var/lib/pgsql/10/data --new-datadir /var/lib/pgsql/13/data
    --username postgres --old-options '-c config_file=/var/lib/pgsql/10/data/postgresql.conf'
    --new-options '-c config_file=/var/lib/pgsql/13/data/postgresql.conf'

  1. Start the PostgreSQL 13 service: 
    2. systemctl start postgresql-13.service
  1. Start all datical services: 
    2. datical-control service start keycloak
datical-control service start datical-service
datical-control service start proxy
  1. Confirm that the data has been copied to the PostgreSQL 13 instance and DMC has a connection to PostgreSQL: 
    2. psql --host=<HOSTNAME> --port=5432 --username=<DMC_USER> --password --dbname=<DB_NAME>

    Note: If the migration fails, you can start the PostgreSQL 10 server by running systemctl start postgresql-10.service and will have all the data for PostgreSQL 10.

  1. Disable or delete the PostgreSQL 10 service.

Upgrade for PostgreSQL on AWS RDS

If you use DMC with PostgreSQL 10 on Amazon RDS for an existing DMC DB and want to upgrade it to version 13, follow the AWS documentation – Upgrading the PostgreSQL DB engine for Amazon RDS.