Azure PostgreSQL Lesson Learned#12: Private Endpoint Approval Fails for Cross Subscription
Co‑authored with HaiderZ-MSFT Symptoms Customers experience issues when attempting to approve a Private Endpoint for Azure PostgreSQL Flexible Server, particularly in cross‑subscription or cross‑tenant setups: Private Endpoint remains stuck in Pending state Portal approval action fails silently or reverts Selecting the Private Endpoint displays a “No Access” message Activity logs show repeated retries followed by failure Common Error Message AuthorizationFailed: The client '<object-id>' does not have authorization to perform action 'Microsoft.Network/privateEndpoints/privateLinkServiceProxies/write' over scope '<private-endpoint-resource-id>' or the scope is invalid. Root Cause Although the approval action is initiated from the PostgreSQL Flexible Server (service provider resource), Azure performs additional network‑level operations during approval. Specifically, Azure must update a Private Link Service Proxy on the Private Endpoint resource, which exists in the consumer subscription. When the Private Endpoint resides in a different subscription or tenant, the approval process fails if: Required Resource Providers are not registered, or The approving identity lacks network‑level permissions on the Private Endpoint scope In this case, the root cause was missing Resource Provider registration, resulting in an AuthorizationFailed error during proxy updates. Required Resource Providers Microsoft.Network Microsoft.DBforPostgreSQL If either provider is missing on either subscription, the approval process will fail regardless of RBAC configuration. Mitigation Steps Step 1: Register Resource Providers (Mandatory) Register the following providers on both subscriptions: Microsoft.Network Microsoft.DBforPostgreSQL This step alone resolves most cross‑subscription approval failures. Azure resource providers and types - Azure Resource Manager | Microsoft Learn Step 2: Validate Network Permissions Ensure the approving identity can perform: Microsoft.Network/privateEndpoints/privateLinkServiceProxies/write Grant Network Contributor if needed. Step 3: Refresh Credentials and Retry If changes were made recently: Sign out and sign in again Retry the Private Endpoint approval Post‑Resolution Outcome After correcting provider registration and permissions: Private Endpoint approval succeeds immediately Connection state transitions from Pending → Approved No further authorization or retry errors PostgreSQL connectivity works as expected Prevention & Best Practices Pre‑register required Resource Providers in landing zones Validate cross‑subscription readiness before creating Private Endpoints Document service‑specific approval requirements (PostgreSQL differs from Key Vault) Automate provider registration via policy or IaC where possible Include provider validation in enterprise onboarding checklists Why This Matters Missing provider registration can lead to: Failed Private Endpoint approvals Confusing authorization errors Extended troubleshooting cycles Production delays during go‑live A simple subscription readiness check prevents downstream networking failures that are difficult to diagnose from portal errors alone. Key Takeaways Issue: Azure PostgreSQL private endpoint approval fails across subscriptions Root Cause: Missing Resource Provider registration Fix: Register Microsoft.Network and Microsoft.DBforPostgreSQL on both subscriptions Result: Approval succeeds without backend authorization failures References Manage Azure Private Endpoints – Azure Private Link Approve Private Endpoint Connections – Azure Database for PostgreSQL Private Endpoint Overview – Azure Private LinkGeneral Availability of Graph Database Support in Azure Database for PostgreSQL
We are excited to announce the general availability of the Apache AGE extension for Azure Database for PostgreSQL! This marks a significant milestone in empowering developers and businesses to harness the potential of graph data directly within their PostgreSQL environments, offering fully managed graph database service. Unlocking Graph Data Capabilities Apache AGE (A Graph Extension) is a powerful PostgreSQL extension. It allows users to store and query graph data within Postgres seamlessly, enabling advanced insights through intuitive graph database queries via the openCypher query language. Graph data is instrumental in applications such as social networks, recommendation systems, fraud detection, network analysis, and knowledge graphs. By integrating Apache AGE into Azure Database for PostgreSQL, developers can now benefit from a unified platform that supports both relational and graph data models, unlocking deeper insights and streamlining data workflows. Benefits of Using Apache AGE in Azure Database for PostgreSQL The integration of Apache AGE (AGE) in Azure Database for PostgreSQL brings numerous benefits to developers and businesses looking to leverage graph processing capabilities: Enterprise-grade Managed Graph Database Service: AGE in Azure Database for PostgreSQL provides a fully managed graph database solution, eliminating infrastructure management while delivering built-in security, updates, and high availability. Simplified Data Management: AGE's ability to integrate graph and relational data simplifies data management tasks, reducing the need for separate graph database solutions. Enhanced Data Analysis: With AGE, you can perform complex graph analyses directly within your PostgreSQL database, gaining deeper insights into relationships and patterns in your data. Cost Efficiency: By utilizing AGE within Azure Database for PostgreSQL, you can consolidate your database infrastructure, lowering overall costs and reducing the complexity of your data architecture. Security and Compliance: Leverage Azure's industry-leading security and compliance features, ensuring your graph data is protected and meets regulatory requirements. Index Support: Index graph properties with BTREE and GIN indexes. Real-World Applications Apache AGE opens up a range of possibilities for graph-powered applications. Here are just a few examples: Social Networks: Model and analyze complex relationships, such as user connections and interactions. Fraud Detection: Identify suspicious patterns and connections in financial transactions. Recommendation Systems: Leverage graph data to deliver personalized product or content recommendations. Knowledge Graphs: Structure facts and concepts as nodes and relationships, enabling AI-driven search and data discovery. In the following example, we need to provide Procurement with an updated status of all statements of work (SOW) by vendor, including their invoice status. With AGE and Postgres, this once complex task becomes quite simple. We’ll start by creating the empty graph. SELECT ag_catalog.create_graph('vendor_graph'); Then, we’ll create all the ‘vendor’ nodes from the vendors table. SELECT * FROM ag_catalog.cypher( 'vendor_graph', $$ UNWIND $rows AS v CREATE (:vendor { id: v.id, name: v.name }) $$, ARRAY( SELECT jsonb_build_object('id', id, 'name', name) FROM vendors ) ); Next, we’ll create all the ‘sow’ nodes. SELECT * FROM ag_catalog.cypher( 'vendor_graph', $$ UNWIND $rows AS s CREATE (:sow { id: s.id, number: s.number }) $$, ARRAY( SELECT jsonb_build_object('id', id, 'number', number) FROM sows ) ); Then, we’ll create the ‘has_invoices’ relationships (edges). SELECT * FROM ag_catalog.cypher( 'vendor_graph', $$ UNWIND $rows AS r MATCH (v:vendor { id: r.vendor_id }) MATCH (s:sow { id: r.sow_id }) CREATE (v)-[:has_invoices { payment_status: r.payment_status, amount: r.invoice_amount }]->(s) $$, ARRAY( SELECT jsonb_build_object( 'vendor_id', vendor_id, 'sow_id', sow_id, 'payment_status', payment_status, 'invoice_amount', amount ) FROM invoices ) ); Now that we’ve completed these steps, we have a fully populated vendor_graph with vendor nodes, sow nodes, and has_invoices edges with the invoice attributes. We’re ready to query the graph to start our report for Procurement. SELECT * FROM ag_catalog.cypher('vendor_graph' , $$ MATCH (v:vendor)-[rel:has_invoices]->(s:sow) RETURN v.id AS vendor_id, v.name AS vendor_name, s.id AS sow_id, s.number AS sow_number, rel.payment_status AS payment_status, rel.amount AS invoice_amount $$) AS graph_query(vendor_id BIGINT, vendor_name TEXT, sow_id BIGINT, sow_number TEXT, payment_status TEXT, invoice_amount FLOAT); This statement invokes Apache AGE’s Cypher engine that treats our graph as a relational table: ag_catalog.cypher('vendor_graph', $$ … $$) executes the Cypher query against the graph named “vendor_graph.” The inner Cypher fragment, MATCH (v:vendor)-[rel:has_invoices]->(s:sow) RETURN v.id AS vendor_id, v.name AS vendor_name, s.id AS sow_id, s.number AS sow_number, rel.payment_status AS payment_status, rel.amount AS invoice_amount finds every vendor node with outgoing has_invoices edges to SOW nodes projects each vendor’s ID/name, the target sow’s ID/number, and invoice attributes. Wrapping that in … ) AS graph_query( vendor_id BIGINT, vendor_name TEXT, sow_id BIGINT, sow_number TEXT, payment_status TEXT, invoice_amount FLOAT ); tells PostgreSQL how to map each returned column into a regular SQL result set with proper types. The result? You get a standard table of rows—one per invoice edge—with those six columns populated and ready for further SQL joins, filters, aggregates, etc. Performance notes for this example: AGE will scan all “vendor–has_invoices–sow” paths in the graph. If the graph is large, consider an index on the vendor or sow label properties or filter by additional predicates. You can also push WHERE clauses into the Cypher fragment for more selective matching. Scaling to Large Graphs with AGE The Apache AGE extension in Azure Database for PostgreSQL enables seamless scaling to large graphs. Indexing plays a pivotal role in enhancing query performance, particularly for complex graph analyses. Effective Indexing Strategies To optimize graph queries, particularly those involving joins or range queries, implementing the following indexes is recommended: BTREE Index: Ideal for exact matches and range queries. For vertex tables, create an index on the unique identifier column (e.g., id). CREATE INDEX ON graph_name."VLABEL" USING BTREE (id); GIN Index: Designed for efficient searches within JSON fields, such as the properties column in vertex tables. CREATE INDEX ON graph_name."VLABEL" USING GIN (properties); Edge Table Indexes: For relationship traversal, use BTREE indexes on start_id and end_id columns. CREATE INDEX ON graph_name."ELABEL" USING BTREE (start_id); CREATE INDEX ON graph_name."ELABEL" USING BTREE (end_id); Example: Targeted Key-Value Indexing For targeted queries that focus on specific attributes within the JSON field, a smaller BTREE index can be created for precise filtering. CREATE INDEX ON graph_name.label_name USING BTREE (agtype_access_operator(VARIADIC ARRAY[properties, '"KeyName"'::agtype])); Using these indexing strategies ensures efficient query execution, even when scaling large graphs. Additionally, leveraging the EXPLAIN command helps validate index utilization and optimize query plans for production workloads. How to Get Started Enabling Apache AGE in Azure Database for PostgreSQL is simple: 1. Update Server Parameters Within the Azure Portal, navigate to the PostgreSQL Flexible Server instance and select the Server Parameters option. Adjust the following settings: azure.extensions: In the parameter filter, search for and enable AGE among the available extensions. shared_preload_libraries: In the parameter filter, search for and enable AGE. Click Save to apply these changes. The server will restart automatically to activate the AGE extension. Note: Failure to enable the shared_preload_libraries will result in the following error when you first attempt to use the AGE schema in a query. “ERROR: unhandled cypher(cstring) function call error on first cypher query” 2. Enable AGE Within PostgreSQL Once the server restart is complete, connect to the PostgreSQL instance using the psql interpreter. Execute the following command to enable AGE: CREATE EXTENSION IF NOT EXISTS AGE CASCADE; 3. Configure Schema Paths AGE adds a schema called ag_catalog, which is essential for handling graph data. Ensure this schema is included in the search path by executing: SET search_path=ag_catalog,"$user",public; That’s it! You’re ready to create your first graph within PostgreSQL on Azure. Ready to dive in? Experience the power of graph data with Apache AGE on Azure Database for PostgreSQL. Visit AGE on Azure Database for PostgreSQL Overview for more details, and explore how this extension can transform your data analysis and application development. Get started for free with an Azure free accountAzure PostgreSQL Lesson Learned#9: How to Stay Informed About Planned Maintenance and Alerts
Customers often miss planned maintenance notifications for Azure Database for PostgreSQL Flexible Server because emails go only to subscription owners. This post explains why that happens and how to stay informed by using Azure Service Health alerts, checking the Planned Maintenance page, and configuring proactive notifications. Following these best practices ensures operational readiness and prevents unexpected downtime.Azure PostgreSQL Lesson Learned#7: Database Missing After Planned Maintenance
Co‑authored with HaiderZ-MSFT Overview If you’re running Azure Database for PostgreSQL Flexible Server, you might encounter a scenario where your database disappears after planned maintenance. This blog explains: Root cause of the issue Troubleshooting steps Best practices to prevent data loss Symptoms After a maintenance window, customers reported connection failures with errors like: connection failed: connection to server at 'IP', port 5432 failed: FATAL: database 'databaseName' does not exist DETAIL: The database subdirectory 'pg_tblspc/.../PG_...' is missing. Even after a successful restore, the database remains inaccessible! Root Cause The missing database files were located in a temporary tablespace. On Azure Database for PostgreSQL Flexible Server: A default temporary tablespace is created for internal operations (e.g., sorting). It is not backed up during maintenance, restarts, or HA failovers. If permanent objects or entire databases are created in this temporary tablespace, they will be lost after: Planned maintenance windows Server restarts High availability failovers Important: Temporary tablespaces are designed for transient data only. Storing persistent objects here is unsafe. [Limits in...soft Learn | External], [Overview o...soft Learn | External] Operational Checks To confirm if a database uses a temporary tablespace: select datname, dattablespace from pg_database where datname = '<dbname>'; Compare dattablespace OID with pg_tablespace: select oid, spcname, spcowner from pg_tablespace If OID matches temptblspace, the database resides in a temporary tablespace. Mitigation Unfortunately, data cannot be recovered because temporary tablespaces are excluded from backups during maintenance activities or server restarts. Recommended actions: Do not create permanent objects or databases in temporary tablespaces. Always use the default tablespace inherited from the template database. Prevention & Best Practices Avoid using temptblspace for persistent data. Validate tablespace before creating databases:SQL Follow official guidelines: Limits in Azure Database for PostgreSQL Flexible Server Business Continuity in Azure Database for PostgreSQL Why This Matters Creating databases in temporary tablespaces leads to: Permanent data loss after maintenance. Failed connections and restore attempts. Operational disruption and downtime. Key Takeaways Issue: Databases created in temporary tablespace are lost after maintenance, restarts, or HA failovers. Fix: Use default tablespace for all permanent objects. Best Practice: Never store persistent data in temporary tablespace.Azure PostgreSQL Lesson Learned#5: Why SKU Changes from Non-Confidential to Confidential Fail
Co-authored with HaiderZ-MSFT Issue Summary The customer attempted to change the server configuration from Standard_D4ds_v5 (non-Confidential Compute) to Standard_DC4ads_v5 (Confidential Compute) in the West Europe region. The goal was to enhance the performance and security profile of the server. However, the SKU change could not be completed due to a mismatch in security profiles between the current and target SKUs. Root Cause The issue occurred because SKU changes from non-Confidential to Confidential Compute types are not supported in Azure Database for PostgreSQL Flexible Server. Each compute type uses different underlying hardware and isolation technologies. As documented in Azure Confidential Computing for PostgreSQL Flexible Server, operations such as Point-in-Time Restore (PITR) from non-Confidential Compute SKUs to Confidential ones aren’t allowed. Similarly, direct SKU transitions between these compute types are not supported due to this security model difference. Mitigation To resolve the issue, the customer was advised to migrate the data to a new server created with the desired compute SKU (Standard_DC4ads_v5). This ensures compatibility while achieving the intended performance and security goals. Steps: Create a new PostgreSQL Flexible Server with the desired SKU (Confidential Compute). Use native PostgreSQL tools to migrate data: pg_dump -h <source_server> -U <user> -Fc -f backup.dump pg_restore -h <target_server> -U <user> -d <database> -c backup.dump 3. Validate connectivity and performance on the new server. 4. Decommission the old server once migration is confirmed successful. Prevention & Best Practices To avoid similar issues in the future: Review documentation before performing SKU changes or scaling operations: Azure Confidential Computing for PostgreSQL Flexible Server Confirm compute type compatibility when planning scale or migration operations. Plan migrations proactively if you anticipate needing a different compute security profile. Use tools such as pg_dump / pg_restore or Azure Database Migration Service. Check regional availability for Confidential Compute SKUs before deployment. Why these matters Understanding the distinction between Confidential and non-Confidential Compute is essential to maintain healthy business progress. By reviewing compute compatibility and following the documented best practices, customers can ensure smooth scaling, enhanced security, and predictable database performance.
