Azure Postgres Flexible Server bring Database from DEV to Prod Postgres

Question

Hallo,

i have test postgres flexible Server and an prod enviroment.

I want to bring every Month a new version of the Data, so the data it self is just read only data.

After a test of the date i just want to bring this special version that is tested to the prod flexible postgres server.

I there a way to automate?

An other Question: Is there a way to create Readonly replicas for the Postgres flexible server? (maybe also in other regions?)

Answer 1

Hi @Viktor Zerf , welcome to Microsoft Q&A forum.

As per the description there are 2 queries, first is related to bringing the data from one environment to another and second is related to read replicas in Azure Postgres Flexible Server.

We can use the dump and restore functionality to achieve the same. However there is no direct way of automating the data migration from one server to another.

Upgrade your PostgreSQL database using dump and restore

This link provides multiple ways for restoring the back to new server like

1. Method 1: Using pg_dump and psql
2. Method 2: Using pg_dump and pg_restore
3. Method 3: Using streaming the dump data to the target database
4. Method 4: Using parallel dump and restore

Also Read replicas are currently not supported in Flexible server:

Limitations

But we can create the stand by replicas in Flexible server where the replication happens in synchronous mode. Zone redundant configuration enables automatic failover capability with zero data loss during planned events.

High availability concepts in Azure Database for PostgreSQL - Flexible Server

Please let us know if this helps or we can discuss more on this.

Answer 2

Hi i think i found a soltion to do the migration of the DBs for Postgres that can be later automated , so i wanted to share my solution:

1. Create an Container-Instance which contains pgAdmin and mount a share storage to that instance --> https://learn.microsoft.com/bs-latn-ba/azure/container-instances/container-instances-volume-azure-files
2. now over the azure cli i can executes comands on the instanace so i would execute pg_dump and pg_restore on that instance by creating the dump on the mounted shared storage --> https://learn.microsoft.com/en-us/azure/container-instances/container-instances-exec
3. Now the hole process can be automated by a job in jenkins or what ever by using the azure cli

The same thing can be done by azure functions but it has some limitations e.g. the timeout for long running tasks