Azure PostgreSQL flexible server not allowing connections

CF 0

seems to be complaining about missing directories and files?

no changes had been made and all of a sudden we're getting these messages.

" FATAL: "pg_tblspc/[redacted]/[redacted]/[redacted]" is not a valid data directory
DETAIL: File "pg_tblspc/[redacted]/[redacted]/[redacted]/PG_VERSION" is missing. "

have tried to set this server param to "off".. but to no avail.

azure.enable_temp_tablespaces_on_local_ssd

CF 0 Reputation points

2024-09-20T13:44:06.1333333+00:00

Seems to only affect 1 database within the instance.. other databases are fine???
NIKHILA NETHIKUNTA 2,630 Reputation points Microsoft Vendor

2024-09-23T03:45:18.7833333+00:00
@CF
Thank you for the question and using Microsoft Q&A platform.

It seems like there is an issue with the data directory of the affected database. The error message indicates that the PostgreSQL server is unable to find a valid data directory for the database.

The error message also indicates that the file "PG_VERSION" is missing. This file is typically present in the data directory of a PostgreSQL database and contains the version number of the database.

One possible reason for this error could be that the data directory has been deleted or moved. Another possible reason could be that the file system containing the data directory has become corrupted.

Since this issue seems to be affecting only one database within the instance, it is possible that the data directory for this database has become corrupted or deleted.

You can try to create a new data directory for the affected database and restore the data from a backup. If you do not have a backup, you can try to recover the data using PostgreSQL's recovery tools.

Regarding the server parameter "azure.enable_temp_tablespaces_on_local_ssd", this parameter is used to enable or disable the use of local SSDs for temporary tablespaces. This parameter is not related to the issue you are facing with the missing data directory.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-troubleshoot-common-connection-issues

If the above doesn't help and if you have a support plan, could you, please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.

I hope this helps! Let me know if you have any further questions.
CF 0 Reputation points

2024-09-23T09:04:14.71+00:00

Thank Nikhila,

problem is that it's a "flexible server" we have no permissions to do anything and microsoft support (having been back and forth with them all weekend) allegedly don't have access to customer databases to generate a dump...

A very flawed implementation (that we were forced on to)
NIKHILA NETHIKUNTA 2,630 Reputation points Microsoft Vendor

2024-09-26T06:43:39.6966667+00:00

@CF
It seems that you have created the Database in temporary Tablespace. This temporary tablespace is created by default in Azure database for PostgreSQL Flexible Server to store the temporary files. As this tablespace is only meant to store temporary files, we do not back it up during maintenance activities or server restarts. Because of this, the files inside this temporary tablespace got cleaned up.

Unfortunately, we don't have a way to recover the data in this situation, we totally recommend to not create any permanent objects or databases under temporary tablespace.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-limits#storage
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-business-continuity

Hope this helps. Please let us know if you have any further questions.
CF 0 Reputation points

2024-09-26T07:59:11.4466667+00:00

Appreciate the response. We have a ticket logged with microsoft support.

We've not created it in the temptablespace, seems microsoft during some maintenance did...
NIKHILA NETHIKUNTA 2,630 Reputation points Microsoft Vendor

2024-09-27T16:27:01.3433333+00:00

@CF
Thank you for the details. Can you please provide the support ticket number here?
NIKHILA NETHIKUNTA 2,630 Reputation points Microsoft Vendor

2024-09-30T17:35:55.4933333+00:00

@CF
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

Azure PostgreSQL flexible server not allowing connections

Your answer