Postgresql Flexble server on-demand backup

Question

HI Microsoft,

I was doing a test of the preview feature 'on-demand' backup for Azure PostreSQL flexing instance.

When I clicked "Backup (Preview)" option under Settings ==> Backup it started the backup as expected but I noticed that the Flexible instance status turned from "Ready" to "Updating"

Any reason why the database goes down when backup?

Answer 1

Hi subhash-DBA,

Any reason why the database goes down when backup?

When performing an on-demand backup, the server will enter an updating state. During this time, server management operations such as modifying server parameters, changing high availability options, or adding or removing firewalls will be unavailable.

This status change indicates that the system is performing maintenance operations to ensure data consistency during the backup process. It's important to note that the updating state only affects server management operations and does not affect data plane operations.

This means that your database server will remain fully functional and able to accept connections, as well as serve read and write traffic.

Answer 2

@PratikLad

Thanks for your response.

Answer 3

Hello subhash-DBA,

Thank you for posting your question in the Microsoft Q&A forum.

The behavior you're observing, where the Azure PostgreSQL Flexible Server instance status changes from "Ready" to "Updating" during an on-demand backup, is expected and occurs due to the underlying architecture and process of how backups are handled in Azure PostgreSQL Flexible Server.

Azure PostgreSQL Flexible Server uses a snapshot-based backup mechanism for on-demand backups. This involves creating a point-in-time snapshot of the storage volume associated with the database. To ensure data consistency during the backup process, the database instance may need to temporarily pause certain operations or enter a state where it is not fully accessible.

Few insights on why status changes to “Updating”:

• Storage-Level Snapshot: When you initiate an on-demand backup, Azure triggers a storage-level snapshot. This requires the database to briefly pause write operations to ensure the snapshot is consistent.
• Instance State: During this process, the instance status changes to "Updating" to reflect that the database is in a transitional state while the backup is being taken.
• Temporary Impact: While the backup is in progress, the database may experience a brief period of reduced availability or performance, depending on the workload and the size of the database.

Some recommended best practices as below:

• Schedule Backups During Off-Peak Hours: If you are concerned about the impact of on-demand backups, consider scheduling automated backups during periods of low activity.
• Monitor Backup Duration: Keep an eye on how long the backup process takes. If the "Updating" state persists for an unusually long time, investigate potential issues with the database or storage.
• Use Read Replicas: If high availability is critical, consider using read replicas to offload read traffic during backup operations.

The status change to "Updating" during an on-demand backup is a normal part of the backup process for Azure PostgreSQL Flexible Server. It ensures data consistency and is typically short-lived. If the behavior persists or causes significant downtime, you may want to review your database configuration.

If the above answer helped, please do not forget to "Accept Answer" as this may help other community members to refer the info if facing a similar issue. Your contribution to the Microsoft Q&A community is highly appreciated.

Answer 4

Thanks a lot again for your responses @Mallaiah Sangi

So, if this is(Write pause) part of daily backup, then - what is the impact to connected applications/clients during that time?

Note that there are no option to schedule automated backup from customer side, it happens automatically. For example, for our workload running for Australia the backup timings are in the middle of the day (times show below are in UTC). so wondering how that impacts to our transaction

Answer 5

@Mallaiah Sangi

I think you are not answering the questions , let me phrase it this way.

1. What's the impact to connected applications/sessions/clients when backup happens (backup leads to 'write pause') . You have mentioned earlier that 'write pause' happen for both on-demand and 'automated daily' backup.
2. Scheduling the backup 'non-business hours' : There are no options to schedule "automated backup" (the default snapshot backup i mean) for Azure PostgreSQL flexible instance. So what do you mean by the best practice of scheduling backup ?