DB data migration using ‘pg_dump/pg_restore’

NOTE: This document describes DB data migration only!
Use separate manual to migrate charm on new juju interfaces, etc.

The legacy VM charms is archived in the latest/stable channel, read more here. The minor difference in commands might be necessary for different revisions and/or Juju versions but the general logic is common:

• 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

Please check your application compatibility with Charmed PostgreSQL before migrating production data from legacy charm!

Do you need to migrate?

A database migration is only required if the output of the following command is latest/stable:

juju show-application postgresql | yq '.[] | .channel'

Prerequisites

• Client machine with access to deployed legacy charm
• Juju version 2.9+ (check the Juju tech details for the different Juju versions)
• 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 to be migrated. Use those credentials in migration steps.

# define and tune your application/DB names
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 >

# obtain username from the existing legacy database from DB relation
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 databases nearby:

# deploy new PostgreSQL database charm
juju deploy postgresql ${NEW_DB_APP} --channel 14/stable

# obtain `operator` user password of new PostgreSQL database from PostgreSQL charm
NEW_DB_USER=operator
NEW_DB_PASS=$(juju run ${NEW_DB_APP} get-password | yq '.password')

DB migration

Use the credentials and information obtained in previous steps to perform the database migration by executing the following commands:

# ensure that there are no new connections are made and that database is not altered
# remove relation between your_application charm and  legacy charm
juju remove-relation  ${CLIENT_APP}  ${OLD_DB_APP}

# Connect DB VM of a legacy charm:
juju ssh ${OLD_DB_APP} bash

# Create a dump via Unix socket using credentials from the relation:
mkdir -p /srv/dump/
OLD_DB_DUMP="legacy-postgresql-${DB_NAME}.sql"
pg_dump -Fc -h /var/run/postgresql/ -U ${OLD_DB_USER} -d ${DB_NAME} > "/srv/dump/${OLD_DB_DUMP}"

# Leave DB VM
exit

# Fetch dump locally:
juju scp ${OLD_DB_APP}:/srv/dump/${OLD_DB_DUMP}  ./${OLD_DB_DUMP}

Uploading the dump to the new charm:

# Upload the dump to new Charmed PostgreSQL
juju scp ./${OLD_DB_DUMP}  ${NEW_DB_APP}:.

# Go into new Charmed PostgreSQL charm to upload the dump:
juju ssh ${NEW_DB_APP} bash

# Create a new database (using ${NEW_DB_PASS}):
createdb -h localhost -U ${NEW_DB_USER} --password ${DB_NAME}

# Restore the dump (using ${NEW_DB_PASS}):
pg_restore -h localhost -U ${NEW_DB_USER} --password -d ${DB_NAME} --no-owner --clean --if-exists ${OLD_DB_DUMP}

Relate to modern charm

# relate your application and new PostgreSQL database charm (using modern `database` endpoint)
juju relate ${CLIENT_APP}  ${NEW_DB_APP}:database

# IF `database` endpoint (postgresql_client interface) is not yes supported, use legacy `pgsql` interface (endpoint `db`):
juju relate ${CLIENT_APP}  ${NEW_DB_APP}:db

Verify DB migration

Test your application to make sure the data is available and in a good condition.

Remove old databases

Test your application and if you are happy with a data migration, do not forget to remove legacy charms to keep the house clean:

juju remove-application --destroy-storage <legacy_postgresql>