Raw New Markdown
Generating updated version of doc...
Rendered New Markdown
Generating updated version of doc...
---
title: Copy data from PostgreSQL V1
titleSuffix: Azure Data Factory & Azure Synapse
description: Learn how to copy data from PostgreSQL V1 to supported sink data stores by using a copy activity in an Azure Data Factory or Synapse Analytics pipeline.
author: jianleishen
ms.subservice: data-movement
ms.topic: conceptual
ms.date: 04/14/2025
ms.author: jianleishen
ms.custom:
- synapse
- sfi-image-nochange
- sfi-ropc-nochange
---
# Copy data from PostgreSQL V1 using Azure Data Factory or Synapse Analytics
[!INCLUDE[appliesto-adf-asa-md](includes/appliesto-adf-asa-md.md)]
This article outlines how to use the Copy Activity in Azure Data Factory and Synapse Analytics pipelines to copy data from a PostgreSQL database. It builds on the [copy activity overview](copy-activity-overview.md) article that presents a general overview of copy activity.
> [!IMPORTANT]
> The PostgreSQL V1 connector is at [removal stage](connector-release-stages-and-timelines.md). You are recommended to [upgrade the PostgreSQL connector](connector-postgresql.md#upgrade-the-postgresql-linked-service) from V1 to V2.
## Supported capabilities
This PostgreSQL connector is supported for the following capabilities:
| Supported capabilities|IR |
|---------| --------|
|[Copy activity](copy-activity-overview.md) (source/-)|① ②|
|[Lookup activity](control-flow-lookup-activity.md)|① ②|
*① Azure integration runtime ② Self-hosted integration runtime*
For a list of data stores that are supported as sources/sinks by the copy activity, see the [Supported data stores](copy-activity-overview.md#supported-data-stores-and-formats) table.
Specifically, this PostgreSQL connector supports PostgreSQL **version 7.4 and above**.
## Prerequisites
[!INCLUDE [data-factory-v2-integration-runtime-requirements](includes/data-factory-v2-integration-runtime-requirements.md)]
The Integration Runtime provides a built-in PostgreSQL driver starting from version 3.7, therefore you don't need to manually install any driver.
## Getting started
[!INCLUDE [data-factory-v2-connector-get-started](includes/data-factory-v2-connector-get-started.md)]
## Create a linked service to PostgreSQL using UI
Use the following steps to create a linked service to PostgreSQL in the Azure portal UI.
1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New:
# [Azure Data Factory](#tab/data-factory)
:::image type="content" source="media/doc-common-process/new-linked-service.png" alt-text="Screenshot showing Create a new linked service with Azure Data Factory UI.":::
# [Azure Synapse](#tab/synapse-analytics)
:::image type="content" source="media/doc-common-process/new-linked-service-synapse.png" alt-text="Screenshot showing Create a new linked service with Azure Synapse UI.":::
2. Search for Postgre and select the PostgreSQL connector.
:::image type="content" source="media/connector-postgresql-legacy/postgresql-legacy-connector.png" alt-text="Screenshot showing Select the PostgreSQL connector.":::
1. Configure the service details, test the connection, and create the new linked service.
:::image type="content" source="media/connector-postgresql-legacy/configure-postgresql-legacy-linked-service.png" alt-text="Screenshot showing Configure a linked service to PostgreSQL.":::
## Connector configuration details
The following sections provide details about properties that are used to define Data Factory entities specific to PostgreSQL connector.
## Linked service properties
The following properties are supported for PostgreSQL linked service:
| Property | Description | Required |
|:--- |:--- |:--- |
| type | The type property must be set to: **PostgreSql** | Yes |
| connectionString | An ODBC connection string to connect to Azure Database for PostgreSQL. <br/>You can also put password in Azure Key Vault and pull the `password` configuration out of the connection string. Refer to the following samples and [Store credentials in Azure Key Vault](store-credentials-in-key-vault.md) article with more details. | Yes |
| connectVia | The [Integration Runtime](concepts-integration-runtime.md) to be used to connect to the data store. Learn more from [Prerequisites](#prerequisites) section. If not specified, it uses the default Azure Integration Runtime. |No |
A typical connection string is `Server=<server>;Database=<database>;Port=<port>;UID=<username>;Password=<Password>`. More properties you can set per your case:
| Property | Description | Options | Required |
|:--- |:--- |:--- |:--- |
| EncryptionMethod (EM)| The method the driver uses to encrypt data sent between the driver and the database server. E.g., `EncryptionMethod=<0/1/6>;`| 0 (No Encryption) **(Default)** / 1 (SSL) / 6 (RequestSSL) | No |
| ValidateServerCertificate (VSC) | Determines whether the driver validates the certificate that is sent by the database server when SSL encryption is enabled (Encryption Method=1). E.g., `ValidateServerCertificate=<0/1>;`| 0 (Disabled) **(Default)** / 1 (Enabled) | No |
> [!NOTE]
> In order to have full SSL verification via the ODBC connection when using the Self Hosted Integration Runtime you must use an ODBC type connection instead of the PostgreSQL connector explicitly, and complete the following configuration:
>
> 1. Set up the DSN on any SHIR servers.
> 1. Put the proper certificate for PostgreSQL in C:\Windows\ServiceProfiles\DIAHostService\AppData\Roaming\postgresql\root.crt on the SHIR servers. This is where the ODBC driver looks > for the SSL cert to verify when it connects to the database.
> 1. In your data factory connection, use an ODBC type connection, with your connection string pointing to the DSN you created on your SHIR servers.
**Example:**
```json
{
"name": "PostgreSqlLinkedService",
"properties": {
"type": "PostgreSql",
"typeProperties": {
"connectionString": "Server=<server>;Database=<database>;Port=<port>;UID=<username>;Password=<Password>"
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
```
**Example: store password in Azure Key Vault**
```json
{
"name": "PostgreSqlLinkedService",
"properties": {
"type": "PostgreSql",
"typeProperties": {
"connectionString": "Server=<server>;Database=<database>;Port=<port>;UID=<username>;",
"password": {
"type": "AzureKeyVaultSecret",
"store": {
"referenceName": "<Azure Key Vault linked service name>",
"type": "LinkedServiceReference"
},
"secretName": "<secretName>"
}
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
```
If you were using PostgreSQL linked service with the following payload, it is still supported as-is, while you are suggested to use the new one going forward.
**Previous payload:**
```json
{
"name": "PostgreSqlLinkedService",
"properties": {
"type": "PostgreSql",
"typeProperties": {
"server": "<server>",
"database": "<database>",
"username": "<username>",
"password": {
"type": "SecureString",
"value": "<password>"
}
},
"connectVia": {
"referenceName": "<name of Integration Runtime>",
"type": "IntegrationRuntimeReference"
}
}
}
```
## Dataset properties
For a full list of sections and properties available for defining datasets, see the [datasets](concepts-datasets-linked-services.md) article. This section provides a list of properties supported by PostgreSQL dataset.
To copy data from PostgreSQL, the following properties are supported:
| Property | Description | Required |
|:--- |:--- |:--- |
| type | The type property of the dataset must be set to: **PostgreSqlTable** | Yes |
| schema | Name of the schema. |No (if "query" in activity source is specified) |
| table | Name of the table. |No (if "query" in activity source is specified) |
| tableName | Name of the table with schema. This property is supported for backward compatibility. Use `schema` and `table` for new workload. | No (if "query" in activity source is specified) |
**Example**
```json
{
"name": "PostgreSQLDataset",
"properties":
{
"type": "PostgreSqlTable",
"typeProperties": {},
"schema": [],
"linkedServiceName": {
"referenceName": "<PostgreSQL linked service name>",
"type": "LinkedServiceReference"
}
}
}
```
If you were using `RelationalTable` typed dataset, it's still supported as-is, while you are suggested to use the new one going forward.
## Copy activity properties
For a full list of sections and properties available for defining activities, see the [Pipelines](concepts-pipelines-activities.md) article. This section provides a list of properties supported by PostgreSQL source.
### PostgreSQL as source
To copy data from PostgreSQL, the following properties are supported in the copy activity **source** section:
| Property | Description | Required |
|:--- |:--- |:--- |
| type | The type property of the copy activity source must be set to: **PostgreSqlSource** | Yes |
| query | Use the custom SQL query to read data. For example: `"query": "SELECT * FROM \"MySchema\".\"MyTable\""`. | No (if "tableName" in dataset is specified) |
> [!NOTE]
> Schema and table names are case-sensitive. Enclose them in `""` (double quotes) in the query.
**Example:**
```json
"activities":[
{
"name": "CopyFromPostgreSQL",
"type": "Copy",
"inputs": [
{
"referenceName": "<PostgreSQL input dataset name>",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "<output dataset name>",
"type": "DatasetReference"
}
],
"typeProperties": {
"source": {
"type": "PostgreSqlSource",
"query": "SELECT * FROM \"MySchema\".\"MyTable\""
},
"sink": {
"type": "<sink type>"
}
}
}
]
```
If you were using `RelationalSource` typed source, it is still supported as-is, while you are suggested to use the new one going forward.
## Lookup activity properties
To learn details about the properties, check [Lookup activity](control-flow-lookup-activity.md).
## Related content
For a list of data stores supported as sources and sinks by the copy activity, see [supported data stores](copy-activity-overview.md#supported-data-stores-and-formats).
View on GitHub