pgspider tds fdw sql server docker 镜像试用
前边有制作过一个tds 的fdw，以下是一个简单的试用
环境准备
- docker-compose 文件
使用sql server Linux 版本
version: "3"
services:
pg-tds:
image: dalongrong/pgspider:tds
ports:
- "5432:5432"
environment:
- "POSTGRES_PASSWORD:dalong"
db:
image: microsoft/mssql-server-linux
environment:
- "ACCEPT_EULA=Y"
- "SA_PASSWORD=Dalong!123%"
ports:
- "1433:1433"
启动&&试用
- 启动
docker-compose up -d
- 创建sql server 数据库以及表
create DATABASE appdemos;
use appdemos;
create table apps (
id int,
age int,
name VARCHAR(256)
);
insert into apps VALUES(1,22,'appdemo');
- 使用扩展
// 创建扩展
CREATE EXTENSION tds_fdw;
// 创建server
CREATE SERVER mssql_svr
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername 'db', port '1433', database 'appdemos');
// 创建用户映射
CREATE USER MAPPING FOR postgres
SERVER mssql_svr
OPTIONS (username 'sa', password 'Dalong!123%');
// 创建外部表映射
CREATE FOREIGN TABLE mssql_table (
id integer,
age integer,
name varchar)
SERVER mssql_svr
OPTIONS (table_name 'dbo.apps', row_estimate_method 'showplan_all');
// 或者，导入schema
IMPORT FOREIGN SCHEMA dbo
FROM SERVER mssql_svr
INTO public
OPTIONS (import_default 'true');
- 效果
- 数据查询&&效果
select * from apps
参考资料
https://github.com/rongfengliang/tds-fdw-learning
https://github.com/tds-fdw/tds_fdw
https://hub.docker.com/repository/docker/dalongrong/pgspider
https://hub.docker.com/r/microsoft/mssql-server-linux
