【PostgreSQL】【Sql Server】Postgresql和sql server实现双向、数据夸库实时读取(上)-PostgreSQL篇

前言

最近在公司的项目中遇到PostgreSQL和Sql Server双向通信，需要跨不同的数据库进行数据查询的操作，经过公司大佬提点，发现postgreSQL和SQL Server都支持连接外部数据库，所以我们先来把PostgreSQL到sql server的路径打通。

注意：关于Postgresql的安装这里直接跳过，docker直接起一个就好了

一、安装PostgreSQL的外表插件

翻阅了官方Wiki，提到了连接SQL server用到插件的GitHub地址，GitHub真是个好东西！！
点我可以看到GitHub的内容
一款名为tds_fdw的插件出现在眼前，还详细提供了各个平台上的安装教程。
以Debian为例：
1.安装需要的基础freetds

sudo apt-get update
sudo apt-get install libsybdb5 freetds-dev freetds-common

2.安装gcc编译工具

sudo apt-get install gnupg gcc make

3.安装PostgreSql

sudo bash -c 'source /etc/os-release; echo "deb http://apt.postgresql.org/pub/repos/apt/ ${VERSION_CODENAME}-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-key adv --keyserver hkp://pool.sks-keyservers.net --recv-keys 0xACCC4CF8
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-11 postgresql-client-11 postgresql-server-dev-11

注意最后一个命令安装的时候，修改为对应的你需要的PostgreSQL版本，如果是已经安装PostgreSQL，可不安装postgresql-11,一定要确保已经安装postgresql-server-dev-xx
4.安装tds_fdw插件
方式一(推荐)：

export TDS_FDW_VERSION="2.0.2"
sudo apt-get install wget
wget https://github.com/tds-fdw/tds_fdw/archive/v${TDS_FDW_VERSION}.tar.gz
tar -xvzf v${TDS_FDW_VERSION}.tar.gz
cd tds_fdw-${TDS_FDW_VERSION}/
make USE_PGXS=1
sudo make USE_PGXS=1 install

方式二：

sudo apt-get install git
git clone https://github.com/tds-fdw/tds_fdw.git
cd tds_fdw
make USE_PGXS=1
sudo make USE_PGXS=1 install

5.登录到PostgreSQL进行验证

sudo service postgresql start
psql -U postgres
postgres=# CREATE EXTENSION tds_fdw;

二、PostgreSQL中使用插件

1.导入tds_fdw扩展

create EXTENSION tds_fdw with schema public;

2.创建server

CREATE SERVER test_mssql 
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (
	servername '192.168.1.77',
	port '1433',
	database 'ERP2021', tds_version '7.1'
  );

test_mssql表示外表服务的名称，自己定义
servername 配置sql server的数据库地址
port sql server 数据库端口
database sql server 数据库名称
3.授权访问

GRANT USAGE ON FOREIGN SERVER test_mssql to test_user;
CREATE USER MAPPING FOR test_user
  SERVER test_mssql
  OPTIONS ("username" 'test_user', "password" '123456');

test_user 表示PostgreSQL中的用户
test_mssql 外表插件的服务名称

4.PostgreSQL中建立外表

drop FOREIGN table if exists ft_kis_Fgoodsbarcode cascade;
CREATE FOREIGN table ft_kis_Fgoodsbarcode(
FitemID Text,FGoodsBarCode Text,FName Text,FModel text, FFullName text,FErpClsID int4
)
SERVER odoo_mssql
OPTIONS (table_name 'dbo.t_ICItem', row_estimate_method 'showplan_all');

ft_kis_Fgoodsbarcode 外表名称，自定义
odoo_mssql 外表插件的服务名称
table_name sql server 中的表或者视图名称

5.验证是否可用

select * from ft_kis_Fgoodsbarcode

结尾

ok,到这里在PostgreSQL中读取SQL server中的数据已经实现，更多的问题可以参考tds_fdw的GitHub
最后提供配置了tds_fdw插件的Dockerfile

FROM postgres:10

LABEL maintainer="thoughtsdump"

ENV LANG C.UTF-8

RUN sed -i 's/deb.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list && \
sed -i 's/security.debian.org/mirrors.ustc.edu.cn/g' /etc/apt/sources.list && \
sed -i 's/apt.postgresql.org\/pub/mirrors.ustc.edu.cn\/postgresql/g' /etc/apt/sources.list.d/pgdg.list && \
apt-get update && apt-get -y install libsybdb5 freetds-dev freetds-common gnupg gcc make wget curl && \
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - && \
apt-get -y install postgresql-server-dev-10 && \
export TDS_FDW_VERSION="2.0.2" && wget https://github.com/tds-fdw/tds_fdw/archive/v${TDS_FDW_VERSION}.tar.gz && \
tar -xvzf v${TDS_FDW_VERSION}.tar.gz && cd tds_fdw-${TDS_FDW_VERSION}/ && \
make USE_PGXS=1 && make USE_PGXS=1 install && \
cd .. && rm -rf v${TDS_FDW_VERSION}.tar.gz && rm -rf tds_fdw-${TDS_FDW_VERSION} && apt-get -y clean