Unable to access PostgreSQL database after restore - 'database does not exist' error

Question

The other day I had issues with my database and decided to do a fast restore. Unless I didn't understand something, I had to create a new database resource for that,. In the new resource, if I go in the "Database" menu, I can see my 2 databases properly. However, when I try to connect (by clicking connect next to the database) and after entering my password in the cloud shell that appeared, I get the following error:

psql: error: connection to server at "<name>.postgres.database.azure.com" (<ip address>), port 5432 failed: FATAL: database "<db-name>" does not exist> DETAIL: The database subdirectory "pg_tblspc/16386/PG_16_202307071/24798" is missing.

Same error if I try to connect via pgAdmin or my API. What is happening?

Answer 1

@Robouste

Thank you for reaching out.

Sorry about the issue you are facing.

Seems like Your Database was created under 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. References:

• Limits in Azure Database for PostgreSQL - Flexible Server
• Overview of business continuity with Azure Database for PostgreSQL - Flexible Server

Please check and let us know.

Regards,

Oury

Answer 2

Hi
Robouste, Thanks for reaching out to Microsoft Q&A.

I think this might be a disk failure or misconfiguration. The error message says the database name does not exist. Though you say you are able to see the databases, what do you see when you list the database using 'psql -l' command.

Also what about when you try to connect the default databases? are they working fine?

I can see my 2 databases properly

What are those 2 databases?

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.