Charmed PostgreSQL

Channel Revision Published Runs on
latest/stable 345 09 Nov 2023
Ubuntu 22.04 Ubuntu 20.04 Ubuntu 18.04 Ubuntu 16.04
14/stable 336 18 Oct 2023
Ubuntu 22.04
14/candidate 336 18 Oct 2023
Ubuntu 22.04
14/beta 336 18 Oct 2023
Ubuntu 22.04
14/edge 350 Today
Ubuntu 22.04
juju deploy postgresql --channel 14/stable
Show information



DB data migration using ‘pg_dump/pg_restore’

:information_source: 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!

Tip: Always test migration in LAB before performing it in Production!

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'

Tip: No migration necessary if the output above is 14/stable! Still, this manual can be used to copy data between different installations of the same (modern) charm postgresql, however the backup/restore is recommended for migrations between modern charms.


  • 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_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/
pg_dump -Fc -h /var/run/postgresql/ -U ${OLD_DB_USER} -d ${DB_NAME} > "/srv/dump/${OLD_DB_DUMP}"

# Leave DB VM

# 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>