How I Migrated to Azure PostgreSQL Flex from Single Server

I did a couple of posts previously on dumping/restoring Azure PostgreSQL databases and also using the Azure migration tool. I had to ultimately do a combo of those because of the limitations of the Azure migration tool.

Getting your roles in place

The Azure migration process does not handle this, so you need to do this yourself.

First, I dumped the roles into a sql file.

export PGPASSWORD=yourpasswordhere
pg_dumpall -h sourcepgserver.postgres.database.azure.com -U username@sourcepgserver --roles-only -p 5432 > /yourfilepath/source_roles_dump.sql

Once it’s dumped, I had to modify the ALTER on the roles to ensure it doesn’t fail.

Note: To run these scripts, I will be logged in as the server admin.

On all of my alter roles, I had to remove NOSUPERUSER, or it gives an error saying you don’t have the perms to grant superuser — even though you aren’t trying to.

So taking out NOSUPERUSER:

So it becomes this way for all the ALTERs

CREATE ROLE rolename;
ALTER ROLE rolename WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md507b933cabb78e11c8d3562bd5c95b7e0';

Also, you will need to modify your role memberships if you have roles acting as groups. When you dump out the roles, it has a GRANTED BY component and that won’t work on your PG flex server as the admin will be different (in my case anyway, because I’m merging three single servers into one PG flex server.) I took off the bit in the pink box below.

So it becomes:

GRANT read_only to rolename;

Once you have your roles in place, you are ready to migrate the db in Azure. Theoretically, you could do this after the Azure migration part, but this is always how I did it old school with pg by putting the globals (roles) in place first. But old school dump/restore, I had the db perms mixed in with the schema dump—anyway, I still do the globals first.

I also have something fancier to do, which is I can’t just move over the db_read, and db_rw roles and let them be granted to all the databases as I’m migrating three different servers into one. To facilitate this, I will create db specific roles to put other roles into. This is more a note to myself for later, but if it helps you, then great.

CREATE ROLE dbname1_ro;
CREATE ROLE dbname1_rw;
CREATE ROLE dbname1_owner; --to mimic old owner perms for the app that was granted that level of perms, unfortunately (for now)
CREATE ROLE dbname2_ro;
CREATE ROLE dbname2_rw;
CREATE ROLE dbname2_owner;
CREATE ROLE dbname3_ro;
CREATE ROLE dbname3_rw;
CREATE ROLE dbname3_owner;

Using the Azure migration to migrate your user db(s)

First things first, as you already know, not everything migrates with your db when using the Azure migration. This was thorny for me regarding roles and their associated perms, but that’s covered in the previous section. Other things, the migration service doesn’t migrate for you:

• Firewall Rules
• Server Parameters
• Tags
• Alerts
• AAD Users

Here are some more limitations.

If you want more details on the pros and cons of Flex and some additional options, please see my previous post.

Here, I will cover the offline migration version. My databases are tiny, so this is the most efficient way to migrate them. I can also have a bit of downtime. If yours are large and you can’t afford downtime, you will want to do an online migration.

Before you configure your migration task

NOTE: Scale your PG Flex server to at least 4 vCores (Standard_D4ds_v4), or your migration will be VERY slow, even if your databases are tiny. Also, don’t use burstable during your migration—per Microsoft. I agree it doesn’t work well and runs very slowly. Scale to your migration needs and scale back down afterwards.

Don’t scale up to gigantic vCores if you don’t need that because it turns out your migration could fail, at least mine did with my small databases (approx 5-10 GB). 4 vCores was the right balance and I wound up with about a 10 minute migration. There’s a table provided with approximate timing based on db sizes with 4 vCores. Test yours to make sure it’s in alignment with your allowed outage window because it wasn’t as fast as that table says.

You also need to account for all these prerequisites

They are different for online and offline, so determine which you are doing.

• 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
  • 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 start a migration. I’ve selected the two additional extensions that are on my Single server. You can’t enable them until they are allowed.

• Enable extensions—Ensure that they are enabled on the destination. Here, 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 change 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. A skewed data distribution could slow migration speed. In this case, migrating the large table in parallel can increase migration speed.

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.

Before you configure the migration task, you need a flex server. We use Terraform where I work, so that’s how I set mine up.

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. Only India, China, and UAE servers enable cross-region migrations.

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 more than eight user databases exist, repeat the migration process between source and target servers for the next set of databases. The migration process overwrites matching databases on the target server.

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 quick with tiny databases.

Your migration is not over, yet

So, the Azure migration copied your schema and data, but you aren’t done yet. It’s not as bad as that sign above makes it seem, though, but you will need to script out your role perms in the single server db and apply them to the flex db.

You will need to dump GRANTs, REVOKEs, and ALTER DEFAULT PRIVILEGES using the following script.

export PGPASSWORD=yourpassword
pg_dump -v -h sourcepgserver.postgres.database.azure.com -U username@sourcepgserver -d yourdbname -s | grep -E '^(GRANT|REVOKE|ALTER DEFAULT PRIVILEGES)' > /yourfilepath/db_grants_revokes_defaultprivs.sql

Note: To run these scripts in PG flex, I am logged in as the server admin, as shown in the pink box below.

You need to change the owner of the db to the server admin name, otherwise you can’t apply default privileges easily. If you aren’t applying default privileges, this isn’t so important, but I think having those is an important part of granting perms in PG because you will inevitably want future objects granted the same perms as existing objects. In my case, it’s this admin from the Azure portal in the pink box.

ALTER DATABASE yourdbname owner to youradminuser; 

Then exec that file with psql:

export PGPASSWORD=yourpassword
psql -h targetpgserver.postgres.database.azure.com -p 5432 -U adminusername -d yourdbname -f /yourfilepath/db_grants_revokes_defaultprivs.sql

Theoretically, that all works, and your migration is “complete.” Validate and verify with the last section.

Check the migration once complete

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

I do my check with tuple count on each side, source and target.

SELECT schemaname, relname AS table_name, n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Making sure the tuples match.

Microsoft also recommends these items after migration:

• Monitor the database performance closely to see if it requires performance tuning.
• 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 How I Migrated to Azure PostgreSQL Flex from Single Server appeared first on sqlkitty.