Configuring and Migrating to Azure Database for PostgreSQL

Why Migration and HA Are the Same Conversation

Migrating a database and keeping it highly available afterward aren't two separate projects - they're the same discipline applied at two different moments. Both come down to the same underlying mechanism: PostgreSQL's write-ahead log (WAL), the record of every change made to the database, streamed to wherever it needs to go - a standby replica for HA, a target server for migration, or a backup archive for disaster recovery. This is a granular, DBA-level build: migrating a real database with minimal downtime, configuring zone-redundant high availability, setting up read replicas, tuning a slow query, and locking down identity and network access.

Before the how, the what - three terms this build leans on:

• WAL (Write-Ahead Log) - before PostgreSQL changes a data page on disk, it first writes a record of that change to the WAL. This is what makes crash recovery possible (replay the log since the last checkpoint) and what makes replication possible (stream the log to another server and replay it there too).
• RTO vs. RPO - RTO is how long you're down; RPO is how much data you can afford to lose. A synchronous HA replica targets near-zero RPO; an hourly backup targets a much larger one - the configuration choice below is really an RPO decision in disguise.
• Logical vs. Physical Replication - physical replication (what WAL streaming does by default) copies the entire database byte-for-byte to an identical replica - fast, but the replica must be the same PostgreSQL version and can't be selective. Logical replication copies changes at the row level for specific tables, which is slower but allows replicating between different PostgreSQL versions - exactly what makes a near-zero-downtime migration possible.

Migration Strategy: Picking the Right Tool for the Downtime You Can Afford

Three real options, in order of increasing complexity and decreasing downtime:

1.1 The Simple Path: pg_dump / pg_restore

pg_dump -h source-server.postgres.database.azure.com \
  -U dbadmin -d production_db -Fc -f production_db.dump \
  pg_restore -h target-server.postgres.database.azure.com \
  -U dbadmin -d production_db --no-owner --no-acl \
  production_db.dump -Fc

Custom format (-Fc) is worth defaulting to over plain SQL - it's compressed, supports parallel restore (pg_restore -j 4), and lets you restore selectively (a single table) without re-running the whole dump. The --no-owner and --no-acl flags strip role/permission definitions that likely don't exist identically on the target - set those explicitly afterward instead of letting the restore fail on a missing role.

1.2 The Near-Zero-Downtime Path: Logical Replication

Set up the source to publish changes, the target to subscribe to them, let them stay in sync for as long as needed, then cut over:

• On the source server:

  ALTER SYSTEM SET wal_level = 'logical';
  

  This requires a restart to take effect.

• Create a publication on the source:

  CREATE PUBLICATION migration_pub FOR ALL TABLES;
  

• On the target server (schema already created via pg_dump --schema-only):

  CREATE SUBSCRIPTION migration_sub 
  CONNECTION 'host=source-server.postgres.database.azure.com dbname=production_db user=replicator password=...'
  PUBLICATION migration_pub;
  

• Monitor replication lag on the source before cutting over:

  SELECT slot_name, active, pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
  AS lag
  FROM pg_replication_slots;
  

• Cutover: Once lag reaches zero, point the application's connection string at the target, verify writes land there, then drop the subscription. Total application downtime is however long it takes to flip the connection string - seconds, not the hours a pg_dump/pg_restore cycle needs on a large database.

Configuring High Availability

Azure Database for PostgreSQL Flexible Server offers two HA modes:

• Zone-redundant HA - a synchronous standby in a different availability zone. Survives a full zone outage; adds cross-zone network latency to every write since the primary waits for the standby to confirm before acknowledging a commit.

• Same-zone HA - a synchronous standby in the same zone. Lower latency than zone-redundant, but doesn't protect against a zone-level outage - only protects against the primary instance itself failing.

To enable zone-redundant HA:

az postgres flexible-server update \
  --resource-group rg-database \
  --name pg-prod-primary \
  --zonal-resiliency Enabled \
  --standby-zone 2

Failover is automatic - if the primary becomes unreachable, the standby promotes itself and DNS updates to point at it. Verify actual failover time rather than trusting the SLA number:

az postgres flexible-server restart \
  --resource-group rg-database \
  --name pg-prod-primary \
  --failover

Forcing a failover in a non-production environment and timing it follows the same "run the failure, measure the real number" discipline as the Alibaba Cloud disaster-recovery lab, ensuring you know your actual RTO instead of assuming the documented one applies to your specific configuration.

Read Replicas for Scaling

A read replica is a separate, asynchronously-replicated copy - useful for offloading read-heavy workloads (reporting queries, analytics), but not a substitute for HA since replication is asynchronous and a replica can lag behind.

az postgres flexible-server replica create \
  --name pg-prod-replica-reporting \
  --resource-group rg-database \
  --source-server pg-prod-primary

Monitor replication lag - a replica silently falling behind by hours defeats the purpose without anyone noticing:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Disaster Recovery: Backups and Point-in-Time Restore

Independent of HA - HA protects against an instance/zone failure; backups protect against mistakes (a bad DELETE, a botched migration) that HA would faithfully replicate to the standby too.

• Geo-redundant backup can only be set at server creation - there's no --geo-redundant-backup flag on the update command, only on create/restore.

• Retention is updatable any time (7-35 days):

  az postgres flexible-server update \
    --resource-group rg-database \
    --name pg-prod-primary \
    --backup-retention 35
  

If geo-redundant backup wasn't enabled at creation, the fix is a geo-restore into a new server (which can target a different region) rather than an in-place toggle:

az postgres flexible-server create \
  --resource-group rg-database \
  --name pg-prod-primary \
  --geo-redundant-backup Enabled \
  --backup-retention 35 \
  --location eastus

Point-in-time restore to a new server, right before a bad migration runs:

az postgres flexible-server restore \
  --resource-group rg-database \
  --name pg-prod-restored \
  --source-server pg-prod-primary \
  --restore-time "2026-09-14T09:58:00Z"

Restore always creates a new server rather than overwriting the original - verify the restored data is actually correct before repointing anything at it, rather than assuming the timestamp you picked was precise enough.

Query Performance: Finding and Fixing a Slow Query

The same detection method from the Alibaba Cloud Observability Lab applies directly here - find the slow query, then read its actual execution plan rather than guessing:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_email = 'u***@example.com' ORDER BY created_at DESC;

Result:

Seq Scan on orders(cost=0.00..45231.00 rows=1 width=120) (actual time=0.045..892.113 rows=12 loops=1)
Filter: (customer_email = 'u***@example.com'::text)
Rows Removed by Filter: 1239988
Planning Time: 0.112 ms
Execution Time: 892.201 ms

The bottleneck is clear: no index on customer_email. The fix:

CREATE INDEX CONCURRENTLY idx_orders_customer_email ON orders (customer_email);

CONCURRENTLY builds the index without holding a lock that blocks writes to the table - slower to build, but doesn't stall production traffic the way a plain CREATE INDEX would on a large, actively-written table. Afterwards, the improved query performs dramatically better:

Index Scan using idx_orders_customer_email on orders(cost=0.42..8.44 rows=1 width=120) (actual time=0.031..0.034 rows=12 loops=1)
Execution Time: 0.058 ms

This mirrors the classic slow-query incident seen in the observability article - a common real-world performance bug caused by missing indexes on frequently filtered columns.

Connection pooling is also important. Azure Database for PostgreSQL Flexible Server includes built-in PgBouncer support:

az postgres flexible-server parameter set \
  --resource-group rg-database \
  --server-name pg-prod-primary \
  --name pgbouncer.enabled \
  --value true

Identity and Network Access

Use Microsoft Entra authentication instead of password-only:

az postgres flexible-server microsoft-entra-admin create \
  --resource-group rg-database \
  --server-name pg-prod-primary \
  --display-name "db-admins-group" \
  --object-id "<entra-group-object-id>"

Application connections then authenticate with an Entra access token instead of a static database password - following the same "no long-lived credential to leak" principle as the OIDC pattern in the Azure Load Testing article.

Network access should be private, not public:

az postgres flexible-server update \
  --resource-group rg-database \
  --name pg-prod-primary \
  --public-access Disabled

Create a private endpoint for internal access:

az postgres flexible-server update \
  --resource-group rg-database \
  --name pg-prod-primary \
  --public-access Disabled \
  az network private-endpoint create \
    --resource-group rg-database \
    --name pe-postgres-prod \
    --vnet-name vnet-app \
    --subnet snet-data \
    --private-connection-resource-id "$(az postgres flexible-server show \
      --resource-group rg-database --name pg-prod-primary \
      --query id -o tsv)" \
    --group-id postgresqlServer \
    --connection-name pg-connection

Role-level permissions, not a single shared admin account:

CREATE ROLE app_readwrite WITH LOGIN PASSWORD NULL;
GRANT CONNECT ON DATABASE production_db TO app_readwrite;

CREATE ROLE reporting_readonly WITH LOGIN PASSWORD NULL;
GRANT CONNECT ON DATABASE production_db TO reporting_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO reporting_readonly;

An application that only needs to read and write its own tables should connect as app_readwrite, never as the server admin - applying the same least-privilege discipline as Key Vault RBAC roles in cloud security.

Closing Thoughts

None of this is exotic on its own - a migration plan, a standby replica, a backup policy, an index, a private endpoint. What separates a database that's merely "running" from one that's actually production-ready is testing the failover instead of trusting the SLA number, measuring the query instead of guessing at the fix, and defaulting to private network access and role-scoped permissions from the start rather than retrofitting them after an incident.

GitHub Repository: [azure-postgresql-migration-ha-lab](https://github.com/... - migration scripts, HA/replica Bicep, the slow-query fix, and the identity/network hardening scripts, ready to run).

Reviewed against current Azure Database for PostgreSQL documentation as of September 2026. Azure Database for PostgreSQL · Migration · High Availability · Disaster Recovery · Query Performance · Microsoft Entra ID. Originally published on my portfolio.