Tutorial: Migrate from on-premises or an Azure VM hosted PostgreSQL to Azure Database for PostgreSQL using the migration service

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

This tutorial guides you in migrating a PostgreSQL instance from your on-premises or Azure virtual machines (VMs) to Azure Database for PostgreSQL flexible server using the Azure portal and Azure CLI.

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

• Configure your Azure Database for PostgreSQL Flexible Server
• Configure the migration task
• Monitor the migration
• Cancel the migration
• Post migration

Prerequisites (offline)

Before you start your migration with migration service in Azure Database for PostgreSQL, fulfilling the following prerequisites, which apply to offline migration scenarios is essential.

Verify the source version

Source PostgreSQL version should be >= 9.5. If the source PostgreSQL version is less than 9.5, upgrade the source PostgreSQL version to 9.5 or higher before migration.

Target setup

• Azure Database for PostgreSQL must be set up in Azure before migration.

• The SKU chosen for the Azure Database for PostgreSQL should correspond with the specifications of the source database to ensure compatibility and adequate performance.

• For detailed instructions on creating a new Azure Database for PostgreSQL, refer to the following link: Quickstart: Create server.

Network setup

Proper networking setup is essential to ensure successful connectivity between the source and target during migration. Here's a guide to help you establish the network connection for different scenarios:

Networking requirements for migration:

• ExpressRoute/IPsec VPN/VPN tunneling: When connecting your on-premises/AWS source to Azure, you might need to set up an ExpressRoute, IPsec VPN, or VPN tunneling to facilitate secure data transfer.

• VNET peering: Establish virtual network peering between the two distinct VNets to enable direct network connectivity, a prerequisite for migration between the Azure VM and the Azure Database for PostgreSQL.

Connectivity Scenarios:

The following table can help set up the network between the source and target.

Source	Target	Connectivity Tips
Public	Public	No other action is required if the source is whitelisted in the target's firewall rules.
Private	Public	This configuration isn't supported; use pg_dump/pg_restore for data transfer.
Public	Private	No other action is required if the source is whitelisted in the target's firewall rules.
Private	Private	Establish an ExpressRoute, IPsec VPN, VPN Tunneling, or virtual network Peering between the source and target.
Private	Private Endpoint	This configuration isn't supported; contact Microsoft support.

Additional Networking Considerations:

• pg_hba.conf Configuration: To facilitate connectivity between the source and target PostgreSQL instances, it is essential to verify and potentially modify the pg_hba.conf file. This file includes client authentication and must be configured to allow the target PostgreSQL to connect to the source. Changes to the pg_hba.conf file typically require a restart of the source PostgreSQL instance to take effect.

Note

The pg_hba.conf file is located in the data directory of the PostgreSQL installation. This file should be checked and configured if the source database is an on-premises PostgreSQL server or a PostgreSQL server hosted on an Azure VM. For PostgreSQL instances on AWS RDS or similar managed services, the pg_hba.conf file is not directly accessible or applicable. Instead, access is controlled through the service's provided security and network access configurations.

For more information about network setup, visit Network guide for migration service in Azure Database for PostgreSQL - Flexible Server.

Extensions

Extensions are extra features that can be added to PostgreSQL to enhance its functionality. Extensions are supported in Azure Database for PostgreSQL but must be enabled manually. To enable extensions, follow these steps:

• Use the select command in the source to list all the extensions that are being used - select extname,extversion from pg_extension;

• Search for azure.extensions server parameter on the Server parameter page on your Azure Database for PostgreSQL. Enable the extensions found in the source within PostgreSQL.

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

  

• Check if the list contains any of the following extensions:

  • PG_CRON
  • PG_HINT_PLAN
  • PG_PARTMAN_BGW
  • PG_PREWARM
  • PG_STAT_STATEMENTS
  • PG_AUDIT
  • PGLOGICAL
  • WAL2JSON

If yes, search the server parameters page for the shared_preload_libraries parameter. This parameter indicates the set of extension libraries that are preloaded at the server restart.

Users and roles

When migrating to Azure Database for PostgreSQL, it's essential to address the migration of users and roles separately, as they require manual intervention:

• Manual Migration of Users and Roles: Users and their associated roles must be manually migrated to the 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, replacing <<username>> with the actual username and <<filename>> with your desired output file name:

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

• Restriction on Superuser Roles: Azure Database for PostgreSQL doesn't support superuser roles. Therefore, users with superuser privileges must have those privileges removed before migration. Ensure that you adjust the permissions and roles accordingly.

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

Server parameters

These 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 Azure Database for PostgreSQL by accessing the "Server parameters" section in the Azure portal and manually updating the values accordingly.

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

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

• Disabling high availability (reliability) and reading replicas in the target environment is essential. These features should be enabled only after the migration has been completed.

• By following these guidelines, you can help ensure a smooth migration process without the added variables introduced by HA and Read Replicas. Once the migration is complete and the database is stable, you can proceed to enable these features to enhance the availability and scalability of your database environment in Azure.

Portal

You can migrate using the Azure portal.

Configure the migration task

The migration service comes with a simple, wizard-based experience on the Azure portal.

1. Open your web browser and go to the portal. Enter your credentials to sign in. The default view is your service dashboard.

2. Go to your Azure Database for the PostgreSQL flexible server.

3. In the Overview tab of the flexible server, on the left menu, scroll down to Migration and select it.

   

4. Select the Create button to migrate to a flexible server from on-premises or Azure VMs.

   Note

   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 have already been created, the grid now contains information about attempted migrations.

5. Select the Create button to go through a wizard-based series of tabs to perform a migration.

   

Setup

The first tab is the setup tab.

The user needs to provide multiple details related to the migration, like the migration name, source server type, option, and mode.

• Migration name is the unique identifier for each migration to this Flexible Server target. This field accepts only alphanumeric characters and doesn't accept any special characters except a hyphen (-). The name can't start with a hyphen and should be unique for a target server. No two migrations to the same Flexible Server target can have the same name.

• Source Server Type - Depending on your PostgreSQL source, you can select Azure Database for PostgreSQL single server, on-premises, Azure VM.

• Migration Option - Allows you to perform validations before triggering a migration. You can pick any of the following options

  • Validate - Checks your server and database readiness for migration to the target.
  • Migrate - Skips validations and starts migrations.
  • Validate and Migrate - Performs validation before triggering a migration. Migration gets triggered if there are no validation failures.
    • Choosing the Validate or Validate and Migrate option is always a good practice to perform premigration validations before running the migration.

To learn more about the premigration validation, visit premigration.

• Migration mode allows you to pick the mode for the migration. Offline is the default option.

Select the Next: Connect to source button.

Connect to the source

The Connect to Source tab prompts you to give details related to the source selected in the Setup Tab, which is the source of the databases.

• Server Name - Provide the Hostname or the IP address of the source PostgreSQL instance

• Port - Port number of the Source server

• Server admin login name - Username of the source PostgreSQL server

• Password - Password of the source PostgreSQL server

• SSL Mode - Supported values are preferred and required. When the SSL at the source PostgreSQL server is OFF, use the SSLMODE=prefer. If the SSL at the source server is ON, use the SSLMODE=require. SSL values can be determined in postgresql.conf file.

• Test Connection - Performs the connectivity test between target and source. Once the connection is successful, users can go ahead with the next step; they need to identify the networking issues between the target and source and verify the username/password for the source. Test connection takes a few minutes to establish a connection between the target and source.

After the successful test connection, select the Next: Select Migration target button.

Connect to the target

The select migration target tab displays metadata for the Flexible Server target, like subscription name, resource group, server name, location, and PostgreSQL version.

• Admin username - Admin username of the target PostgreSQL server

• Password - Password of the target PostgreSQL server

• Test Connection - Performs the connectivity test between target and source. Once the connection is successful, users can proceed with the next step. Otherwise, we need to identify the networking issues between the target and the source and verify the username/password for the target. Test connection takes a few minutes to establish a connection between the target and source

After the successful test connection, select the Next: Select Database(s) for Migration

Select databases for migration

Under the Select database for migration tab, you can choose a list of user databases to migrate from your source PostgreSQL server.

After selecting the databases, select the Next: Summary.

Summary

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

Monitor the migration

After you select the Start Validation and Migration button, a notification appears in a few seconds to say that the validation or migration creation is successful. You're automatically redirected to the flexible server's Migration page. The entry is in the InProgress state and PerformingPreRequisiteSteps substate. The workflow takes 2-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 and Start time. The entries are displayed in the descending order of the start time, with the most recent entry on the top. You can use the refresh button to refresh the status of the validation or migration run.

Migration details

Select the migration name in the grid to see the associated details.

In the Setup tab, we have selected the migration option as Validate and Migrate. In this scenario, validations are performed first before migration starts. After the PerformingPreRequisiteSteps substrate is completed, the workflow moves into the substrate of Validation in Progress.

• 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 of Migrating Data.

Validation details are available at Instance and Database level.

• Validation at Instance level
  • Contains validation related to the connectivity check, source version, that is, PostgreSQL version >= 9.5, server parameter check, that is, if the extensions are enabled in the server parameters of the Azure Database for PostgreSQL - flexible server.
• Validation at Database level
  • It contains validation of the individual databases related to extensions and collations support in Azure Database for PostgreSQL, a flexible server.

You can see the validation and the migration status under the migration details page.

Possible migration states include:

• 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 has failed.
• Validation Failed : The validation has failed.
• Succeeded: The migration has succeeded and is complete.
• WaitingForUserAction: Applicable only for online migration. Waiting for user action to perform cutover.

Possible migration substates include:

• 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 has been completed.
• Failed: Migration is failed.

Possible validation substates include:

• Failed: Validation is failed.
• Succeeded: Validation is successful.
• Warning: Validation is in Warning. Warnings are informative messages that you must remember while planning the migration.

Cancel the migration using the portal

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

• Canceling a validation stops further validation activity, and the validation moves to a Cancelled state.
• Canceling a migration stops further migration activity on your target server and moves to a Cancelled state. The cancel action will roll back all changes made by the migration service on your target server.

CLI

You can migrate using Azure CLI.

To begin migrating using Azure CLI, you need to install the Azure CLI on your local machine.

Set up Azure CLI for migration service in Azure Database for PostgreSQL - Flexible Server

• Install the Azure CLI depending on the operating system to run the CLI commands.
• Azure CLI can be installed from - How to install the Azure CLI.
• If Azure CLI is already installed, check the version by issuing the az version command. The version should be at least 2.56.0 or above to use the migration service.
• Once installed, run the az sign in command. This opens the default browser and loads an Azure sign-in page to authenticate. Pass in your Azure credentials to do a successful authentication. For other ways to sign with Azure CLI, visit this link.

Setup CLI commands for the migration service

All the CLI commands start with az postgres flexible-server migration. There are also help statements provided to assist you in understanding the various options and framing the correct syntax for the CLI commands.

Once the CLI is installed, open the command prompt and log into the Azure account using the below command.

Example with Windows command prompt: az login.

Commands to use with the migration service

The migration service provides the following commands to help you migrate your PostgreSQL databases to Azure Database for PostgreSQL - Flexible Server.

The help command

The command az postgres flexible-server migration –-help provides the name and the corresponding verbs.

The create command

The create command helps to migrate from a source server to a target server.

The help command allows users to understand the different arguments used for creating and initiating the migration az postgres flexible-server migration create --help.

az postgres flexible-server migration create --subscription 11111111-1111-1111-1111-111111111111 --resource-group my-learning-rg --name myflexibleserver --migration-name migration1 --migration-option ValidateAndMigrate --properties "C:\Users\Administrator\Documents\migrationBody.JSON" --migration-mode offline

Different substates flow when the create command is triggered.

• The migration moves to the InProgress state and the PerformingPreRequisiteSteps substate.

• After the PerformingPreRequisiteSteps substate is completed, the migration moves to the substate of Migrating Data, where the Cloning/Copying of the databases takes place.

• Each database migrated has its section with all migration details, such as table count, incremental inserts, deletions, and pending bytes.

• The time that the Migrating Data substate takes to finish depends on the size of the databases that are migrated.

• The migration moves to the Succeeded state after the Migrating Data substate finishes successfully. If there's a problem at the Migrating Data substate, the migration moves into a Failed state.

Let us understand the details of each parameter used in the create command.

Parameter Name	Description
subscription	Subscription ID of PostgreSQL Flexible server
resource-group	Resource group of PostgreSQL Flexible server
name	Name of the PostgreSQL Flexible server
migration-name	Unique identifier to migrations attempted to Flexible Server. This field accepts only alphanumeric characters and doesn't accept any special characters except a hyphen (-). The name can't start with -, and no two migrations to a Flexible Server target can have the same name.
migration-mode	This is an optional parameter. Default value: Offline. Offline migration involves copying your source databases at a point in time to your target server.
migration-option	Allows you to perform validations before triggering a migration. Default is ValidateAndMigrate. Allowed values are - Migrate, Validate, ValidateAndMigrate.
properties	Absolute path to a JSON file that has the information about the source server

You must provide a JSON file with the absolute path as a parameter while initiating the migration.

The JSON file contains the following information:

Property Name	Description
sourceDbServerResourceId	Source server details in the format for on-premises, Azure virtual machines (VMs), AWS - <<hostname or IP address>>:<<port>>@<<username>>
AdminCredentials	This parameter lists passwords for admin users for both the source server and the target PostgreSQL flexible server. These passwords help to authenticate against the source and target servers
targetServerUserName	The default value is the admin user created during the creation of the flexible server, and the password provided is used for authentication against this user. If you aren't using the default user, this parameter is the user or role on the target server for migration. This user should be a member of azure_pg_admin, pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables roles and should have the Create role, Create DB attributes
dbsToMigrate	Specify the list of databases that you want to migrate to Flexible Server. You can include a maximum of eight database names at a time. Providing the list of DBs in array format.
OverwriteDBsInTarget	When set to true (default), if the target server happens to have an existing database with the same name as the one you're trying to migrate, the migration tool automatically overwrites the database
MigrationMode	Mode of the migration. The supported value is "Offline"
sourceType	Required parameter. Values can be - OnPremises, AWS, AzureVM, PostgreSQLSingleServer
sslMode	SSL modes for migration. SSL mode for PostgreSQLSingleServer is VerifyFull and Prefer/Require for other source types

The list command

The list command lists all the migration attempts made to an Azure Database for PostgreSQL target.

az postgres flexible-server migration list [--subscription] [--resource-group][--name] [--filter]

Parameter Name	Description
subscription	Subscription ID of PostgreSQL Flexible server target
resource-group	Resource group of PostgreSQL Flexible server target
name	Name of the PostgreSQL Flexible server target
filter	To filter migrations, two values are supported – Active and All

The show command

The show command helps you monitor ongoing migrations and gives the current state and substate of the migration. These details include information on the current state and substate of the migration.

az postgres flexible-server migration show [--subscription][--resource-group][--name][--migration-name]

Parameter Name	Description
subscription	Subscription ID of PostgreSQL Flexible server target
resource-group	Resource group of PostgreSQL Flexible server target
name	Name of the PostgreSQL Flexible server target
migration-name	Unique identifier to migrations attempted to Flexible Server. This field accepts only alphanumeric characters and doesn't accept any special characters except a hyphen (-). The name can't start with -, and no two migrations to a Flexible Server target can have the same name.

Different states supported within the show command

Migration State	Description
InProgress	The migration infrastructure is set up, or the actual data migration is in progress
Canceled	The migration is canceled or deleted
Failed	The migration has failed
Succeeded	The migration has succeeded and is complete
WaitingForUserAction	Applicable only for online migration. Waiting for user action to perform cutover
ValidationFailed	The validation at the database or server level has failed

Different sub states supported within the show command for migration

Migration SubState	Description
PerformingPreRequisiteSteps	Infrastructure is set up and is prepped for data migration
MigratingData	Data migration is in progress
Completed	Migration is completed
Failed	Migration is failed
Validation in Progress	Validation is in progress
CompletingMigration	Migration is in the final stages of completion

Validation details are at the Instance and Database level

• Validation at Instance level

  • Contains validation related to the connectivity check, source version that is, PostgreSQL version >= 9.5, server parameter check that is, if the extensions are enabled in the server parameters of the Azure Database for PostgreSQL - flexible server.

• Validation at Database level

  • Contains validation of the individual databases related to extensions, collations support in Azure Database for PostgreSQL - flexible server.

Different sub states supported within show command for validation.

Validation SubState	Description
Failed	Validation is failed
Succeeded	Validation is successful
Warning	Validation is in Warning. Warnings are informative messages that you need to keep in mind while planning the migration

Cancel the migration

You can cancel any ongoing migration attempts by using the cancel command. This command stops the particular migration attempt but doesn't drop or roll back any changes on your target server. Here's the CLI command to delete a migration:

az postgres flexible-server migration update cancel [--subscription][--resource-group][--name][--migration-name]

For example:

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

For more information about this command, use the help parameter:

az postgres flexible-server migration update cancel -- help

Connect to the source

We're going to migrate "ticketdb","inventorydb","salesdb" into Azure Database for PostgreSQL flexible server.

Perform migration using CLI

• Open the command prompt and sign in to the Azure using az login command

  

• Edit the below placeholders << >> in the JSON lines and store them in the local machine as <<filename>>.json where the CLI is being invoked. In this tutorial, we have saved the file in C:\migration-CLI\migration_body.json

  {
  "properties": {
  "SourceDBServerResourceId": "<<source hostname or IP address>>:<<port>>@<<username>>",
          "SecretParameters": {
              "AdminCredentials": {
                  "SourceServerPassword": "<<Source Password>>",
                  "TargetServerPassword": "<<Target Password>>"
              }
          },
       "targetServerUserName":"<<Target username>>",
          "DBsToMigrate": [
             "<<comma separated list of databases like - "ticketdb","timedb","salesdb">>"
          ],
          "OverwriteDBsInTarget": "true",
          "MigrationMode": "Offline",
          "sourceType": "AzureVM",
          "sslMode": "Prefer"
      }
  }
  

• Run the following command to check if any migrations are running. The migration name is unique across the migrations within the Azure Database for PostgreSQL flexible server target.

  az postgres flexible-server migration list --subscription <<subscription ID>> --resource-group <<resource group name>> --name <<Name of the Flexible Server>> --filter All
  

  

• In the above 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 <<subscription ID>> --resource-group <<resource group name>> --name <<Name of the Flexible Server>> --migration-name <<Unique Migration Name>> --migration-option ValidateAndMigrate --properties "C:\migration-cli\migration_body.json"
  

• 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 <<subscription ID>> --resource-group <<resource group name>> --name <<Name of the Flexible Server>> --migration-name <<Migration ID>>
  

• The status of the migration progress is shown in the CLI.

  

• You can also see the status in the Azure Database for PostgreSQL flexible server portal.

  

Cancel the migration using CLI

You can cancel any ongoing migration attempts by using the cancel command. This command stops the particular migration attempt and rolls back all changes on your target server. Here's the CLI command to delete a migration:

az postgres flexible-server migration update cancel [--subscription]
                                            [--resource-group]
                                            [--name]
                                            [--migration-name]

For example:

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

For more information about this command, use the help parameter:

 az postgres flexible-server migration update cancel -- help

The command gives you the following output:

---

Post migration

After completing the databases, you need to manually validate the data between source and target and verify that all the objects in the target database are successfully created.

After migration, you can perform the following tasks:

• Verify the data on your flexible server and ensure it's an exact copy of the source instance.

• Post verification, enable the high availability option on your flexible server as needed.

• Change the SKU of the flexible server to match the application needs. This change needs a database server restart.

• If you change any server parameters from their default values in the source instance, copy those server parameter values in the flexible server.

• Copy other server settings like 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

• Migration service
• Migrate from AWS RDS
• Best practices
• Known Issues and limitations
• Network setup
• Premigration validations