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

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

• Create or use an existing Azure Database for PostgreSQL flexible server.
  • If you don't have an Azure Database for PostgreSQL flexible server, create a new flexible server.
  • As a tutorial, we recommend using a copy of one of your existing databases or any existing test or development database that you can recover quickly from a backup. If you want to use a database from an existing backup, see Restore a database from a backup in Azure Database for PostgreSQL flexible server.
• You need an existing capacity for Fabric. If you don't, start a Fabric trial.
• The Fabric capacity needs to be active and running. A paused or deleted capacity also stops Mirroring and no data will be replicated.
• Ensure the following Fabric tenant settings are enabled. To learn how to enable tenant settings, see Fabric Tenant settings.
  • Service principals can use Fabric APIs
  • Users can access data stored in OneLake with apps external to Fabric
• You need to have a member or admin role in your workspace when create a mirrored database from the Fabric portal. During creation, the managed identity of Azure Database for PostgreSQL is automatically granted "Read and write" permission on the mirrored database. Users with the contributor role don't have the Reshare permission necessary to complete this step.
• If your Flexible Server is not publicly accessible and doesn't allow Azure services to connect to it, you can create a virtual network data gateway to mirror the data. Make sure the Azure Virtual Network or the gateway machine's network can connect to the Azure Database for PostgreSQL flexible server via a private endpoint or is allowed by the firewall rule.
• Fabric Mirroring can't be configured on a Read Replica, or on a Primary where a Read Replica exists.

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.

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.

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. You should connect with a principal that is a member of the role azure_pg_admin.

2. Create a PostgreSQL role named fabric_user. You can choose any name for this role. Provide your own strong password. Grant the permissions needed for Fabric mirroring in the database. Run the following SQL script to grant the CREATEDB, CREATEROLE, LOGIN, REPLICATION, and azure_cdc_admin permissions to the new role named fabric_user.

   -- create a new user to connect from Fabric
   CREATE ROLE fabric_user  CREATEDB CREATEROLE LOGIN REPLICATION PASSWORD '<strong password>';
   
   -- grant role for replication management to the new user
   GRANT azure_cdc_admin TO fabric_user;
   -- grant create permission on the database to mirror to the new user
   GRANT CREATE ON DATABASE <database_to_mirror> TO fabric_user;
   

3. The database user created also needs to be owner of the tables to replicate in the mirrored database. This means that tables have been created by that user, or that the ownership of those tables has been changed using ALTER TABLE <table name here> OWNER TO fabric_user;.

   • When switching ownership to new user, you might need to grant to that user all privileges on public schema before. For more information regarding user account management, see Azure Database for PostgreSQL user management documentation, PostgreSQL product documentation for Database Roles and Privileges, GRANT Syntax, and Privileges.

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 or select the New item button. Select the Create icon.
4. Scroll to the Data Warehouse section and then select Mirrored Azure Database for PostgreSQL (preview).

Connect to your Azure Database for PostgreSQL flexible server

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.
   • Data Gateway: select an available VNET Data Gateway to connect an Azure Database for PostgreSQL flexible server with VNET integration or Private Endpoints.
   • Authentication kind (only Basic is available in the current preview):
     • Basic (PostgreSQL Authentication)
   • Leave Use encrypted connection checkbox selected, and This connection can be used with on-premises data gateway and VNET data gateway unselected.
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.

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

• Troubleshoot Fabric mirrored databases from Azure Database for PostgreSQL flexible server
• Mirroring Azure Database for PostgreSQL flexible server
• What is Mirroring in Fabric?