Separate Databases for Reporting and Repository in Deploy

This article explains, how to use separate database schemas and separate databases for reporting and repository.

Deploy supports two different databases/schemas. It can be either the same database with different schemas or two different databases for reporting and repositories.

Important: You must ensure that you setup the databases and schemas correctly before using them with Deploy. You should configure the deploy-repository.yaml file according to the databases/schemas and users you have created. Tables, on the other hand, will be created by Deploy automatically. For more information, see Deploy Properties.

Setting up separate database schemas

To use Deploy with separate database schemas for reporting and repository, you must begin by creating different schemas in the database of your choice.

After setting up the different schemas, you should configure the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml to point to the database schema.

As you are setting up Deploy with a database of your choice, you should add a JDBC driver inside the XL_DEPLOY_SERVER_HOME/lib directory.

Here is a sample configuration for PostgreSQL:

xl.reporting:
  database:
    db-driver-classname: "org.postgresql.Driver"
    db-password: "reporting-user-password"
    db-url: "jdbc:postgresql://localhost:5432/reportingdb"
    db-username: "reporting-user"
    connection-timeout: "${xl.repository.database.connection-timeout}"    

xl.repository:
  database:
    db-driver-classname: "org.postgresql.Driver"
    db-password: "repository-user-password"
    db-url: "jdbc:postgresql://localhost:5432/repositorydb"
    db-username: "repository-user"
    connection-timeout: 30000

Note:

• The default port for PostgreSQL is 5432. If you are using a different port, replace 5432 with the appropriate port number.
• In production environment, localhost should be replaced with the actual hostname or IP address of the database server running on a different machine.

Setting up two separate databases

To use Deploy with two separate databases for reporting and repository, you must begin by create a schema on both of the databases.

After setting up the schemas in each database, you should configure the XL_DEPLOY_SERVER_HOME/centralConfiguration/deploy-repository.yaml to point to the database schema.

Important: Ensure that you add the JDBC drivers of each of the databases in the XL_DEPLOY_SERVER_HOME/lib directory.

Here is a sample configuration for MySQL for repository and PostgreSQL for reporting.

xl.reporting:
  database:
    db-driver-classname: "org.postgresql.Driver"
    db-password: "reporting-user-password"
    db-url: "jdbc:postgresql://localhost:5432/reportingdb"
    db-username: "reporting-user"
    connection-timeout: "${xl.repository.database.connection-timeout}" 
    

xl.repository:
  database:
    db-driver-classname: "com.mysql.jdbc.Driver"
    db-password: "repository-user-password"
    db-url: "jdbc:mysql://localhost:3316/xld?autoReconnect=true&useLegacyDatetimeCode=false" 
    db-username: "repository-user"
    connection-timeout: 30000

• The default port for PostgreSQL is 5432. If you are using a different port, replace 5432 with the appropriate port number.
• The default port for MySQL is 3306. If you are using a different port, replace 3306 with the appropriate port number.
• In production environment, localhost should be replaced with the actual hostname or IP address of the database server running on a different machine.

Migrating the database

If you are willing to change the database that you use, these are the tables you should migrate:

XLD_ARCHIVED_CONTROL_TASKS
XLD_ARCHIVED_DEPLOYMENT_TASKS
XLD_ARCHIVED_DT_APPLICATIONS
XLD_ARCHIVED_TASKS
XLD_ARCHIVED_UNKNOWN_TASKS
XLD_ARCHIVE_ROLES
XLD_ARCHIVE_ROLE_PERMISSIONS
XLD_ARCHIVE_ROLE_PRINCIPALS
XLD_ARCHIVE_ROLE_ROLES

Important: If you want to update your xl-deploy-server, the updater scripts will not migrate your databases. That should be done manually by the user.