Single to Flexible server PostgreSQL migration tool is now in Preview!

This article is contributed. See the original author and article here.

Many enterprises deploy applications using Azure Database for PostgreSQL Single Server, a fully managed Postgres database service, that is best suited for minimal customizations of your database. In November 2021, we announced that the next generation of the Azure Database for PostgreSQL, Flexible Server, was generally available. Since then, customers have been using methods like dump/restore, Azure Database Migration Service (DMS), and custom scripts to migrate their databases from Single to Flexible Server.

 

If you are a user of Azure Database for PostgreSQL Single Server, and looking to migrate to Postgres Flexible Server, At Microsoft, our mission is to empower every person and organization on the planet to achieve more. It’s this mission that drives our firm commitment to collaborate and contribute to the Postgres community and invest in bringing the best migration experience to all our Postgres users.

Why migrate to Azure Database for PostgreSQL Flexible Server?

 

If you are not familiar with Flexible server, it provides you with the simplicity of a managed database service together with maximum flexibility over your database, and built-in cost-optimization controls. Azure Database for PostgreSQL Flexible Server is the next generation Postgres service in Azure and offers a robust value proposition and benefits including:

 

1. Infrastructure: Linux-based VMs, premium managed disks, zone-redundant backups


2. Cost optimization/Dev friendly: Burstable SKUs, start/stop features, default deployment choices


3. Additional Improvements over Single Server: Zone-redundant High Availability (HA), support for newer PG versions, custom maintenance windows, connection pooling with PgBouncer, etc.

 

Learn more about why Flexible Server is the top destination for Azure Database for PostgreSQL

• Top 10 reasons to choose Flexible Server  


• What is exciting in Flexible Server


• Modernize your applications using Flexible Server

 

Why do you need the Single to Flexible Server PostgreSQL migration tool?

 

Now, let’s go over some of the nuances of migrating data from Single to Flexible Server.

Single Server and Flexible Server run on different OS platforms (Windows vs Linux) with different physical layouts (Basic/ 4TB/16TB storage Vs Managed disk), and different collation. Flexible Server supports PostgreSQL DB versions 11 and above. If you are using PG 10 and below on Single Server (those are retired, by the way), you must make sure your application is compatible with higher versions. These challenges prohibit us from making a simple physical copy of the data. Only a logical data copy, such as dump/restore (offline mode), or logical decoding (online mode) can be performed.

 

While the offline mode of migration is simple, many customers prefer an online migration experience that keeps your source PostgreSQL server up and operational until the cutover, however, there is a lengthy checklist of things to be aware of during this process. These steps include

1. Setting the source’s WAL_LEVEL to LOGICAL


2. Schema copy


3. Creation of databases at the target,


4. Disabling foreign keys and triggers at the target.


5. Enabling them post migration, and so on.

Here is where the Single to Flexible migration tool comes into help to make the migration experience simpler for you.

 

What is Single to Flexible Server PostgreSQL migration tool?

 

This migration tool is tailormade to alleviate the pain of migrating the PostgreSQL schema and data from Single to Flexible Server. This tool offers a seamless migration experience, while abstracting the migration complexities under the hood. This tool is targeted for less than 1 TiB database size. This migration tool automates the majority of the migration steps, allowing you to focus on minor administration and pre-requisite tasks.

 

You can either use the Azure portal or Azure CLI to perform online or offline migrations.

 

• Offline mode: This offers a simple database dump and restore of the data and schema and is best for smaller sized databases where downtime can be afforded.


• Online mode: This mode of migration performs data dump and restore, and initiates change data capture using Postgres logical decoding and is best in scenarios where downtime cannot be afforded.

For a detailed comparison, see this documentation.

 

Figure 1: Block diagram to show the migration feature steps and processes

 

1. Create a Flexible PostgreSQL server (public or VNET)


2. Invoke migration from Azure Portal or Azure CLI and choose databases to migrate.


3. A Migration infrastructure is provisioned (DMS) on your behalf


4. Initiates migration 
   
   
   
   • (4a) Initial dump/restore (online & offline)
   
   
   • (4b) streams the changes using logical decoding – CDC (online only)
   
   
   
   


5. You can then cutover if you are doing online migration. If you are using offline mode, then post restore, the cutover is automatically performed.

What are the steps involved for a successful migration?

 

1. Planning for Single to Flexible PostgreSQL server migration

 

This is a very critical step. Some of the steps in the planning phase include:

1. Getting the list of source Single servers, SKUs, storage, public/private access (Discovery)


2. Single server provides Basic, General purpose, and Memory optimized SKUs. Flexible Server offers Burstable SKUs, General purpose, and Memory optimized SKUs. While General purpose and Memory optimized SKUs can be migrated to the equivalent SKUs, for your Basic SKU, you can consider either Burstable SKU or General purpose SKU depending in your workload. See Flexible server compute & storage documentation for details.


3. Get the list of each database in the server, the size, and extensions usage. If your database sizes are larger than 1TiB, then we recommend you reach out to your account team or contact us at AskAzureDBforPostgreSQL@service.microsoft.com to help with your migration requirement..


4. Decide on the mode of migration. This may require batching of databases for each server.


5. You can also choose to do a different Database:Server layout compared to Single Server. For example, you can choose to consolidate databases on Flexible (or) you want to spread out databases across multiple Flexible servers.


6. Plan for the day/time for migrating your data to make sure your activity is reduced at the source server.

2. Migration pre-requisites

 

Once you have the plan in place, take care of few pre-requisites. For example

1. Provision Flexible Server in public/VNET with the desired compute tier.


2. Set the source Single Server WAL level to LOGICAL.


3. Create an Azure AD application and register with the source server, target server, and the Azure resource group.

3. Migrating your databases from Single to Flexible Server PostgreSQL

 

Now that you have taken care of pre-requisites, you can invoke migration using the Azure Portal or Azure CLI. Create one or more migration tasks using Azure portal or Azure CLI. High-level steps include

1. Select the source server


2. Choose up to 8 databases per migration task


3. Choose online or offline mode of migration


4. Select network to deploy the migration infrastructure (if using private network).


5. Create the migration

Following the invocation of the migration, the source and the target server and details are validated before initiating the migration infrastructure using Azure DMS, copying of schema, perform dump/restore steps, and if doing online then continue with CDC.

6. Verify data at the target


7. If doing online migration, when ready, perform cutover.

 

4. Post migration tasks

 

Once the data is available in the target Flexible PostgreSQL server, perform post-migration tasks including copying of roles, recreating large objects and copying of settings such as server parameters, firewall rules, monitoring alerts, and tags to the target Flexible server.

 

 

What are the limitations?

 

You can find a list of limitations in this documentation.

 

What do our early adopter customers say about their experience?

 

We’re thankful to our many customers who have evaluated the managed migration tooling and trusted us with migrating their business-critical applications to Azure Database for PostgreSQL Flexible Server. Customers appreciate the migration tool’s ease of use, features, and functionality to migrate from different Single Server configurations. Below are comments from a few of our customers who have migrated to the Flexible Server using the migration tool.

 

Allego, a leading European public EV charging network, continued to offer smart charging solutions for electric cars, motors, buses and trucks without interruption. Electric mobility increases the air quality of our cities and reduces noise pollution. To fast forward the transition towards sustainable mobility Allego believes that anyone with an electric vehicle should be able to charge whenever and wherever they need. That’s why we have partnered with Allego and are working towards providing simple, reliable and affordable charging solutions. Read more about the Allego story here.

 

 

“The Single to Flexible migration tool was critical for us to have a minimal downtime. While migrating 30 databases with 1.4TB of data across Postgres versions, we had both versions living side-by-side until cutover and with no impact to our business.”   Mr. Oliver Fourel, Head of EV Platform, Allego

 

Digitate is a leading software provider bringing agility, assurance, and resiliency to IT and business operations.

“We had a humongous task of upgrading 50+ PostgreSQL 9.6 of database size 100GB+ to a higher version in Flexible Postgres server. Single to Flexible Server Migration tool provided a very seamless approach for migration. This tool is easy to use with minimum downtime.” Vittal Shirodkar, Azure specialist, Digitate

 

Why don’t you try migrating your Single server to Flexible PostgreSQL server?

 

• If you haven’t explored Flexible server yet, you may want to start with Flexible Server docs, which provide a great place to roll up your sleeves. Also visit our website to learn more about our Azure Database for PostgreSQL managed service. 


• Check out the Single Server to Flexible server migration tool demo in the Azure Database for PostgreSQL migration on-demand webinar.


• We look forward to helping you have a pleasant migration experience to Flexible Server using the migration feature. If you would like to reach out us about the migration experience or migrations to PostgreSQL in general, you can always reach out to our product team via email at Ask Azure DB for PostgreSQL.

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.