Databases & Reliability · Sep 2026 · 25 min read

Configuring and Migrating to Azure Database for PostgreSQL

A granular DBA-level walkthrough — migrating a database with minimal downtime, configuring zone-redundant high availability, tuning query performance, and locking down identity and network access.

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). Nearly everything in this article — HA, replicas, point-in-time recovery — is WAL being put to a different use.
• RTO vs. RPO — covered in depth in the Disaster Recovery on Alibaba Cloud article, but the short version: 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.

01 — 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) 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. --no-owner --no-acl strips 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';
-- requires a restart to take effect

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;

-- Watch 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.

02 — 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.

# --zonal-resiliency enables HA; --standby-zone picks the standby's zone.
# Omit --allow-same-zone for zone-redundant (standby lands in a different
# zone from the primary); pass it to force same-zone HA instead.
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 Forced

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

03 — Read Replicas for Scaling Reads (Not HA)

A read replica is a separate, asynchronously-replicated copy — useful for offloading read-heavy workloads (reporting queries, analytics), but not a substitute for HA: replication is asynchronous, so a replica can lag behind and isn't guaranteed to have the very latest committed data.

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

Point reporting/analytics connections at the replica's own hostname, application writes stay on the primary. Monitor replication lag — a replica silently falling behind by hours defeats the purpose without anyone noticing until a report shows stale data:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

04 — Disaster Recovery: Backups and Point-in-Time Restore

Independent of HA — HA protects against an instance/zone failure; backups protect against a mistake (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, on the other hand, 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 ran
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.

05 — 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 = 'user@example.com' ORDER BY created_at DESC;

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 = 'user@example.com'::text)
  Rows Removed by Filter: 1239988
Planning Time: 0.112 ms
Execution Time: 892.201 ms

Seq Scan scanning 1.24M rows to find 12 matching ones is the tell — no index on customer_email.

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.

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

892ms down to 0.058ms — the same class of fix, and the same root cause, as the unindexed-query incident in the observability article; it shows up constantly because it's genuinely one of the most common real-world performance bugs.

Connection pooling

Azure Database for PostgreSQL Flexible Server includes built-in PgBouncer support — enable it rather than letting an application open a raw connection per request, since PostgreSQL's per-connection memory overhead makes a large number of idle connections expensive:

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

06 — Identity and Network Access

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 — the same "no long-lived credential to leak" principle as the OIDC pattern in the Azure Load Testing article, applied to database auth instead of pipeline auth.

Network access: private, not public

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

Disabling public network access and reaching the server only via a Private Endpoint inside your VNet is the same principle as the Zero Trust networking article's Private Link section — a database is exactly the kind of resource that should never be reachable from the open internet in the first place.

Role-level permissions, not one shared admin account

CREATE ROLE app_readwrite WITH LOGIN PASSWORD NULL; -- auth via Entra, no local password
GRANT CONNECT ON DATABASE production_db TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

CREATE ROLE reporting_readonly WITH LOGIN PASSWORD NULL;
GRANT CONNECT ON DATABASE production_db TO reporting_readonly;
GRANT SELECT 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 — the same least-privilege discipline as the Key Vault RBAC roles in the cloud security article, applied to database roles instead of vault permissions.

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 — 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