MSDN Documentation

Backup and Restore Azure Database for PostgreSQL

This guide provides detailed instructions on how to perform backups and restore operations for your Azure Database for PostgreSQL instances, ensuring data durability and disaster recovery capabilities.

Understanding Backups in Azure Database for PostgreSQL

Azure Database for PostgreSQL automatically handles backups of your data. These backups are stored in geo-redundant storage and are essential for recovering your database to a specific point in time. The service provides two primary backup types:

  • Full Backups: Taken daily to back up the entire database.
  • Differential Backups: Taken every 5 to 12 hours, backing up only the data that has changed since the last full backup.
  • Transaction Log Backups: Taken every 5 to 10 minutes, backing up all transactions that have occurred since the last transaction log backup.

Point-in-Time Restore (PITR)

Point-in-time restore allows you to restore your database to any specific second within your defined backup retention period (typically 7 to 35 days). This is invaluable for recovering from accidental data corruption or deletion.

Steps for Performing a Point-in-Time Restore:

  1. Navigate to your Azure Database for PostgreSQL server in the Azure portal.
  2. In the left-hand menu, under "Settings," select "Restore."
  3. Choose the restore point (date and time) you wish to restore to.
  4. Specify a new server name for the restored database. You cannot restore to the original server name.
  5. Select the subscription, resource group, and location for the new server.
  6. Configure the compute and storage settings for the new server, which can be different from the original.
  7. Click "Review + create" and then "Create" to initiate the restore process.
Note: Restoring a database creates a new server. You will need to update your application connection strings to point to the new server.

Manual Backups with pg_dump

While Azure Database for PostgreSQL provides automated backups, you might need to create manual backups for specific use cases, such as migrating data or creating an exact copy of your database at a particular moment.

Prerequisites:

  • Install PostgreSQL client tools, including pg_dump.
  • Have the connection details for your Azure Database for PostgreSQL server (server name, username, password, database name).

Creating a Manual Backup:

Use the pg_dump command-line utility. Here's an example of how to create a custom-format archive file:


pg_dump -h <your_server_name>.postgres.database.azure.com -U <your_username>@<your_server_name> -d <your_database_name> -Fc > my_backup.dump

You will be prompted to enter your password.

Tip: For large databases, consider using the --jobs option with pg_dump for parallel backup execution.

Restoring from a Manual Backup (pg_restore)

To restore a database from a backup file created with pg_dump -Fc, you can use the pg_restore utility.

Steps for Restoring from a Manual Backup:

  1. Ensure you have a target Azure Database for PostgreSQL server created.
  2. Use the pg_restore command:

pg_restore -h <your_server_name>.postgres.database.azure.com -U <your_username>@<your_server_name> -d <your_database_name> -v my_backup.dump

This command will restore the contents of my_backup.dump into the specified database on your server. You'll be prompted for your password.

Warning: Restoring a manual backup to an existing database will overwrite its current contents. Ensure you are restoring to the correct database and have a separate backup if needed.

Backup Retention and Management

The default backup retention period for Azure Database for PostgreSQL is 7 days. You can configure this to be between 7 and 35 days for any server. Automated backups are managed by Azure, and you do not need to take explicit action to initiate them. However, understanding the retention period is crucial for your data recovery strategy.

Changing Backup Retention Period:

  1. In the Azure portal, go to your PostgreSQL server.
  2. Under "Settings," select "Server parameters."
  3. Search for backup_retention_days.
  4. Adjust the value to your desired retention period (7-35 days).
  5. Click "Save."

Geo-Redundancy

By default, Azure Database for PostgreSQL offers geo-redundant backups. This means your backups are replicated to a secondary region, providing an additional layer of data protection against regional outages. You can choose between locally redundant or geo-redundant storage for your backups when creating your server.

Considerations for Large Databases

For very large databases, consider the following:

  • Backup Time: Full backups can take a significant amount of time.
  • Restore Time: Restoring large databases also takes time. Plan your maintenance windows accordingly.
  • Network Bandwidth: Ensure sufficient network bandwidth for manual backup and restore operations if transferring large files.
  • Performance Impact: Automated backups run with minimal impact on database performance, but manual backups using pg_dump can consume server resources.

By leveraging Azure Database for PostgreSQL's automated backup features and understanding manual backup strategies, you can effectively protect your data and ensure business continuity.

⬆