Installing a Database

Owned by Stonebranch (Deactivated)
Last updated: Nov 25, 2024 by Olivier Michaut

Overview

Universal Data Mover Gateway can use a database space of an existing database or you can install a database specifically for the Controller.

We recommend an initial size of 100MB.

Database Permissions

In order to install Universal Data Mover Gateway, the database user configured for the UDMG Server requires DDL (Data Definition Language) permission in the database during the install and during the migration steps for release upgrade.

Once the install has been completed successfully, the configured database user requires only DML (Data Manipulation Language) permissions for running the UDMG Server.

Database Management Systems

The following database management systems are supported:

MySQL

8.0.x

PostgreSQL

12, 13, 14, 15

Oracle19c, 21c
Microsoft SQL Server2019, 2022

Note

Databases that are compatible with and use the same drivers as the supported databases are also supported, for example, AWS RDS (MySQL), MariaDB (MySQL), AWS RDS (Oracle).  We recommend that customers periodically review the database documentation for incompatibilities and feature differences as these may change over time.

Below are the generic steps for the preparation of a database instance for UDMG and how this can be done for the different database types. 

  • database server
  • database instance
  • database user

Depending on the exact flavor, database type, environment topology, and other performance or security requirements, more advanced configuration parameters or procedures may be needed that are not in scope for this guide. 

Once the database user is ready, the database parameters can be setup in UDMG Server configuration. The database content is initialized on the first execution and further maintained during release upgrades (see the "Upgrading UDMG Server" in the Linux and Windows installation guides)

MySQL

Note

MySQL versions 8.0.x are supported.

Install MySQL server for Linux

Step 1

Download MySQL installation instructions.

Step 2

Download the installation package from the official MySQL site.

  • For Linux, you can use a tar.gz download or select a systems package installer appropriate for your environment, such as Yum.

Step 3

Install MySQL as per the instructions.

  • For Linux, enable and start the MySQL Server:

# sudo systemctl enable mysql

# sudo systemctl start mysql

Install MySQL server for Windows

Step 1

Download MySQL installation instructions.

Step 2

Download the installation package from the official MySQL site.

  • For Windows, select Windows (x86, 32-bit), MSI Installer

Step 3

Install MySQL as per the instructions.

Common configuration

Step 1

Create a blank database on the server. An already existing database can be used, but this is not recommended.

mysql> CREATE DATABASE udmg;

Note

Depending on the database flavor and version, the default encoding may not be fit for the language and the characters that will be used in the file names.
For example, MariaDB has a default encoding of Latin1. It is recommended to use UTF-8 encoding. 

MariaDB [(none)]> CREATE DATABASE udmg DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Step 2

Add the user that will be the owner of the UDMG database objects and the operation user which which the UDMG Server connects to the database.

The username name and password will be written in the UDMG Server configuration file.

To install or perform upgrades, this database user requires DDL (Data Definition Language) permission in the database during the installation or upgrade.
Once the installation or upgrade has been completed successfully, the configured database user requires only DML (Data Manipulation Language) permissions.

mysql> CREATE USER 'udmg_user'@ IDENTIFIED BY 'udmg_password';
mysql> GRANT ALL PRIVILEGES ON udmg.* TO 'udmg_user'@ WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
Step 3

UDMG Server requires the ability to create functions and triggers during the initial installation and release upgrades

MySQL has several limitations regarding the creation of stored procedures and triggers which usually require the owner to have the SUPER privilege.

This can be relaxed by enabling the system variable log_bin_trust_function_creators. 

Either permanently by adding the following to the mysql.ini configuration file and restarting the database service:

[mysqld]
# Fix 'ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled'
log_bin_trust_function_creators = 1

Or by setting the global variable only for the duration of the UDMG Server installation or upgrade:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

Note

Depending on the MySQL version and the database configuration (binlog_format), a deprecated warning message may appear.

More specifically, bin_log_format and the related variables are deprecated as of MySQL 8.0.34: 
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-34.html#mysqld-8-0-34-deprecation-removal

PostgreSQL

Note

PostgreSQL versions 12, 13, 14, and 15 are supported.

PostgreSQL for Linux

  1. Create a blank database on the server. An already existing database can be used, but this is not recommended.

  2. Add a user that will be used as the owner of the related tables but also to authenticate with the server.
    In order to install or perform upgrades, this database user requires DDL (Data Definition Language) permission in the database during the installation or upgrade.
    Once the installation or upgrade has been completed successfully, the configured database user requires only DML (Data Manipulation Language) permissions.

Here is how to configure the database for a local installation where the database server is on the same host. For multi-node installation, please refer to your database administrator.

Note

The following steps require administrative privilege, be sure that you have the correct access before continuing.


  • Install a PostgreSQL database server.

  • Once the installation is complete, initialize the PostgreSQL database.

  • Enable and start the PostgreSQL Server:

# sudo systemctl enable postgresql

# sudo systemctl start postgresql

  • Create a user for UDMG Server.

Login as a PostgreSQL administrative user, start the PostgreSQL Console (psql), and create the database user for UDMG:

# sudo su - postgres
$ psql
psql (14.3)
Type "help" for help.

postgres=#

CREATE DATABASE udmg;
CREATE USER udmg_user WITH ENCRYPTED PASSWORD 'udmg_password';
GRANT ALL PRIVILEGES ON DATABASE udmg TO udmg_user;

Note

For PostgreSQL 15, the default user permissions have changed, see https://www.postgresql.org/about/news/postgresql-15-released-2526 and the following is also required:

ALTER DATABASE udmg OWNER TO udmg_user;
  • Finally, change the pg_hba.conf, to allow database connection with a password. For example, for a system where the database server is on the same host as the UDMG Server, by changing this line from:

host all all 127.0.0.1/32 ident

to:

host all all 127.0.0.1/32 md5

The exact configuration depends on the OS and database version, on the preferred security settings, and on the system architecture.

The location of the pg_hba.conf can be returned by psql:
$ sudo su - postgres
$ psql
postgres=# SHOW hba_file;

PostgreSQL for Windows

  1. Create a blank database on the server. An already existing database can be used, but this is not recommended.

  2. Add a user that will be used as the owner of the related tables but also to authenticate with the server.
    To install or perform upgrades, this database user requires DDL (Data Definition Language) permission in the database during the installation or upgrade.
    Once the installation or upgrade has been completed successfully, the configured database user requires only DML (Data Manipulation Language) permissions.

Here is how to configure the database for a local installation where the database server is on the same host. For multi-node installation please refer to your database administrator.

The following steps require Administrator privilege, be sure that you have the correct access before continuing.

  • Install a PostgreSQL database server.

  • Once the installation is complete, initialize the PostgreSQL database.

  • Start the PostgreSQL Server:

For example, from the Service Management Console management:

  • Create a user for UDMG Server.

Login as a PostgreSQL user, start the PostgreSQL Console (psql), and create the database user:

postgres=# create database udmg;
CREATE DATABASE
postgres=# create user udmg_user with encrypted password 'udmg_password';
CREATE ROLE
postgres=# grant all privileges on database udmg to udmg_user;
GRANT

Finally, change the pg_hba.conf, to allow database connection with a password.

For example, for a system where the database server is on the same host as the UDMG Server, by changing this line from:

host all all 127.0.0.1/32 ident

to:

host all all 127.0.0.1/32 scram-sha-256

For a system where the database server is on the same subnet as the UDMG server, change it to:

host all all samenet scram-sha-256

The exact configuration depends on the OS and database version, preferred security settings, and system architecture.

The location of the pg_hba.conf can be returned by PostgreSQL Console (psql):

postgres=# SHOW hba_file;

The current password encryption method can be returned by PostgreSQL Console (psql):

postgres=# SHOW password_encryption;

Oracle

Note

Oracle versions 19c and 21c are supported.


Universal Data Mover Gateway requires the Oracle client libraries for connectivity to the Oracle database.


Step 1

Download and install Oracle Database Server software as per the Oracle documentation.

Step 2

Create the UDMG database. You can use any legal name, but we recommend the name udmg.

Step 3

Create the user that will be used as the owner of the tables but also to authenticate with the server.

CREATE USER udmg IDENTIFIED BY "udmg_password" QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO udmg;

Make a note of the user and password to be used later when installing the UDMG Server.

Step 4

Download and install Oracle Client on the node where UDMG Server is to be installed.

Detailed installation procedure can be found on the Oracle documentation:

As an example, the installation on a RHEL Linux system would be:

wget https://download.oracle.com/otn_software/linux/instantclient/2110000/instantclient-basiclite-linux.x64-21.10.0.0.0dbru.zip

sudo mkdir /opt/oracle

sudo unzip -d /opt/oracle ./instantclient-basiclite-linux.x64-21.10.0.0.0dbru.zip

sudo yum install libaio

sudo sh -c "echo /opt/oracle/instantclient_21_10 > /etc/ld.so.conf.d/oracle-instantclient.conf"

Microsoft SQL Server

Note

Microsoft SQL Server versions 2019, and 2022 are supported.


Step 1

Download and install MS SQL Server as per the Microsoft documentation.

Step 2

Create the UDMG database and the application user.

You can use any legal name, but we recommend the names udmg and udmg_user.

Here is an example of a simple database creation script.

USE [master]
GO
CREATE DATABASE udmg;
GO
CREATE LOGIN [udmg_user] WITH PASSWORD='pa55W0rd', DEFAULT_DATABASE=udmg;
GO
ALTER AUTHORIZATION ON DATABASE::[udmg] TO [udmg_user];
GO

Step 3

Make a note of the userid and password to be used later when installing the UDMG Server.