Use tds_fdw to access SQL Server data from PostgreSQL - ApsaraDB RDS

The tds_fdw extension is a Foreign Data Wrapper (FDW) that lets you query data from Microsoft SQL Server instances directly within PostgreSQL, without moving data between databases.

Prerequisites

Before you begin, ensure that you have:

An RDS instance running PostgreSQL 11 or later
If the major engine version meets the requirement but the extension is still unavailable, updated the minor engine version. For PostgreSQL 17, the minor engine version must be 20241030 or later. For details, see Update the minor engine version
The CIDR block of the virtual private cloud (VPC) where your RDS instance resides added to the whitelist of the SQL Server instance you want to connect to. For example: 172.xx.xx.xx/16

Find the VPC CIDR block on the Database Connection page of the ApsaraDB RDS console.

How it works

tds_fdw uses the Tabular Data Stream (TDS) protocol to establish a connection from your PostgreSQL instance to a SQL Server instance. After the connection is set up, you define foreign tables in PostgreSQL that map to SQL Server tables and run SQL queries against them as if the data were local.

The setup involves the following steps:

Create the tds_fdw extension.
Create a foreign server pointing to the SQL Server instance.
Create a foreign table that maps to a SQL Server table.
Create a user mapping with the SQL Server credentials.
(Optional) Import an entire schema from the SQL Server instance.

Set up the tds_fdw extension

Step 1: Create the extension

Connect to your RDS PostgreSQL instance and run:

CREATE EXTENSION tds_fdw;

Step 2: Create a foreign server

Create a foreign server that defines the connection to your SQL Server instance:

CREATE SERVER mssql_svr
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (
    servername '<internal-endpoint>',
    port '<internal-port>',
    database 'tds_fdw_test',
    tds_version '7.1'
  );

Important

Use the internal endpoint and internal port of the SQL Server instance, not the public ones. The RDS PostgreSQL instance connects to SQL Server over the VPC network.

Replace the placeholders:

Placeholder	Description	Example
`<internal-endpoint>`	Internal endpoint of the SQL Server instance	`rm-xxx.sqlserver.rds.aliyuncs.com`
`<internal-port>`	Internal port of the SQL Server instance	`1433`

The OPTIONS parameters:

Parameter	Required	Default	Description
`servername`	Yes	—	Internal endpoint of the SQL Server instance. Must be the internal (not public) endpoint.
`port`	No	—	Port of the SQL Server instance.
`database`	No	—	The database to connect to on the SQL Server instance.
`tds_version`	No	—	The TDS protocol version. Use `7.1` for most SQL Server versions.

Step 3: Create a foreign table

A foreign table maps a SQL Server table (or query result) to a PostgreSQL table definition. Choose one of the following methods based on your needs:

Method 1: Specify `table_name` with schema prefix

CREATE FOREIGN TABLE mssql_table (
  id integer,
  data varchar
)
SERVER mssql_svr
OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');

Method 2: Specify `schema_name` and `table_name` separately

CREATE FOREIGN TABLE mssql_table (
  id integer,
  data varchar
)
SERVER mssql_svr
OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

Method 3: Use a custom query (mutually exclusive with `table_name`)

CREATE FOREIGN TABLE mssql_table (
  id integer,
  data varchar
)
SERVER mssql_svr
OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');

Method 4: Map a local column name to a different remote column name

CREATE FOREIGN TABLE mssql_table (
  id integer,
  col2 varchar OPTIONS (column_name 'data')
)
SERVER mssql_svr
OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');

The OPTIONS parameters for foreign tables:

Parameter	Required	Default	Description
`table_name`	Yes (mutually exclusive with `query`)	—	The SQL Server table to query. Use `schema.table` format or combine with `schema_name`.
`schema_name`	No	—	The SQL Server schema. If omitted, include the schema in `table_name`.
`query`	Yes (mutually exclusive with `table_name`)	—	A custom SQL query to run on the SQL Server instance.
`row_estimate_method`	No	—	Method for estimating row counts. Use `showplan_all` for better performance on large tables.
`column_name`	No	—	Column-level option. Maps a local PostgreSQL column to a differently named column in SQL Server.

Step 4: Create a user mapping

A user mapping provides the credentials PostgreSQL uses to authenticate against the SQL Server instance:

CREATE USER MAPPING FOR postgres
  SERVER mssql_svr
  OPTIONS (username 'sa', password '123456');

Warning

PostgreSQL stores user mapping credentials in plain text in the system catalog (pg_catalog.pg_user_mapping). Anyone with access to this table can view the credentials. Use a dedicated read-only SQL Server account with the minimum required permissions for this connection.

Step 5: Import a foreign schema (optional)

To import all tables from a SQL Server schema at once instead of creating foreign tables one by one:

IMPORT FOREIGN SCHEMA dbo
  EXCEPT (mssql_table)
  FROM SERVER mssql_svr
  INTO public
  OPTIONS (import_default 'true');

The OPTIONS parameters for schema import:

Parameter	Required	Default	Description
`import_default`	No	—	Whether to import column default values from the SQL Server schema.

Query the foreign table

After the setup is complete, query the foreign table in PostgreSQL as you would any local table:

SELECT * FROM mssql_table;

For more information about PostgreSQL foreign data wrappers, see postgres_fdw.