Azure Postgres Flexible server - custom read replication

Question

Greetings,

I have a need to create a custom read replication for my Azure Postgres Flexible server instance. It is currently not deployed as High-Availability, but it can be promoted. I am mentioning HA because I've read in a few places read-replication depends on it.

The main requirement is to be able to create a logical replication on the server which can be subscribed to by a custom postgres server, specifically one outside of Azure cloud. We can ignore network requirements for the purpose of this question.

The subscribed server should have a read-only replica available on the local system where the server is running.

Is this currently possible with Azure Postgres Flexible server?

Answer 1

Hi @ Nemanja Simpraga

Yes, Azure Database for PostgreSQL Flexible Server supports logical replication, which allows you to replicate data to a custom PostgreSQL server outside of Azure. Logical replication works by using publications and subscriptions, meaning your Azure PostgreSQL server can act as a publisher, and your external PostgreSQL server can act as a subscriber.

Steps to enable logical replication:

Enable logical replication by setting the wal_level parameter to logical in your Azure PostgreSQL Flexible Server.

Grant replication permissions to the admin user with ALTER ROLE <adminname> WITH REPLICATION;.

Create a publication on your Azure PostgreSQL Flexible Server using:

CREATE PUBLICATION my_publication FOR TABLE my_table;

Set up a subscription on your external PostgreSQL server:

CREATE SUBSCRIPTION my_subscription CONNECTION 'host=<azure_server> dbname=<dbname> user=<user> password=<password>' PUBLICATION my_publication;

Ensure replication slots are properly configured to stream change

Read-Only Replica on Local System:

Since logical replication only replicates data changes, the external PostgreSQL server will need to enforce read-only access manually. You can achieve this by:

• Using database permissions (REVOKE INSERT, UPDATE, DELETE ON ALL TABLES).

Running the replica in a read-only mode using default_transaction_read_only = on.

Please go through these documentations that might help you:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-logical

https://techcommunity.microsoft.com/blog/adforpostgresql/azure-database-for-postgresql--logical-replication/3799509

Hope this helps. Do let us know if you any further queries.