Migrate data via pg_dump

This guide describes database data migration from a legacy PostgreSQL charm running PostgreSQL 16 to a modern PostgreSQL 16 charm.

To migrate charms on new Juju interfaces, refer to the guide Integrate with your charm.

A minor difference in commands might be necessary for different revisions and/or Juju versions, but the general logic remains:

  • Deploy the modern charm nearby

  • Request credentials from legacy charm

  • Remove relation to legacy charm (to stop data changes)

  • Perform legacy DB dump (using the credentials above)

  • Upload the legacy charm dump into the modern charm

  • Add relation to modern charm

  • Validate results and remove legacy charm

Always try this in a test environment before performing it in production!

Contact us for more guidance.

Prerequisites

  • Your application supports modern PostgreSQL interfaces

  • A client machine with access to the deployed legacy charm

  • Enough storage in the cluster to support backup/restore of the databases.

Obtain existing database credentials

To obtain credentials for existing databases, execute the following commands for each database that will be migrated. Take note of these credentials for future steps.

First, define and tune your application and database (db) names. For example:

CLIENT_APP=<my-application/0>
OLD_DB_APP=<legacy-postgresql/leader | postgresql/0>
NEW_DB_APP=<new-postgresql/leader | postgresql/0>
DB_NAME=<your_db_name_to_migrate>

CLIENT_APP=<my-application-k8s/0>
OLD_DB_APP=<legacy-postgresql-k8s/leader | postgresql-k8s/0>
NEW_DB_APP=<new-postgresql-k8s/leader | postgresql-k8s/0>
DB_NAME=<your_db_name_to_migrate>

Then, obtain the username from the existing legacy database via its relation info:

OLD_DB_USER=$(juju show-unit ${CLIENT_APP} | yq '.[] | .relation-info | select(.[].endpoint == "db") | .[0].application-data.user')

Deploy new PostgreSQL databases and obtain credentials

Deploy new PostgreSQL database charm:

juju deploy postgresql ${NEW_DB_APP} --channel 16/stable

juju deploy postgresql-k8s ${NEW_DB_APP} --channel 16/stable --trust

Obtain the operator user password of the new PostgreSQL database