Create and manage read replicas in Azure Database for PostgreSQL flexible server from the Azure portal, CLI, or REST API

  • Article

APPLIES TO: Azure Database for PostgreSQL flexible server

In this article, you learn how to create and manage read replicas in Azure Database for PostgreSQL Flexible Server from the Azure portal, CLI, and REST API. To learn more about read replicas, see the overview.

Prerequisites

Create an instance of Azure Database for PostgreSQL flexible server to be the primary server.

Note

When deploying read replicas for persistent heavy write-intensive primary workloads, the replication lag could continue to grow and might never catch up with the primary. This might also increase storage usage at the primary as the WAL files are only deleted once received at the replica.

Review primary settings

Before setting up a read replica for Azure Database for PostgreSQL Flexible Server, ensure the primary server is configured to meet the necessary prerequisites. Specific settings on the primary server can affect the ability to create replicas.

Storage auto-grow: Storage autogrow settings on the primary server and its read replicas must adhere to specific guidelines to ensure consistency and prevent replication disruptions. Refer to the Storage autogrow for detailed rules and settings.

Premium SSD v2: The current release doesn't support the creation of read replicas for primary servers using Premium SSD v2 storage. If your workload requires read replicas, choose a different storage option for the primary server.

  1. In the Azure portal, choose the Azure Database for PostgreSQL flexible server instance you want for the replica.

  2. On the Overview dialog, note the PostgreSQL version (ex 15.4). Also, note the region your primary is deployed to.

    Screenshot of review primary settings.

  3. On the server sidebar, under Settings, select Compute + storage.

  4. Review and note the following settings:

    • Compute Tier, Processor, Size (ex Standard_D4ds_v4).

    • Storage

      • Storage size (ex 128GB)
      • Autogrow

    • High Availability

      • Enabled / Disabled
      • Availability zone settings

    • Backup settings

      • Retention period
      • Redundancy Options

  5. Under Settings, select Networking.

  6. Review the network settings.

    Screenshot of server settings.

Create a read replica

To create a read replica, follow these steps:

  1. Select an existing Azure Database for PostgreSQL flexible server instance to use as the primary server.

  2. On the server sidebar, under Settings, select Replication.

  3. Select Create replica.

    Screenshot of create a replica action.

  4. Enter the Basics form with the following information.

    Screenshot showing entering the basics information.

  5. Select Review + create to confirm the creation of the replica or Next: Networking if you want to add, delete, or modify any firewall rules.

    Screenshot of modify firewall rules action.

  6. Leave the remaining defaults and then select the Review + create button at the bottom of the page or proceed to the next forms to add tags or change data encryption method.

  7. Review the information in the final confirmation window. When you're ready, select Create. A new deployment is created.

    Screenshot of reviewing the information in the final confirmation window.

  8. During the deployment, you see the primary in Updating state.

    Screenshot of primary entering into updating status. After the read replica is created, it can be viewed from the Replication window.

    Screenshot of viewing the new replica in the replication window.

  • Set the replica server name.

    Tip

    It is a Cloud Adoption Framework (CAF) best practice to use a resource naming convention that will allow you to easily determine what instance you are connecting to or managing and where it resides.

  • Select a location different from your primary but note that you can select the same region.

    Tip

    To learn more about which regions you can create a replica in, visit the read replica concepts article.

  • Set the compute and storage to what you recorded from your primary. If the displayed compute doesn't match, select Configure server and select the appropriate one.

    Note

    If you select a compute size smaller than the primary, the deployment will fail. Also be aware that the compute size might not be available in a different region.

    Screenshot of chose the compute size.

Important

Review the considerations section of the Read Replica overview.

To avoid issues during promotion of replicas constantly change the following server parameters on the replicas first, before applying them on the primary: max_connections, max_prepared_transactions, max_locks_per_transaction, max_wal_senders, max_worker_processes.

Create virtual endpoints

Note

All operations involving virtual endpoints - like adding, editing, or removing - are executed in the context of the primary server.

  1. In the Azure portal, select the primary server.

  2. On the server sidebar, under Settings, select Replication.

  3. Select Create endpoint.

  4. In the dialog, type a meaningful name for your endpoint. Notice the DNS endpoint that is being generated.

    Screenshot of creating a new virtual endpoint with custom name.

  5. Select Create.

    Note

    If you do not create a virtual endpoint you will receive an error on the promote replica attempt.

    Screenshot of promotion error when missing virtual endpoint.

List virtual endpoints

To list virtual endpoints, use the following steps:

  1. In the Azure portal, select the primary server.

  2. On the server sidebar, under Settings, select Replication.

  3. At the top of the page, you see both the reader and writer endpoints displayed, along with the names of the servers they're pointing to.

    Screenshot of virtual endpoints list.

Modify application to point to the virtual endpoint

Modify any applications that are using your Azure Database for PostgreSQL flexible server instance to use the new virtual endpoints (ex: corp-pg-001.writer.postgres.database.chinacloudapi.cn and corp-pg-001.reader.postgres.database.chinacloudapi.cn).

Promote replicas

With all the necessary components in place, you're ready to perform a promote replica to primary operation.

To promote replica from the Azure portal, follow these steps:

  1. In the Azure portal, select your primary Azure Database for PostgreSQL flexible server instance.

  2. On the server menu, under Settings, select Replication.

  3. Under Servers, select the Promote icon for the replica.

    Screenshot of selecting to promote for a replica.

  4. In the dialog, ensure the action is Promote to primary server.

  5. For Data sync, ensure Planned - sync data before promoting is selected.

    Screenshot of how to select promote for a replica.

  6. Select Promote to begin the process. Once it completes, the roles reverse: the replica becomes the primary, and the primary assumes the role of the replica.

Note

The replica you are promoting must have the reader virtual endpoint assigned, or you will receive an error on promotion.

Test applications

To perform some operations, restart your applications and then attempt those operations. Your applications should function seamlessly without modifying the virtual endpoint connection string or DNS entries. Leave your applications running this time.

Failback to the original server and region

Repeat the same operations to promote the original server to the primary.

  1. In the Azure portal, select the replica.

  2. On the server sidebar, under Settings, select Replication

  3. Under Servers, select the Promote icon for the replica.

  4. In the dialog, ensure the action is Promote to primary server.

  5. For Data sync, ensure Planned - sync data before promoting is selected.

  6. Select Promote, the process begins. Once it completes, the roles reverse: the replica becomes the primary, and the primary assumes the role of the replica.

Test applications

Again, switch to one of the consuming applications. Wait for the primary and replica status to change to Updating and then attempt to perform some operations. During the replica promote, your application might encounter temporary connectivity issues to the endpoint:

Screenshot of potential promote connectivity errors.

Add secondary read replica

Create a secondary read replica in a separate region to modify the reader virtual endpoint and to allow for creating an independent server from the first replica.

  1. In the Azure portal, choose the primary Azure Database for PostgreSQL flexible server instance.

  2. On the server sidebar, under Settings, select Replication.

  3. Select Create replica.

  4. Enter the Basics form with information in a third region (ex chinanorth3 and corp-pg-chinanorth-001)

  5. Select Review + create to confirm the creation of the replica or Next: Networking if you want to add, delete, or modify any firewall rules.

  6. Verify the firewall settings. Notice how the primary settings are copied automatically.

  7. Leave the remaining defaults and then select the Review + create button at the bottom of the page or proceed to the following forms to configure security or add tags.

  8. Review the information in the final confirmation window. When you're ready, select Create. A new deployment is created.

  9. During the deployment, you see the primary in Updating state.

    Screenshot of primary entering into updating status.

Modify virtual endpoint

  1. In the Azure portal, choose the primary Azure Database for PostgreSQL flexible server instance.

  2. On the server sidebar, under Settings, select Replication.

  3. Select the ellipses and then select Edit.

    Screenshot of editing the virtual endpoint.

  4. In the dialog, select the new secondary replica.

    Screenshot of selecting the secondary replica.

  5. Select Save. The reader endpoint is now pointed at the secondary replica, and the promote operation is now tied to this replica.

Promote replica to independent server

Rather than switchover to a replica, it's also possible to break the replication of a replica such that it becomes its standalone server.

  1. In the Azure portal, choose the Azure Database for PostgreSQL flexible server primary server.

  2. On the server sidebar, on the server menu, under Settings, select Replication.

  3. Under Servers, select the Promote icon for the replica you want to promote to an independent server.

    Screenshot of how to select to promote for a replica 2.

  4. In the dialog, ensure the action is Promote to independent server and remove from replication. This won't impact the primary server.

  5. For Data sync, ensure Planned - sync data before promoting is selected.

    Screenshot of promoting the replica to independent server.

  6. Select Promote, the process begins. Once completed, the server is no longer a replica of the primary.

Note

Once a replica is promoted to an independent server, it cannot be added back to the replication set.

Delete virtual endpoint

  1. In the Azure portal, select the primary server.

  2. On the server sidebar, under Settings, select Replication.

  3. At the top of the page, locate the Virtual endpoints section. Navigate to the three dots (menu options) next to the endpoint name, expand it, and choose Delete.

  4. A delete confirmation dialog appears. It warns you: "This action deletes the virtual endpoint virtualendpointName. Any clients connected using these domains may lose access." Acknowledge the implications and confirm by clicking on Delete.

Delete a replica

You can delete a read replica similar to how you delete a standalone Azure Database for PostgreSQL flexible server instance.

  1. In the Azure portal, open the Overview page for the read replica. Select Delete.

    Screenshot of the replica Overview page, select to delete the replica.

You can also delete the read replica from the Replication window by following these steps:

  1. In the Azure portal, select your primary Azure Database for PostgreSQL flexible server instance.

  2. On the server menu, under Settings, select Replication.

  3. Select the read replica to delete and then select the ellipses. Select Delete.

    Screenshot of select the replica to delete.

  4. Acknowledge Delete operation.

Delete a primary server

You can only delete the primary server once you delete all read replicas. To delete replicas, follow the instructions in the Delete a replica section and then proceed with the steps provided.

To delete a server from the Azure portal, follow these steps:

  1. In the Azure portal, select your primary Azure Database for PostgreSQL flexible server instance.

  2. Open the Overview page for the server and select Delete.

    Screenshot of the server Overview page, select to delete the primary server.

  3. Enter the name of the primary server to delete. Select Delete to confirm the deletion of the primary server.

    Screenshot of confirming to delete the primary server.

Monitor a replica

Two metrics are available to monitor read replicas.

Max Physical Replication Lag

Available only on the primary.

The Max Physical Replication Lag metric shows the byte lag between the primary server and the most lagging replica.

  1. In the Azure portal, select the primary server.

  2. Select Metrics. In the Metrics window, select Max Physical Replication Lag.

    Screenshot of the Metrics page showing Max Physical Replication Lag metric.

  3. For your Aggregation, select Max.

Read Replica Lag metric

The Read Replica Lag metric shows the time since the last replayed transaction on a replica. If no transactions occur on your primary, the metric reflects this time lag. For instance, if no transactions occur on your primary server, and the last transaction was replayed 5 seconds ago, then the Read Replica Lag shows a 5-second delay.

  1. In the Azure portal, select read replica.

  2. Select Metrics. In the Metrics window, select Read Replica Lag.

    Screenshot of the Metrics page showing Read Replica Lag metric.

  3. For your Aggregation, select Max.

Related content