Migration from MariaDB to PostgreSQL
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
dbproperties in yournevisadmin4.ymlfile 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.<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 withUTC-1. If it was inUTC-1, replace it withUTC+1.Run this command to run the migration. Replace
<path>/postgres_migration.loadwith the absolute path topostgres_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.loadExample 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.787snoteThe 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.
- 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.yamlThis will download the current helm values to
old-values.yaml.Make a copy of
old-values.yamlcallednew-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.databaseproperties- Change the
database.typetopostgresql. - Change the values of
database.hostanddatabase.portto point to the service that hosts the PostgreSQL database. - Change the root user credentials to the PostgreSQL root user credentials.
- Example config
- before modification:
database:
host: mariadb
root:
password: Generated1!
username: root - after modification:
database:
type: postgresql
host: postgres
port: 5432
root:
password: postgres_password
username: postgres
- before modification:
- Change the
nevisAdmin4.databaseproperties- Change the
nevisAdmin4.database.applicationUserandnevisAdmin4.database.applicationUserPasswordto the PostgreSQL user and user-password. - Change the
nevisAdmin4.database.schemaUserandnevisAdmin4.database.schemaUserPasswordto the PostgreSQL schema owner and schema-owner-password.noteThese users will be created if they don't exist yet.
- Example config
- before modification:
nevisAdmin4:
database:
applicationUserPassword: Generated1!
enableSSL: false
schemaUserPassword: Generated1! - after modification:
nevisAdmin4:
database:
applicationUser: na4_user
applicationUserPassword: user_password
enableSSL: false
schemaUser: na4_owner
schemaUserPassword: owner_password
- before modification:
- Change the
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>noteReplace
<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_NAMESPACECreate 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.<postgres-user>needs to be either the schema owner of<postgres-schema>, or a superuser.- 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 withUTC-1. If it was inUTC-1, replace it withUTC+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: 1Apply the ConfigMap and Job
kubectl apply -f postgres-migration-config.yaml -n $RELEASE_NAMESPACE
kubectl apply -f postgres-migration-job.yaml -n $RELEASE_NAMESPACECheck 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.
Clean up the Job and ConfigMap
kubectl delete job postgres-migration-job -n $RELEASE_NAMESPACE
kubectl delete configmap postgres-migration-config -n $RELEASE_NAMESPACEStart the nevisAdmin4 service again
kubectl scale statefulset nevisadmin4 --replicas 1 -n $RELEASE_NAMESPACE