Back to Blog

Copying Azure Database for PostgreSQL Data with Fabric Pipelines - A Practical Guide

November 23, 2026•7 min read•Michael Ridland

PostgreSQL has quietly become one of the most common databases we find behind Australian line-of-business applications. A lot of SaaS products built locally over the last decade run on it, and plenty of internal apps built by .NET and Node teams moved to Azure Database for PostgreSQL Flexible Server once they left on-premises hardware. So when a client starts a Microsoft Fabric project, there is a good chance one of the first questions is "how do we get our Postgres data into the lakehouse?"

The answer most of the time is a pipeline with a Copy activity. Microsoft's documentation on configuring Azure Database for PostgreSQL in a pipeline Copy activity walks through each setting. I want to add the context around those settings: which ones matter, which defaults to change, and what bit us on real projects.

The basic shape

A Copy activity has a source and a destination. Azure Database for PostgreSQL can be either. In most of our work it is the source, with a lakehouse table or a warehouse as the destination, but we have also pushed curated data back into Postgres for an application to consume.

You set up a connection to the server first. You need the server name (the full yourserver.postgres.database.azure.com address), the database name and an authentication method. Basic authentication with a username and password works. Where clients support it, we prefer Microsoft Entra authentication, because it means one less database password floating around in a connection definition that half the data team can see.

Once the connection exists, the Copy activity's Source tab gives you a few choices.

Source settings that matter

Table versus query. You can point at a table or write a query. For anything beyond a quick proof of concept, I recommend a query. A query lets you select only the columns you need, filter to recent rows for incremental loads and cast awkward types into something the destination will accept. Pointing at a table with 80 columns when you need 12 is wasted network traffic and wasted storage.

Incremental loading. The connector does not give you change data capture out of the box in the Copy activity. The pattern we use most is a watermark: store the last updated_at value you loaded, then query WHERE updated_at > @lastWatermark. You pass the watermark in as a pipeline parameter or look it up from a control table with a Lookup activity first. It is not glamorous, but it is reliable, and it works as long as the application actually maintains that updated_at column. Check that it does. We once found an app that only set it on insert, never on update, and the client had been reporting on stale data for months.

Partition options. This is the setting most people skip, and it's the one that makes the biggest difference on large tables. By default the copy reads with a single connection. For a table with a few hundred thousand rows, fine. For 200 million rows, it is painfully slow.

The connector supports partitioned reads, either using the physical partitions of a table if you have partitioned it in Postgres, or a dynamic range on a column you choose. With dynamic range you pick a numeric or date column, give it upper and lower bounds (or let it work them out), and the Copy activity runs parallel queries against slices of that range. On one migration this took a full initial load from about six hours down to a little over one.

Pick the partition column carefully. It should be indexed and reasonably evenly distributed. An identity column is usually a good choice. A status column with three values is not.

Also, think about the source server. Parallel reads mean parallel load on the production database. If that database backs a customer-facing application, talk to the application team before running eight parallel connections against it at 10am.

Data types will catch you

Postgres has a richer type system than most destinations, and this is where things get fiddly.

  • jsonb and json columns generally come across as strings. That is usually fine for a lakehouse, where you can parse them later in a notebook, but don't expect the copy to flatten them for you.
  • Arrays come across as strings as well. Same approach applies.
  • timestamp with time zone values get converted, and you should verify what lands in the destination. Australian clients care about this more than most because of AEST, AEDT, ACST and AWST all in play. Our default is to store UTC in the lakehouse and convert at the reporting layer.
  • numeric without a defined precision can cause trouble in some destinations. Casting in the source query to an explicit precision avoids surprises.
  • Custom enum types come across as text, which is usually what you want.

The Mapping tab lets you import schemas and adjust column mappings. For lakehouse destinations, letting Fabric create the table automatically is convenient for a first load, but I would review the resulting schema before you build anything on top of it.

Postgres as a destination

When Postgres is the destination, the Destination tab lets you choose a table and a write behaviour. Insert is the simple option. Depending on the connector version, you also get an upsert option, which needs key columns to match on. There is also a pre-copy script field, which runs SQL before the load starts. People use it to truncate a staging table, and that's a fine use, but be careful. A pre-copy script that truncates the wrong table is a very fast way to ruin your afternoon.

For loading back into application databases, we usually land data into a staging table and then call a stored procedure or function to merge it into the real tables. That gives you a place to validate data and keeps the application's tables safe from half-finished loads.

Networking is where projects stall

If your Postgres server has public access enabled with firewall rules, connecting from Fabric is straightforward. Most enterprise clients don't, and they shouldn't. Their servers sit behind private endpoints or inside a virtual network.

In that case you need either a virtual network data gateway or an on-premises data gateway that can reach the server. The virtual network data gateway is the cleaner option in Azure, but it has its own setup requirements, including a delegated subnet and the right capacity licensing. We have seen projects lose a week here, not because the work is hard but because the network team, the database team and the data team all needed to agree, and none of them had done it before.

My advice: sort out connectivity in week one with a trivial test copy. Don't wait until the pipelines are built.

What works well and what doesn't

The Copy activity for Postgres is solid. It is not exciting, and that is a compliment. Once configured, it runs reliably, and the monitoring view shows rows read, rows written and throughput clearly enough to troubleshoot.

What is still rough: error messages on type conversion failures can be vague, and you sometimes end up bisecting columns to find the offending one. Partition settings are not obvious to people new to the tool. And there is no native CDC path in the Copy activity, so near real-time scenarios need a different approach, such as mirroring or an eventstream, depending on what Fabric supports for your Postgres setup at the time.

Getting help

We build these pipelines regularly as part of Fabric and data platform projects. If you want a hand designing an ingestion approach that won't fall over at scale, our Microsoft Data Factory consultants can help, and our Microsoft Fabric consultants cover the wider platform design. If the Postgres data feeds an AI use case down the line, our AI integration services page explains how we connect data platforms to agents and models.

The short version: use queries, use partitioning on big tables, check your types and sort the network out first. Do those four things and the Copy activity will treat you well.