In this blog post, we will demonstrate how to migrate a JFrog Artifactory PostgreSQL Database managed by Crunchy Operator to a CloudNativePG Operator online without having an outage (the only outage could be produced by reconfiguring your application to point to the new database). For PostgreSQL logical replication, we based it on a blog post written by Percona.

Motivation

Why did we migrate Artifactory PostgreSQL DB from Crunchy Operator to CloudNativePG Operator?
Both, Crunchy and CloudNativePG Operators are very similar but there is one difference which motivated us to perform this migration - costs. While usage of CloudNativePG Operator is free of charge the Crunchy Operator requires a subscription.

There are exceptions where Crunchy Operator can be used without a subscription. For more details you can refer to Crunchy Data Terms Of Use.

Our Environment

For the sake of simplicity, we use a 2-pod setup for the current Artifactory DB managed by Crunchy Operator and 1 pod for the Target Artifactory DB managed by CloudNativePG. Once the migration is done, you can scale the Target Artifactory DB up to many pods as you require.

Migration

To perform the DB migration, an empty CloudNativePG DB Cluster for JFrog Artifactory is required. If you need assistance setting up a CloudNativePG PostgreSQL Cluster, please check the official CloudNativePG Documentation or contact us.

Step 1: Create a Migration User

Create a migration user on Primary and Target DB pod:

CREATE USER dbmigration WITH SUPERUSER PASSWORD '***';

The dbmigration user is only used for migration purposes. After the migration, this user should be deleted.

Step 2: Create a Publication

Log in as the dbmigration user to the artifactory DB on the Primary DB pod:

psql -U dbmigration -h source-db-lp6h-0 -d artifactory

Create a publication for all tables in the artifactory DB:

CREATE PUBLICATION artifactorypub FOR ALL TABLES;

Step 3: Create a Logical Replication Slot

On the Primary DB pod, create a logical replication slot to capture database changes:

SELECT pg_create_logical_replication_slot('artifactorypub_slot', 'pgoutput');

Verify the logical replication slot:

SELECT * FROM pg_replication_slots;

Step 4: Pause Replication

Log in as the postgres user to the Secondary DB:

psql -U postgres -h source-db-9d4m-0

Pause the replication between the Primary and Secondary DB:

SELECT pg_wal_replay_pause();

With paused replication we can make sure that no updates are written to the secondary DB.
Verify if the replication is paused:

SELECT pg_is_wal_replay_paused();

Step 5: Note the replay_lsn for replica

On the Primary DB pod note replay_lsn (Log Sequence Number). Later we will configure the replay_lsn on Target DB from which Log Sequence Number the replication should be continued:

SELECT client_addr, application_name, replay_lsn FROM pg_stat_replication;

The output should be similar to below

client_addr |   application_name    |  replay_lsn
------------+-----------------------+---------------
10.128.8.21 |   source-db-9d4m-0    | 12CB/9500BFA8
(1 row)

Step 6: Dump and restore artifactory DB

On target pod target-db-1 create an artifactory DB dump from Secondary DB pod:

pg_dump -d artifactory -U artifactory -h source-db-9d4m-0.source-pods.postgres-db.svc -Fc > artifactory_dmp.db

Restore artifactory DB on Target DB:

pg_restore -h target-db-1 -U artifactory -d artifactory artifactory_dmp.db

Step 7: Resume the replication

Since we have the current state of the Secondary DB restored to the Target DB and have noted the Log Sequence Number the replication between the Primary and the Secondary DB can be enabled again.

Log in as the postgres user to the Secondary DB:

psql -U postgres -h source-db-9d4m-0

resume the replication:

SELECT pg_wal_replay_resume();

Verify if the replication is resumed:

SELECT pg_is_wal_replay_paused();

Step 8: Create a subscription

Login as the dbmigration user to artifactory db on Target DB pod:

psql -U dbmigration -h target-db-1 -d artifactory

Create subscription pointing to Primary DB's service:

CREATE SUBSCRIPTION artifactorysub CONNECTION 'host=source-primary.postgres-db.svc port=5432 dbname=artifactory user=dbmigration password=***' PUBLICATION artifactorypub WITH (copy_data = false, create_slot=false, enabled=false, slot_name=artifactorypub_slot);

With a subscription the Target DB will receive updates from the publication we created earlier on Primary DB. Verify if the subscription was created:

SELECT * FROM pg_stat_subscription;

Step 9: Advance the replication

To advance the replication on the Target DB we require the identifier returned by pg_replication_origin:

SELECT roident, subname, roname FROM pg_subscription sub, pg_replication_origin ro WHERE 'pg_' || sub.oid = ro.roname;

The output should be similar to below

roident |    subname     |  roname
--------+----------------+----------
      1 | artifactorysub | pg_16400
(1 row)

Now update the replication origin with the roname value you got from above command and with replay_lsn gathered in step 5

SELECT pg_replication_origin_advance('pg_16400', '12CB/9500BFA8');

Step 10: Enable subscription

On the Target DB pod enable the subscription. Once enabled, the logical replication will start:

ALTER SUBSCRIPTION artifactorysub ENABLE;

Verify if the subscription is enabled:

SELECT * FROM pg_stat_subscription;

You should now see timestamps of send and received messages

subid |    subname     | pid  | relid | received_lsn  |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time
------+----------------+------+-------+---------------+-------------------------------+-------------------------------+----------------+-------------------------------
18167 | artifactorysub | 3117 |       | 12CB/A5001958 | 2024-11-22 07:53:41.152916+00 | 2024-11-22 07:53:41.153084+00 | 12CB/A5001958  | 2024-11-22 07:53:41.152916+00
(1 row)

Step 11: Verify Database Synchronization

To ensure data synchronization between the Primary and Target DBs, execute this query on both DB pods:

SELECT count(*) FROM access_tokens;

The results should match.

Step 12: Cleanup

Once your application points to the new Target DB, remove the migration setup:

Disable the subscription:

ALTER SUBSCRIPTION artifactorysub DISABLE;

Drop the subscription:

DROP SUBSCRIPTION artifactorysub;

Delete the migration user:

DROP USER dbmigration;

Conclusion

Before you run the DB migration on production environment you might repeat the above steps several times on your test environment to become more familiar with the migration. For this you can delete your Target DB Cluster and remove logical replication slot and publication on your Source DB. If you have questions or you require an assistance to migrate your PostgreSQL database do not hesitate to contact us.