This topic describes how to use the tds_fdw plug-in provided by PostgreSQL to query
data of SQL Server instances.
Prerequisites
- Your ApsraDB RDS for PostgreSQL instance runs one of the following database engine
versions:
- PostgreSQL 12 with a minor engine version of 20200421 or later
- PostgreSQL 11 with a minor engine version of 20200402 or later
Note To view the minor engine version of your RDS instance, you must log on to the ApsaraDB
RDS console and go to the
Basic Information page. In the
Configuration Information section of the page, you can check whether the
Upgrade Kernel Version button is displayed. If the button is displayed, you can click the button to view
and update the minor engine version of your RDS instance. If the button is not displayed,
your RDS instance runs the latest minor engine version. For more information, see
Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
- You must add the CIDR block of the virtual private cloud (VPC) in which your RDS instance
resides to an IP address whitelist of the SQL Server instance that you want to connect.
Example of an CIDR block:
172.xx.xx.xx/16
.
Note You can view the CIDR block of the VPC in which an RDS instance resides on the
Database Connection page of the ApsaraDB RDS console.

Background information
The tds_fdw plug-in is a Foreign Data Wrapper (FDW) provided by PostgreSQL that you
can use to connect with foreign instances such as Sybase and Microsoft SQL Server
instances. These instances use the Tabular Data Stream (TDS) protocol.
For more information, see postgres_fdw.
Create a tds_fdw plug-in
After you have connected to an SQL Server instance, execute the following statement
to create a tds_fdw plug-in:
create extension tds_fdw;
Use the tds_fdw plug-in
- Execute the following statement to create a server:
CREATE SERVER mssql_svr
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername '<The endpoint used to connect to the SQL Server instance>', port '<The port used to connect to the SQL Server instance>', database 'tds_fdw_test', tds_version '7.1');
Note You must set the servername
parameter to the internal endpoint used to connect to the SQL Server instance and
the port
parameter to the internal port used to connect to the SQL Server instance.
- Create a foreign table. You can use one of the following methods to create a foreign
table:
- Specify the table_name parameter and execute the following statement to create a foreign
table:
CREATE FOREIGN TABLE mssql_table (
id integer,
data varchar)
SERVER mssql_svr
OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');
- Specify the schema_name and table_name parameters and execute the following statement
to create a foreign table:
CREATE FOREIGN TABLE mssql_table (
id integer,
data varchar)
SERVER mssql_svr
OPTIONS (schema_name 'dbo', table_name 'mytable', row_estimate_method 'showplan_all');
- Specify the query parameter and execute the following statement to create a foreign
table:
CREATE FOREIGN TABLE mssql_table (
id integer,
data varchar)
SERVER mssql_svr
OPTIONS (query 'SELECT * FROM dbo.mytable', row_estimate_method 'showplan_all');
- Specify a foreign column name and execute the following statement to create a foreign
table:
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');
- Execute the following statement to create a user mapping:
CREATE USER MAPPING FOR postgres
SERVER mssql_svr
OPTIONS (username 'sa', password '123456');
- Execute the following statement to import a schema from a foreign table:
IMPORT FOREIGN SCHEMA dbo
EXCEPT (mssql_table)
FROM SERVER mssql_svr
INTO public
OPTIONS (import_default 'true');