postgres-admin-cli

name

postgres-admin-cli

description

Manage Azure Database for PostgreSQL Flexible Server infrastructure via Azure CLI. Use when the user wants to: (1) provision or configure a Flexible Server, (2) manage high availability and read replicas including cascading replicas and virtual endpoints, (3) configure private networking with VNet integration, Private DNS zones, and NSG rules, (4) set up Entra ID authentication, (5) manage backups including geo-redundant backup, on-demand backup, point-in-time restore, geo-restore, and long-term retention, (6) plan BCDR and disaster recovery with cross-region replicas, (7) tune server parameters, (8) monitor server health and replication metrics, (9) manage extensions and major version upgrades. Triggers: "create postgres server", "configure HA", "add firewall rule", "setup entra auth", "restore postgres", "server parameters", "postgres backup", "read replica", "geo-restore", "upgrade postgres version", "enable pgbouncer", "postgres monitoring", "postgres security", "vnet integration postgres", "private endpoint postgres", "private dns zone postgres", "cascading replica", "virtual endpoint", "promote replica", "long term retention", "geo-redundant backup", "disaster recovery postgres", "on-demand backup", "cross region replica", "nsg rules postgres".

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

PostgreSQL Administration — CLI Skill

Table of Contents

Task	Reference	Notes
Azure PostgreSQL Topology	COMMON-CORE.md § Azure PostgreSQL Topology
Authentication	COMMON-CLI.md § Authentication Recipes	Azure CLI login
Server Management	COMMON-CLI.md § Server Management	Create, show, update, restart
Firewall Rules	COMMON-CLI.md § Firewall Rules	Add/list/delete rules
Database Management	COMMON-CLI.md § Database Management	Create, list databases
Extensions	COMMON-CLI.md § Extensions Management	Enable via server parameter
High Availability & Replicas	COMMON-CLI.md § High Availability	Zone-redundant HA, replicas, virtual endpoints
Backup, BCDR & LTR	COMMON-CLI.md § Backup and Restore	PITR, geo-restore, on-demand, LTR
Private Networking	See below	VNet integration, Private DNS, NSG
Gotchas	COMMON-CLI.md § Gotchas	CLI-specific issues

---

Tool Stack

Tool	Role	Install
az CLI	Primary: Server lifecycle, configuration, security, monitoring.	Pre-installed in most dev environments
psql	SQL-level admin tasks (roles, permissions, extensions).	apt-get install postgresql-client / brew install libpq
jq	Parse JSON from az commands	Pre-installed or trivial

---

Server Provisioning

Create Flexible Server

az postgres flexible-server create \
  --resource-group your-resource-group \
  --name your-server-name \
  --location eastus \
  --tier GeneralPurpose \
  --sku-name Standard_D4ds_v5 \
  --storage-size 128 \
  --version 16 \
  --admin-user your-admin \
  --admin-password "your-secure-password" \
  --high-availability ZoneRedundant \
  --public-access 0.0.0.0

Compute Tiers

Tier	Use Case	vCores	Memory
Burstable	Dev/test, low-traffic apps	1-20	2-80 GB
General Purpose	Production workloads	2-96	8-384 GB
Memory Optimized	Large caches, analytics	2-96	16-672 GB

---

Security Configuration

Set Up Entra ID Admin

# Get current user's object ID
USER_OID=$(az ad signed-in-user show --query id -o tsv)

# Set as Entra ID admin
az postgres flexible-server ad-admin create \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --display-name "your-display-name" \
  --object-id $USER_OID \
  --type User

Configure SSL

# Verify SSL is enforced (default on Azure)
az postgres flexible-server parameter show \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name require_secure_transport \
  --query value -o tsv

---

Private Networking

Networking Options

Option	Description	When to Use
VNet integration (injection)	Server deployed into a delegated subnet	New servers, full network isolation
Public access + Private Endpoint	Private Link connection to public server	Existing servers, hybrid scenarios
Public access + Firewall	IP-based access rules	Dev/test environments

Create Server with VNet Integration

# Create VNet and delegated subnet (min /28)
az network vnet create \
  --resource-group your-resource-group \
  --name your-vnet \
  --address-prefix 10.0.0.0/16 \
  --subnet-name postgres-subnet \
  --subnet-prefix 10.0.1.0/24

# Delegate subnet to PostgreSQL
az network vnet subnet update \
  --resource-group your-resource-group \
  --vnet-name your-vnet \
  --name postgres-subnet \
  --delegations Microsoft.DBforPostgreSQL/flexibleServers

# Create Private DNS zone
az network private-dns zone create \
  --resource-group your-resource-group \
  --name your-zone.postgres.database.azure.com

# Link DNS zone to VNet
az network private-dns zone vnet-link create \
  --resource-group your-resource-group \
  --zone-name your-zone.postgres.database.azure.com \
  --name postgres-dns-link \
  --virtual-network your-vnet \
  --registration-enabled false

# Create server in VNet
az postgres flexible-server create \
  --resource-group your-resource-group \
  --name your-server-name \
  --location eastus \
  --tier GeneralPurpose \
  --sku-name Standard_D4ds_v5 \
  --storage-size 128 \
  --version 16 \
  --vnet your-vnet \
  --subnet postgres-subnet \
  --private-dns-zone /subscriptions/{sub-id}/resourceGroups/{rg}/providers/Microsoft.Network/privateDnsZones/your-zone.postgres.database.azure.com \
  --admin-user your-admin \
  --admin-password "your-secure-password"

NSG Rules for PostgreSQL Subnet

# Create NSG
az network nsg create --resource-group your-resource-group --name postgres-nsg

# Allow intra-subnet PostgreSQL traffic (port 5432)
az network nsg rule create \
  --resource-group your-resource-group \
  --nsg-name postgres-nsg \
  --name allow-postgres-intra-subnet \
  --priority 100 --direction Inbound \
  --source-address-prefixes 10.0.1.0/24 \
  --destination-port-ranges 5432 --protocol Tcp --access Allow

# Allow outbound to Azure Storage (WAL archival)
az network nsg rule create \
  --resource-group your-resource-group \
  --nsg-name postgres-nsg \
  --name allow-storage \
  --priority 200 --direction Outbound \
  --destination-address-prefixes Storage \
  --destination-port-ranges 443 --protocol Tcp --access Allow

# Associate NSG with subnet
az network vnet subnet update \
  --resource-group your-resource-group \
  --vnet-name your-vnet \
  --name postgres-subnet \
  --network-security-group postgres-nsg

Cross-VNet Access (VNet Peering)

# Peer VNet1 (app) with VNet2 (postgres)
az network vnet peering create \
  --resource-group your-resource-group \
  --name app-to-pg \
  --vnet-name app-vnet \
  --remote-vnet /subscriptions/{sub-id}/resourceGroups/{rg}/providers/Microsoft.Network/virtualNetworks/your-vnet \
  --allow-vnet-access

# Link app VNet to PostgreSQL Private DNS zone
az network private-dns zone vnet-link create \
  --resource-group your-resource-group \
  --zone-name your-zone.postgres.database.azure.com \
  --name app-dns-link \
  --virtual-network app-vnet \
  --registration-enabled false

Cross-Region Networking (for Read Replicas)

For cross-region replicas with VNet integration:

1. Create VNet and delegated subnet in each region
2. Peer VNets (global peering) or use VPN gateway
3. Link Private DNS zone to both VNets
4. Ensure port 5432 (bidirectional) and port 443 (Storage) are open

Custom DNS Server

If using a custom DNS server, configure a forwarder to Azure DNS:

# Forward to Azure's recursive resolver
Forwarder: 168.63.129.16

Networking Limitations

• Cannot move server between VNets/subnets after creation
• Cannot resize subnet while resources exist in it
• VNet-injected servers cannot use Private Link by default
• Always use FQDN (not IP) for connections — IPs may change

---

Manage Firewall Rules

# Add specific IP
az postgres flexible-server firewall-rule create \
  --resource-group your-resource-group \
  --name your-server-name \
  --rule-name allow-office \
  --start-ip-address 203.0.113.0 \
  --end-ip-address 203.0.113.255

# Allow Azure services
az postgres flexible-server firewall-rule create \
  --resource-group your-resource-group \
  --name your-server-name \
  --rule-name allow-azure \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

---

High Availability & Read Replicas

HA Modes

Mode	Protection	RTO	RPO
Zone-redundant HA	AZ-level failure	< 120s	0 (synchronous)
Same-zone HA	Node-level failure	< 120s	0 (synchronous)

HA is supported only on General Purpose and Memory Optimized tiers (not Burstable).

Enable Zone-Redundant HA

az postgres flexible-server update \
  --resource-group your-resource-group \
  --name your-server-name \
  --high-availability ZoneRedundant

Create Read Replica

# Same-region replica
az postgres flexible-server replica create \
  --resource-group your-resource-group \
  --source-server your-server-name \
  --replica-name your-server-replica \
  --location eastus

# Cross-region replica (for DR)
az postgres flexible-server replica create \
  --resource-group your-resource-group \
  --source-server your-server-name \
  --replica-name your-server-geo-replica \
  --location westus

Cascading Read Replicas

Up to 5 direct replicas per server, 2 levels deep, up to 30 total replicas.

# Create a second-level replica from an existing replica
az postgres flexible-server replica create \
  --resource-group your-resource-group \
  --source-server your-server-replica \
  --replica-name your-server-cascade-replica \
  --location eastus

Cascading replicas require PostgreSQL 14+. Virtual endpoints are not supported on cascading replicas.

List Replicas

az postgres flexible-server replica list \
  --resource-group your-resource-group \
  --name your-server-name \
  -o table

Promote Replica

Two promotion modes:

Mode	Effect	Use Case
Promote to primary (switchover)	Replica becomes primary, old primary becomes replica	Planned failover, regional failover
Promote to independent server	Detaches replica as standalone read-write server	DR, permanent split

# Promote to independent server (detach from replication)
az postgres flexible-server replica stop-replication \
  --resource-group your-resource-group \
  --name your-server-replica

Virtual Endpoints

Stable connection points that automatically route traffic.

# Create a writer endpoint (points to primary)
az postgres flexible-server virtual-endpoint create \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name writer-endpoint \
  --endpoint-type ReadWrite \
  --members your-server-replica

# Create a reader endpoint (points to replica)
az postgres flexible-server virtual-endpoint create \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name reader-endpoint \
  --endpoint-type ReadOnly \
  --members your-server-replica

Monitor Replication

-- On primary: check replication lag in bytes
SELECT * FROM pg_stat_replication;

-- Key Azure metrics for alerting:
-- physical_replication_delay_in_bytes (on primary)
-- physical_replication_delay_in_seconds (on replica)
-- logical_replication_delay_in_bytes (enhanced metrics)

# Enable enhanced replication metrics
az postgres flexible-server parameter set \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name metrics.collector_database_activity --value "on"

Set alerts when replica lag exceeds acceptable thresholds (e.g., > 5 minutes or storage > 80%).

Scaling Order for Replicas

Operation	Order
Scale UP compute	Replica first, then primary
Scale DOWN compute	Primary first, then replica
Scale UP storage	Replica first, then primary

Compute on primary must always be ≤ smallest replica. Storage autogrow must match primary setting.

Replica Server Parameter Requirements

These parameters on replicas must be ≥ the primary values:

• max_connections
• max_prepared_transactions
• max_locks_per_transaction
• max_wal_senders
• max_worker_processes

Replica Limitations

• Burstable tier: Not supported for replicas
• Backups: No backups taken from replicas; only from primary
• HA: Cannot enable HA on replicas
• Major version upgrade: Remove all replicas first, upgrade, then recreate

---

Backup & Restore

Backup Redundancy Options

Option	Description	RPO	When
Zone-redundant	3 copies across AZs in region	< 5 min	Default (multi-AZ regions)
Locally redundant	Multiple copies in same datacenter	< 5 min	Regions without AZ support
Geo-redundant	Copies to paired region	< 1 hour	Configured at creation only

Create Server with Geo-Redundant Backup

# Geo-redundant backup must be set at server creation time
az postgres flexible-server create \
  --resource-group your-resource-group \
  --name your-server-name \
  --location eastus \
  --tier GeneralPurpose \
  --sku-name Standard_D4ds_v5 \
  --storage-size 128 \
  --version 16 \
  --geo-redundant-backup Enabled \
  --admin-user your-admin \
  --admin-password "your-secure-password"

Set Backup Retention (7-35 Days)

az postgres flexible-server update \
  --resource-group your-resource-group \
  --name your-server-name \
  --backup-retention 35

Point-in-Time Restore

az postgres flexible-server restore \
  --resource-group your-resource-group \
  --name your-server-restored \
  --source-server your-server-name \
  --restore-time "2026-03-23T10:00:00Z"

Fast Restore (from Full Snapshot)

# List available backup snapshots
az postgres flexible-server backup list \
  --resource-group your-resource-group \
  --name your-server-name -o table

# Restore from a specific snapshot (fastest option)
az postgres flexible-server restore \
  --resource-group your-resource-group \
  --name your-server-restored \
  --source-server your-server-name \
  --restore-time "2026-03-22T23:00:00Z"

Geo-Restore (Disaster Recovery)

# Restore to paired region from geo-redundant backup
az postgres flexible-server geo-restore \
  --resource-group your-resource-group \
  --name your-server-geo-restored \
  --source-server /subscriptions/{sub-id}/resourceGroups/{rg}/providers/Microsoft.DBforPostgreSQL/flexibleServers/your-server-name \
  --location westus

On-Demand Backup

# Trigger an on-demand backup (up to 7 per server)
az postgres flexible-server backup create \
  --resource-group your-resource-group \
  --name your-server-name \
  --backup-name on-demand-backup

Restore a Deleted Server

Deleted servers can be restored within the retention period. Use Azure resource locks to prevent accidental deletion.

Long-Term Retention (Azure Backup)

Azure Backup provides LTR for up to 10 years using pg_dump-based logical backups.

• LTR is independent of the 7-35 day automated backup retention
• Scheduled weekly, monthly, or yearly
• Stored in Azure Backup vaults (separate security/fault domain)
• Max supported database size: 1 TiB
• Restores are currently "Restore as Files" to storage accounts

Configure LTR via Azure Backup in the Azure portal or CLI. See Azure Backup for PostgreSQL.

Post-Restore Checklist

1. Update connection strings to point to the new server
2. Reconfigure server parameters (not copied from original)
3. Reapply firewall rules, private endpoints, or VNet rules
4. Re-enable high availability if needed
5. Recreate read replicas if needed
6. Reconfigure diagnostic settings and alert rules
7. Verify logins and database-level permissions

Check Backup Retention

az postgres flexible-server show \
  --resource-group your-resource-group \
  --name your-server-name \
  --query "backup.backupRetentionDays" -o tsv

---

Server Parameters

Key Parameters to Tune

Parameter	Purpose	Default	Notes
shared_buffers	Memory for caching	25% of RAM	Restart required
work_mem	Per-operation sort memory	4MB	Dynamic, be conservative
maintenance_work_mem	VACUUM, CREATE INDEX	64MB	Increase for large tables
effective_cache_size	Planner hint	75% of RAM	Dynamic
max_connections	Connection limit	100	Restart required
log_min_duration_statement	Slow query logging	-1 (off)	Dynamic

Set a Parameter

az postgres flexible-server parameter set \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name log_min_duration_statement \
  --value "1000"

Enable PgBouncer

az postgres flexible-server parameter set \
  --resource-group your-resource-group \
  --server-name your-server-name \
  --name pgbouncer.enabled \
  --value "true"

---

Major Version Upgrade

az postgres flexible-server update \
  --resource-group your-resource-group \
  --name your-server-name \
  --version 16

Warning: Always test upgrades in a non-production environment first. Create a point-in-time restore copy and upgrade that.

---

Must

• Test configuration changes in non-production first
• Verify parameter restart requirements before changing
• Use Entra ID authentication for production
• Configure firewall rules (never leave fully open)
• Enable SSL (require_secure_transport = on)
• Set up backup retention appropriate for RTO/RPO
• Enable geo-redundant backup at server creation if cross-region DR is needed
• Scale replicas before primary (up) or primary before replicas (down)
• Use FQDN (not IP) for all connections

Prefer

• Zone-redundant HA for production
• PgBouncer for connection pooling
• VNet integration over public access for production
• Private DNS zones for VNet-integrated servers
• Virtual endpoints for stable connection points
• Managed identity for Azure-hosted applications
• Scheduled maintenance windows during off-peak
• On-demand backups before risky operations
• Azure Backup LTR for compliance (> 35 day retention)
• Cross-region read replicas for disaster recovery

Avoid

• Public access without firewall rules
• Superuser credentials for applications
• Skipping backup verification
• Changing max_connections without considering PgBouncer
• Major version upgrades without testing
• Creating geo-redundant backup after server creation (not supported)
• Burstable tier for read replicas (not supported)
• DNS private zone locks when using HA (interferes with failover)
• Moving VNet-integrated servers between subnets (not supported)