Upgrading DMC DB to Newer PostgreSQL Versions

Demo Mode

DMC Version 8.7

When you install DMC version 8.7+ you automatically update to PostgreSQL 15. No other action is necessary to update your version of PostgreSQL.

DMC Versions 7.15 to 8.6

You can upgrade the existing DMC DB from an old PostgreSQL version to the latest PostgreSQL version:

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 the latest version of PostgreSQL.

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.

2. 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 and migrate your data. DMC will continue to function and 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 an old PostgreSQL version to the latest PostgreSQL version in production mode:

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

Upgrade for a Shared PostgreSQL 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 database.

Prerequisites

• Have DMC installed in production mode
• Have PostgreSQL installed on any of your servers that are similar to CentOS

Upgrading a shared old version of PostgreSQL cluster or host to a shared new version of PostgreSQL cluster or host

1. Using the CLI, back up the data of the DMC database from the old PostgreSQL instance:

/usr/pgsql-<old-version>/bin/pg_dump datical --username=dmcuser --password > <old-version>dump.sql

Then enter your password. The command creates a <old-version>dump.sql file in the current directory of the local machine.

2. Stop all services by running the following command:

datical-control service stop all

3. Stop the service for PostgreSQL 12:

systemctl stop postgresql-<old-version>.service

4. Install the new PostgreSQL version in a different directory than the old PostgreSQL version.

5. Start the new PostgreSQL version if it is not running:

systemctl start postgresql-<new-version>

6. Follow all steps mentioned in the DMC production mode documentation because you need a new database server.

7. Restore the old PostgreSQL version backup to the new PostgreSQL version. Perform this step in the same directory you have run the pg_dump command so that the <old-version>dump.sql file can be found.

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

psql -d datical -f <old-version>dump.sql --username=dmcuser --password

8. Start all datical services:

datical-control service start keycloak
datical-control service start datical-service
datical-control service start proxy

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

psql --host=<HOSTNAME> --port=5432 --username=<DMC_USER> --password --dbname=<DB_NAME>

10. Disable or delete the old PostgreSQL service.

Upgrade for a Dedicated PostgreSQL Cluster or Host

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

Prerequisites

• Have DMC installed in production mode
• Have PostgreSQL installed on any of your servers that are similar to CentOS

Upgrading a dedicated old PostgreSQL cluster or host to a dedicated new PostgreSQL cluster or host

1. Stop all services by running the following command:

datical-control service stop all

2. Stop the service for the old PostgreSQL version:

systemctl stop postgresql-<old-version>.service

3. Install the new Postgres version in a different directory than the old PostgreSQL version.

4. Update the pg_hba.conf and postgresql.conf files as defined by steps 6–9 in the DMC production mode documentation.
5. Start the new PostgreSQL version if it is not running:

systemctl start postgresql-<new-version>

6. Drop any databases that may exist in the new PostgreSQL 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 Cluster or Host instructions for pg_dump.

7. Drop any users that may exist in the new PostgreSQL instance, except for the postgres user.

8. Stop the new PostgreSQL:

systemctl stop postgresql-<new-version>

9. Switch to the PostgreSQL user:

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.

10. In the /tmp directory or any other directory with the PostgreSQL files to which you have write access, run the following command:

${pg<new-version>bin}/pg_upgrade --old-bindir ${pg<old-version>bin} 
--new-bindir ${pg<new-version>bin} --old-datadir ${pg<old-version>data.toString()} 
--new-datadir ${pg<new-version>data.toString()} --username postgres 
--old-options '-c config_file=$pg<old-version>data/postgresql.conf' 
--new-options '-c config_file=$pg<new-version>data/postgresql.conf

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

11. Start the new PostgreSQL service:

systemctl start postgresql-<new-version>.service

12. Start all datical services:

datical-control service start keycloak
datical-control service start datical-service
datical-control service start proxy

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

psql --host=<HOSTNAME> --port=5432 --username=<DMC_USER> --password --dbname=<DB_NAME>

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

14. Disable or delete the old PostgreSQL service.

Upgrade for PostgreSQL on AWS RDS

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