Edit

Share via

Tutorial: Configure Microsoft Fabric mirrored databases from Azure Database for PostgreSQL flexible server

  • Article

Mirroring in Fabric is an enterprise, cloud-based, zero-ETL, SaaS technology. In this section, you learn how to create a mirrored Azure Database for PostgreSQL flexible server, which creates a read-only, continuously replicated copy of your PostgreSQL data in OneLake.

Prerequisites

Prepare your Azure Database for PostgreSQL

Mirroring in Azure Database for PostgreSQL flexible server is based on Logical Replication and requires some specific prerequisites to be configured before being able to connect to your data.

Important

For guiding users in enabling these prerequisites, we created a specific Fabric Mirroring page in Azure portal that automates all this for you. For more information, see Fabric mirroring concepts for PostgreSQL flexible server.

  • System-Assigned Managed Identity (SAMI) must be enabled.
  • The wal_level server parameter for the write ahead log (WAL) must be set to logical.
  • The extension (azure_cdc) is required, and must be allowlisted and preloaded (requires restart).
  • The max_worker_processes server parameter must be increased by 3 for each mirrored database in the source server.

Database role for Fabric Mirroring

Next, you need to provide or create a PostgreSQL role for the Fabric service to connect to your Azure Database for PostgreSQL flexible server.

Important

In the current preview, using Microsoft Entra ID users and service principals to connect to the source database in Azure Database for PostgreSQL flexible server isn't supported. Use Basic (PostgreSQL Authentication).

You can accomplish this by specifying a database role for connecting to your source system.

Use a database role

  1. Connect to your Azure Database for PostgreSQL flexible server using pgAdmin or Azure Data Studio.

  2. Create a database user and assign the appropriate permissions.

    • Create a PostgreSQL role named fabric_user. You can choose any name for this role. Provide your own strong password. Run the following SQL script to grant the CREATEDB, CREATEROLE, LOGIN, REPLICATION, and azure_cdc_admin permissions to a new role named fabric_user.
    CREATE ROLE fabric_user  CREATEDB CREATEROLE LOGIN REPLICATION PASSWORD '<strong password>';
GRANT azure_cdc_admin TO fabric_user;

Create a mirrored Azure Database for PostgreSQL flexible server

  1. Open the Fabric portal.
  2. Use an existing workspace, or create a new workspace.
  3. Navigate to the Create pane. Select the Create icon.
  4. Scroll to the Data Warehouse section and then select Mirrored Azure Database for PostgreSQL (preview).
  5. Either select Azure Database for PostgreSQL under New sources to create a new connection for your source flexible server or select an existing connection under OneLake catalog.
  6. In the New source page, insert your flexible server name and database, then you can either select existing connection credentials or insert database role and password created in the previous step.
  7. Leave the Use encrypted connection checkbox selected and Allow this connection to be utilized with either on-premises or VNet data gateways unselected.
  8. Select Connect.
  9. In the Choose data select the database tables you want to replicate in the Mirrored database in Fabric.

Connect to your Azure Database for PostgreSQL flexible server

Next, connect to the Azure Database for PostgreSQL flexible server from Fabric. The following steps guide you through the process of creating the connection to your Azure Database for PostgreSQL flexible server:

  1. Under New sources, select Azure Database for PostgreSQL (preview). Or, select an existing Azure Database for PostgreSQL flexible server connection from the OneLake hub.
  2. If you selected New connection, enter the connection details to the Azure Database for PostgreSQL flexible server.
    • Server: You can find the Server name by navigating to the Azure Database for PostgreSQL flexible server Overview page in the Azure portal. For example, <server-name>.postgres.database.azure.com.
    • Database: Enter the name of your Azure Database for PostgreSQL flexible server.
    • Connection: Create new connection.
    • Connection name: An automatic name is provided. You can change it.
    • Authentication kind (only Basic is available in the current preview):
      • Basic (PostgreSQL Authentication)
  3. Select Connect.

Start mirroring process

  1. The Configure mirroring screen allows you to mirror all data in the database, by default.

    • Mirror all data means that any new tables created after Mirroring is started will be mirrored.

    • Optionally, choose only certain objects to mirror. Disable the Mirror all data option, then select individual tables from your database.

    For this tutorial, we select the Mirror all data option.

  2. Select Mirror database. Mirroring begins.

  3. Wait for 2-5 minutes. Then, select Monitor replication to see the status.

  4. After a few minutes, the status should change to Running, which means the tables are being synchronized.

    If you don't see the tables and the corresponding replication status, wait a few seconds and then refresh the panel.

  5. When they have finished the initial copying of the tables, a date appears in the Last refresh column.

  6. Now that your data is up and running, there are various analytics scenarios available across all of Fabric.

Important

Any granular security established in the source database must be reconfigured in the mirrored database in Microsoft Fabric. See SQL granular permissions in Microsoft Fabric.

Monitor Fabric mirroring

Once mirroring is configured, you're directed to the Mirroring Status page. Here, you can monitor the current state of replication. For more information and details on the replication states, see Monitor Fabric mirrored database replication.

Related content