TDS_FDW的安装与使用

近期需要 在 PgSql 用到 SqlServer 服务器上的数据 所以需要连表插件 用这个插件可以做到

TDS_FDW(Foreign data wrapper 远程数据封装)

• 插件作者: Geoff Montee
• 插件名称: tds_fdw

如何在 CentOS 上安装 TDS_FDW

当前文档仅适用于CentOs/RedHat的服务器安装

安装 EPEL (用于扩展Yum源 安装更多的软件)

CentOS, 官方文档 EPEL repository installed 在这里主要用于安装 FreeTDS.

sudo yum install epel-release

安装 FreeTDS

TDS_FDW 依赖于 FreeTDS 的数据库连接接口 所以需要安装依赖,

什么是FreeTDS: FreeTDS 是一个程序库，可以实现在Linux系统下访问微软的SQL数据库!

such as FreeTDS.

sudo yum install freetds freetds-devel

安装 TDS_FDW

从 Git 安装最新的插件

注意: 需要已经完成 Postgresql 的安装以及环境变量的配置

$ git clone https://github.com/GeoffMontee/tds_fdw.git
$ cd tds_fdw
$ make USE_PGXS=1
$ make USE_PGXS=1 install

重启服务器

$ pg_ctl -D $PGDATA -l logfile restart

安装插件

$ psql -U postgres
postgres=# CREATE EXTENSION tds_fdw;

---

使用 TDS_FDW

远程服务器创建

创建远程服务器参数说明

• servername

  必选参数

  默认值: 127.0.0.1

  远程数据库的连接地址.

  这里可以使用 DSN(Data Source Name), 在配置文件 freetds.conf. 详见 FreeTDS name lookup.

• port

  可选参数

  远程服务器的端口.
  如果过服务器使用了 DSN 则读取配置文件 freetds.conf.

• database

  可选参数

  远程服务器的数据库名称.

• dbuse

  可选参数

  默认值: 0

  This option tells tds_fdw to connect directly to database if dbuse is 0.
  If dbuse is not 0, tds_fdw will connect to the server’s default database, and
  then select database by calling DB-Library’s dbuse() function.

  For Azure, dbuse currently needs to be set to 0.

• language

  可选参数

  数据消息格式化的时候使用的语言.
  FreeTDS 会默认使用 us_english. 你可以在 freetds.conf 中修改.

  如果远程服务器是MSSQL, see 设置 MSSQL Server 的语言.

  For information related to Sybase ASE, see Sybase ASE login options
  and SET LANGUAGE in Sybase ASE.

• character_set

  可选参数

  设置客户端连接的字符集

  在 TDS 7.0+, 连接将总是使用 UCS-2, 所以一般不需要指定. 详见 TDS 7.0 的本地化处理.

• tds_version

  可选参数

  指定连接远程服务器的TDS版本. 详见 选择 TDS 协议版本 和 TDS 历史版本.

• msg_handler

  可选参数

  默认值: blackhole

  TDS 的默认消息处理. 可选值为 [“notice”, “blackhole”]. 选项 “notice” TDS 会给 PostgreSQL 发送通知. 选项 “blackhole” TDS 会忽略消息.

• fdw_startup_cost

  可选参数

  A cost that is used to represent the overhead of using this FDW used in query planning.

• fdw_tuple_cost

  可选参数

  A cost that is used to represent the overhead of fetching rows from this server used in query planning.

服务器定义中接受的表参数

一些表的选项可以设置为服务器级别 可用选项:

- use_remote_estimate
- row_estimate_method

服务器创建示例

CREATE SERVER mssql_svr
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername '192.168.0.8', port '21533', database 'tds_fdw_test', tds_version '7.1');

---

创建用户映射

用户映射参数说明

• username

Required: Yes

The username of the account on the foreign server.

• password

Required: Yes

The password of the account on the foreign server.

用户映射创建示例

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

---

远程表创建

创建远程表参数说明

• query

  必须参数 (或者使用 table)

  用于查询远程数据表的查询串 表将会在每次查询时通过这个查询串生成.

• schema_name

  可选参数

  The schema that the table is in. The schema name can also be included in table_name, so this is not required.

• table_name

  Aliases: table

  必须参数 (或者使用 query)

  远程服务器的表名.

• match_column_names

  可选参数

  是否通过比较它们的表名称或是否使用它们出现在结果集中的顺序来与远程列相匹配的本地列。

• use_remote_estimate

  可选参数

  无论我们估计表的大小由远程服务器上执行一些操作(如row_estimate_method定义) 或者我们是否只使用一个局部估计 通过定义 local_tuple_estimate。

• local_tuple_estimate

  可选参数

  A locally set estimate of the number of tuples that is used when use_remote_estimate is disabled.

• row_estimate_method

  可选参数

  Default: execute

  This can be one of the following values:

• execute: 执行查询操作, 并且计算获得本次查询返回的结果行数.

• showplan_all: 使 Microsoft SQL Server 不执行 Transact-SQL 语句. SQL Server 返回有关语句执行情况的详细信息,并估计语句对资源的需求. 详见MSDN SHOWPLAN_ALL.

远程表字段参数说明

• column_name

  可选参数

  用于设置远程数据表的字段名称 如果不设置 则使用本次创建表的名称 看例三

创建远程表示例

Using a table_name definition:

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

Or using a schema_name and table_name definition:

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

Or using a query definition:

CREATE FOREIGN TABLE mssql_query (
    id integer,
    content varchar)
    SERVER mssql_svr
    OPTIONS (query 'SELECT * FROM dbo.test', row_estimate_method 'showplan_all');

Or setting a 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');