Transformation: self-managed PostgreSQL to Azure Database for PostgreSQL Flexible Server

#1. Compatibility check

#2. Create the target

• Compute: General Purpose or Memory Optimized; start from source vCPU/RAM and the buffer cache hit ratio.
• Storage: size with headroom; enable storage autogrow; choose performance tier (IOPS) to match source peaks.
• High availability: zone-redundant HA for production.
• Networking: private access (VNet integration) or public access with private endpoint, decided once (it can't be switched freely later).
• Authentication: Entra ID authentication plus PostgreSQL authentication if apps still need passwords.
• Server parameters: copy relevant ones (work_mem, max_connections, shared_preload_libraries for pg_stat_statements, timezone).

az postgres flexible-server create -g rg-db -n pgflex-sales --version 16 \
  --tier GeneralPurpose --sku-name Standard_D4ds_v5 --storage-size 512 \
  --high-availability ZoneRedundant --vnet vnet-db --subnet snet-pgflex \
  --private-dns-zone pgflex-sales.private.postgres.database.azure.com
az postgres flexible-server parameter set -g rg-db -s pgflex-sales --name azure.extensions --value "PG_STAT_STATEMENTS,PGCRYPTO,UUID-OSSP"

#3a. Offline migration (small databases or long windows)

pg_dump -h oldpg -U postgres -d sales -Fd -j 8 -f /mnt/dump/sales
pg_restore -h pgflex-sales.postgres.database.azure.com -U pgadmin -d sales -j 8 --no-owner --no-acl /mnt/dump/sales

Run pg_dumpall --globals-only and adapt roles (remove SUPERUSER and other attributes Flexible Server doesn't allow), then recreate grants.

#3b. Online migration (large or busy databases)

Use the migration service in Azure Database for PostgreSQL (portal: the target server → Migration). Online mode does an initial copy, then uses logical replication to keep the target in sync until you cut over.

Source requirements for online mode: wal_level = logical, enough max_replication_slots / max_wal_senders, a user with replication rights, and primary keys on all tables (tables without them can't replicate updates/deletes):

SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog','information_schema')
  AND NOT EXISTS (SELECT 1 FROM pg_constraint p WHERE p.conrelid = c.oid AND p.contype = 'p');

#4. Validate

• Row counts and sample checksums per table.
• Sequences: logical replication doesn't sync sequence values. Set them on the target before cut-over.
• Extensions enabled and at expected versions.
• Run the top queries from pg_stat_statements on the source against the target; compare plans and timings.

#5. Cut-over

1. Stop writes on source (maintenance page or read-only role).
2. Wait for replication lag 0 (online), or run the final dump/restore (offline).
3. Fix sequences; run ANALYZE on the target.
4. Change connection strings / DNS; enable TLS sslmode=require (or verify-full) in apps.
5. Keep the source read-only for the rollback window.

#6. After go-live

• Enable Query Store-style insights (pg_qs.query_capture_mode), Intelligent Tuning if suitable, and Azure Monitor alerts (CPU, storage %, connections, replication lag for read replicas).
• Use built-in PgBouncer on Flexible Server if the app opens many short connections.
• Backups: set retention and geo-redundant backup as required. You no longer run your own backup jobs.

Warning

Choose networking (private VNet access vs public/private endpoint) carefully at creation. Changing it later generally means creating a new server and migrating again.

#Checklist

• Extensions, superuser use and PK coverage checked
• Server sized; HA, networking, auth decided
• Migration method rehearsed with timings
• Sequences, roles and grants handled
• Monitoring, PgBouncer and backups configured