Share via

Azure backup vault for Postgresql Flexi instance

subhash-DBA 45 Reputation points
2024-05-01T13:43:38.7333333+00:00

HI ,

Question on the long term backup for Azure postgresql flexi - as per the doc below

https://learn.microsoft.com/en-us/azure/backup/backup-azure-database-postgresql-flex

I observed that, after the successful backup, I see the backup files in object store are .sql files. Does it mean this backup feature is using pg_dump/pg_dumpall to take postgresql flexi backup internally?

would be scale if we need to backup large databases (say 6TB). pg_dump/pg_dumpall are logical backups and usually they don't scale well compared to physical backup like snapshot etc..

Azure Database for PostgreSQL

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Azar 19,645 Reputation points
    2024-05-01T14:00:43.8033333+00:00

    Hi there subhash-DBA

    Thats a good question and thanks for posting on QandA platform

    I guess yes, you're correct in your observation. The backup feature for Azure Database for PostgreSQL - Flexible Server does utilize pg_dump internally to perform logical backups of your PostgreSQL database. These backups are stored as .sql files in the Azure Blob Storage.

    logical backups uses pg_dump are suitable for smaller databases and provide a reliable backup mechanism, they may not scale well for very large databases, such as the 6TB database you mentioned. Logical backups involve extracting SQL statements that represent the database's schema and data, For large databases like yours, you might want to consider supplementing logical backups with other backup strategies.

    If this helps kindly accept the answer thanks much.

  2. subhash-DBA 45 Reputation points
    2024-05-01T23:48:28.21+00:00

    HI Azar,

    Thanks for your response. That's interesting, especially for customers in Banking/Finance with long term retention requirement with sizeable amount of data

    would you know how and what types of backups are performed for the default one (short term retention upto 35 days) https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-backup-restore

    @Microsoft - What's the alternate for PostgreSQL Flexi instnace long term backup (faster/phsyical backup)?

    0 comments
  3. Oury Ba-MSFT 16,731 Reputation points Microsoft Employee
    2024-05-02T19:45:14.7533333+00:00

    @subhash-DBA

    Thank you for asking your question on Microsoft Q&A forum.

    In addition to Azar's answer above.@Microsoft - What's the alternate for PostgreSQL Flexi instnace long term backup (faster/phsyical backup)?

    Azure Backup offers a long-term retention (LTR) feature in preview. This allows you to define a backup schedule and retention duration that can range from seven days to 10 years in the Backup data store2. This LTR feature is designed to complement the automated physical backups provided by Azure, which are suited for operational recoveries and restoring from the latest backups1.

    GA will be available later next month or early August. And this will scale for larger databases as well and as they are logical backups, they will take more time than snapshots.

    Check the below link also.

    Source: Back up Azure Database for PostgreSQL Flexible server with long-term retention (preview)

    Hope that also helps.

    Regards,

    Oury

  4. subhash-DBA 45 Reputation points
    2024-05-06T10:27:12.9266667+00:00

    Thanks @Oury Ba-MSFT

    Any benchmark results to share the time this logical backups for a DB with 1TB size. Other question I have is - does this provide an incremental backup