Tutorial: Migrate offline from AWS RDS PostgreSQL to Azure Database for PostgreSQL with the migration service
This article explores how to migrate your PostgreSQL database from AWS RDS to Azure Database for PostgreSQL offline.
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 server.
- Prerequisites
- Perform the migration
- Monitor the migration
- Check the migration when completed
Prerequisites
To complete the migration, you need the following prerequisites:
Before starting the migration with the Azure Database for PostgreSQL migration service, it is important to fulfill the following prerequisites, specifically designed for offline migration scenarios.
- Verify the source version
- Configure target setup
- Configure network setup
- Enable extensions
- Check server parameters
- Check users and roles
- Disable high availability (reliability) and read replicas in the target
Verify the source version
The source PostgreSQL server version must be 9.5 or later.
If the source PostgreSQL version is less than 9.5, upgrade it to 9.5 or higher before you start the migration.
Configure target setup
Before you begin the migration, you must set up an Azure Database for PostgreSQL in Azure.
The SKU chosen for the Azure Database for PostgreSQL should correspond with the specifications of the source database to ensure compatibility and adequate performance.
Configure network setup
Network setup is crucial for the migration service to function correctly. Ensure that the source PostgreSQL server can communicate with the target Azure Database for PostgreSQL server. The following network configurations are essential for a successful migration.
For information about network setup, visit Network guide for migration service.
Enable extensions
To ensure a successful migration with the migration service in Azure Database for PostgreSQL, you might need to verify extensions to your source PostgreSQL instance. Extensions provide additional functionality and features that might be required for your application. Make sure to verify the extensions on the source PostgreSQL instance before initiating the migration process.
Enable supported extensions identified in the source PostgreSQL instance on the target Azure Database for PostgreSQL flexible server.
For more information about extensions, visit Extensions in Azure Database for PostgreSQL.
Note
A restart is required when there are any changes to the shared_preload_libraries
parameter.
Check 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.
Check 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.
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.
Perform the migration
You can migrate by using the Azure portal or the Azure CLI.
The Azure portal provides a simple and intuitive wizard-based experience that guides you through migration. Following the steps outlined in this tutorial, you can seamlessly transfer your database to Azure Database for PostgreSQL - Flexible Server and take advantage of its powerful features and scalability.
To migrate with the Azure portal, you first configure the migration task, connect to the source and target, and then perform the migration.
Configure the migration task
The migration service comes with a simple, wizard-based experience on the Azure portal.
Open your web browser and go to the portal. Enter your credentials to sign in. The default view is your service dashboard.
Go to your Azure Database for the PostgreSQL flexible server.
In the Overview tab of the flexible server, on the left menu, scroll down to Migration and select it.
Select the Create button to migrate from AWS RDS to a flexible server.
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 are already created, the grid now contains information about attempted migrations.
Select the Create button to go through a wizard-based series of tabs to perform a migration.
Setup
The user needs to provide multiple details related to the migration, such as 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 AWS RDS for PostgreSQL.
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. If there are no validation failures, the migration is triggered.
Choosing the Validate or Validate and Migrate option is always a good practice for performing 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.
Select Runtime Server
The migration Runtime Server is a specialized feature within the migration service, designed to act as an intermediary server during migration. It's a separate Azure Database for PostgreSQL - Flexible Server instance that isn't the target server but is used to facilitate the migration of databases from a source environment that is only accessible via a private network.
For more information about the Runtime Server, visit the Migration Runtime Server.
Connect to 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 the target and source. Once the connection is successful, users can proceed to the next step; they need to identify the networking issues between the target and source and verify the username/password for the source. Establishing a test connection takes a few minutes.
After the successful test connection, select the Next: Select Migration target button.
Select migration 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 target's username/password. 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 database 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 redirected to the flexible server Migration page instance. 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 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, 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.
Some possible migration states:
Migration states
State | Description |
---|---|
InProgress | The migration infrastructure setup is underway, or the actual data migration is in progress. |
Canceled | The migration is canceled or deleted. |
Failed | The migration 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. |
Migration substates
Substate | Description |
---|---|
PerformingPreRequisiteSteps | Infrastructure setup is underway for data migration. |
Validation in Progress | Validation is in progress. |
MigratingData | Data migration is in progress. |
CompletingMigration | Migration is in the final stages of completion. |
Completed | Migration has been completed. |
Failed | Migration has failed. |
Validation substates
Substate | Description |
---|---|
Failed | Validation has failed. |
Succeeded | Validation is successful. |
Warning | Validation is in warning. |
Cancel the migration
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 migration stops further migration activity on your target server and moves to a Canceled state. The cancel action rolls back all changes made by the migration service on your target server.
Check the migration when complete
After completing the databases, you need to manually validate the data between the source, and the 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, such as tags, alerts, and firewall rules (if applicable), from the source instance to the flexible server.
- Make changes to your application to point the connection strings to a flexible server.
- Monitor the database performance closely to see if it requires performance tuning.