Tutorial: Migrate offline from Amazon Aurora PostgreSQL to Azure Database for PostgreSQL with the migration service

This article describes how to migrate your PostgreSQL database from Amazon Aurora to Azure Database for PostgreSQL offline.

The migration service in Azure Database for PostgreSQL is a fully managed service that's integrated into the Azure portal and the Azure CLI. It's designed to simplify your migration journey to Azure Database for PostgreSQL.

In this tutorial, you:

• Complete prerequisites
• Initiate the migration
• Monitor the migration
• Verify the migration

Prerequisites

Before you start a migration by using the migration service in Azure Database for PostgreSQL, it's important to complete the following prerequisites. These prerequisites are specifically designed for offline migration scenarios.

• Verify the source version
• Configure the target setup
• Configure the network setup
• Enable extensions
• Check server parameters
• Check users and roles
• Disable high availability (reliability) and read replicas on the target

Verify the source version

The source PostgreSQL server version must be 9.5 or later. If the source PostgreSQL version is earlier than 9.5, upgrade the version to 9.5 or later before you start the migration.

Configure the target setup

Before you begin the migration, you must create an instance of Azure Database for PostgreSQL in Azure. The SKU that's provisioned for Azure Database for PostgreSQL - Flexible Server should match the source.

For more information, see Create an instance of Azure Database for PostgreSQL.

Configure the network setup

Network setup is crucial for the migration service to function correctly. Ensure that the source PostgreSQL server can communicate with the target server in Azure Database for PostgreSQL.

For information about network setup, see Network scenarios for the migration service.

Enable extensions

To ensure a successful migration by using the migration service in Azure Database for PostgreSQL, you might need to verify extensions to your source PostgreSQL instance. Extensions provide functionality and features that might be required for your application. Make sure that you verify the extensions on the source PostgreSQL instance before you initiate the migration process.

In the target instance of Azure Database for PostgreSQL - Flexible Server, enable supported extensions that are identified in the source PostgreSQL instance.

For more information, see Extensions in Azure Database for PostgreSQL.

Note

A restart is required when you make any changes to the shared_preload_libraries parameter.

Check server parameters

Server parameters aren't automatically migrated to the target environment and must be manually configured.

• Match server parameter values from the source PostgreSQL database to the instance of Azure Database for PostgreSQL. In the Azure portal, go to Server parameters and manually update the values.

• Save the parameter changes and restart the instance of Azure Database for PostgreSQL to apply the new configuration if necessary.

Check users and roles

When you migrate to Azure Database for PostgreSQL, it's essential to address the migration of users and roles separately because they require manual intervention.

• Manual migration of users and roles: Users and their associated roles must be manually migrated to the instance of Azure Database for PostgreSQL. To facilitate this process, you can use the pg_dumpall utility with the --globals-only flag to export global objects such as roles and user accounts.

  Execute the following command. Replace <username> with the actual username, and replace <filename> with the name you want to use for the output file.

  pg_dumpall --globals-only -U <username> -f <filename>.sql
  

• Restriction on superuser roles: Azure Database for PostgreSQL doesn't support superuser roles. Superuser permissions must be removed before migration. Ensure that you adjust the permissions and roles accordingly.

By completing these steps, you can ensure that user accounts and roles are correctly migrated to Azure Database for PostgreSQL without issues related to superuser restrictions.

Disable high availability (reliability) and read replicas on the target

It's critical that you disable high availability (reliability) and read replicas in the target environment before you initiate migration. These features should be enabled only after the migration is completed.

Initiate the migration

You can migrate by using the Azure portal or the Azure CLI.

Azure portal

The Azure portal offers a simple and intuitive wizard-based experience to guide you through migration. By completing the steps that are outlined in this tutorial, you can seamlessly transfer your database to Azure Database for PostgreSQL - Flexible Server and take advantage of its powerful features and scalability.

To migrate by using the Azure portal, first configure the migration task. Then, connect to the source and target, and initiate the migration.

Configure the migration task

To configure the migration task in the Azure portal:

1. Open your web browser and go to the Azure portal. Enter your credentials to sign in.

2. Go to your instance of Azure Database for PostgreSQL - Flexible Server.

3. On the service menu, select Migration.

   

4. Select Create to migrate from Amazon Aurora to a flexible server.

   The first time you use the migration service, an empty grid appears with a prompt to begin your first migration. If migrations to your flexible server target are already created, the grid contains information about attempted migrations.

5. Select Create to step through a series of tabs to set up a migration.

   

Setup

Enter or select the following information:

• Migration name: Enter a unique identifier for each migration to this flexible server target. You can use only alphanumeric characters and hyphens (-) in the migration name. The name can't start with a hyphen, and it must be unique for a target server. No two migrations to the same flexible server target can have the same name.

• Source server type: Select the source type that corresponds to your PostgreSQL source, such as a cloud-based PostgreSQL service, an on-premises setup, or a virtual machine.

• Migration option: Choose one of the following options for a pre-migration validation:

  • Validate. Checks your server and database readiness for migration to the target source.
  • Migrate. Skips validations and starts the migration.
  • Validate and Migrate. Performs validation before triggering a migration. If there are no validation failures, the migration is triggered.

  A good practice is to select the Validate or Validate and Migrate option for pre-migration validations.

  For more information, see Premigration validations.

• Migration mode: Select the mode for the migration. The default option is Offline.

Select Next: Connect to source.

Select the runtime server

The migration runtime server is a specialized feature of the migration service. The runtime server acts as an intermediary server during migration. It's a separate instance of Azure Database for PostgreSQL - Flexible Server that isn't the target server. The runtime server facilitates the migration of databases from a source environment that is accessible only via a private network.

For more information, see Migration runtime server.

Connect to the source

On the Connect to source tab, enter or select the following information for the database source:

• Server name: Enter the host name or the IP address of the source PostgreSQL instance.
• Port: Enter the port number of the source server.
• Server admin login name: Enter the username of the source PostgreSQL server.
• Password: Enter the password of the source PostgreSQL server.
• SSL mode: Supported values are Prefer and Require. When Secure Sockets Layer (SSL) at the source PostgreSQL server is off, select Prefer. If SSL at the source server is on, select Require. SSL values are set in the postgresql.conf file.
• Test Connection: Initiates a connectivity test between the target and the source. When the connection is successful, go to the next step to identify networking issues between the target and source and to verify the username and password for the source. Establishing a test connection takes a few minutes.

After a successful test connection, select Next: Select migration target.

Select the migration target

On the Select migration target tab, enter or select the following information for the flexible server target, in addition to subscription, resource group, and server name:

• Admin username: The admin username of the target PostgreSQL server.
• Password: The password of the target PostgreSQL server.
• Custom FQDN/IP (Optional): The custom FQDN/IP field is optional and can be used when the target is behind a custom DNS server or has custom DNS namespaces, making it accessible only via specific FQDNs or IP addresses. For example, this could include entries like flexibleserver.example.com, 198.1.0.2, or a PostgreSQL FQDN such as flexibleserver.postgres.database.azure.com, if the custom DNS server contains the DNS zone postgres.database.azure.com or forwards queries for this zone to 168.63.129.16, where the FQDN is resolved in the Azure public or private DNS zone.
• Test Connection: Initiates a connectivity test between the target and the source. When the connection is successful, go to the next step to identify networking issues between the target and source and to verify the username and password for the target server. Establishing a test connection takes a few minutes.

After a successful test connection, select Next: Select database(s) for migration.

Select databases for migration

On the Select database for migration tab, select from a list of user databases to migrate from your source PostgreSQL server.

After you select the databases, select Next: Summary.

Summary

The Summary tab summarizes all the source and target details for creating the validation or migration. Review the details, and then select Start Validation and Migration.

Monitor the migration

Within a few seconds after you select Start Validation and Migration, a notification appears to say that the validation or migration creation is successful. You're redirected to the Flexible Server instance Migration pane. The state entry is InProgress and the substate is PerformingPreRequisiteSteps. The workflow takes 2 to 3 minutes to set up the migration infrastructure and check network connections.

The grid that displays the migrations has these columns:

• Name
• Status
• Migration mode
• Migration type
• Source server
• Source server type
• Databases
• Duration
• Start time

The entries are displayed in descending order of start time, with the most recent entry on the top. You can select Refresh in the menu bar to refresh the status of the validation or migration run.

Migration details

In the list of migrations, select the name of a migration to see associated details.

On the Setup tab, select the migration option Validate and Migrate. In this scenario, validations are completed before migration starts. After the PerformingPreRequisiteSteps substate is completed, the workflow moves into the Validation in Progress substate.

• If validation has errors, the migration moves into a Failed state.

• If validation is complete without any error, the migration starts, and the workflow moves into the substate Migrating Data.

You can check validation details at the instance level and at the database level:

• Validation at the instance level:

  • Check validation related to the connectivity check for the source version (the PostgreSQL version >= 9.5 server parameter check) if the extensions are enabled in the server parameters of the instance of Azure Database for PostgreSQL - Flexible Server.

• Validation at the database level:

  • Check validation of the individual databases related to extensions and collations support in Azure Database for PostgreSQL - Flexible Server.

You can see the current status for the migration and validation on the migration details pane.

The following tables describe some possible migration states and substates.

Migration states

State	Description
InProgress	The migration infrastructure setup is underway, or the actual data migration is in progress.
Canceled	The migration is canceled or deleted.
Failed	The migration failed.
Validation failed	The validation failed.
Succeeded	The migration succeeded and is completed.
WaitingForUserAction	Applicable only in online migrations. Waiting for a user to perform a cutover.

Migration substates

Substate	Description
PerformingPreRequisiteSteps	Infrastructure setup is underway for data migration.
Validation in Progress	Validation is in progress.
MigratingData	Data migration is in progress.
CompletingMigration	Migration is in the final stages of completion.
Completed	Migration is completed.
Failed	Migration failed.

Validation substates

Substate	Description
Failed	Validation failed.
Succeeded	Validation is successful.
Warning	Validation shows a warning.

Cancel the migration

You can cancel any ongoing validations or migrations. The workflow must be in the In progress state to be canceled. You can't cancel a validation or migration that's in the Succeeded or Failed state.

Canceling a migration stops further migration activity on your target server and moves the migration attempt to a Canceled state. The cancel action rolls back all changes made on your target server by the migration service.

Azure CLI

This article describes how to use the Azure CLI to migrate your PostgreSQL database from Amazon Aurora to Azure Database for PostgreSQL. The Azure CLI is a powerful and flexible command-line interface that you can use to complete various tasks, including database migration.

For more information, see Set up the Azure CLI for the migration service.

After the Azure CLI is installed, at the command line, sign in to your Azure account by using the following command:

az login

Configure the migration task

1. To begin the migration, create a JSON file to hold the migration details. Save the JSON file on your local computer as [filename].json. For example, you can save the file as C:\migration-CLI\migration_body.json.

   Copy the following JSON and paste it in the JSON file. Replace <placeholders> with relevant information from your scenario.

   {
   "properties": {
   "SourceDBServerResourceId": "<source host name or IP address>:<port>@<username>",
           "SecretParameters": {
               "AdminCredentials": {
                   "SourceServerPassword": "<source password>",
                   "TargetServerPassword": "<target password>"
               },
               "targetServerUserName": "<target username>"
           },
           "DBsToMigrate": "<a comma-separated list of databases in an array, similar to the example "ticketdb","timedb","inventorydb">",
           "OverwriteDBsInTarget": "true",
           "sourceType": "AWS_AURORA",
           "sslMode": "Require"
       }
   }
   

2. Run the following command to check if any migrations are running. The migration name is unique for migrations in the Azure Database for PostgreSQL - Flexible Server target.

   az postgres flexible-server migration list --subscription 11111111-1111-1111-1111-111111111111 --resource-group my-learning-rg --name myflexibleserver --filter All
   

3. In the preceding steps, there are no migrations performed so we start with the new migration by running the following command

   az postgres flexible-server migration create --subscription 11111111-1111-1111-1111-111111111111 --resource-group my-learning-rg --name myflexibleserver --migration-name migration1 --migration-mode offline --migration-option ValidateAndMigrate --properties "C:\migration-cli\migration_body.json"
   

4. Run the following command to initiate the migration status in the previous step. You can check the status of the migration by providing the migration name

   az postgres flexible-server migration show --subscription 11111111-1111-1111-1111-111111111111 --resource-group my-learning-rg --name myflexibleserver --migration-name migration1
   

   The status of the migration progress is shown in the Azure CLI. You also can see the status of the instance of Azure Database for PostgreSQL - Flexible Server in the Azure portal.

Cancel or delete a migration

You can cancel any ongoing migration attempts by using the cancel command. This command stops the specific migration attempt and rolls back all changes on your target server.

Here's the Azure CLI command to delete a migration:

az postgres flexible-server migration update cancel --subscription 11111111-1111-1111-1111-111111111111 --resource-group my-learning-rg --name myflexibleserver --migration-name migration1

Verify the migration

When the database migration is finished, manually validate the data between the source and the target. Verify that all the objects in the target database are successfully created.

After migration, you can complete these tasks:

• Verify the data on your flexible server and ensure that it's an exact copy of the source instance.
• After verification, enable the high-availability option on your flexible server as needed.
• Change the SKU (version) of the flexible server to match the needs of your application. This change requires a restart of the database server.
• If you change any server parameters from their default values in the source instance, copy those server parameter values to the flexible server.
• Copy other server settings, such as tags, alerts, and firewall rules (if applicable), from the source instance to the flexible server.
• Make changes to your application to point the connection strings to a flexible server.
• Monitor the database performance closely to see if it requires performance tuning.

Related content

• Migrate online from Amazon Aurora PostgreSQL
• Migration service
• Migrate from on-premises and Azure VMs
• Known issues and limitations