Use the tds_fdw plug-in to query data of SQL Server instances - RDS PostgreSQL Database| Alibaba Cloud Documentation Center

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');