PostgreSQL Server Became Unresponsive During Fabric Mirroring Setup - Required Failover to Backup

Question

I was following the official documentation for mirroring PostgreSQL into Microsoft Fabric, specifically working through the "Prepare databases for mirroring" workflow.

What Happened: When I reached the mirroring preparation step, it triggered a server restart that lasted over 1 hour (the lengthy downtime was unexpected and not mentioned in the docs). This caused our production APIs that depend on this database to go offline, forcing us to cancel the Fabric mirroring deployment in an attempt to restore service.

Issue: After cancelling the mirroring deployment, our database server never recovered to a usable state. The Azure metrics page showed abnormally high throughput, and our applications were unable to connect with the following error:

django.db.utils.OperationalError: connection failed: connection to server at "xxxxx", port xxxx failed: FATAL:  server login has been failing, try again later (server_login_retry)

The database remained unresponsive, and we ultimately had to fail over to our backup server to restore service.

Environment:

• PostgreSQL version: 17.5
• Service: Azure PostgreSQL Flexible Server

Questions:

1. What could have triggered this prolonged restart and subsequent connection failures during the Fabric mirroring preparation?
2. Is there a way to recover from this state without requiring a failover?
3. What precautions should we take before attempting Fabric mirroring setup again to avoid a repeat of this incident?

Answer 1

1. The prolonged restart and subsequent connection failures during the Fabric mirroring preparation could have been triggered by several factors, including resource constraints or configuration changes that led to high throughput on the server. If the Fabric capacity was paused or exceeded, it might have caused mirroring to stop or pause, leading to unexpected behavior during the setup process. Additionally, any persistent resource errors could disable mirroring, resulting in connection issues.
2. To recover from this state without requiring a failover, you can try the following steps:
   • Check the Azure metrics for any resource bottlenecks and address them accordingly.
   • Execute the SQL query SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction'; to identify any hanging transactions that could be causing the unresponsiveness.
   • Review the configuration of the mirrored PostgreSQL database using SELECT * FROM pg_replication_slots; to ensure it was properly enabled.
   • If the database remains unresponsive, contacting Azure support may be necessary for further assistance.
3. Before attempting the Fabric mirroring setup again, consider the following precautions:
   • Ensure that your Azure Database for PostgreSQL flexible server has sufficient capacity to handle the expected load during the mirroring process.
   • Monitor the server's performance metrics closely to identify any potential issues before starting the mirroring setup.
   • Review the official documentation for any updates or additional recommendations regarding mirroring setup.
   • Consider performing the mirroring setup during off-peak hours to minimize the impact on production services.

---

References:

• Troubleshoot Fabric mirrored databases from Azure Database for PostgreSQL flexible server
• SQL queries for troubleshooting