Charmed PostgreSQL VM

Channel Revision Published Runs on
latest/stable 345 09 Nov 2023
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
latest/stable 239 09 Feb 2022
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
latest/stable 226 01 Apr 2021
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04 Ubuntu 14.04
14/stable 430 28 Jun 2024
Ubuntu 22.04
14/stable 429 28 Jun 2024
Ubuntu 22.04
14/candidate 429 26 Jun 2024
Ubuntu 22.04
14/candidate 430 26 Jun 2024
Ubuntu 22.04
14/beta 430 26 Jun 2024
Ubuntu 22.04
14/beta 429 26 Jun 2024
Ubuntu 22.04
14/edge 436 10 Jul 2024
Ubuntu 22.04
14/edge 435 10 Jul 2024
Ubuntu 22.04
juju deploy postgresql --channel 14/stable
Show information

Platform:

Ubuntu
22.04 20.04 18.04 16.04 14.04

Migrate database data using pg_dump / pg_restore

This guide describes database data migration only. To migrate charms on new juju interfaces, refer to the guide How to integrate a database with my charm.

Note: All commands are written for juju >= v.3.0

If you are using an earlier version, be aware that:

  • juju run replaces juju run-action --wait in juju v.2.9
  • juju integrate replaces juju relate and juju add-relation in juju v.2.9

For more information, check the Juju 3.0 Release Notes.

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'

Migration is not necessary if the output above is 14/stable!

This guide can be used to copy data between different installations of the same (modern) charm postgresql, but the backup/restore is more recommended for migrations between modern charms.

Summary

The legacy VM charm archived in the latest/stable channel, read more here. 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

Note: Always test migration in a safe environment before performing it in production!

Prerequisites

  • Your application is compatible with Charmed PostgreSQL VM
  • A client machine with access to the deployed legacy charm
  • juju v.2.9 or later (check Juju 3.0 Release Notes for more information about key differences)
  • 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 db (database) 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 >

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 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')

Migrate database

Use the credentials and information obtained in previous steps to perform the database migration with the following procedure.

Make sure no new connections were made and that the database has not been altered!

Create dump from legacy charm

Remove the relation between application charm and legacy charm:

juju remove-relation  ${CLIENT_APP}  ${OLD_DB_APP}

Connect to the database 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}"

Exit the database VM:

exit

Upload dump to new charm

Fetch dump locally and upload it to the new Charmed PostgreSQL charm:

juju scp ${OLD_DB_APP}:/srv/dump/${OLD_DB_DUMP}  ./${OLD_DB_DUMP}
juju scp ./${OLD_DB_DUMP}  ${NEW_DB_APP}:.

ssh into new Charmed PostgreSQL charm and create a new database (using ${NEW_DB_PASS}):

juju ssh ${NEW_DB_APP} bash
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}

Integrate with modern charm

Integrate (formerly “relate” in juju v.2.9) your application and new PostgreSQL database charm (using the modern database endpoint)

juju integrate ${CLIENT_APP}  ${NEW_DB_APP}:database

If the database endpoint (from the postgresql_client interface) is not yet supported, use instead the db endpoint from the legacy pgsql interface:

juju integrate ${CLIENT_APP}  ${NEW_DB_APP}:db

Verify database 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>

Help improve this document in the forum (guidelines). Last updated 5 months ago.