PostgreSQL - Tutorial - How to migrate from Enterprise Cloud Databases to Public Cloud Databases

Learn how to migrate a PostgreSQL Enterprise Cloud Databases instance to Public Cloud Databases for PostgreSQL

Last updated March 2nd 2022

Objective

This guide details a procedure to migrate a PostgreSQL instance running on OVHcloud Enterprise Cloud Databases (soon to be EOL) to OVHcloud Public Cloud Databases for PostgreSQL.

Requirements

  • A Public Cloud project in your OVHcloud account
  • A PostgreSQL database running on OVHcloud Enterprise Cloud Databases (the "source" instance)
  • A PostgreSQL database running on OVHcloud Public Cloud Databases (the "target" instance)
  • A PostgreSQL client that can connect to both database instances, source and target.
  • Access to the OVHcloud Control Panel

These guides can help you to meet these requirements:

Considerations

While the Enterprise Cloud Databases offer granted you super user privilege, Public Cloud Databases for PostgreSQL only grants its user admin level privilege. Thus, any application or administrative task that may have utilized that super user access needs to be adapted so that it requires at most the privilege granted to the default avnadmin admin user.

  • This document outlines an offline migration path for your database, which means you'll have to suspend all the writes from your application and schedule for maintenance for the duration of the migration. Ensure you plan sufficient downtime to carry out all the migration tasks.
  • Ensure the source and destination PostgreSQL versions match.
  • Ensure you have good enough bandwidth between the client machine and both source and destination databases.
  • Ensure you choose a Database plan with appropriate compute, storage and memory resources.

Procedure

Step 1: Stop writing to the source database

Ensure client applications stop all write activity on the source database side. Depending on your system, this might entail letting your customers know in advance about unavailability, stopping other upstream applications, or some other administrative, operations or development tasks.

Step 2: Export the schema

Use the pg_dump command to export the database schema to the client machine, as an SQL plaintext file:

$ pg_dump --file "path/to/dump.sql" --host "xxxxxxxxxxx.prm.clouddb.ovh.net" --port "<write port>" \
    --username "postgres" --verbose --format=p --schema-only "database-name"

Step 3: Export the data

Use the pg_dump command to export the database data to the client machine, in a .tar archive file:

$ pg_dump --file "path/to/dump.tar" --host "xxxxxxxxxxx.prm.clouddb.ovh.net" --port "<write port>" \
    --no-owner --username "postgres" --no-password --verbose --format=t --blobs --encoding "UTF8"

Exporting the full dataset may take time depending on the size of the database and available bandwidth.

Step 4: Edit the schema

Since you won't have access to super user privilege (usually named postgres) on the destination database, you need to replace references to that role in the schema dump file with another role, either avnadmin, the initial admin user of your managed database, or another user you'd have created beforehand.

Open the schema dump file with a text editor and search for lines such as:

ALTER TABLE public.<table> OWNER TO postgres;

Edit it so that it reads:

ALTER TABLE public.<table> OWNER TO avnadmin;

Then, search the schema dump file for anything that is not compatible with the OVHcloud Public Cloud Databases offer. Review PostgreSQL - Capabilities and Limitations to learn about what the destination database supports.

Pay particular attention to anything related to extensions, users, roles and schemas. For example, if you installed any third party extensions, you'll need to remove them as extensions are to be handled differently going forward (check the list of supported extensions here: PostgreSQL - Available extensions).

Step 5: Import the schema

Use the following psql command to restore the schema on the destination:

$ psql -v ON_ERROR_STOP=1 -h postgresql-xxxxxxxx.database.cloud.ovh.net -p <port> \
    -d defaultdb -U avnadmin < path/to/the/dump.sql

This step should complete quickly.

Step 6: Verify the schema import

Connect to your database service to check if the schema restore completed successfully:

$ psql "postgres://<username>:<password>@<hostname>:<port>/defaultdb?sslmode=require"

Check the table(s):

defaultdb=> \dt

Verify the schema:

defaultdb=> \d <tablename>
defaultdb=> select * from information_schema.columns;

Step 7: Import the data

Use th pg_restore command to restore the data:

$ pg_restore -d defaultdb -h <postgresql-xxxxxxxxx.database.cloud.ovh.net> -p <port> \
     --no-owner -U avnadmin --data-only path/to/the/dump.tar -v

As for the data export step and depending on the dataset size and the available bandwidth, the operation might take some time.
You can monitor the disk & network activity from the Metrics section of the DB service page in your OVHcloud Control Panel:

Metrics Tab

See, for example, the disk usage:

Metrics - Disk

or the incoming network:

Metrics - Network

Step 8: Verify the data import

You can check if the data was migrated successfully by querying the destination database, e.g.:

defaultdb=> select pg_size_pretty(pg_total_relation_size('<tablename>'));
defaultdb=> select count(*) from <tablename>;

Step 9: Resume operations using the destination database

Once you verified that the database migration was successful, update client applications to have them connect to the destination database. You can now resume normal operations.

Go further

Visit our dedicated Discord channel: https://discord.gg/PwPqWUpN8G. Ask questions, provide feedback and interact directly with the team that builds our databases services.

Join our community of users on https://community.ovh.com/en/.


Haben Ihnen die Anleitungen geholfen?

Bevor Sie Ihre Meinung abgeben, nehmen wir gerne Ihre Vorschläge auf, wie wir diese Dokumente verbessern können.

Woran liegt es? An den Bildern, dem Inhalt oder Aufbau der Anleitungen? Schreiben Sie es uns gerne, dann machen wir es zusammen besser.

Ihre Support-Anfragen werden in diesem Formular nicht entgegengenommen. Verwenden Sie hierfür bitte das Formular "Ein Ticket erstellen" .

Vielen Dank. Ihr Feedback wurde gesendet.


Diese Anleitungen könnten Sie auch interessieren...

OVHcloud Community

Besuchen Sie Ihren Community-Bereich und tauschen Sie sich mit anderen Mitgliedern der OVHcloud Community aus. Hier können Sie Fragen stellen, zusätzliche Informationen finden und eigene Inhalte veröffentlichen.

Tauschen Sie sich mit der Community aus

Alle Preise verstehen sich inklusive der gesetzlichen Mehrwertsteuer.

In Übereinstimmung mit der Richtlinie 2006/112/EG in der geänderten Fassung können die Preise ab 01.01.2015 je nach Wohnsitzland des Kunden variieren
(die Preise in den Angeboten verstehen sich inklusive der gesetzlichen Mehrwertsteuer für die Bundesrepublik Deutschland).