Version: 8.2607.x.x LTS

Migration from MariaDB to PostgreSQL

caution

While we have tested the migration process thoroughly and found it to work in our scenarios, we cannot guarantee that it will be successful in all possible cases. Please be aware that you use this guide and the pgloader tool at your own risk. You are responsible for verifying that all your data has been successfully migrated.

This guide assumes that you already have a running nevisAdmin 4 instance and a MariaDB database you want to migrate from.

nevisAppliance and RPM-based installations

If you haven't done so already, stop the nevisAdmin4 service, so that it doesn't write in the database during migration.
Setup the PostgreSQL database. For the setup guide, see PostgreSQL initial setup.
Change the db properties in your nevisadmin4.yml file to point to the PostgreSQL database.
Start the nevisAdmin4 service. This will initialize the schema in the new database, preparing it for the data migration.
Stop the nevisAdmin4 service.

Create the following migration file. Name it postgres_migration.load.

LOAD DATABASE
FROM mysql://<mariadb-user>:<mariadb-pw>@<mariadb-host>:<mariadb-port>/nevisadmin4
INTO postgresql://<postgres-user>:<postgres-pw>@<postgres-host>:<postgres-port>/nevisadmin4?sslmode=allow
alter schema 'nevisadmin4' rename to '<postgres-schema>'

SET
    timezone to '<inverse-UTC-of-the-admin4-server>'

cast
    type clob to text,
    type binary to bytea,
    column analytics_meta.last_confirmation to timestamp,
    column job_status.end_time to timestamp,
    column job_status.start_time to timestamp,
    column job_status.creation_time to timestamp,
    column job_status.progress to real

WITH
    data only,
    truncate

excluding table names matching
    ~<flyway_schema_history>
;

Replace <mariadb-user>, <mariadb-pw>, <mariadb-host>, <mariadb-port>, <postgres-user>, <postgres-pw>, <postgres-host>, <postgres-port>, and <postgres-schema> with the appropriate values.
1. <postgres-user> needs to be either the schema owner of <postgres-schema>, or a superuser.
Replace the timezone value with the timezone of your nevisAdmin4 server, with a negated sign. For example, if your nevisAdmin4 server was in UTC+1, replace it with UTC-1. If it was in UTC-1, replace it with UTC+1.

Run this command to run the migration. Replace <path>/postgres_migration.load with the absolute path to postgres_migration.load.

docker run --rm -it --network host -v <path>/postgres_migration.load:/postgres_migration.load dimitri/pgloader:latest pgloader --no-ssl-cert-verification /postgres_migration.load

Example output:

2024-08-14T08:06:47.036001Z LOG pgloader version "3.6.7~devel"
2024-08-14T08:06:47.250007Z LOG Migrating from #<MYSQL-CONNECTION mysql://mariadb@localhost:3306/nevisadmin4 {1007EAEE53}>
2024-08-14T08:06:47.250007Z LOG Migrating into #<PGSQL-CONNECTION pgsql://na4_owner@localhost:5432/nevisadmin4 {1007EAFB93}>
2024-08-14T08:06:47.926026Z WARNING PostgreSQL warning: constraint "fk_binary_secret_permission" of relation "binary_secret_permission" does not exist, skipping
2024-08-14T08:06:47.928026Z WARNING PostgreSQL warning: constraint "fk_dha_id_dh_id" of relation "deployment_history_action" does not exist, skipping
2024-08-14T08:06:47.929026Z WARNING PostgreSQL warning: constraint "fk_dhdt_id_dh_id" of relation "deployment_history_deployment_target" does not exist, skipping
2024-08-14T08:06:47.932026Z WARNING PostgreSQL warning: constraint "ptc_id_pt_id" of relation "project_template_categories" does not exist, skipping
2024-08-14T08:06:47.933026Z WARNING PostgreSQL warning: constraint "fk_secret_permission" of relation "secret_permission" does not exist, skipping
2024-08-14T08:06:47.934026Z WARNING PostgreSQL warning: constraint "fk_gm_user" of relation "group_member" does not exist, skipping
2024-08-14T08:06:47.935026Z WARNING PostgreSQL warning: constraint "fk_gm_usergroup" of relation "group_member" does not exist, skipping
2024-08-14T08:06:49.210063Z LOG report summary reset
                                    table name     errors       rows      bytes      total time
----------------------------------------------  ---------  ---------  ---------  --------------
                               fetch meta data          0         42                     0.258s
                             Drop Foreign Keys          0         14                     0.029s
                                      Truncate          0         35                     0.332s
----------------------------------------------  ---------  ---------  ---------  --------------
                        na4_owner.binary_cache          0          2     0.1 kB          0.076s
                      na4_owner.analytics_meta          0          1     0.0 kB          0.051s
                 na4_owner.assigned_group_role          0          1     0.0 kB          0.113s
           na4_owner.assigned_group_permission          0          1     0.0 kB          0.093s
                       na4_owner.assigned_role          0          1     0.0 kB          0.118s
                 na4_owner.assigned_permission          0          1     0.0 kB          0.131s
            na4_owner.binary_secret_permission          0          1     0.0 kB          0.163s
                       na4_owner.binary_secret          0          1     0.0 kB          0.158s
                       na4_owner.bundle_entity          0          1     0.0 kB          0.167s
                      na4_owner.blacklisttoken          0          1     0.0 kB          0.226s
                            na4_owner.constant          0          1     0.0 kB          0.251s
                                  na4_owner.ca          0          1     5.6 kB          0.281s
           na4_owner.deployment_history_action          0          1     0.1 kB          0.237s
                  na4_owner.deployment_history          0          1     0.1 kB          0.269s
                        na4_owner.group_member          0          2     0.0 kB          0.285s
                           na4_owner.inventory          0          1     0.1 kB          0.334s
na4_owner.deployment_history_deployment_target          0          2     0.0 kB          0.364s
                                na4_owner.head          0          1     0.1 kB          0.351s
              na4_owner.inventory_modification          0          1     0.1 kB          0.357s
                      na4_owner.inventory_head          0          1     0.1 kB          0.407s
                        na4_owner.modification          0          1     0.1 kB          0.383s
                          na4_owner.job_status          0          1     0.1 kB          0.443s
                   na4_owner.pki_store_content          0          1     0.0 kB          0.438s
         na4_owner.project_template_categories          0          2     0.0 kB          0.458s
                           na4_owner.pki_store          0          1     0.1 kB          0.463s
               na4_owner.resource_modification          0          1     0.1 kB          0.505s
                    na4_owner.project_template          0          1     0.2 kB          0.534s
                              na4_owner.secret          0          1     0.0 kB          0.523s
                       na4_owner.resource_head          0          1     0.1 kB          0.539s
                              na4_owner.tenant          0          1     0.0 kB          0.551s
                                na4_owner.role          0          1     0.0 kB          0.591s
                              na4_owner."user"          0          2     0.1 kB          0.572s
                   na4_owner.secret_permission          0          0                     0.597s
                                 na4_owner.tip          0          1     0.1 kB          0.610s
                           na4_owner.usergroup          0          1     0.0 kB          0.631s
----------------------------------------------  ---------  ---------  ---------  --------------
                       COPY Threads Completion          0          4                     0.687s
                               Reset Sequences          0          0                     0.078s
                           Create Foreign Keys          0          7                     0.022s
                              Install Comments          0          0                     0.000s
----------------------------------------------  ---------  ---------  ---------  --------------
                             Total import time          ✓         39     7.6 kB          0.787s

note

The warnings for the constraints are false positive.

Start the nevisAdmin4 service again.

Kubernetes-based installation

Setup a PostgreSQL database. For the setup guide, see PostgreSQL initial setup.
1. It might be required to whitelist the IP of the Kubernetes cluster in the PostgreSQL database for the migration to work.
Get the current nevisAdmin4 configuration using helm:
```
RELEASE_NAMESPACE=<release-namespace>

helm get values nevisadmin4 -n $RELEASE_NAMESPACE > old-values.yaml
```
This will download the current helm values to old-values.yaml.
Make a copy of old-values.yaml called new-values.yaml. You will have to change the database properties in it to point to the newly created postgres database. Some properties that you have to change might be missing. In that case, add them.
1. database properties
  1. Change the database.type to postgresql.
  2. Change the values of database.host and database.port to point to the service that hosts the PostgreSQL database.
  3. Change the root user credentials to the PostgreSQL root user credentials.
  4. Example config
    1. before modification:
      database: host: mariadb root: password: Generated1! username: root
    2. after modification:
      database: type: postgresql host: postgres port: 5432 root: password: postgres_password username: postgres
2. nevisAdmin4.database properties
  1. Change the nevisAdmin4.database.applicationUser and nevisAdmin4.database.applicationUserPassword to the PostgreSQL user and user-password.
  2. Change the nevisAdmin4.database.schemaUser and nevisAdmin4.database.schemaUserPassword to the PostgreSQL schema owner and schema-owner-password.
    note
    These users will be created if they don't exist yet.
  3. Example config
    1. before modification:
      nevisAdmin4: database: applicationUserPassword: Generated1! enableSSL: false schemaUserPassword: Generated1!
    2. after modification:
      nevisAdmin4: database: applicationUser: na4_user applicationUserPassword: user_password enableSSL: false schemaUser: na4_owner schemaUserPassword: owner_password

Use helm to update the nevisAdmin4 deployment:

# For the temporary credentials, click the download button for one of the Docker images at https://portal.nevis.net/portal/secure/releases/rolling
CLOUDSMITH_PASSWORD=<cloudsmith-password>

helm upgrade nevisadmin4 nevisadmin4 -n $RELEASE_NAMESPACE -f new-values.yaml \
 --repo https://dl.cloudsmith.io/$CLOUDSMITH_PASSWORD/nevissecurity/rolling/helm/charts/ \
 --version <version>

note

Replace <version> with the version of the nevisAdmin4 chart you are using. If you don't provide a version, helm will use the latest version.

This will also restart the nevisAdmin4 service, this time with the new database configuration, which will initialize the schema in the new database.

Stop nevisAdmin4 by scaling the replicas down to zero:

kubectl scale statefulset nevisadmin4 --replicas 0 -n $RELEASE_NAMESPACE

Create a ConfigMap for the postgres_migration.load file

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-migration-config
data:
  postgres_migration.load: |
    LOAD DATABASE
    FROM mysql://<mariadb-user>:<mariadb-pw>@<mariadb-host>:<mariadb-port>/nevisadmin4
    INTO postgresql://<postgres-user>:<postgres-pw>@<postgres-host>:<postgres-port>/nevisadmin4?sslmode=allow
    alter schema 'nevisadmin4' rename to '<postgres-schema>'
    SET
        timezone to '<inverse-UTC-of-the-admin4-server>'
    cast
        type clob to text,
        type binary to bytea,
        column analytics_meta.last_confirmation to timestamp,
        column job_status.end_time to timestamp,
        column job_status.start_time to timestamp,
        column job_status.creation_time to timestamp,
        column job_status.progress to real
    WITH
        data only,
        truncate
    excluding table names matching
        ~<flyway_schema_history>
    ;

Replace <mariadb-user>, <mariadb-pw>, <mariadb-host>, <mariadb-port>, <postgres-user>, <postgres-pw>, <postgres-host>, <postgres-port>, and <postgres-schema> with the appropriate values.
1. <postgres-user> needs to be either the schema owner of <postgres-schema>, or a superuser.
2. Example:
```
FROM mysql://admin4schemauser:Generated1!@mariadb:3306/nevisadmin4   
INTO postgresql://na4_owner:owner_password@postgres:5432/nevisadmin4?sslmode=allow
alter schema 'nevisadmin4' rename to 'na4_owner'
```
Replace the timezone value with the timezone of your nevisAdmin4 server, with a negated sign. For example, if your nevisAdmin4 server was in UTC+1, replace it with UTC-1. If it was in UTC-1, replace it with UTC+1.

Create a Job to run the pgloader container

apiVersion: batch/v1
kind: Job
metadata:
  name: postgres-migration-job
spec:
  template:
    spec:
      containers:
      - name: pgloader
        image: dimitri/pgloader:latest
        command: ["pgloader", "--no-ssl-cert-verification", "/config/postgres_migration.load"]
        volumeMounts:
        - name: config-volume
          mountPath: /config
      restartPolicy: Never
      volumes:
      - name: config-volume
        configMap:
          name: postgres-migration-config
  backoffLimit: 1

Apply the ConfigMap and Job

kubectl apply -f postgres-migration-config.yaml -n $RELEASE_NAMESPACE
kubectl apply -f postgres-migration-job.yaml -n $RELEASE_NAMESPACE

Check the logs of the job

kubectl logs -n $RELEASE_NAMESPACE $(kubectl get pods -n $RELEASE_NAMESPACE --selector=job-name=postgres-migration-job -o=jsonpath='{.items[0].metadata.name}')

You should see an output that is similar to the one in the nevisAppliance and RPM-based installations section. 12. Clean up the Job and ConfigMap

kubectl delete job postgres-migration-job -n $RELEASE_NAMESPACE
kubectl delete configmap postgres-migration-config -n $RELEASE_NAMESPACE

Start the nevisAdmin4 service again

kubectl scale statefulset nevisadmin4 --replicas 1 -n $RELEASE_NAMESPACE

nevisAppliance and RPM-based installations​

Kubernetes-based installation​

nevisAppliance and RPM-based installations

Kubernetes-based installation