Introduction

This story covers the work to allow the RDBMS system to be used by the Policy Framework to be configured at installation time to be whatever SQL based RDBMS is required.

Specifically, the MariaDB and Postgres databases are to be supported.

To complete this task changes will be made to all policy components that connect to the DB. References to MariaDB will be removed and property files will be created to allow users to choose their configuration. Changes to the db-migrator are also needed as this component is responsible for creating/updating tables in the policyadmin schema.

Solution:

Make changes to policy-api, policy-pap and all pdps to allow configurable connection of MariaDB or Postgres. This will require removing references to MariaDB in the configuration (persistence.xml) of the components. Renaming the persistence units from “PolicyMariaDb” to “PolicyDb”. Additional configuration files will be added to connect to the postgres database.

Changes in all components: (Example w/ policy-api)

{

	"name":"ApiGroup",

		"restServerParameters":{

		"host":"0.0.0.0",

		"port":6968,

		"userName":"healthcheck",

		"password":"zb!XztG34",

		"https":false

	},

	"databaseProviderParameters": {

		"name": "PolicyProviderParameterGroup",

		"implementation": "org.onap.policy.models.provider.impl.DatabasePolicyModelsProviderImpl",

		"databaseDriver": "org.postgresql.Driver",

		"databaseUrl": "jdbc:postgresql://localhost:5432/policyadmin",

		"databaseUser": "policy_user",

		"databasePassword": "policy_user",

		"persistenceUnit": "PolicyDb"

	},

	"preloadPolicyTypes": [

		"policytypes/onap.policies.monitoring.tcagen2.yaml",

		"policytypes/onap.policies.monitoring.dcaegen2.collectors.datafile.datafile-app-server.yaml",

		"policytypes/onap.policies.monitoring.dcae-restconfcollector.yaml",

		"policytypes/onap.policies.monitoring.dcae-pm-subscription-handler.yaml",

		"policytypes/onap.policies.monitoring.dcae-pm-mapper.yaml",

	.

	.

}

db-migrator changes:

The db-migrator will also need to be configurable in order to select what database it will use to create/update tables in the policyadmin schema. As syntax is slightly different between MariaDB and Postgres respectively, the db-migrator main script will need to be updated to reflect this. At the moment, the db-migrator is tailored specifically to MariaDB. An additional main script will be added to use postgres specifically. This means that the respective scripts will be ran based on the database the user selects.

Various checks in the db-migrator script use MariaDB statements to retrieve information from the database which will cause issues when changing to Postgres. For example; to retrieve a list of tables in a database in MariaDB the syntax is: USE DATABASE db_name; SHOW TABLES; whereas in Postgres the syntax is; \c db_name; \dt;

Additional sql scripts have been added for installation of the postgres database. The user will have to set environment variables for the PGPASWORD (postgres password) and what database to use. The prepare_upgrade.sh and prepare_downgrade.sh scripts will then prepare the scripts based on the operation selected for the specified database.

SQL_HOST=postgres
SQL_DB=policyadmin
SQL_USER=policy_user
SQL_PASSWORD=policy_user
POLICY_HOME=/opt/app/policy
PG_PASSWORD=policy_user
SCRIPTS_DIRECTORY=postgres

Testing Procedure:

• Create scripts for postgresSQL Honolulu and Istanbul upgrade and downgrade.
• Create main db-migrator-pg script for postgres.
• Modify the Dockerfile to install postgres and copy the new scripts to the container.
• Set the environment variables for PGPASSWORD and postgresSQL database.
• Run the db-migrator docker container.
• Run the postgres docker container and add both to a test network.
• Docker exec to db-migrator container and prepare the upgrade from v0 to v0800.
• Run the upgrade.
  • Check migration and policyadmin databases have been created with tables created.
  • Check the schema_version table to ensure it is on the correct version.
  • Check the policyadmin_schema_changelog table to make sure the operations have been recorded.
• Upgrade from 0800 to 0900. Perform checks.
• Downgrade from 0900 to 0800. Perform checks.
• Downgrade from 0800 to 0. Perform checks.
• Full upgrade from 0 to 0900. Perform checks.
• Full downgrade from 0900 to 0. Perform checks.

Results:

Test #1 - Verify upgrade from version 0 to 0800 (Honolulu)

/opt/app/policy/bin/prepare_upgrade.sh policyadmin
/opt/app/policy/bin/db-migrator-pg -s policyadmin -o upgrade -f 0 -t 0800

Test #2 - Verify upgrade from version 0800 (Honolulu) to 0900 (Istanbul)

/opt/app/policy/bin/db-migrator-pg -s policyadmin -o upgrade -f 0800 -t 0900

Test #3 - Verify downgrade from version 0900 to 0800 (Honolulu)

/opt/app/policy/bin/db-migrator-pg -s policyadmin -o downgrade -f 0900 -t 0800

Test #4 - Verify upgrade from version 0800 to 0

/opt/app/policy/bin/db-migrator-pg -s policyadmin -o downgrade -f 0800 -t 0

Test #5 - Verify full upgrade from version 0 to 0900 (Istanbul)

/opt/app/policy/bin/db-migrator-pg -s policyadmin -o upgrade -f 0 -t 0900

Test #6 - Verify full downgrade from version 0900 to 0

/opt/app/policy/bin/db-migrator-pg -s policyadmin -o downgrade -f 0900 -t 0