Migrate to Azure PostgreSQL Flexible Server

I need to migrate from a single server to a flex server. Instead of doing a dump and restore, I’m going to try out the migration service that Azure provides.

Single vs Flex

Why Migrate to Flexible Server?

• High availability and disaster recovery: Flexible Server provides higher availability with zone-redundant architecture.
• Customizable maintenance windows: More control over when updates and maintenance tasks occur.
• Performance improvements: Fine-tuned scaling and performance adjustments without downtime.
• Enhanced security: With VNet integration and more advanced networking options.

When is Single Server Going Away?

Microsoft is deprecating Azure PostgreSQL Single Server and encouraging users to migrate to the Flexible Server offering. The Single Server will be retired by March 2025.

• No new features are being added to Single Server, so users should migrate to Flexible Server well before the deadline.
• The Azure Database Migration Service helps facilitate the migration from Single Server to Flexible Server, offering both online and offline options.

Azure PostgreSQL Single Server Versus Flexible Server Features

I thought Single Server handled one thing WAY better than Flexible Server: Entra (AD) group authentication. Single Server was nice, easy, and smooth, but now Flex requires constant token refreshing and re-entering, which is very frustrating.

Offline vs. online migration

When migrating from Azure Database for PostgreSQL—Single Server to Flexible Server using the Azure Database Migration Service (DMS), you can choose between two migration approaches: online migration and offline migration. The primary difference between them is how much downtime is involved and how the migration is performed.

Online Migration

Purpose: Minimize downtime during migration.

How it Works:

• Initial Full Backup: A full data backup is taken from the source (Single Server) and restored to the target (Flexible Server).
• Continuous Replication: Changes made to the source database during the migration are continuously replicated to the target database.
• Switchover: When you’re ready to finalize the migration, a brief downtime is required to apply the last set of changes and switch the application to the new Flexible Server.

  Pros:

• Minimal Downtime: Suitable for applications that need to remain available and experience minimal disruption.
• Data Consistency: Since the data is being replicated continuously when you switch over, the data on the target is almost identical to the source.
• Testing: You can verify that the data is being migrated successfully while the source database remains live.

  Cons:

• Slightly More Complex: Requires continuous monitoring of the replication and management of the cutover process.
• Use Cases: Critical applications where downtime must be minimized, such as production environments.

Offline Migration

Purpose: Simpler, but involves more downtime.

How it Works:

• Full Data Transfer: The source database (Single Server) is taken offline, and a full database backup is migrated to the target (Flexible Server).
• Downtime: The application connected to the database must remain offline until the entire migration process is complete.

  Pros:

• Simplicity: Easier to manage and perform than online migration because there’s no need to replicate data or handle cutover scenarios continuously.
• Fast for Small Databases: It can be faster for smaller databases since there is no ongoing replication.

  Cons:

• Extended Downtime: The database and connected applications must remain offline for the entire migration process. This could lead to significant downtime, especially for large databases.
• No Continuous Replication: There is no real-time syncing between source and target databases, so once the source is offline, any changes made to it during migration will not be reflected in the target.
• Use Cases: Non-critical applications or smaller databases where extended downtime is acceptable, such as development or staging environments.

Summary of Key Differences

Which Should You Choose?

• Online Migration: If your application cannot tolerate significant downtime, you must keep it running while the migration occurs.
• Offline Migration: If you can afford the downtime and prefer a more straightforward approach.

Prerequisites for offline

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

• Verify source version – must be >= 9.5
• SKU choice – The SKU chosen for the Azure Database for PostgreSQL should correspond with the specifications of the source database to ensure compatibility and adequate performance.
• Change the password_encryption server parameter on your flexible server from SCRAM-SHA-256 to MD5 before initiating the migration. This is essential for the existing credentials on a single server to work on your flexible server.
• PostgreSQL flexible server must be deployed and properly configured
  • Make sure to choose an appropriate SKU to match the compatibility and performance of your single server
  • For guided steps, refer to the quickstart Create an Azure Database for PostgreSQL flexible server using the portal.
  • Verify extensions on your source PostgreSQL instance. Make sure to analyze your extension to allow/enable accordingly.
    • To see the list of extensions installed on your PostgreSQL instance, use this query, making sure to run this in each db you want to migrate:

      SELECT name, installed_version

      FROM pg_available_extensions

      WHERE installed_version is not null

      ORDER BY name;

    • On Single server, it looks like these are installed by default:
      • pg_buffercache
      • pg_stat_statements
      • plpgsql
    • On Flex server, it looks like only this one is installed by default:
      • plpgsql
    • For more information about extensions, visit Extensions in Azure Database for PostgreSQL.
  • Allowlist extensions whose libraries must be loaded at server start. The extension must be on the allowlist before you initiate a migration. I’ve gone ahead and selected the two additional extensions that are on Single server. You can’t enable them until they are allowed.

• Enable extensions—Ensure that they are enabled on the destination. In this case, the two that are allowed are on the existing Single server.

  CREATE EXTENSION IF NOT EXISTS pg_buffercache;

  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

• Network setup – make sure the servers can communicate with each other.
  • For information about network setup, visit Network guide for migration service.
• Check the server parameters—Match them on the destination by comparing them to the source. Access the Server parameters section in the Azure portal and manually update the values accordingly. Save the parameter changes and, if necessary, restart the flexible server to apply the new configuration.

  Use this query to check for changed parameters:

  SELECT NAME, SETTING, RESET_VAL, SOURCE, SHORT_DESC

  FROM PG_SETTINGS

  WHERE SETTING <> RESET_VAL

  AND SOURCE <> ‘default’;

  Change parameters accordingly on the flex compared with your single server. It appears to be best to change these parameters via the portal rather than using an SQL query.

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

• Disable high availability and read replicas in the target – These features should be enabled only after the 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.

Prerequisites for online

Besides what you do with offline above, you need these things. The parameters can be set in the Azure portal under Settings->Server Parameters or 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 + 1;
• ALTER SYSTEM SET max_replication_slots = number of databases to migrate + 1;

For more information, visit https://learn.microsoft.com/en-us/azure/postgresql/migrate/migration-service/tutorial-migration-service-single-to-flexible?tabs=portal%2Conline

Configuring the migration task

So you’ve done all the configuration steps above. Now I’m going to walk through the offline migration. My databases are small and can have a bit of downtime. If your databases are large and can’t have downtime, then it will be best to do it online. The migration service comes with a simple, wizard-based experience on the Azure portal.

Note: You can start the migration from the single server side, which is on the overview page. But I’m going to start from the flex server side.

It’s easy to get started in the portal. Go to your flex server under Migration and click Create.

Validate

First, I will validate the migration.

I won’t need the runtime server because I don’t have private endpoints. For more information about the Runtime Server, visit the Migration Runtime Server.

Next, you’ll connect to the source single server. 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.

Then connect to the target flex server.

Then, select which databases you want to migrate. You can choose 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. Selected databases on the target server with the same names are overwritten.

Then you get a summary page, and in this case, you click Validate since that’s all I wanted to do.

Now, you will see the migration dashboard with a migration listed.

You can click on it to get more details

The validation succeeded, so now I can plan the migration.

Validate and migrate

When I’m ready to migrate, I will validate and migrate to ensure it can migrate correctly. There is an option only to migrate, but I would never use this. I feel like it’s always best to validate first.

Fill out the rest of the steps, then return to the dashboard.

In the details, you will see its status.

It’s very fast with tiny databases.

Try to make it fail

Let’s see what happens when we get an error. I will enable an extension in another db and try to migrate it without allowing/enabling it in the destination server. CREATE EXTENSION "uuid-ossp";

Now, I will try to validate and migrate that db from single to flex. Nope, it didn’t fail at all. The extension is allowed and enabled on the migrated database. Completely fine migration. Maybe it’s because there are no tables in that database using that extension?

So maybe it’s not critically urgent to get all that in place before the migration, but still, best practice to ensure it will be smooth.

If you do wind up having errors, you can see them in the migration.

Check the migration once complete

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. Source: https://learn.microsoft.com/en-us/azure/postgresql/migrate/migration-service/tutorial-migration-service-single-to-flexible?tabs=portal%2Coffline#check-the-migration-once-complete

After migration, you can perform the following tasks:

• Monitor the database performance closely to see if it requires performance tuning.
• 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.

I will update this post as I go through our production offline migration and let you know if there are any other things you need to be aware of.

The post Migrate to Azure PostgreSQL Flexible Server appeared first on sqlkitty.