Backup Vault Corruption - PostgreSQL Flexible Server

Question

Backup Vault Corruption - PostgreSQL Flexible Server

Chris J 25

I'm having problems with the long term retention option of PostgreSQL Flexible Server.

When I configure a backup vault everything is fine and I'm able to create a backup on schedule or on-demand.

However, when I restore the data to a storage account the restored data is mostly corrupt. The restore consists of text files (SQL scripts), one for each database.

I say mostly, because some of the files e.g. the roles and schemas seem ok. But the database SQL scripts are unreadable.

I've tried deleting the vault and recreating, but I get the same issue. If I run pg_dump against the server, all the databases dump to file as expected.

Also very easy to reproduce, just create a PostgreSQL Flexible Server and configure Azure Backup Vault. Restore the default databases and they're all corrupt.

Seems to be a fault with the Azure Backup process, anyone else had this problem?

Mahad Subhan 0 Reputation points

2025-10-31T14:43:30.66+00:00

The Core Problem: Corrupted Logical Dump

When you enable Long-Term Retention (LTR) using the Backup Vault and restore to a Storage Account, Azure is performing a logical backup and restore. This means it's running a tool like pg_dump on the database to create text files containing the SQL commands needed to rebuild your data.

Your finding—that the schema and roles look fine, but the large database scripts are corrupted—strongly suggests one of two things:

Character Encoding Failure: The most likely culprit. The tool Azure uses to run pg_dump is likely using an incorrect character encoding (like writing the file as UTF-8 but using an older standard like Latin-1 for interpretation). When it dumps the actual data (which is the majority of the file), all the non-ASCII characters get scrambled, making the file unreadable ("corrupt").

Compression/Decompression Error: The backup process often compresses the SQL files to save space. A bug in the decompression part of the restore process (when moving the file back to the Storage Account) could be introducing random corruption into the data blocks.

The fact that your manual pg_dump works perfectly confirms that the issue is not with your server, your data, or PostgreSQL itself, but specifically with the Azure Backup service's execution environment.
Chris J 25 Reputation points

2025-10-31T16:42:05.8+00:00

That's a good shout, and could be either of those. Hopefully someone from MS will see this.

Here's what the restored file typically looks like from Storage Account. Opening is VS Code is the same and thinks its binary;
Mahad Subhan 0 Reputation points

2025-10-31T17:24:14.92+00:00
. The file is actually binary

If the file is something like .zip, .bak, .dll, .exe, .jpg, etc., it’s not a text file.

Fix:

Check the file’s extension.

Open it with the correct software (e.g., .bak → SQL Server, .zip → WinRAR, .jpg → image viewer).

🔹 2. Encoding issue

Sometimes text files (like .json, .log, .txt) get restored with the wrong encoding, which makes them look unreadable.

Fix:

Open the file in VS Code.

Look at the bottom-right corner for the encoding (e.g., UTF-8, UTF-16).

Click it → choose “Reopen with Encoding” → select UTF-8.

If that doesn’t work, try UTF-16 LE or ASCII.

🔹 3. File got corrupted during restore

If the file didn’t restore properly, its content might be damaged.

Fix:

Try downloading or restoring it again.

Compare its size with the original file to make sure it’s complete.

🔹 4. File was downloaded incorrectly from Blob Storage

If you used code or Azure CLI, maybe the file was downloaded in text mode instead of binary mode, which corrupts it.

Fix (for developers):

In Python:

with open('file.bin', 'wb') as f:

blob_client.download_blob().readinto(f)

Or using Azure CLI:

az storage blob download --container-name mycontainer --name myfile --file myfile.ext

Answer accepted by question author

1 additional answer

Your answer

Mahad Subhan 0 Reputation points

2025-10-31T14:43:30.66+00:00

The Core Problem: Corrupted Logical Dump

When you enable Long-Term Retention (LTR) using the Backup Vault and restore to a Storage Account, Azure is performing a logical backup and restore. This means it's running a tool like pg_dump on the database to create text files containing the SQL commands needed to rebuild your data.

Your finding—that the schema and roles look fine, but the large database scripts are corrupted—strongly suggests one of two things:

Character Encoding Failure: The most likely culprit. The tool Azure uses to run pg_dump is likely using an incorrect character encoding (like writing the file as UTF-8 but using an older standard like Latin-1 for interpretation). When it dumps the actual data (which is the majority of the file), all the non-ASCII characters get scrambled, making the file unreadable ("corrupt").

Compression/Decompression Error: The backup process often compresses the SQL files to save space. A bug in the decompression part of the restore process (when moving the file back to the Storage Account) could be introducing random corruption into the data blocks.

The fact that your manual pg_dump works perfectly confirms that the issue is not with your server, your data, or PostgreSQL itself, but specifically with the Azure Backup service's execution environment.
Chris J 25 Reputation points

2025-10-31T16:42:05.8+00:00

That's a good shout, and could be either of those. Hopefully someone from MS will see this.

Here's what the restored file typically looks like from Storage Account. Opening is VS Code is the same and thinks its binary;
Mahad Subhan 0 Reputation points

2025-10-31T17:24:14.92+00:00

. The file is actually binary

If the file is something like .zip, .bak, .dll, .exe, .jpg, etc., it’s not a text file.

Fix:

Check the file’s extension.

Open it with the correct software (e.g., .bak → SQL Server, .zip → WinRAR, .jpg → image viewer).

🔹 2. Encoding issue

Sometimes text files (like .json, .log, .txt) get restored with the wrong encoding, which makes them look unreadable.

Fix:

Open the file in VS Code.

Look at the bottom-right corner for the encoding (e.g., UTF-8, UTF-16).

Click it → choose “Reopen with Encoding” → select UTF-8.

If that doesn’t work, try UTF-16 LE or ASCII.

🔹 3. File got corrupted during restore

If the file didn’t restore properly, its content might be damaged.

Fix:

Try downloading or restoring it again.

Compare its size with the original file to make sure it’s complete.

🔹 4. File was downloaded incorrectly from Blob Storage

If you used code or Azure CLI, maybe the file was downloaded in text mode instead of binary mode, which corrupts it.

Fix (for developers):

In Python:

with open('file.bin', 'wb') as f:

blob_client.download_blob().readinto(f)

Or using Azure CLI:

az storage blob download --container-name mycontainer --name myfile --file myfile.ext

Answer 1

Hi Chris J,

Thank you for reaching out to Microsoft QA. We understand the restored files from the Backup Vault look corrupted.

In most cases, these files are not damaged but saved in a compressed or custom PostgreSQL format. That is why smaller files like schema or roles look fine, but large database files appear unreadable in editors.

Please try these steps:

Download one of the affected files using Azure CLI or Storage Explorer (avoid browser download).
Run this command to check the file type: file <filename>
If it shows gzip compressed, run: gunzip -c <filename> > restored.sql If it shows PostgreSQL custom dump, run: pg_restore -Fc <filename> -d <target_database>

If the file still looks unreadable, please share these details in private message
Backup Vault name and region, Restore Job ID and time (UTC), One affected file name, how the file was downloaded

We will check the backend logs to confirm whether this is related to compression or encoding during restore.

Kindly let us know if you have any questions or concerns.

Answer 2

Chris J 25

After running the file <filename> command I realised the format of the database sql scripts was indeed PostgreSQL custom database dump - v1.15-0 so not corrupt at all.

Thanks for your help, I was able to successfully test the restore process which worked.

I think the documentation should state this, because I like many will assume the sql script should be text and its not.

Manoj Kumar Boyini 1,330 Reputation points Microsoft External Staff Moderator

2025-11-06T12:01:55.9166667+00:00

Hi Chris J,

Hope this helps. Let me know if you have any further questions or need additional assistance. Also, if these answers your query, do click the "Upvote" and click "Accept the answer" of which might be beneficial to other community members reading this thread.

Share via

Backup Vault Corruption - PostgreSQL Flexible Server

The Core Problem: Corrupted Logical Dump

1 additional answer

Your answer