This is more complicated than using the Azure Migration method, but because it’s maxed out on resources for the last week in the east regions (and possibly central), and who knows when they will fix it, I had to resort to other methods. I’m getting on flex sooner than later. I want to get this over with and get to those performance improvements and better features. I will preface this all by saying, if you have big databases, this may not be the right path for you. Look into streaming replication or wait for Microsoft to fix their migration tool and do an online migration via that. Also, if you don’t have strong Postgres skills, this is far more complicated than the migration tool in Azure, far more complicated.

Note: I only recommend this method if the Azure migration tool is broken. I’ve had problems with it especially in eastus2.

Prerequisites

First and foremost, you need a flex server to migrate to. And very important! Follow all the prerequisites that I outlined in a previous post on how to migrate with the Azure migration tool (which may one day work again in eastus/eastus2).

I highly recommend scaling your flex server up to at least general 4 vcores to migrate to it so it’s not super slow. If you already had more resources than that, great, but if not, you can scale it back down afterwards.

This is where I will set the database(s) I will need and assign the owner as the pg flex admin (whatever you’ve named it). The owner part is especially important later if you want to alter default privileges. You can’t seem to do that unless the db is owner by the server admin. Even if you are logged in as the server admin, you still have to set the owner to the server admin to set default privileges. I think you want those so perms will apply to future objects in your pg databases.

CREATE DATABASE dbname
    WITH
    OWNER = pgflexadmin; 

Also, note with default privileges, it will use the old server admin in those statements, so you need to update it to the new server admin. You need to swap out oldrole in this case.

ALTER DEFAULT PRIVILEGES FOR ROLE oldrole IN SCHEMA yourschema GRANT ALL ON SEQUENCES  TO yourrole;

The virtual machine

I needed to dump/restore without it being so slow to my local machine. Ran this from my mac, so tweak as needed for windows. Very similar in there, but might not need that pwsh -Command ‘your commands in here’ but just the commands in here bits. This setups up a small Linux VM with a 100gb disk, and attaches that disk. Adjust according to your needs.

Creating the VM

pwsh -Command '
$resourceGroupName = "josephineworkingrg";
$location = "eastus2";
$vmName = "pgworkload";
$username = "josephine";
$image = "Canonical:0001-com-ubuntu-minimal-jammy:minimal-22_04-lts-gen2:latest";
$dataDiskName = "pgworkload-datadisk";
$dataDiskSize = 100; # Size in GB
# Step 1: Create Resource Group
az group create --name $resourceGroupName --location $location;
# Step 2: Create VM
az vm create `
  --resource-group $resourceGroupName `
  --name $vmName `
  --image $image `
  --admin-username $username `
  --assign-identity `
  --generate-ssh-keys `
  --public-ip-sku Standard;
# Step 3: Create and Attach a Data Disk
az disk create `
  --resource-group $resourceGroupName `
  --name $dataDiskName `
  --size-gb $dataDiskSize;
az vm disk attach `
  --resource-group $resourceGroupName `
  --vm-name $vmName `
  --name $dataDiskName; 
# Step 4: Get VM Public IP
$ipAddress = az vm show `
  --show-details `
  --resource-group $resourceGroupName `
  --name $vmName `
  --query "publicIps" `
  --output tsv;
# Step 6: SSH and Prepare
Write-Host "ssh -o StrictHostKeyChecking=no $username@$ipAddress";'

Partitioning and mounting the disk

After you get that setup, you will need to SSH into the machine. That command is written out at the end of the last command. Use that to get connected. Then you need to partition and mount your disk.

sudo parted /dev/sdc --script mklabel gpt mkpart xfspart xfs 0% 100%
sudo mkfs.xfs /dev/sdc1
sudo partprobe /dev/sdc1
sudo mkdir /datadrive
sudo mount /dev/sdc1 /datadrive

Once it’s mounted, now you can create a folder in there, if needed, or just write directly to that mount location.

Verify your mount is there.

lsblk -o NAME,SIZE,MOUNTPOINT,FSTYPE

Installing PG

You need Postgres on there to run your dumps and restores.

sudo apt update
sudo apt install postgresql postgresql-contrib

Ownership or perms on the mount

You will need to modify the perms on the mount for you to write to that location. You can chmod or chown. I did a chown because I’m the only one using it. You don’t need both, but I provided examples of both below.

sudo chown josephine:josephine /datadrive
sudo chmod 777 /datadrive

I’m also going to add a folder in the /datadrive mount to store my dump.

cd /datadrive 
mkdir yourdumpfolder

Dumping single server

This is how I migrated Postgres from one VM to another, and it works here for single to flex, as well.

Globals/Getting your roles in place

To start, we need to dump the globals. I had to tweak mine because it’s not a one-to-one dump/restore from single to flex.

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 in Azure Data Studio.

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;

Migrating schema part 1

I just dump this on my local machine, as it doesn’t take long. You could dump it to your VM if you want to keep dumps together. Basically, you are restoring schema and perms minus constraints and indexes in this step. This is because we are going to restore the data in parallel with the -j flag. If you try to restore the data in parallel, it won’t work with the constraints in place. I like to add the indexes after migrating data because the data will restore faster without the indexes (indexes slow down writes and speed up reads – RDMS 101). Note: this has –no-owner because ownership is more complicated in flex and since I have a different flex admin username than on single server, I’m leaving owner info off the schema dump.

export PGPASSWORD=yourpassword
pg_dump -v -h yourserver.postgres.database.azure.com -U youradmin@yourserver -d yourdb -s  -f /yourpath/schema_dump_no_owner.sql --no-owner

I’m going to go into that file now and fish out the add constraints and create indexes commands. They are after all the other object creates but before the perm grants. I will put them into another file to restore later after the data is restored.

Dumping the data from the source

We need to dump only the data from the source at this point to store it on the VM for later restoration on the destination. If you want more info on what each flag does in the pg_dump command, visit postgres.org. High level, it connects, and it dumps the data (-a), it does it with four jobs running concurrently (-j 4), and it puts in a folder with multiple files -F d. When selecting the jobs, make sure it’s not more than the number of vcores you have.

export PGPASSWORD=yourpassword
pg_dump -v -h yourserver.postgres.database.azure.com 
    -U youruser@yourserver 
    -d yourdb 
    -j 4 
    -a 
    -F d 
    -f /datadrive/yourfolder/your_data_dump

You will wind up with something like this. Lots of files and a TOC in a folder. You can do ls -lh to see the list.

Restoring data to destination

Now, we will restore the data dumped from the source.

export PGPASSWORD=yourpassword
pg_restore -v -h yourdbserver.postgres.database.azure.com 
    -U youradminuser 
    -d yourdb 
    -j 4 
    /datadrive/yourfolder/your_data_dump

Verify data restore

I just spot check a couple of the bigger tables because my tables at work are quite small and call it good. I wouldn’t verify row counts in SQL Server after a backup and restore, so I’m not sure why I felt compelled to check row counts or live tuples in Postgres.

Note: if you have materialized views, you need to refresh them so they are accessible. You can refresh them all at once with the following script.

DO $$ 
DECLARE r RECORD;
BEGIN 
  FOR r IN (SELECT schemaname, matviewname FROM pg_matviews) 
  LOOP 
    EXECUTE 'REFRESH MATERIALIZED VIEW ' || quote_ident(r.schemaname) || '.' || quote_ident(r.matviewname);
  END LOOP; 
END $$;

Adding indexes and constraints

You pulled those out earlier from your schema dump. Might be some triggers mixed in there too, by the way as I look through mine. The point was to not have constraints and indexes in the middle when you are adding data, and since no one should hit your flex yet. Now, you can add all that stuff you pulled out.

Depending on how big your tables are, could take a while to apply. Mine are small, so this is an acceptable method.

Vacuum analyze after migration

Best practice for after you add the indexes.

SELECT 'VACUUM ANALYZE ' || schemaname || '.' || tablename || ';'
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

Now that you are on flex, you will want to schedule some stuff with cron for this kind of work, especially reindex. You might be able to tweak the autovac enough to avoid having to schedule vacuum/analyze. I will post later about setting up cron jobs for the reindex, and also posting to document my process around auto vac settings.

I’m putting this reindex script here for future reference to put in a cron job (thank you past self for all your posts that helped me out in a bind!), and in case you need a one off. This script will create a reindex script for each table. Setting the timeout to 0 is important because those reindex scripts can be long running and your session may timeout, so I put that before the reindex scripts that are created.

SET statement_timeout =0;
SELECT 'REINDEX TABLE ' || schemaname || '.' || tablename || ';'
FROM pg_tables
WHERE schemaname = 'schemaname';

Rename old databases

To make sure nothing is connecting over there.

--See what's connected before terminating connections (if needed) 
SELECT pid, usename, application_name, client_addr, state, query, backend_start
FROM pg_stat_activity
WHERE datname = 'yourolddb';
-- Terminate existing connections to the database
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'yourolddb' AND pid <> pg_backend_pid();
-- Rename the database
ALTER DATABASE yourolddb RENAME TO yourolddb_old;

You should be done, except for possible tweaking of server resources and settings.

The post Migrating from Azure PG Single Server to Flex with Azure VM and pg_dump/restore appeared first on sqlkitty.