PostgreSQL flexible server stuck in 'Starting' state

Question

My PostegreSQL server was having issues where it was 100% CPU usage and not accepting connections. I have shut it down and booted it back up in the hopes to restore it but now it is stuck with this message for the last 3 days:

The server is currently in 'starting' state. Please wait for the operation to complete. You can refresh to check status. Reach out to Microsoft support if the operation is taking too long. 

I am unable to delete the resource or change any of its settings. I am not sure how to recover this resource, but happy to delete it too since I have a restore point available.

Any help would be greatly appreciated.

Answer 1

Hi Adam Rybak,

Following up our discussion that you confirmed that your server is back to online, so knidly please review the root cause of the issue.

• Your server had logical replication slots that were inactive (i.e., the replication clients were not consuming changes).
• PostgreSQL retains WAL files required by replication slots to maintain data consistency.
• Because the replication clients were inactive, the WAL files kept accumulating and were never marked for cleanup.
• Eventually, these WAL files consumed all available space on the data drive.
• Once disk usage hit 100%, PostgreSQL was unable to write new data or WALs, triggering the issue..

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hello Adam , Welcome to MS Q&A

Can you check the following article for the issue especially long-running transaction may cause this problem

https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/why-restarting-azure-database-for-postgresql-took-long/ba-p/1885780

In this scenario, the best practice is to prepare for the server restart since this is a transactional database. First, work on reducing the workload from the application by stopping the application and then kill the running processes using pg_terminate_backend on the Azure PostgreSQL database. You may also consider stopping new incoming connetion by updating the firewall rules to prevent new connections. Once all the connections are termintated run a manual checkpoint and then restart the server.

Pls let me know if any ques

Kindly accept answer if it helps

Thanks

Deepanshu