Migrate PostgreSQL from Azure to a Vultr Managed Database

Introduction

This guide provides you with all the information you need to successfully migrate your data from an Azure Managed PostgreSQL Database to a Vultr Managed Database for PostgreSQL. It explains each step of the migration process, from preparing your source database in Azure to setting up a migration workstation to ensure a successful transition. By the end of this guide, you can confidently make a successful move from Azure to Vultr's Managed Database for PostgreSQL.

Prerequisite

To complete the steps in this guide, you need an Azure Managed PostgreSQL Database.

Create a New Vultr Managed Database for PostgreSQL

To get started, log into the Vultr Console and deploy a Vultr Managed Database for PostgreSQL that meets your requirements for database size, RAM, and number of replica nodes.

After this is complete, set your Azure database's network access control as described in the next section.

Set the Azure Database Network Access Control

Migrating from Azure to Vultr requires that your Azure database is configured to be publicly accessible. Navigate to your Azure Managed PostgreSQL Database portal and follow the configuration steps below.

1. Navigate to Access control (IAM)>deny assignments, ensure permission to perform migration is not denied. If denied, enable permission.

   

2. Navigate to Authentication, enable PostgreSQL Authentication.

   

3. Navigate to Networking>Network connectivity, enable public access (allowed public IP address).

   

4. Below the Network connectivity option, enable your firewall rules to Allow all IP, which makes your database publicly accessible.

   

Now that you've configured the network access, use one of these two options to migrate your data.

Option 1: Use Vultr's Migration Tool

Vultr's built-in migration tool can migrate the database for you if it meets all the requirements. If you are unable to perform this process, see Option 2 below for another migration option.

1. Navigate to Connection strings in the Azure portal, which contains your connection details. For psql, it may look like this.

    psql "host=[your_hostname].postgres.database.azure.com port=5432 dbname=[your_database] user=[your_username] password=[your_password] sslmode=require"
   

2. Copy the necessary details needed for the connection, including your password and the database name you need to migrate.

3. Open your deployed Vultr Managed Databases for PostgreSQL and navigate to the Migration tab to input your details.

4. After inputting your details, click Test Connection, which allows Vultr to check if your connection is publicly accessible before it lets you begin migration.

5. If the connection is successful, you'll get a message confirming your migration is ready.

    Connection successful! Ready to migrate via replication method

   or

    Connection successful! Ready to migrate via pg_dump method.
   

6. Click Begin migration to proceed with the migration.

Option 2: Migrate Using PostgreSQL (psql) Client Tool

After you configure the Azure Managed PostgreSQL Database to be publicly accessible, you can migrate your database with the psql client tool by using a temporary cloud server as a migration workstation.

To get started, deploy an Ubuntu 18.04 Vultr cloud server as a temporary workstation.

1. Download PostgreSQL's updated apt package list for Ubuntu and update it in your Vultr cloud workstation.

    $ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
   

2. Add PostgreSQL's updated keys from the package list to the list of keys.

    $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
   

3. Download and update the new package information from all configured sources.

    $ sudo apt-get update
   

4. Install PostgreSQL, which includes the psql client. You must install the same version, or later, as the Azure database you are migrating from.

   In this example, install PostgreSQL 14.

    $ sudo apt-get -y  install postgresql-14
   

5. Check the client version to ensure the install was successful.

    $ psql --version
   

6. Navigate to Connection strings in the Azure portal, which contains your connection details. Copy your hostname, username, and the database you need to migrate. For psql it may look like this.

    psql "host=[azure_hostname].postgres.database.azure.com port=[azure_port] dbname=[azure_database] user=[azure_username] password=[azure_password] sslmode=require"
   

7. Export the database you need to migrate into a SQL file using the pg_dump utility.

    $ pg_dump --host=[azure_hostname] --port=[azure_port] --username=[azure_username] --dbname=[azure_database_name] --file=migrateDb.sql

   Use the following arguments on your export command.

   • --host is the Azure database hostname.
   • --port is the Azure database port number.
   • --username is the username for your Azure database.
   • --file is the file holding your source database in your directory.
   • --dbname is the name of the database you are migrating from.

8. List all files in your directory to ensure your dump file is available.

    $ ls
   

9. Import the database to your Vultr Managed Databases for PostgreSQL.

    $ psql --file=migrateDb.sql --host=[vultr_hostname] --port=[vultr_port] --username=[vultr_username] --dbname=[vultr_database_name]

   Use the following arguments on your import command.

   • --host is the Vultr database hostname.
   • --port is the Vultr database port number.
   • --username is the username for your Vultr database.
   • --file is the file holding your source database in your directory.
   • --dbname is the name of the database you are migrating to.

   Note: ERROR: role "azure_pg_admin" does not exist. and ERROR: role "azuresu" does not exist are shown because azure_pg_admin and azuresu aren't granted any role in your Vultr Managed Databases for PostgreSQL, and won't affect the migration process. To avoid this error message, you can modify your SQL file to eliminate the GRANT permissions.

10. When the database migration is complete, log in to your Vultr PostgreSQL database.

     $ psql postgres://[hostname]:[port]/[migrated_database]
    

11. Verify the migrated database data is available.

     $ SELECT * FROM [TABLENAME];

    The command SELECT * FROM [TABLENAME] displays all data from the database table selected with the * command.

12. After the migration is complete, destroy the temporary Ubuntu cloud server; it's no longer required.

More Information

Read more about Vultr Managed Databases:

• Vultr Managed Databases for PostgreSQL Reference Guide