About Azure Database for PostgreSQL backup

Azure Backup and Azure Database Services have come together to build an enterprise-class backup solution for Azure Database for PostgreSQL servers that retains backups for up to 10 years. Besides long-term retention, the solution offers the following capabilities:

• Customer controlled scheduled and on-demand backups at the individual database level.
• Database-level restores to any PostgreSQL server or to any blob storage.
• Central monitoring of all operations and jobs.
• Backups are stored in separate security and fault domains. If the source server or subscription is compromised in any circumstances, the backups remain safe in the Backup vault (in Azure Backup managed storage accounts).
• Use of pg_dump allows a greater flexibility in restores. This helps you restore across database versions

You can use this solution independently or in addition to the native backup solution offered by Azure PostgreSQL that offers retention up to 35 days. The native solution is suited for operational recoveries, such as when you want to recover from the latest backups. The Azure Backup solution helps you with your compliance needs and more granular and flexible backup/restore.

Changes to Vaulted backups for PostgreSQL single server

Azure Database for PostgreSQL single servers is scheduled for retirement on March 28, 2025. On this date, changes will be implemented to Azure Backup for PostgreSQL single servers. Learn more about the retirement announcement here.

Azure Backup provides compliance and resiliency solutions, including vaulted backups and long-term retention of restore points. Starting March 28, 2025, the following changes will take effect:

• The backup configuration won't be allowed for new PostgreSQL single server workloads.
• All scheduled backup jobs will be permanently discontinued.
• Creation of new backup policies or modification of existing ones for this workload won't be possible.

Scheduled backup jobs for your PostgreSQL single server databases will be permanently stopped, and no new restore points will be created after this date. However, your existing PostgreSQL single server database backups will be retained as per the backup policy. The restore point will only be deleted after the expiration of the retention period. To retain the restore points indefinitely or delete them before the expiration of their retention period, see the Azure Business Continuity Center console.

Changes in billing

You'll no longer be charged Protected Instance (PI) fee after March 31, 2025 for protecting your PostgreSQL single server databases. But, Storage fee for storing your backups will still apply after March 31, 2025. To avoid the Storage fee, delete all restore points from the Azure Business Continuity Center.

Бележка

Azure Backup will retain the last restore point even after the expiration of its retention period. This feature ensures that you have access to the last restore point for future use. You can only delete the last restore point manually. If you want to delete the last restore point and save on Storage fee, stop the database protection.

Changes in restore

You can restore PostgreSQL single server databases as Restore as Files. Then you need to manually create a new PostgreSQL flexible server from the restored files.

Бележка

Restore as Database won't be supported after March 28, 2025; only Restore as Files will be supported.

Backup process

1. As a backup admin, you can specify the Azure PostgreSQL databases that you intend to back up. Additionally, you can also specify the details of the Azure key vault that stores the credentials needed to connect to the specified database(s). These credentials are securely seeded by the database admin in the Azure key vault.
2. The backup service then validates if it has appropriate permissions to authenticate with the specified PostgreSQL server and to back up its databases.
3. Azure Backup spins up a worker role (VM) with a backup extension installed in it to communicate with the protected PostgreSQL server. This extension consists of a coordinator and a PostgreSQL plugin. The coordinator triggers workflows for various operations, such as backup and restore, and the plugin manages the actual data flow.
4. At the scheduled time, the coordinator communicates with the plugin, for it to start streaming the backup data from the PostgreSQL server using pg_dump (custom).
5. The plugin sends the data directly to the Azure Backup managed storage accounts (masked by the Backup vault), eliminating the need for a staging location. The data is encrypted using Microsoft-managed keys and stored by the Azure Backup service in storage accounts.

Azure Backup authentication with the PostgreSQL server

Azure Backup follows strict security guidelines laid down by Azure; permissions on the resource to be backed up aren't assumed and need to be explicitly given by the user.

Key-vault based authentication model

The Azure Backup service needs to connect to the Azure PostgreSQL while taking each backup. While ‘username + password’ (or connection string), corresponding to the database, are used to make this connection, these credentials aren’t stored with Azure Backup. Instead, these credentials need to be securely seeded by the database admin in the Azure key vault as a secret. The workload admin is responsible to manage and rotate credentials; Azure Backup calls for the most recent secret details from the key vault to take the backup.

Set of permissions needed for Azure PostgreSQL database backup

1. Grant the following access permissions to the Backup vault’s MSI:

   • Reader access on the Azure PostgreSQL server.
   • Key Vault Secrets User (or get, list secrets) access on the Azure key vault.

2. Network line of sight access on:

   • The Azure PostgreSQL server – Allow access to Azure services flag to be set to Yes.
   • The key vault – Allow trusted Microsoft services flag to be set to Yes.

3. Database user’s backup privileges on the database

Бележка

You can grant these permissions within the configure backup flow with a single click if you (the backup admin) have ‘write’ access on the intended resources, or use an ARM template if you don’t have the required permissions (when multiple personas are involved).

Set of permissions needed for Azure PostgreSQL database restore

Permissions for restore are similar to the ones needed for backup and you need to grant the permissions on the target PostgreSQL server and its corresponding key vault. Unlike in configure backup flow, the experience to grant these permissions inline is currently not available. Therefore, you need to manually grant the access on the Postgres server and the corresponding key vault.

Additionally, ensure that the database user (corresponding to the credentials stored in the key vault) has the following restore privileges on the database:

• ALTER USER username CREATEDB;
• Assign the role azure_pg_admin to the database user.

Microsoft Entra ID based authentication model

We had earlier launched a different authentication model that was entirely based on Microsoft Entra ID. However, we now provide the new key vault-based authentication model (as explained above) as an alternative option, which eases the configuration process.

Download this document to get an automated script and related instructions to use this authentication model. It’ll grant an appropriate set of permissions to an Azure PostgreSQL server, for backup and restore.

Бележка

All the new configure protection will take place with the new key vault authentication model only. However, all the existing backup instances configured protection with the Microsoft Entra ID based authentication will continue to exist and have regular backups taken. To restore these backups, you need to follow the Microsoft Entra ID based authentication.

Grant access on the Azure PostgreSQL server and Key vault manually

To grant all the access permissions needed by Azure Backup, refer to the following sections:

Access permissions on the Azure PostgreSQL server

1. Set Backup vault’s MSI Reader access on the Azure PostgreSQL server.

   

2. Network line of sight access on the Azure PostgreSQL server: Set ‘Allow access to Azure services’ flag to ‘Yes’.

   

Access permissions on the Azure Key vault (associated with the PostgreSQL server)

1. Set Backup vault’s MSI Key Vault Secrets User (or get, list secrets) access on the Azure key vault. To assign permissions, you can use role assignments or access policies. It’s not required to add the permission using both the options as it doesn’t help.

   • Using Azure role-based access control (Azure RBAC) authorization (that is, Permission model is set to Azure role-based access control):

     • Under Access control, grant the backup vault’s MSI Key Vault Secrets User access on the key vault. Bearers of that role will be able to read secrets.
     • Grant permission to applications to access an Azure key vault using Azure RBAC.

   

   

   • Using access policies (that is, Permission model is set to Vault access policy):

     • Set Get and List permissions on secrets.
     • Learn about Assign an Azure Key Vault access policy

     

2. Network line of sight access on the key vault: Set the Allow trusted Microsoft services flag to Yes.

   

Database user’s backup privileges on the database

Run the following query in PG admin tool (replace username with the database user ID):

DO $do$
DECLARE
sch text;
BEGIN
EXECUTE format('grant connect on database %I to %I', current_database(), 'username');
FOR sch IN select nspname from pg_catalog.pg_namespace
LOOP
EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO username $$, sch);
EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO username $$, sch);
EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO username $$, sch);
EXECUTE format($$ GRANT SELECT ON ALL SEQUENCES IN SCHEMA %I TO username $$, sch);
EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON SEQUENCES TO username $$, sch);
END LOOP;
END;
$do$

Бележка

If a database for which backup was already configured is failing with UserErrorMissingDBPermissions Please refer to this troubleshooting guide for assistance in resolving the issue.

Use the PG admin tool

Download PG admin tool if you don’t have it already. You can connect to the Azure PostgreSQL server through this tool. Also, you can add databases and new users to this server.

Create new server with a name of your choice. Enter the Host name/address name same as the Server name displayed in the Azure PostgreSQL resource view in the Azure portal.

Ensure that you add the current client ID address to the Firewall rules for the connection to go through.

You can add new databases and database users to the server. For database users, add a new Login/Group Roles’. Ensure Can login? is set to Yes.

Next steps

Azure Database for PostgreSQL backup