...
Make changes to policy-api, policy-pap and all pdps to allow configurable connection of MariaDB or Postgres. This will require replacing removing references to MariaDB in the configuration json (persistence.xml) of the components with Maven properties to populate the connection fields. Renaming the persistence unit would also be a possible change units from “PolicyMariaDb” to a more universal “PolicyDb”“PolicyDb”. Additional configuration files will be added to connect to the postgres database.
Changes in all components: (Example w/ policy-api)
| Code Block | ||||
|---|---|---|---|---|
| ||||
{
"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",
.
.
} | ||||
| Code Block | ||||
| ||||
{ "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": "${DATABASE_DRIVER}", "databaseUrl": "${DATABASE_URL}", "databaseUser": "${DATABASE_USER}", "databasePassword": "${DATABASE_PASSWORD}", "persistenceUnit": "${PERSISTENCE_UNIT}" }, "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" ] . . . } | ||||
| ||||
DATABASE_DRIVER=org.postgresql.Driver
DATABASE_URL=dbc:postgresql://localhost:5432/policyadmin
DATABASE_USER=policy_user
DATABASE_PASSWORD=policy_user
PERSISTENCE_UNIT=PolicyDb |
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;
Updates:
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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
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)
| Code Block | ||||
|---|---|---|---|---|
| ||||
/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)
| Code Block | ||||
|---|---|---|---|---|
| ||||
/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)
| Code Block | ||||
|---|---|---|---|---|
| ||||
/opt/app/policy/bin/db-migrator-pg -s policyadmin -o downgrade -f 0900 -t 0800 |
Test #4 - Verify upgrade from version 0800 to 0
| Code Block | ||||
|---|---|---|---|---|
| ||||
/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)
| Code Block | ||
|---|---|---|
| ||
/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
| Code Block | ||
|---|---|---|
| ||
/opt/app/policy/bin/db-migrator-pg -s policyadmin -o downgrade -f 0900 -t 0 |
12/7 - 90% of the work has been completed in the db-migrator. Due to syntax differences between MariaDB and Postgres, further changes are needed in the db-migrator to allow postgres to work while keeping the script common for both. Work ongoing on this.