Tutorial: Migrate from Azure Database for PostgreSQL - Single Server to Azure Database for PostgreSQL - Flexible Server using the migration service

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

Using the Azure portal, you can migrate an instance of Azure Database for PostgreSQL – Single Server to Azure Database for PostgreSQL – Flexible Server. In this tutorial, we perform migration of a sample database from an Azure Database for PostgreSQL single server to a PostgreSQL flexible server using the Azure portal.

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

Portal

You can migrate using the Azure portal.

Offline

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.

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.

Configure your Azure Database for PostgreSQL Flexible Server

• Create the target flexible server. For guided steps, refer to the quickstart Create an Azure Database for PostgreSQL flexible server using the portal.

• Allowlist extensions whose libraries must be loaded at server start. It's essential that the extension is on the allowlist before you initiate a migration.

• Check if the data distribution among a database's tables is skewed, with most of the data present in a single (or few) tables. If it's skewed, the migration speed could be slower than expected. In this case, the migration speed can be increased by migrating the large table in parallel.

Configure the migration task

The migration service comes with a simple, wizard-based experience on the Azure portal. Here's how to start:

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

2. Go to your Azure Database for PostgreSQL Flexible Server target.

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 start a migration from a single server to a flexible server. If this is your first time using the migration service, an empty grid appears with a prompt to begin your first migration.

   

   If you've already created migrations to your Flexible Server target, the grid contains information about migrations that were attempted to this target from the Single Server.

5. Select the Migrate from Single Server button. You go through a wizard-based series of tabs to create a migration into this Flexible Server target from any source Single Server.

Alternatively, you can initiate the migration process from the Azure Database for PostgreSQL Single Server.

1. Open your web browser and go to the portal. To sign in, you must enter your credentials. The default view is your service dashboard.

2. Upon selecting the Single Server, you can observe a migration-related banner in the Overview tab. Select Migrate now to get started.

   

3. You're taken to a page with two options. If you've already created a Flexible Server and want to use that as the target, choose Select existing, and select the corresponding Subscription, Resource group, and Server name details. Once the selections are made, select Go to Migration wizard and skip to the instructions under this page's Setup tab section.

   

4. Should you choose to Create a new Flexible Server, select Create new and select Go to Create Wizard. This action takes you through the Flexible Server creation process and deploys the Flexible Server.

   

After deploying the Flexible Server, follow the steps 3 to 5 under Configure the migration task.

Setup tab

The first tab is Setup. In case you missed it, allowlist necessary extensions as shown in It's essential to allowlist these extensions before you initiate a migration.

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 indicates the source. In this case, it's Azure Database for PostgreSQL Single server

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 only if there are no validation failures.

It's always a good practice to choose Validate or Validate and Migrate option to perform premigration validations before running the migration.

If the Online migration preview is selected, Logical replication must be turned on in the source Single server. If it's not turned on, the migration service automatically turns on logical replication at the source Single server. Replication can also be set up manually under the Replication tab in the Single server-side pane by setting the Azure replication support level to Logical. Either approach restarts the source single server.

Select the Next : Connect to Source button.

Source tab

The Source tab prompts you to give details related to the Single Server, which is the source of the databases.

After you make the Subscription and Resource Group selections, the dropdown list for server names shows Single Servers under that resource group across regions. Select the source that you want to migrate databases from. You can migrate databases from a Single Server to a target Flexible Server in the same region. Cross-region migrations are enabled only for India, China, and UAE servers.

After you choose the Single Server source, the Location, PostgreSQL version, and Server admin login name boxes are populated automatically. The server admin sign-in name is the admin username used to create the Single Server. In the Password box, enter the password for that admin user. The migration service migrates single server databases as the admin user.

After filling out all the fields, select the Connect to source link. This validates that the source server details entered are correct and that the source server is reachable.

Select the Next : Select migration target button to continue.

Target tab

The Target tab displays metadata for the Flexible Server target, such as subscription name, resource group, server name, location, and PostgreSQL version.

For Server admin login name, the tab displays the admin username used during creating the Flexible Server target. Enter the corresponding password for the admin user. After filling out the password, select the Connect to target link. This validates that the target server details entered are correct and target server is reachable.

Select the Next button to select the databases to migrate.

Select Databases for the migration tab

Under this tab, there's a list of user databases inside the Single Server. You can select and migrate up to eight databases in a single migration attempt. If there are more than eight user databases, the migration process is repeated between the source and target servers for the next set of databases. By default, selected databases with the same name on the target are overwritten.

Select the Next button to review the details.

Summary

The Summary tab summarizes all the details for creating the validation or migration. Review the details and select on the start button.

Monitor the migration portal

After you select the start button, a notification appears in a few seconds to say that the validation or migration creation is successful. You're redirected automatically to the Migration page of Flexible Server. This has a new entry for the recently created validation or migration.

The grid that displays the migrations has these columns: Name, Status, Migration type, Migration mode, Source server, Source server type, Databases, Start time and Duration. 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. You can also select the migration name in the grid to see the associated details.

When the validation or migration is created, it moves to the InProgress state and PerformingPreRequisiteSteps substate. The workflow takes 2-3 minutes to set up the migration infrastructure and network connections.

Let us look at how to monitor migrations for each Migration Option.

Validate

After the PerformingPreRequisiteSteps substate is completed, the validation moves to the substate of Validation in Progress where checks are done on the source and target server to assess the readiness for migration.

The validation moves to the Succeeded state if all validations are either in Succeeded or Warning state.

The validation grid has the

• Validation details for instance and Validation details for databases sections, representing the validation rules used to check migration readiness.
• Validation Status - Represents the result for each rule and can have any of the three values
  • Succeeded - If no errors were found.
  • Failed - If there are validation errors.
  • Warning - If there are validation warnings.
• Duration - Time taken for the Validation operation.
• Start and End time - Start and end time of the validation operation in UTC.

The Validation status moves to Failed state if there are any errors in the validation. Select the Validation name or Database name validation that has failed, and a fan-out pane gives the details and the corrective action you should take to avoid this error.

Migrate

After the PerformingPreRequisiteSteps substrate is completed, the migration moves to the substrate of Migrating Data when the Cloning/Copying of the databases takes place. The time for migration to complete depends on the size and shape of the databases you're migrating. The migration is quick if the data is mostly evenly distributed across all the tables. Skewed table sizes take a relatively longer time.

When you select any of the databases in migration, a fan-out pane appears. It has all the table count - copied, queued, copying, and errors apart from the database migration status.

The migration moves to the Succeeded state when the Migrating Data state finishes successfully. If there's an issue at the Migrating Data state, the migration moves into a Failed state.

Once the migration moves to the Succeeded state, schema and data migration from your Single Server to your Flexible Server target is complete. You can use the refresh button on the page to confirm the same.

Validate and Migrate

In this option, validations are performed first before migration starts. After the PerformingPreRequisiteSteps substate is completed, the workflow moves into the substate 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.

You can see the results of Validate and Migrate once the operation is complete.

Online (preview)

Prerequisites (online)

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.

Set up Online migration parameters

For Online migration, the replication support should be set to Logical under replication settings of the source PostgreSQL server. In addition, the server parameters max_wal_senders and max_replication_slots values should be equal to the number of Databases that need to be migrated. They can also be configured in the command line using the following commands:

• ALTER SYSTEM SET wal_level = logical;
• ALTER SYSTEM SET max_wal_senders = number of databases to migrate;
• ALTER SYSTEM SET max_replication_slots = number of databases to migrate;

You'll need to restart the source PostgreSQL server after completing all the Online migration prerequisites.

Note

For online migration with Azure Database for PostgreSQL single server, the Azure replication support is set to logical under the replication settings of the single server page in the Azure portal.

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's 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.

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.

Configure your Azure Database for PostgreSQL Flexible Server

• Create the target flexible server. For guided steps, refer to the quickstart Create an Azure Database for PostgreSQL flexible server using the portal.

• Allowlist extensions whose libraries must be loaded at server start. It's essential that the extension is on the allowlist before you initiate a migration.

• Check if the data distribution among a database's tables is skewed, with most of the data present in a single (or few) tables. If it's skewed, the migration speed could be slower than expected. In this case, the migration speed can be increased by migrating the large table in parallel.

Configure the migration task

The migration service comes with a simple, wizard-based experience on the Azure portal. Here's how to start:

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

2. Go to your Azure Database for PostgreSQL Flexible Server target.

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 start a migration from a single server to a flexible server. If this is your first time using the migration service, an empty grid appears with a prompt to begin your first migration.

   

   If you've already created migrations to your Flexible Server target, the grid contains information about migrations that were attempted to this target from the Single Server.

5. Select the Migrate from Single Server button. You go through a wizard-based series of tabs to create a migration into this Flexible Server target from any source Single Server.

Alternatively, you can initiate the migration process from the Azure Database for PostgreSQL Single Server.

1. Open your web browser and go to the portal. To sign in, you must enter your credentials. The default view is your service dashboard.

2. Upon selecting the Single Server, you can observe a migration-related banner in the Overview tab. Select Migrate now to get started.

   

3. You're taken to a page with two options. If you've already created a Flexible Server and want to use that as the target, choose Select existing, and select the corresponding Subscription, Resource group, and Server name details. Once the selections are made, select Go to Migration wizard and skip to the instructions under this page's Setup tab section.

   

4. Should you choose to Create a new Flexible Server, select Create new and select Go to Create Wizard. This action takes you through the Flexible Server creation process and deploys the Flexible Server.

   

After deploying the Flexible Server, follow the steps 3 to 5 under Configure the migration task.

Setup tab

The first tab is Setup. In case you missed it, allowlist necessary extensions as shown in It's essential to allowlist these extensions before you initiate a migration.

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 indicates the source. In this case, it's Azure Database for PostgreSQL Single server

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 only if there are no validation failures.

It's always a good practice to choose Validate or Validate and Migrate option to perform premigration validations before running the migration.

If the Online migration preview is selected, Logical replication must be turned on in the source Single server. If it's not turned on, the migration service automatically turns on logical replication at the source Single server. Replication can also be set up manually under the Replication tab in the Single server-side pane by setting the Azure replication support level to Logical. Either approach restarts the source single server.

Select the Next : Connect to Source button.

Source tab

The Source tab prompts you to give details related to the Single Server, which is the source of the databases.

After you make the Subscription and Resource Group selections, the dropdown list for server names shows Single Servers under that resource group across regions. Select the source that you want to migrate databases from. You can migrate databases from a Single Server to a target Flexible Server in the same region. Cross-region migrations are enabled only for India, China, and UAE servers.

After you choose the Single Server source, the Location, PostgreSQL version, and Server admin login name boxes are populated automatically. The server admin sign-in name is the admin username used to create the Single Server. In the Password box, enter the password for that admin user. The migration service migrates single server databases as the admin user.

After filling out all the fields, select the Connect to source link. This validates that the source server details entered are correct and that the source server is reachable.

Select the Next : Select migration target button to continue.

Target tab

The Target tab displays metadata for the Flexible Server target, such as subscription name, resource group, server name, location, and PostgreSQL version.

For Server admin login name, the tab displays the admin username used during creating the Flexible Server target. Enter the corresponding password for the admin user. After filling out the password, select the Connect to target link. This validates that the target server details entered are correct and target server is reachable.

Select the Next button to select the databases to migrate.

Select Databases for the migration tab

Under this tab, there's a list of user databases inside the Single Server. You can select and migrate up to eight databases in a single migration attempt. If there are more than eight user databases, the migration process is repeated between the source and target servers for the next set of databases. By default, selected databases with the same name on the target are overwritten.

Select the Next button to review the details.

Summary

The Summary tab summarizes all the details for creating the validation or migration. Review the details and select on the start button.

Monitor the migration using Azure portal

After you select the start button, a notification appears in a few seconds to say that the validation or migration creation is successful. You're redirected automatically to the Migration page of Flexible Server. This has a new entry for the recently created validation or migration.

The grid that displays the migrations has these columns: Name, Status, Migration type, Migration mode, Source server, Source server type, Databases, Start time and Duration. 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. You can also select the migration name in the grid to see the associated details.

When the validation or migration is created, it moves to the InProgress state and PerformingPreRequisiteSteps substate. The workflow takes 2-3 minutes to set up the migration infrastructure and network connections.

Let us look at how to monitor migrations for each Migration Option.

Validate

After the PerformingPreRequisiteSteps substate is completed, the validation moves to the substate of Validation in Progress where checks are done on the source and target server to assess the readiness for migration.

The validation moves to the Succeeded state if all validations are either in Succeeded or Warning state.

The validation grid has the

• Validation details for instance and Validation details for databases sections, representing the validation rules used to check migration readiness.
• Validation Status - Represents the result for each rule and can have any of the three values
  • Succeeded - If no errors were found.
  • Failed - If there are validation errors.
  • Warning - If there are validation warnings.
• Duration - Time taken for the Validation operation.
• Start and End time - Start and end time of the validation operation in UTC.

The Validation status moves to Failed state if there are any errors in the validation. Select the Validation name or Database name validation that has failed, and a fan-out pane gives the details and the corrective action you should take to avoid this error.

Migrate

After the PerformingPreRequisiteSteps substrate is completed, the migration moves to the substrate of Migrating Data when the Cloning/Copying of the databases takes place. The time for migration to complete depends on the size and shape of the databases you're migrating. The migration is quick if the data is mostly evenly distributed across all the tables. Skewed table sizes take a relatively longer time.

When you select any of the databases in migration, a fan-out pane appears. It has all the table count - copied, queued, copying, and errors apart from the database migration status.

The migration moves to the Succeeded state when the Migrating Data state finishes successfully. If there's an issue at the Migrating Data state, the migration moves into a Failed state.

Once the migration moves to the Succeeded state, schema and data migration from your Single Server to your Flexible Server target is complete. You can use the refresh button on the page to confirm the same.

Validate and Migrate

In this option, validations are performed first before migration starts. After the PerformingPreRequisiteSteps substate is completed, the workflow moves into the substate 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.

You can see the results of Validate and Migrate once the operation is complete.

Cutover migration

If there's both Migrate and Validate and Migrate, completion of the Online migration requires another step - a Cutover action is required from the user. After the copy/clone of the base data is complete, the migration moves to the WaitingForUserAction state and the WaitingForCutoverTrigger substrate. In this state, user can trigger cutover from the portal by selecting the migration.

Before initiating cutover, it's essential to ensure that:

• Writes to the source are stopped -Latency (minutes) parameter is 0 or close to 0 The Latency (minutes) information can be obtained from the migration details screen as shown below:

Latency (minutes) parameter indicates when the target last synced up with the source. For example, for the Orders Database above, it's 0.33333. It means that the changes that occurred in the last ~0.3 minutes at the source are yet to be synced to the target, for the Orders Database. At this point, writes to the source can be stopped and cutover initiated. In case there's heavy traffic at the source, it's recommended to stop writes first so that Latency (minutes) can come close to 0, and then a cutover is initiated. The Cutover operation applies all pending changes from the source to the Target and completes the migration. If you trigger a "Cutover" even with non-zero Latency, the replication stops until that point in time. All the data on the source until the cutover point is then applied to the target. Say a latency was 15 minutes at the cutover point, so all the change data in the last 15 minutes are applied to the target. Time taken depends on the backlog of changes that occurred in the last 15 minutes. Hence, it's recommended that the latency goes to zero or near zero, before triggering the cutover.

The migration moves to the Succeeded state as soon as the Migrating Data substate or the cutover (in Online migration) finishes successfully. If there's a problem at the Migrating Data substate, the migration moves into a Failed state.

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.

Possible migration substates include:

• PerformingPreRequisiteSteps: Infrastructure set up is underway for data migration.
• Validation in Progress: Validation is in progress.
• MigratingData: Data migration is in progress.
• CompletingMigration: Migration is in final stages of completion.
• Completed: Migration has successfully completed.

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 any further validation activity and the validation moves to a Canceled state. Canceling a migration stops further migration activity on your target server and moves to a Canceled state. It doesn't drop or roll back any changes on your target server. Be sure to drop the databases on your target server involved in a canceled migration.

CLI

You can migrate using Azure CLI.

Offline

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.

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.

Get started

1. If you're new to Microsoft Azure, create an account to evaluate the offerings.

2. Install the latest Azure CLI for your operating system from the Azure CLI installation page.

   If the Azure CLI is already installed, check the version by using the az version command. The version should be 2.50.0 or later to use the migration CLI commands. If not, update your Azure CLI version.

3. Run the az login command:

   az login
   

   A browser window opens with the Azure sign-in page. Provide your Azure credentials to do a successful authentication. For other ways to sign with the Azure CLI, refer this article.

Migration CLI commands (offline)

The migration service comes with easy-to-use CLI commands to do migration-related tasks. All the CLI commands start with az postgres flexible-server migration. It's important to allowlist the extensions before you initiate a migration.

For help with understanding the options associated with a command and with framing the right syntax, you can use the help parameter:

az postgres flexible-server migration --help

The above command gives you the following output:

The output lists the supported migration commands, along with their actions. Let's look at these commands in detail.

Create a migration using the Azure CLI

The create command helps in creating a migration from a source server to a target server:

az postgres flexible-server migration create -- help

The above command gives you the following result:

It lists the expected arguments and has an example syntax for successfully creating a migration from the source server to the target server. Here's the CLI command to create a new migration:

az postgres flexible-server migration create [--subscription]
                                            [--resource-group]
                                            [--name]
                                            [--migration-name]
                                            [--migration-mode]
                                            [--properties]

Parameter	Description
subscription	Subscription ID of the Flexible Server target.
resource-group	Resource group of the Flexible Server target.
name	Name of the 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.
migration-mode	This is an optional parameter. Default value: Offline. Offline migration involves copying of your source databases at a point in time, to your target server.
properties	Absolute path to a JSON file that has the information about the Single Server source.

For example:

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

The migration-name argument used in the create command is used in other CLI commands, such as update, delete, and show. It uniquely identifies the migration attempt in the corresponding actions in all those commands.

Finally, the create command needs a JSON file to be passed as part of its properties argument.

The structure of the JSON is:

{
"properties": {
 "sourceDbServerResourceId":"/subscriptions/<subscriptionid>/resourceGroups/<src_ rg_name>/providers/Microsoft.DBforPostgreSQL/servers/<source server name>",
"secretParameters": {
    "adminCredentials":
    {
      "sourceServerPassword": "<password>",
      "targetServerPassword": "<password>"
    },
   "sourceServerUserName": "<username>@<servername>",
   "targetServerUserName": "<username>"
},

"dbsToMigrate":
   [
   "<db1>","<db2>"
   ],

"overwriteDbsInTarget":"true"

}
}

The create parameters that go into the JSON file format are as shown below:

Parameter	Type	Description
sourceDbServerResourceId	Required	This parameter is the resource ID of the Single Server source and is mandatory.
adminCredentials	Required	This parameter lists passwords for admin users for both the Single Server source and the Flexible Server target. These passwords help to authenticate against the source and target servers.
sourceServerUserName	Required	The default value is the admin user created during the creation of a single 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 source server for performing the migration. This user should have the necessary privileges and ownership of the database objects involved in the migration and should be a member of the azure_pg_admin role.
targetServerUserName	Required	The default value is the admin user created during the creation of a flexible server, and the password provided is used for authentication against this user. In case you aren't using the default user, this parameter is the user or role on the target server used for performing the migration. This user should be a member of azure_pg_admin, pg_read_all_settings, pg_read_all_stats,pg_stat_scan_tables roles and should have the Create role, Create DB attributes.
dbsToMigrate	Required	Specify the list of databases that you want to migrate to Flexible Server. Only user databases are migrated. System databases or template databases such as template0 and template1 won't be migrated.
overwriteDbsInTarget	Required	When set to true, if the target server happens to have an existing database with the same name as the one you're trying to migrate, migration service automatically overwrites the database.
SetupLogicalReplicationOnSourceDBIfNeeded	Optional	You can automatically enable logical replication on the source server by setting this property to true. This change in the server settings requires a server restart with two to three minutes of downtime.
SourceDBServerFullyQualifiedDomainName	Optional	Use it when a custom DNS server is used for name resolution for a virtual network. Provide the FQDN of the Single Server source according to the custom DNS server for this property.
TargetDBServerFullyQualifiedDomainName	Optional	Use it when a custom DNS server is used for name resolution inside a virtual network. Provide the FQDN of the Flexible Server target according to the custom DNS server. SourceDBServerFullyQualifiedDomainName and TargetDBServerFullyQualifiedDomainName are included as a part of the JSON only in the rare scenario that a custom DNS server is used for name resolution instead of Azure-provided DNS. Otherwise, don't include these parameters in the JSON file.

Note these essential points for the command response:

• When the create command is triggered, the migration moves to the InProgress state and the PerformingPreRequisiteSteps substrate. The migration workflow takes a few minutes to deploy the migration infrastructure and set up connections between the source and target.
• After the PerformingPreRequisiteSteps substate is completed, the migration moves to the substrate of Migrating Data, where the Cloning/Copying of the databases takes place.
• Each database migrated has its own 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 as soon as the Migrating Data substate finishes successfully. If there's a problem at the Migrating Data substate, the migration moves into a Failed state.

Setup replication

If the Online migration preview is selected, Logical replication must be turned on in the source Single server. If it isn't turned on, the migration service automatically turns on logical replication at the source Single server when the SetupLogicalReplicationOnSourceDBIfNeeded parameter is passed with a value of true in the accompanying JSON file. Replication can also be set up manually at the source after starting the migration using the command below. Either approach of turning on logical replication restarts the source Single server.

For example:

az postgres flexible-server migration update --subscription 11111111-1111-1111-1111-111111111111 --resource-group my-learning-rg --name myflexibleserver --migration-name CLIMigrationExample --setup-replication

This command is required to advance the migration when the flexible server is waiting in the WaitingForLogicalReplicationSetupRequestOnSourceDB state.

To perform Online migration in any of the above regions, use:

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

List the migrations

The list command lists all the migration attempts made to a Flexible Server target:

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

The filter parameter has two options:

• Active: Lists the current active migration attempts (in progress) into the target server. It doesn't include the migrations that have failed, canceled, or succeeded.
• All: Lists all the migration attempts into the target server. This includes both the active and past migrations, regardless of the state.

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

az postgres flexible-server migration list -- help

Monitor the migration

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]

The migration_name parameter is the name assigned to the migration during the create command. Here's a snapshot of the sample response from the CLI command for showing details:

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

 az postgres flexible-server migration show -- help

The following tables describe the migration states and substates.

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.

Migration substate	Description
PerformingPreRequisiteSteps	Infrastructure is set up and is prepped for data migration.
MigratingData	Data migration is in progress.
CompletingMigration	Migration cutover is in progress.
Completed	cutover was successful, and migration is complete.

Online (preview)

Prerequisites (online)

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.

Set up Online migration parameters

For Online migration, the replication support should be set to Logical under replication settings of the source PostgreSQL server. In addition, the server parameters max_wal_senders and max_replication_slots values should be equal to the number of Databases that need to be migrated. They can also be configured in the command line using the following commands:

• ALTER SYSTEM SET wal_level = logical;
• ALTER SYSTEM SET max_wal_senders = number of databases to migrate;
• ALTER SYSTEM SET max_replication_slots = number of databases to migrate;

You'll need to restart the source PostgreSQL server after completing all the Online migration prerequisites.

Note

For online migration with Azure Database for PostgreSQL single server, the Azure replication support is set to logical under the replication settings of the single server page in the Azure portal.

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's 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.

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.

Get started

1. If you're new to Microsoft Azure, create an account to evaluate the offerings.

2. Install the latest Azure CLI for your operating system from the Azure CLI installation page.

   If the Azure CLI is already installed, check the version by using the az version command. The version should be 2.50.0 or later to use the migration CLI commands. If not, update your Azure CLI version.

3. Run the az login command:

   az login
   

   A browser window opens with the Azure sign-in page. Provide your Azure credentials to do a successful authentication. For other ways to sign with the Azure CLI, refer this article.

Migration CLI commands (online)

The migration service comes with easy-to-use CLI commands to do migration-related tasks. All the CLI commands start with az postgres flexible-server migration. It's important to allowlist the extensions before you initiate a migration.

For help with understanding the options associated with a command and with framing the right syntax, you can use the help parameter:

az postgres flexible-server migration --help

The above command gives you the following output:

The output lists the supported migration commands, along with their actions. Let's look at these commands in detail.

Create a migration using the Azure CLI

The create command helps in creating a migration from a source server to a target server:

az postgres flexible-server migration create -- help

The above command gives you the following result:

It lists the expected arguments and has an example syntax for successfully creating a migration from the source server to the target server. Here's the CLI command to create a new migration:

az postgres flexible-server migration create [--subscription]
                                            [--resource-group]
                                            [--name]
                                            [--migration-name]
                                            [--migration-mode]
                                            [--properties]

Parameter	Description
subscription	Subscription ID of the Flexible Server target.
resource-group	Resource group of the Flexible Server target.
name	Name of the 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.
migration-mode	This is an optional parameter. Default value: Offline. Offline migration involves copying of your source databases at a point in time, to your target server.
properties	Absolute path to a JSON file that has the information about the Single Server source.

For example:

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

The migration-name argument used in the create command is used in other CLI commands, such as update, delete, and show. It uniquely identifies the migration attempt in the corresponding actions in all those commands.

Finally, the create command needs a JSON file to be passed as part of its properties argument.

The structure of the JSON is:

{
"properties": {
 "sourceDbServerResourceId":"/subscriptions/<subscriptionid>/resourceGroups/<src_ rg_name>/providers/Microsoft.DBforPostgreSQL/servers/<source server name>",
"secretParameters": {
    "adminCredentials":
    {
      "sourceServerPassword": "<password>",
      "targetServerPassword": "<password>"
    },
   "sourceServerUserName": "<username>@<servername>",
   "targetServerUserName": "<username>"
},

"dbsToMigrate":
   [
   "<db1>","<db2>"
   ],

"overwriteDbsInTarget":"true"

}
}

The create parameters that go into the JSON file format are as shown below:

Parameter	Type	Description
sourceDbServerResourceId	Required	This parameter is the resource ID of the Single Server source and is mandatory.
adminCredentials	Required	This parameter lists passwords for admin users for both the Single Server source and the Flexible Server target. These passwords help to authenticate against the source and target servers.
sourceServerUserName	Required	The default value is the admin user created during the creation of a single 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 source server for performing the migration. This user should have the necessary privileges and ownership of the database objects involved in the migration and should be a member of the azure_pg_admin role.
targetServerUserName	Required	The default value is the admin user created during the creation of a flexible server, and the password provided is used for authentication against this user. In case you aren't using the default user, this parameter is the user or role on the target server used for performing the migration. This user should be a member of azure_pg_admin, pg_read_all_settings, pg_read_all_stats,pg_stat_scan_tables roles and should have the Create role, Create DB attributes.
dbsToMigrate	Required	Specify the list of databases that you want to migrate to Flexible Server. Only user databases are migrated. System databases or template databases such as template0 and template1 won't be migrated.
overwriteDbsInTarget	Required	When set to true, if the target server happens to have an existing database with the same name as the one you're trying to migrate, migration service automatically overwrites the database.
SetupLogicalReplicationOnSourceDBIfNeeded	Optional	You can automatically enable logical replication on the source server by setting this property to true. This change in the server settings requires a server restart with two to three minutes of downtime.
SourceDBServerFullyQualifiedDomainName	Optional	Use it when a custom DNS server is used for name resolution for a virtual network. Provide the FQDN of the Single Server source according to the custom DNS server for this property.
TargetDBServerFullyQualifiedDomainName	Optional	Use it when a custom DNS server is used for name resolution inside a virtual network. Provide the FQDN of the Flexible Server target according to the custom DNS server. SourceDBServerFullyQualifiedDomainName and TargetDBServerFullyQualifiedDomainName are included as a part of the JSON only in the rare scenario that a custom DNS server is used for name resolution instead of Azure-provided DNS. Otherwise, don't include these parameters in the JSON file.

Note these essential points for the command response:

• When the create command is triggered, the migration moves to the InProgress state and the PerformingPreRequisiteSteps substrate. The migration workflow takes a few minutes to deploy the migration infrastructure and set up connections between the source and target.
• After the PerformingPreRequisiteSteps substate is completed, the migration moves to the substrate of Migrating Data, where the Cloning/Copying of the databases takes place.
• Each database migrated has its own 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 as soon as the Migrating Data substate finishes successfully. If there's a problem at the Migrating Data substate, the migration moves into a Failed state.

Setup replication

If the Online migration preview is selected, Logical replication must be turned on in the source Single server. If it isn't turned on, the migration service automatically turns on logical replication at the source Single server when the SetupLogicalReplicationOnSourceDBIfNeeded parameter is passed with a value of true in the accompanying JSON file. Replication can also be set up manually at the source after starting the migration using the command below. Either approach of turning on logical replication restarts the source Single server.

For example:

az postgres flexible-server migration update --subscription 11111111-1111-1111-1111-111111111111 --resource-group my-learning-rg --name myflexibleserver --migration-name CLIMigrationExample --setup-replication

This command is required to advance the migration when the flexible server is waiting in the WaitingForLogicalReplicationSetupRequestOnSourceDB state.

To perform Online migration in any of the above regions, use:

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

List the migrations

The list command lists all the migration attempts made to a Flexible Server target:

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

The filter parameter has two options:

• Active: Lists the current active migration attempts (in progress) into the target server. It doesn't include the migrations that have failed, canceled, or succeeded.
• All: Lists all the migration attempts into the target server. This includes both the active and past migrations, regardless of the state.

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

az postgres flexible-server migration list -- help

Monitor the migration

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]

The migration_name parameter is the name assigned to the migration during the create command. Here's a snapshot of the sample response from the CLI command for showing details:

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

 az postgres flexible-server migration show -- help

The following tables describe the migration states and substates.

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.

Migration substate	Description
PerformingPreRequisiteSteps	Infrastructure is set up and is prepped for data migration.
MigratingData	Data migration is in progress.
CompletingMigration	Migration cutover is in progress.
Completed	cutover was successful, and migration is complete.

Cutover the migration

In Online migrations, after the base data migration is complete, the migration task moves to WaitingForCutoverTrigger substate. In this state, user can trigger cutover through CLI using the command below. The cutover can also be triggered from the portal by selecting the migration name in the migration grid.

For example:

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

Before initiating cutover, it's important to ensure that:

• Writes to the source are stopped -latency parameter decreases to 0 or close to 0

latency parameter indicates when the target last synced up with the source. For example, here it's 201 and 202 for the two databases as shown in the picture below. It means that the changes that occurred in the last ~200 seconds at the source are yet to be synced to the target. At this point, writes to the source can be stopped and cutover initiated. In case there's heavy traffic at the source, it's recommended to stop writes first so that Latency can come close to 0, and then cutover is initiated. The Cutover operation applies all pending changes from the source to the Target and completes the migration. If you trigger a "Cutover" even with nonzero Latency, the replication stops until that point in time. All the data on the source until the cutover point is then applied to the target. Say a latency was 15 minutes at the cutover point, so all the change data in the last 15 minutes applies to the target. The time taken depends on the backlog of changes that occurred in the last 15 minutes. Hence, it's recommended that the latency goes to zero or near zero, before triggering the cutover. The latency information can be obtained using the migration show command. Here's a snapshot of the migration before initiating the cutover:

After the cutover is initiated, all transactions that happened during the base copy are copied sequentially to the target, and migration is completed.

If the cutover isn't successful, the migration moves to the Failed state.

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

 az postgres flexible-server migration update -- help

Cancel the migration using CLI

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

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