How do I pg_dump/pg_dumpall from Azure postgres Flexi to azure files/storage?

Question

HI All,

We are in the process of designing a new monitoring platform in Azure which Azure Postgresql Flexi server. From database support/operation perspective can some one help me to understand the following. I am new to Azure

How do we use pg_dump/pg_dumpall for postgres flexi database to a storage with in Azure (Azure storage / Azure files?). The requirement is while application upgrades/changes we want to take a copy of certain tables for rollback purpose

Note: I am able to export / import data to and from azure flexi to on-pre using pg_dump/pg_dumpal. I aware of the ad-hoc backup which is in preview here - https://learn.microsoft.com/en-us/azure/backup/backup-azure-database-postgresql-flex.

Answer 1

Hi,@subhash-DBA Welcome to Microsoft Q&A thanks for posting your question.

If I understand correctly you want to know how to use pg_dump or pg_dumpall to backup certain tables in Azure Postgresql Flexi database to a storage within Azure (Azure storage / Azure files) for rollback purposes during application upgrades/changes.

you need to mount the Azure file share onto the VM where you have psql/pg_dump client library installed, you can follow the below steps.

Create an Azure Storage account or Azure Files share to store the backup files.

1. Install pg_dump or pg_dumpall on your local machine or a virtual machine in Azure.
2. Use pg_dump or pg_dumpall to backup the required tables from the Azure Postgresql Flexi database to a file.
3. Upload the backup file to the Azure Storage account or Azure Files share created in step1 Please let me know if you find any issues or need any other information. https://www.postgresql.org/docs/current/app-pg-dumpall.html https://learn.microsoft.com/en-us/azure/storage/common/storage-account-create?tabs=azure-portal https://learn.microsoft.com/en-us/azure/storage/files/storage-how-to-create-file-share?tabs=azure-portal

Regards

Geetha