PostgreSQL Foreign Data Wrapper
外部数据封装器
外部数据封装器（FDW）是 PostgreSQL 提供的一种用于访问外部数据源的手段，它是可扩展的，也兼容业界标准。该机制所支持的外部数据源包括 PostgreSQL 以及其他非 PostgreSQL 数据源。FDW的核心原理在这个页面可以查到。FDW 的核心概念是“外部表”，这种表看起来和当前 PostgreSQL 中其他表的用法完全相同，但事实上其数据本体是存在于外部数据源中的，该数据源甚至可能存在于另外一台物理服务器上。一旦定义好了外部表，其定义就会在当前数据库中持久化，你就可以放心地与使用普通表一样使用它，FDW 完全屏蔽了与外部数据源之间的复杂通信过程。比较流行的 FDW 及其用法示例可以通过 PostgreSQL FDW 维基百科页面查到。可以通过 PGXN FDW 页面和 PGXN Foreign Data Wrapper 页面查到 PostgreSQL 的 FDW 目录。在 GitHub 上搜索“PostgreSQL Foreign Data Wrappers”，可以搜索到前述很多 FDW 的源码，另外还能找到一些不在前述列表中的 FDW。
不通的FDW有不同的用法，但基本都需要下面4个对象
- FOREIGN DATA WRAPPER （包装器）
- SERVER（外部服务器）
- USER MAPPING（用户映射）
- FOREIGN TABLE（外部表）
本文通过实战来测试一下PostgreSQL14版本数据库访问各种关系型或者非关系型数据库
环境准备
两台主机一台PostgreSQL14数据库，一台Docker服务器用于各种数据库的搭建
| IP地址 | 操作系统 | 软件版本 | 用途 |
|---|---|---|---|
| 192.168.17.7 | CentOS7.9 | PostgreSQL14.9 | 源码安装步骤略参考 |
| 192.168.17.8 | CentOS7.9 | Docker24.0.6 | 安装步骤略参考注意配置镜像加速 |
下面我先来看一下PostgreSQL默认自带的两个扩展file_fdw和postgresql_fdw
file_fdw
使用 file_fdw 这个 FDW 来查询平面文件（如CSV等）
创建一个直接读取PostgreSQL CSV日志的表
参考官网
su - postgres
#查看postgresql日志目录下文件
ls /opt/pgdata/log/
psql
#创建file_fdw插件
CREATE EXTENSION file_fdw;
#创建外部服务pglog
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
#创建日志外部表
CREATE FOREIGN TABLE pglog (
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
backend_type text,
leader_pid integer,
query_id bigint
) SERVER pglog
OPTIONS ( filename 'log/postgresql-2023-09-24_165521.csv', format 'csv' );
#查询日志
select * from pglog;
操作如下
[root@pg14 ~]# su - postgres
Last login: Sun Sep 24 16:46:17 CST 2023 on pts/0
[postgres@pg14 ~]$ ls /opt/pgdata/log/
postgresql-2023-09-24_165521.csv postgresql-2023-09-24_165521.log
[postgres@pg14 ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE pglog (
postgres(# log_time timestamp(3) with time zone,
postgres(# user_name text,
postgres(# database_name text,
postgres(# process_id integer,
postgres(# connection_from text,
postgres(# session_id text,
postgres(# session_line_num bigint,
postgres(# command_tag text,
postgres(# session_start_time timestamp with time zone,
postgres(# virtual_transaction_id text,
postgres(# transaction_id bigint,
postgres(# error_severity text,
postgres(# sql_state_code text,
postgres(# message text,
postgres(# detail text,
postgres(# hint text,
postgres(# internal_query text,
postgres(# internal_query_pos integer,
postgres(# context text,
postgres(# query text,
postgres(# query_pos integer,
postgres(# location text,
postgres(# application_name text,
postgres(# backend_type text,
postgres(# leader_pid integer,
postgres(# query_id bigint
postgres(# ) SERVER pglog
postgres-# OPTIONS ( filename 'log/postgresql-2023-09-24_165521.csv', format 'csv' );
CREATE FOREIGN TABLE
postgres=# select * from pglog;
log_time | user_name | database_name | process_id | connection_from | session_id | session_line_num | command_tag | session_start_time | virtual_transaction_id | transaction_id | error_severity | sql_state_code |
message | detail | hint | internal_query | internal_query_pos | context | query | query_pos | location | application_name | backend_type | leade
r_pid | query_id
----------------------------+-----------+---------------+------------+-----------------+---------------+------------------+-------------+------------------------+------------------------+----------------+----------------+----------------+-------------------------------
-----------------------------------------------------------------------------------+--------+--------------------------------------------------------+----------------+--------------------+---------+-------+-----------+----------+------------------+--------------+------
------+----------
2023-09-25 06:55:21.533+08 | | | 13046 | | 650ff979.32f6 | 1 | | 2023-09-25 06:55:21+08 | | 0 | LOG | 00000 | ending log output to stderr
| | Future log output will go to log destination "csvlog". | | | | | | | | postmaster |
| 0
2023-09-25 06:55:21.533+08 | | | 13046 | | 650ff979.32f6 | 2 | | 2023-09-25 06:55:21+08 | | 0 | LOG | 00000 | starting PostgreSQL 14.9 on x8
6_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit | | | | | | | | | | postmaster |
| 0
2023-09-25 06:55:21.535+08 | | | 13046 | | 650ff979.32f6 | 3 | | 2023-09-25 06:55:21+08 | | 0 | LOG | 00000 | listening on IPv4 address "0.0
.0.0", port 5432 | | | | | | | | | | postmaster |
| 0
2023-09-25 06:55:21.536+08 | | | 13046 | | 650ff979.32f6 | 4 | | 2023-09-25 06:55:21+08 | | 0 | LOG | XX000 | could not create IPv6 socket f
or address "::": Address family not supported by protocol | | | | | | | | | | postmaster |
| 0
2023-09-25 06:55:21.537+08 | | | 13046 | | 650ff979.32f6 | 5 | | 2023-09-25 06:55:21+08 | | 0 | LOG | 00000 | listening on Unix socket "/tmp
/.s.PGSQL.5432" | | | | | | | | | | postmaster |
| 0
2023-09-25 06:55:21.539+08 | | | 13054 | | 650ff979.32fe | 1 | | 2023-09-25 06:55:21+08 | | 0 | LOG | 00000 | database system was shut down
at 2023-09-24 16:52:20 CST | | | | | | | | | | startup |
| 0
2023-09-25 06:55:21.543+08 | | | 13046 | | 650ff979.32f6 | 6 | | 2023-09-25 06:55:21+08 | | 0 | LOG | 00000 | database system is ready to ac
cept connections | | | | | | | | | | postmaster |
| 0
(7 rows)
postgres=#
通过执行shell命令来获取操作系统磁盘用量
#如果已经创建插件就不用重复创建了
CREATE EXTENSION file_fdw;
#如果已经创建外部服务器就不用重复创建了
CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw;
#使用df和awk的shell命令输出成csv格式来创建disk_free外部表
CREATE FOREIGN TABLE disk_free (
filesystem text,
size text,
used text,
avail text,
use_precent text,
mounted_on text
) SERVER fs OPTIONS (PROGRAM
$$
df -h | awk '{print $1,$2,$3,$4,$5,$6}' OFS='\037'
$$
, FORMAT 'csv', HEADER 'TRUE', DELIMITER E'\037'
);
#查询外部表获取操作系统磁盘空间
select * from disk_free;
操作如下
[postgres@pg14 ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE disk_free (
postgres(# filesystem text,
postgres(# size text,
postgres(# used text,
postgres(# avail text,
postgres(# use_precent text,
postgres(# mounted_on text
postgres(# ) SERVER fs OPTIONS (PROGRAM
postgres(# $$
postgres$# df -h | awk '{print $1,$2,$3,$4,$5,$6}' OFS='\037'
postgres$# $$
postgres(# , FORMAT 'csv', HEADER 'TRUE', DELIMITER E'\037'
postgres(# );
CREATE FOREIGN TABLE
postgres=# select * from disk_free;
filesystem | size | used | avail | use_precent | mounted_on
-------------------------+-------+------+-------+-------------+----------------
devtmpfs | 2.0G | 0 | 2.0G | 0% | /dev
tmpfs | 2.0G | 108K | 2.0G | 1% | /dev/shm
tmpfs | 2.0G | 13M | 2.0G | 1% | /run
tmpfs | 2.0G | 0 | 2.0G | 0% | /sys/fs/cgroup
/dev/mapper/centos-root | 56G | 5.0G | 51G | 10% | /
/dev/sda1 | 1014M | 174M | 841M | 18% | /boot
tmpfs | 394M | 12K | 394M | 1% | /run/user/42
tmpfs | 394M | 0 | 394M | 0% | /run/user/0
(8 rows)
postgres=#
通过ssh/curl/wget命令网络获取CSV数据
#如果已经创建插件就不用重复创建了
CREATE EXTENSION file_fdw;
#如果已经创建外部服务器就不用重复创建了
CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw;
#curl命令通过web api获取人员信息的csv，创建外部表poeple
CREATE FOREIGN TABLE poeple (
id int,
userid text,
firstname text,
lastname text,
sex text,
email text,
phone text,
birthday text,
jobtitle text
) SERVER fs OPTIONS (PROGRAM
$$
curl -s https://media.githubusercontent.com/media/datablist/sample-csv-files/main/files/people/people-100.csv
$$
, FORMAT 'csv', HEADER 'TRUE', DELIMITER ','
);
#查询人员表前5条记录
select * from poeple limit 5;
操作如下
[postgres@pg14 ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# drop FOREIGN TABLE poeple ;
DROP FOREIGN TABLE
postgres=# \q
[postgres@pg14 ~]$
[postgres@pg14 ~]$
[postgres@pg14 ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE FOREIGN TABLE poeple (
postgres(# id int,
postgres(# userid text,
postgres(# firstname text,
postgres(# lastname text,
postgres(# sex text,
postgres(# email text,
postgres(# phone text,
postgres(# birthday text,
postgres(# jobtitle text
postgres(# ) SERVER fs OPTIONS (PROGRAM
postgres(# $$
postgres$# curl -s https://media.githubusercontent.com/media/datablist/sample-csv-files/main/files/people/people-100.csv
postgres$# $$
postgres(# , FORMAT 'csv', HEADER 'TRUE', DELIMITER ','
postgres(# );
CREATE FOREIGN TABLE
postgres=# select * from poeple limit 5;
id | userid | firstname | lastname | sex | email | phone | birthday | jobtitle
----+-----------------+-----------+----------+--------+----------------------------+------------------------+------------+--------------------
1 | 88F7B33d2bcf9f5 | Shelby | Terrell | Male | elijah57@example.net | 001-084-906-7849x73518 | 1945-10-26 | Games developer
2 | f90cD3E76f1A9b9 | Phillip | Summers | Female | bethany14@example.com | 214.112.6044x4913 | 1910-03-24 | Phytotherapist
3 | DbeAb8CcdfeFC2c | Kristine | Travis | Male | bthompson@example.com | 277.609.7938 | 1992-07-02 | Homeopath
4 | A31Bee3c201ef58 | Yesenia | Martinez | Male | kaitlinkaiser@example.com | 584.094.6111 | 2017-08-03 | Market researcher
5 | 1bA7A3dc874da3c | Lori | Todd | Male | buchananmanuel@example.net | 689-207-3558x7233 | 1938-12-01 | Veterinary surgeon
(5 rows)
postgres=#
postgres_fdw
使用postgres_fdw可以对不同PostgreSQL数据库进行访问，写入，下推等操作。详见官网。本次只是简单操作过程，下推、分布式等操作深入，可参考其他博文
快速准备目标PostgreSQL环境
在192.168.17.8这台docker主机上启动一个pg环境
#拉取postgres官方镜像
docker pull postgres
#运行后台postgres镜像，允许所有IP远程访问，postgres用户设置为123456，宿主机端口映射5432
docker run --name postgrestest -e POSTGRES_PASSWORD=123456 -e ALLOW_IP_RANGE=0.0.0.0/0 -p 5432:5432 -d postgres
#查看postgrestest容器允许状态
docker ps
#进入postgrestest容器构造测试数据
docker exec -it postgrestest bash
#切换到postgres用户下
su - postgres
#创建数据库
create database testdb;
#连接数据库
\c testdb
#创建表t
create table t (id int,info text);
#插入一条测试记录
insert into t values(1,'hello postgres_fdw');
#查询记录
select * from t;
操作如下
[root@docker ~]# docker pull postgres
Using default tag: latest
latest: Pulling from library/postgres
a2abf6c4d29d: Pull complete
e1769f49f910: Pull complete
33a59cfee47c: Pull complete
461b2090c345: Pull complete
8ed8ab6290ac: Pull complete
495e42c822a0: Pull complete
18e858c71c58: Pull complete
594792c80d5f: Pull complete
794976979956: Pull complete
eb5e1a73c3ca: Pull complete
6d6360292cba: Pull complete
131e916e1a28: Pull complete
757a73507e2e: Pull complete
Digest: sha256:f329d076a8806c0ce014ce5e554ca70f4ae9407a16bb03baa7fef287ee6371f1
Status: Downloaded newer image for postgres:latest
docker.io/library/postgres:latest
[root@docker ~]# docker run --name postgrestest -e POSTGRES_PASSWORD=123456 -e ALLOW_IP_RANGE=0.0.0.0/0 -p 5432:5432 -d postgres
c69fec9ed79d102274e8a82df5d46ad53a9f91092493cc34b55d646aaa1ebb4d
[root@docker ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
c69fec9ed79d postgres "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:5432->5432/tcp postgrestest
[root@docker ~]# docker exec -it postgrestest bash
root@c69fec9ed79d:/# su - postgres
postgres@c69fec9ed79d:~$ psql
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.
postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# create table t (id int,info text);
CREATE TABLE
testdb=# insert into t values(1,'hello postgres_fdw');
INSERT 0 1
testdb=# select * from t;
id | info
----+--------------------
1 | hello postgres_fdw
(1 row)
testdb=#
源端创建扩展访问目标数据库
#用psql终端登录
psql
#创建postgres_fdw插件
CREATE EXTENSION postgres_fdw;
#创建外部服务器输入目标端数据库ip，端口和数据库名
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.17.8', port '5432', dbname 'testdb');
#创建用户映射本地用户postgres和目标端用户名，密码
CREATE USER MAPPING FOR postgres SERVER foreign_server OPTIONS (user 'postgres', password '123456');
#创建映射表注意表结构和目标端一致性，输入目标端schema和表名
CREATE FOREIGN TABLE foreign_table (id int,info text) SERVER foreign_server OPTIONS (schema_name 'public', table_name 't');
#查询记录
select * from foreign_table;
#插入两条记录
insert into foreign_table values(2,'go fighting');
insert into foreign_table values(3,'why always me');
#更新一条记录
update foreign_table set info='hello postgres_fdw!!!' where id=1;
#查询最终记录
select * from foreign_table;
操作如下
[postgres@pg14 ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.17.8', port '5432', dbname 'testdb');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER foreign_server OPTIONS (user 'postgres', password '123456');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE foreign_table (id int,info text) SERVER foreign_server OPTIONS (schema_name 'public', table_name 't');
CREATE FOREIGN TABLE
postgres=# select * from foreign_table;
id | info
----+--------------------
1 | hello postgres_fdw
(1 row)
postgres=# insert into foreign_table values(2,'go fighting');
INSERT 0 1
postgres=# insert into foreign_table values(3,'why always me');
INSERT 0 1
postgres=# update foreign_table set info='hello postgres_fdw!!!' where id=1;
UPDATE 1
postgres=# select * from foreign_table;
id | info
----+-----------------------
2 | go fighting
3 | why always me
1 | hello postgres_fdw!!!
(3 rows)
postgres=#
oracle_fdw
使用oracle_fdw插件快速高效的访问oracle数据库，详见官网
快速准备目标Oracle环境
#拉取oracle11g测试镜像
docker pull oracleinanutshell/oracle-xe-11g
#运行oracle11g容器，宿主机开放端口1521，允许远程访问
docker run -d -p 1521:1521 --name oracle11g -e ORACLE_ALLOW_REMOTE=true oracleinanutshell/oracle-xe-11g
#查询容器运行情况
docker ps
#进入oracle11g容器构造测试数据
docker exec -it oracle11g bash
#切换到oracle用户
su - oracle
#sqlplus sys用户登录
sqlplus / as sysdba
#创建测试用户orauser密码orapwd
create user orauser identified by orapwd;
#授权dba给orauser
grant dba to orauser;
#连接orauser
conn orauser/orapwd
#创建测试表oratab
create table oratab (id number,info varchar2(200));
#插入测试数据
insert into oratab values(1,'hello oracle_fdw');
commit;
select * from oratab;
操作如下
[root@docker ~]# docker pull oracleinanutshell/oracle-xe-11g
Using default tag: latest
latest: Pulling from oracleinanutshell/oracle-xe-11g
6cf436f81810: Pull complete
987088a85b96: Pull complete
b4624b3efe06: Pull complete
d42beb8ded59: Pull complete
15522cc0fb47: Pull complete
f747bf1d551d: Pull complete
Digest: sha256:8b740e77d4b90add693fedb22938f340821e89665fb58ecaeeb0dace853b9ee5
Status: Downloaded newer image for oracleinanutshell/oracle-xe-11g:latest
docker.io/oracleinanutshell/oracle-xe-11g:latest
[root@docker ~]# docker run -d -p 1521:1521 --name oracle11g -e ORACLE_ALLOW_REMOTE=true oracleinanutshell/oracle-xe-11g
c369ae97f198fbe60e3b50cbf92a4ef0f855e0e6895b98e44235fc1c4dd8f987
[root@docker ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
c369ae97f198 oracleinanutshell/oracle-xe-11g "/bin/sh -c '/usr/sb…" 2 minutes ago Up 2 minutes 22/tcp, 8080/tcp, 0.0.0.0:1521->1521/tcp oracle11g
c69fec9ed79d postgres "docker-entrypoint.s…" 43 minutes ago Up 43 minutes 0.0.0.0:5432->5432/tcp postgrestest
[root@docker ~]# docker exec -it oracle11g bash
root@c369ae97f198:/# su - oracle
oracle@c369ae97f198:~$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sun Sep 24 12:30:21 2023
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> create user orauser identified by orapwd;
User created.
SQL> grant dba to orauser;
Grant succeeded.
SQL> conn orauser/orapwd
Connected.
SQL> create table oratab (id number,info varchar2(200));
Table created.
SQL> insert into oratab values(1,'hello oracle_fdw');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from oratab;
ID
----------
INFO
--------------------------------------------------------------------------------
1
hello oracle_fdw
SQL>
源端安装oracle客户端
下载oracle客户软件官网，instantclient-basic-linux.x64-11.2.0.4.0.zip和instantclient-sdk-linux.x64-11.2.0.4.0.zip文件
#查看上传客户端文件
ll
#解压客户端软件包到/opt/oracle目录下
unzip instantclient-basic-linux.x64-11.2.0.4.0.zip -d /opt/oracle
unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip -d /opt/oracle
chown -R postgres:postgres /opt/oracle
#添加oracle客户端动态链接库
echo /opt/oracle/instantclient_11_2 > /etc/ld.so.conf.d/oracle.conf
ldconfig
操作如下
[root@pg14 ~]# ll
total 88388
-rw-------. 1 root root 1836 Mar 7 2023 anaconda-ks.cfg
drwxr-xr-x 2 root root 6 Sep 24 16:18 Desktop
drwxr-xr-x 2 root root 6 Sep 24 16:18 Documents
drwxr-xr-x 2 root root 6 Sep 24 16:18 Downloads
-rw-r--r--. 1 root root 1884 Mar 7 2023 initial-setup-ks.cfg
-rw-r--r-- 1 root root 60704657 Sep 24 20:53 instantclient-basic-linux.x64-11.2.0.4.0.zip
-rw-r--r-- 1 root root 643089 Sep 24 20:53 instantclient-sdk-linux.x64-11.2.0.4.0.zip
drwxr-xr-x 2 root root 6 Sep 24 16:18 Music
drwxr-xr-x 2 root root 6 Sep 24 16:18 Pictures
-rw-r--r-- 1 root root 29143610 Sep 24 18:42 postgresql-14.9.tar.gz
drwxr-xr-x 2 root root 6 Sep 24 16:18 Public
drwxr-xr-x 2 root root 6 Sep 24 16:18 Templates
drwxr-xr-x 2 root root 6 Sep 24 16:18 Videos
[root@pg14 ~]# unzip instantclient-basic-linux.x64-11.2.0.4.0.zip -d /opt/oracle
Archive: instantclient-basic-linux.x64-11.2.0.4.0.zip
inflating: /opt/oracle/instantclient_11_2/BASIC_README
inflating: /opt/oracle/instantclient_11_2/adrci
inflating: /opt/oracle/instantclient_11_2/genezi
inflating: /opt/oracle/instantclient_11_2/libclntsh.so.11.1
inflating: /opt/oracle/instantclient_11_2/libnnz11.so
inflating: /opt/oracle/instantclient_11_2/libocci.so.11.1
inflating: /opt/oracle/instantclient_11_2/libociei.so
inflating: /opt/oracle/instantclient_11_2/libocijdbc11.so
inflating: /opt/oracle/instantclient_11_2/ojdbc5.jar
inflating: /opt/oracle/instantclient_11_2/ojdbc6.jar
inflating: /opt/oracle/instantclient_11_2/uidrvci
inflating: /opt/oracle/instantclient_11_2/xstreams.jar
[root@pg14 ~]# unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip -d /opt/oracle
Archive: instantclient-sdk-linux.x64-11.2.0.4.0.zip
creating: /opt/oracle/instantclient_11_2/sdk/
creating: /opt/oracle/instantclient_11_2/sdk/include/
inflating: /opt/oracle/instantclient_11_2/sdk/include/occi.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/occiCommon.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/occiControl.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/occiData.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/occiObjects.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/occiAQ.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/oci.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/oci1.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/oci8dp.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ociap.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ociapr.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ocidef.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ocidem.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ocidfn.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ociextp.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ocikpr.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ocixmldb.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ocixstream.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/odci.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/oratypes.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ori.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/orid.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/orl.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/oro.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ort.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/xa.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/nzt.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/nzerror.h
inflating: /opt/oracle/instantclient_11_2/sdk/include/ldap.h
creating: /opt/oracle/instantclient_11_2/sdk/demo/
inflating: /opt/oracle/instantclient_11_2/sdk/demo/demo.mk
inflating: /opt/oracle/instantclient_11_2/sdk/demo/cdemo81.c
inflating: /opt/oracle/instantclient_11_2/sdk/demo/occidemo.sql
inflating: /opt/oracle/instantclient_11_2/sdk/demo/occidemod.sql
inflating: /opt/oracle/instantclient_11_2/sdk/demo/occidml.cpp
inflating: /opt/oracle/instantclient_11_2/sdk/demo/occiobj.cpp
inflating: /opt/oracle/instantclient_11_2/sdk/demo/occiobj.typ
inflating: /opt/oracle/instantclient_11_2/sdk/SDK_README
extracting: /opt/oracle/instantclient_11_2/sdk/ottclasses.zip
inflating: /opt/oracle/instantclient_11_2/sdk/ott
[root@pg14 ~]# chown -R postgres:postgres /opt/oracle
[root@pg14 ~]# echo /opt/oracle/instantclient_11_2 > /etc/ld.so.conf.d/oracle.conf
[root@pg14 ~]# ldconfig
[root@pg14 ~]#
源码安装oracle_fdw
官网下载oracle_fdw源码包，上传oracle_fdw-ORACLE_FDW_2_6_0.tar.gz到源端/home/postgres目录下
#切换到postgres用户下
su - postgres
#查看oracle_fdw-ORACLE_FDW_2_6_0.tar.gz源码包
ll
#解压oracle_fdw-ORACLE_FDW_2_6_0.tar.gz
tar xvf oracle_fdw-ORACLE_FDW_2_6_0.tar.gz
cd oracle_fdw-ORACLE_FDW_2_6_0/
#设置oracle环境变量
export ORACLE_HOME=/opt/oracle/instantclient_11_2
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2:/opt/pgsql/lib
#创建编译所需的libclntsh.so,libocci.so动态链接库软链文件
cd /opt/oracle/instantclient_11_2
ln -s libclntsh.so.11.1 libclntsh.so
ln -s libocci.so.11.1 libocci.so
cd /home/postgres/oracle_fdw-ORACLE_FDW_2_6_0/
#编译安装
make
make install
操作如下
[postgres@pg14 ~]$ ll
total 28604
-rw-r--r-- 1 postgres postgres 135949 Sep 24 21:05 oracle_fdw-ORACLE_FDW_2_6_0.tar.gz
drwxrwxr-x 6 postgres postgres 4096 Sep 24 16:46 postgresql-14.9
-rw-r--r-- 1 postgres postgres 29143610 Aug 21 10:06 postgresql-14.9.tar.gz
[postgres@pg14 ~]$ tar xvf oracle_fdw-ORACLE_FDW_2_6_0.tar.gz
oracle_fdw-ORACLE_FDW_2_6_0/
oracle_fdw-ORACLE_FDW_2_6_0/.gitignore
oracle_fdw-ORACLE_FDW_2_6_0/CHANGELOG
oracle_fdw-ORACLE_FDW_2_6_0/LICENSE
oracle_fdw-ORACLE_FDW_2_6_0/Makefile
oracle_fdw-ORACLE_FDW_2_6_0/README.md
oracle_fdw-ORACLE_FDW_2_6_0/README.oracle_fdw
oracle_fdw-ORACLE_FDW_2_6_0/TODO
oracle_fdw-ORACLE_FDW_2_6_0/expected/
oracle_fdw-ORACLE_FDW_2_6_0/expected/oracle_fdw.out
oracle_fdw-ORACLE_FDW_2_6_0/expected/oracle_gis.out
oracle_fdw-ORACLE_FDW_2_6_0/expected/oracle_import.out
oracle_fdw-ORACLE_FDW_2_6_0/expected/oracle_join.out
oracle_fdw-ORACLE_FDW_2_6_0/msvc/
oracle_fdw-ORACLE_FDW_2_6_0/msvc/oracle_fdw.props
oracle_fdw-ORACLE_FDW_2_6_0/msvc/oracle_fdw.sln
oracle_fdw-ORACLE_FDW_2_6_0/msvc/oracle_fdw.vcxproj
oracle_fdw-ORACLE_FDW_2_6_0/msvc/oracle_msvc.c
oracle_fdw-ORACLE_FDW_2_6_0/oracle_fdw--1.0--1.1.sql
oracle_fdw-ORACLE_FDW_2_6_0/oracle_fdw--1.1--1.2.sql
oracle_fdw-ORACLE_FDW_2_6_0/oracle_fdw--1.2.sql
oracle_fdw-ORACLE_FDW_2_6_0/oracle_fdw.c
oracle_fdw-ORACLE_FDW_2_6_0/oracle_fdw.control
oracle_fdw-ORACLE_FDW_2_6_0/oracle_fdw.h
oracle_fdw-ORACLE_FDW_2_6_0/oracle_gis.c
oracle_fdw-ORACLE_FDW_2_6_0/oracle_utils.c
oracle_fdw-ORACLE_FDW_2_6_0/sql/
oracle_fdw-ORACLE_FDW_2_6_0/sql/oracle_fdw.sql
oracle_fdw-ORACLE_FDW_2_6_0/sql/oracle_gis.sql
oracle_fdw-ORACLE_FDW_2_6_0/sql/oracle_import.sql
oracle_fdw-ORACLE_FDW_2_6_0/sql/oracle_join.sql
[postgres@pg14 ~]$ cd oracle_fdw-ORACLE_FDW_2_6_0/
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$ export ORACLE_HOME=/opt/oracle/instantclient_11_2
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$ export OCI_LIB_DIR=$ORACLE_HOME
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$ export OCI_INC_DIR=$ORACLE_HOME/sdk/include
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$ export LD_LIBRARY_PATH=/opt/oracle/instantclient_11_2:/opt/pgsql/lib
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$ cd /opt/oracle/instantclient_11_2
[postgres@pg14 instantclient_11_2]$ ll
total 183520
-rwxrwxr-x 1 postgres postgres 25420 Aug 25 2013 adrci
-rw-rw-r-- 1 postgres postgres 439 Aug 25 2013 BASIC_README
-rwxrwxr-x 1 postgres postgres 47860 Aug 25 2013 genezi
-rwxrwxr-x 1 postgres postgres 53865194 Aug 25 2013 libclntsh.so.11.1
-r-xr-xr-x 1 postgres postgres 7996693 Aug 25 2013 libnnz11.so
-rwxrwxr-x 1 postgres postgres 1973074 Aug 25 2013 libocci.so.11.1
-rwxrwxr-x 1 postgres postgres 118738042 Aug 25 2013 libociei.so
-r-xr-xr-x 1 postgres postgres 164942 Aug 25 2013 libocijdbc11.so
-r--r--r-- 1 postgres postgres 2091135 Aug 25 2013 ojdbc5.jar
-r--r--r-- 1 postgres postgres 2739616 Aug 25 2013 ojdbc6.jar
drwxrwxr-x 4 postgres postgres 84 Aug 25 2013 sdk
-rwxrwxr-x 1 postgres postgres 192365 Aug 25 2013 uidrvci
-rw-rw-r-- 1 postgres postgres 66779 Aug 25 2013 xstreams.jar
[postgres@pg14 instantclient_11_2]$ ln -s libclntsh.so.11.1 libclntsh.so
[postgres@pg14 instantclient_11_2]$ ln -s libocci.so.11.1 libocci.so
[postgres@pg14 instantclient_11_2]$ cd /home/postgres/oracle_fdw-ORACLE_FDW_2_6_0/
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$ make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/opt/oracle/instantclient_11_2/sdk/include" -I"/opt/oracle/instantclient_11_2/oci/include" -I"/opt/oracle/instantclient_11_2/rdbms/public" -I"/opt/oracle/instantclient_11_2/" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o oracle_fdw.o oracle_fdw.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/opt/oracle/instantclient_11_2/sdk/include" -I"/opt/oracle/instantclient_11_2/oci/include" -I"/opt/oracle/instantclient_11_2/rdbms/public" -I"/opt/oracle/instantclient_11_2/" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o oracle_utils.o oracle_utils.c
oracle_utils.c: In function ‘allocHandle.isra.1’:
oracle_utils.c:3225:5: warning: ‘rc’ may be used uninitialized in this function [-Wmaybe-uninitialized]
if (rc != OCI_SUCCESS)
^
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/opt/oracle/instantclient_11_2/sdk/include" -I"/opt/oracle/instantclient_11_2/oci/include" -I"/opt/oracle/instantclient_11_2/rdbms/public" -I"/opt/oracle/instantclient_11_2/" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o oracle_gis.o oracle_gis.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/opt/pg14/lib -Wl,--as-needed -Wl,-rpath,'/opt/pg14/lib',--enable-new-dtags -L"/opt/oracle/instantclient_11_2/" -L"/opt/oracle/instantclient_11_2/bin" -L"/opt/oracle/instantclient_11_2/lib" -L"/opt/oracle/instantclient_11_2/lib/amd64" -lclntsh
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$ make install
/bin/mkdir -p '/opt/pg14/lib/postgresql'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/mkdir -p '/opt/pg14/share/doc/postgresql/extension'
/bin/install -c -m 755 oracle_fdw.so '/opt/pg14/lib/postgresql/oracle_fdw.so'
/bin/install -c -m 644 .//oracle_fdw.control '/opt/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql '/opt/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//README.oracle_fdw '/opt/pg14/share/doc/postgresql/extension/'
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$
源端创建扩展访问目标数据库
#psql登录
psql
#创建oracle_fdw插件
CREATE EXTENSION oracle_fdw;
#创建oradb外部服务器，输入ip:端口/实列名
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '192.168.17.8:1521/XE');
#创建用户映射本地用户postgres，输入oracle目标端用户和密码
CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'orauser', password 'orapwd');
#创建外部表注意表结构和目标端一致性，输入目标端schema和表名
CREATE FOREIGN TABLE oratab (id int,info text) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');
select * from oratab;
#插入一条记录
insert into oratab values(2,'add new record');
#查询最终记录
select * from oratab;
操作如下
[postgres@pg14 oracle_fdw-ORACLE_FDW_2_6_0]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '192.168.17.8:1521/XE');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'orauser', password 'orapwd');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE oratab (id int,info text) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');
CREATE FOREIGN TABLE
postgres=# select * from oratab;
id | info
----+------------------
1 | hello oracle_fdw
(1 row)
postgres=# insert into oratab values(2,'add new record');
INSERT 0 1
postgres=# select * from oratab;
id | info
----+------------------
1 | hello oracle_fdw
2 | add new record
(2 rows)
postgres=#
mysql_fdw
使用mysql_fdw需要用到C语言驱动library。详见官网
快速准备目标MySQL环境
#拉取mysql镜像
docker pull mysql:latest
#创建mysql容器mysqltest，映射宿主机端口3306，设置root密码123456
docker run --name=mysqltest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
#查看容器运行状态
docker ps
#进入mysqltest容器，构建测试数据
docker exec -it mysqltest bash
#mysql登录数据库
mysql -uroot -p123456
#切换到mysql数据库
use mysql
#修改8.0caching_sha2_password加密方式，使得源端能远程访问
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
select host, user, plugin from user;
#创建db数据库
create database db;
#切换到db数据库
use db;
#创建mysqltab测试表
create table mysqltab (id int, info text);
#插入测试数据
insert into mysqltab values(1,'hello mysql_fdw');
select * from mysqltab;
#创建主键便于源端pg访问进行DML操作，如果没有主键会报ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation
alter table mysqltab add primary key (id);
操作如下
[root@docker ~]# docker pull mysql:latest
latest: Pulling from library/mysql
72a69066d2fe: Pull complete
93619dbc5b36: Pull complete
99da31dd6142: Pull complete
626033c43d70: Pull complete
37d5d7efb64e: Pull complete
ac563158d721: Pull complete
d2ba16033dad: Pull complete
688ba7d5c01a: Pull complete
00e060b6d11d: Pull complete
1c04857f594f: Pull complete
4d7cfa90e6ea: Pull complete
e0431212d27d: Pull complete
Digest: sha256:e9027fe4d91c0153429607251656806cc784e914937271037f7738bd5b8e7709
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest
[root@docker ~]# docker run --name=mysqltest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
352a58baea129da9866761e11bcbc028a402916f94028eb5d2fd1b76bd71362e
[root@docker ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
352a58baea12 mysql:latest "docker-entrypoint.s…" 14 seconds ago Up 13 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysqltest
c369ae97f198 oracleinanutshell/oracle-xe-11g "/bin/sh -c '/usr/sb…" 3 hours ago Up 3 hours 22/tcp, 8080/tcp, 0.0.0.0:1521->1521/tcp oracle11g
c69fec9ed79d postgres "docker-entrypoint.s…" 4 hours ago Up 4 hours 0.0.0.0:5432->5432/tcp postgrestest
[root@docker ~]# docker exec -it mysqltest bash
root@352a58baea12:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
mysql> create database db;
Query OK, 1 row affected (0.01 sec)
mysql> use db;
Database changed
mysql> create table mysqltab (id int, info text);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into mysqltab values(1,'hello mysql_fdw');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysqltab;
+------+-----------------+
| id | info |
+------+-----------------+
| 1 | hello mysql_fdw |
+------+-----------------+
1 row in set (0.00 sec)
mysql> alter table mysqltab add primary key (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
源端安装mysql驱动
去mysql官网下载客户端，选择操作系统Redhat/Oracle Linux，选择OS版本7（x86，64-bit）下载RPM Package, Client Utilities，RPM Package, Client Plugins，RPM Package, Development Libraries，RPM Package, MySQL Configuration，RPM Package, Shared Libraries的RPM安装包。上传到源端服务器
#查看源端5个rpm安装包
ll
#强制安装rpm安装包
rpm -ivh mysql-community-client-8.0.33-1.el7.x86_64.rpm mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm mysql-community-common-8.0.33-1.el7.x86_64.rpm mysql-community-devel-8.0.33-1.el7.x86_64.rpm mysql-community-libs-8.0.33-1.el7.x86_64.rpm --force --nodeps
#添加mysql客户端动态链接库
echo /usr/lib64/mysql > /etc/ld.so.conf.d/mysql.conf
ldconfig
#查看mysql_fdw使用的C语言驱动library，如果没有的话创建软链
ll /usr/lib64/mysql/libmysqlclient.so
操作如下
[root@pg14 ~]# ll
total 112768
-rw-------. 1 root root 1836 Mar 7 2023 anaconda-ks.cfg
drwxr-xr-x 2 root root 6 Sep 24 16:18 Desktop
drwxr-xr-x 2 root root 6 Sep 24 16:18 Documents
drwxr-xr-x 2 root root 6 Sep 24 16:18 Downloads
-rw-r--r--. 1 root root 1884 Mar 7 2023 initial-setup-ks.cfg
-rw-r--r-- 1 root root 60704657 Sep 24 20:53 instantclient-basic-linux.x64-11.2.0.4.0.zip
-rw-r--r-- 1 root root 643089 Sep 24 20:53 instantclient-sdk-linux.x64-11.2.0.4.0.zip
drwxr-xr-x 2 root root 6 Sep 24 16:18 Music
-rw-r--r-- 1 root root 16972740 Sep 25 08:47 mysql-community-client-8.0.33-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 3746004 Sep 25 08:44 mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 680276 Sep 25 08:44 mysql-community-common-8.0.33-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1969188 Sep 25 08:44 mysql-community-devel-8.0.33-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1587536 Sep 25 08:44 mysql-community-libs-8.0.33-1.el7.x86_64.rpm
drwxr-xr-x 2 root root 6 Sep 24 16:18 Pictures
-rw-r--r-- 1 root root 29143610 Sep 24 18:42 postgresql-14.9.tar.gz
drwxr-xr-x 2 root root 6 Sep 24 16:18 Public
drwxr-xr-x 2 root root 6 Sep 24 16:18 Templates
drwxr-xr-x 2 root root 6 Sep 24 16:18 Videos
[root@pg14 ~]# rpm -ivh mysql-community-client-8.0.33-1.el7.x86_64.rpm mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm mysql-community-common-8.0.33-1.el7.x86_64.rpm mysql-community-devel-8.0.33-1.el7.x86_64.rpm mysql-community-libs-8.0.33-1.el7.x86_64.rpm --force --nodeps
warning: mysql-community-client-8.0.33-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-client-plugins-8.################################# [ 20%]
2:mysql-community-common-8.0.33-1.e################################# [ 40%]
3:mysql-community-libs-8.0.33-1.el7################################# [ 60%]
4:mysql-community-client-8.0.33-1.e################################# [ 80%]
5:mysql-community-devel-8.0.33-1.el################################# [100%]
[root@pg14 ~]# echo /usr/lib64/mysql > /etc/ld.so.conf.d/mysql.conf
[root@pg14 ~]# ldconfig
[root@pg14 ~]# ll /usr/lib64/mysql/libmysqlclient.so
lrwxrwxrwx 1 root root 20 Sep 25 08:48 /usr/lib64/mysql/libmysqlclient.so -> libmysqlclient.so.21
[root@pg14 ~]#
源码安装mysql_fdw
下载源码文件，把源码上传到/home/postgres目录下，进行编译安装
#切换到postgres用户下
su - postgres
#查看源码安装包文件mysql_fdw-REL-2_9_1.tar.gz
ll
#解压mysql_fdw-REL-2_9_1.tar.gz
tar -zxvf mysql_fdw-REL-2_9_1.tar.gz
#设置LD_LIBRARY_PATH环境变量
export LD_LIBRARY_PATH=/usr/lib64/mysql:/opt/pgsql/lib
#进入源码目录
cd mysql_fdw-REL-2_9_1/
#源码编译安装
make USE_PGXS=1
make USE_PGXS=1 install
操作如下
[root@pg14 ~]# su - postgres
Last login: Mon Sep 25 08:50:15 CST 2023 on pts/1
[postgres@pg14 ~]$ ll
total 28776
-rw-r--r-- 1 postgres postgres 168896 Sep 25 00:15 mysql_fdw-REL-2_9_1.tar.gz
drwxrwxr-x 5 postgres postgres 4096 Sep 24 22:04 oracle_fdw-ORACLE_FDW_2_6_0
-rw-r--r-- 1 postgres postgres 135949 Sep 24 21:05 oracle_fdw-ORACLE_FDW_2_6_0.tar.gz
drwx------ 3 postgres postgres 18 Sep 24 22:12 oradiag_postgres
drwxrwxr-x 6 postgres postgres 4096 Sep 24 16:46 postgresql-14.9
-rw-r--r-- 1 postgres postgres 29143610 Aug 21 10:06 postgresql-14.9.tar.gz
[postgres@pg14 ~]$ tar -zxvf mysql_fdw-REL-2_9_1.tar.gz
mysql_fdw-REL-2_9_1/
mysql_fdw-REL-2_9_1/.gitattributes
mysql_fdw-REL-2_9_1/.gitignore
mysql_fdw-REL-2_9_1/CONTRIBUTING.md
mysql_fdw-REL-2_9_1/LICENSE
mysql_fdw-REL-2_9_1/META.json
mysql_fdw-REL-2_9_1/Makefile
mysql_fdw-REL-2_9_1/README.md
mysql_fdw-REL-2_9_1/connection.c
mysql_fdw-REL-2_9_1/deparse.c
mysql_fdw-REL-2_9_1/expected/
mysql_fdw-REL-2_9_1/expected/aggregate_pushdown.out
mysql_fdw-REL-2_9_1/expected/aggregate_pushdown_1.out
mysql_fdw-REL-2_9_1/expected/aggregate_pushdown_2.out
mysql_fdw-REL-2_9_1/expected/aggregate_pushdown_4.out
mysql_fdw-REL-2_9_1/expected/connection_validation.out
mysql_fdw-REL-2_9_1/expected/dml.out
mysql_fdw-REL-2_9_1/expected/join_pushdown.out
mysql_fdw-REL-2_9_1/expected/join_pushdown_1.out
mysql_fdw-REL-2_9_1/expected/join_pushdown_2.out
mysql_fdw-REL-2_9_1/expected/join_pushdown_3.out
mysql_fdw-REL-2_9_1/expected/limit_offset_pushdown.out
mysql_fdw-REL-2_9_1/expected/limit_offset_pushdown_1.out
mysql_fdw-REL-2_9_1/expected/misc.out
mysql_fdw-REL-2_9_1/expected/misc_1.out
mysql_fdw-REL-2_9_1/expected/pushdown.out
mysql_fdw-REL-2_9_1/expected/select.out
mysql_fdw-REL-2_9_1/expected/server_options.out
mysql_fdw-REL-2_9_1/mysql_fdw--1.0--1.1.sql
mysql_fdw-REL-2_9_1/mysql_fdw--1.0.sql
mysql_fdw-REL-2_9_1/mysql_fdw--1.1--1.2.sql
mysql_fdw-REL-2_9_1/mysql_fdw--1.1.sql
mysql_fdw-REL-2_9_1/mysql_fdw--1.2.sql
mysql_fdw-REL-2_9_1/mysql_fdw.c
mysql_fdw-REL-2_9_1/mysql_fdw.control
mysql_fdw-REL-2_9_1/mysql_fdw.h
mysql_fdw-REL-2_9_1/mysql_fdw_pushdown.config
mysql_fdw-REL-2_9_1/mysql_init.sh
mysql_fdw-REL-2_9_1/mysql_pushability.c
mysql_fdw-REL-2_9_1/mysql_pushability.h
mysql_fdw-REL-2_9_1/mysql_query.c
mysql_fdw-REL-2_9_1/mysql_query.h
mysql_fdw-REL-2_9_1/option.c
mysql_fdw-REL-2_9_1/sql/
mysql_fdw-REL-2_9_1/sql/aggregate_pushdown.sql
mysql_fdw-REL-2_9_1/sql/connection_validation.sql
mysql_fdw-REL-2_9_1/sql/dml.sql
mysql_fdw-REL-2_9_1/sql/join_pushdown.sql
mysql_fdw-REL-2_9_1/sql/limit_offset_pushdown.sql
mysql_fdw-REL-2_9_1/sql/misc.sql
mysql_fdw-REL-2_9_1/sql/pushdown.sql
mysql_fdw-REL-2_9_1/sql/select.sql
mysql_fdw-REL-2_9_1/sql/server_options.sql
[postgres@pg14 ~]$ export LD_LIBRARY_PATH=/usr/lib64/mysql:/opt/pgsql/lib
[postgres@pg14 ~]$ cd mysql_fdw-REL-2_9_1/
[postgres@pg14 mysql_fdw-REL-2_9_1]$ make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I/usr/include/mysql -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o connection.o connection.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I/usr/include/mysql -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o option.o option.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I/usr/include/mysql -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o deparse.o deparse.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I/usr/include/mysql -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o mysql_query.o mysql_query.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I/usr/include/mysql -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o mysql_fdw.o mysql_fdw.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I/usr/include/mysql -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o mysql_pushability.o mysql_pushability.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o mysql_fdw.so connection.o option.o deparse.o mysql_query.o mysql_fdw.o mysql_pushability.o -L/opt/pg14/lib -Wl,--as-needed -Wl,-rpath,'/opt/pg14/lib',--enable-new-dtags
[postgres@pg14 mysql_fdw-REL-2_9_1]$ make USE_PGXS=1 install
/bin/mkdir -p '/opt/pg14/lib/postgresql'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/install -c -m 755 mysql_fdw.so '/opt/pg14/lib/postgresql/mysql_fdw.so'
/bin/install -c -m 644 .//mysql_fdw.control '/opt/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//mysql_fdw--1.0.sql .//mysql_fdw--1.1.sql .//mysql_fdw--1.0--1.1.sql .//mysql_fdw--1.2.sql .//mysql_fdw--1.1--1.2.sql .//mysql_fdw_pushdown.config '/opt/pg14/share/postgresql/extension/'
[postgres@pg14 mysql_fdw-REL-2_9_1]$
源端创建扩展访问目标数据库
#使用psql登录数据库
psql
#创建mysql_fdw插件
CREATE EXTENSION mysql_fdw;
#创建mysql_server外部服务器，输入ip，端口
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.17.8', port '3306');
#创建用户映射本地用户postgres，输入MySQL目标端用户和密码
CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'root', password '123456');
#创建外部表注意表结构和目标端一致性，输入目标端数据库名和表名
CREATE FOREIGN TABLE mysqltab (id int, info text) SERVER mysql_server OPTIONS (dbname 'db', table_name 'mysqltab');
#添加插入一条记录
insert into mysqltab values(2,'add new mysql record');
#修改目标端一条记录
update mysqltab set info='hello mysql_fdw good extension!!!' where id=1;
#查询最终记录
select * from mysqltab;
操作如下
[postgres@pg14 mysql_fdw-REL-2_9_1]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.17.8', port '3306');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'root', password '123456');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE mysqltab (id int, info text) SERVER mysql_server OPTIONS (dbname 'db', table_name 'mysqltab');
CREATE FOREIGN TABLE
postgres=# select * from mysqltab;
id | info
----+-----------------
1 | hello mysql_fdw
(1 row)
postgres=# insert into mysqltab values(2,'add new mysql record');
INSERT 0 1
postgres=# select * from mysqltab;
id | info
----+----------------------
1 | hello mysql_fdw
2 | add new mysql record
(2 rows)
postgres=# update mysqltab set info='hello mysql_fdw good extension!!!' where id=1;
UPDATE 1
postgres=# select * from mysqltab;
id | info
----+-----------------------------------
1 | hello mysql_fdw good extension!!!
2 | add new mysql record
(2 rows)
postgres=#
tds_fdw
使用tds_fdw插件访问Sybase和Microsoft SQL Server数据库。详见官网
快速准备目标Microsoft SQL Server环境
#拉取SQL Server 2017镜像
docker pull mcr.microsoft.com/mssql/server:2017-latest
#创建SQL Server 2017容器sqlserver2017，映射宿主机端口1433，设置sa密码Passw0rd
docker run -p 1433:1433 -itd -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' --name sqlserver2017 mcr.microsoft.com/mssql/server:2017-latest
#查看容器运行状态
docker ps
#进入sqlserver2017容器，构建测试数据
docker exec -it sqlserver2017 bash
#切换到master数据库
use master
go
#创建测试表mytable
create table mytable (id int primary key,info varchar(200))
go
#插入测试数据
insert into mytable values(1,'hello tds_fdw');
go
select * from dbo.mytable;
go
操作如下
[root@docker ~]# docker pull mcr.microsoft.com/mssql/server:2017-latest
2017-latest: Pulling from mssql/server
c64da07494d4: Pull complete
c9150ca47089: Pull complete
a9a711ab60d1: Pull complete
Digest: sha256:acc5d6e346854dddc642cfa5d3e0d55f893d8ef3a44ade9232e3abe73ee1341f
Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2017-latest
mcr.microsoft.com/mssql/server:2017-latest
[root@docker ~]# docker run -p 1433:1433 -itd -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' --name sqlserver2017 mcr.microsoft.com/mssql/server:2017-latest
0cfc3508a25349c4e07f116b11ecb408fdd41f69f5c5e7723b8ef3644d5d3663
[root@docker ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0cfc3508a253 mcr.microsoft.com/mssql/server:2017-latest "/opt/mssql/bin/nonr…" 2 minutes ago Up 2 minutes 0.0.0.0:1433->1433/tcp sqlserver2017
352a58baea12 mysql:latest "docker-entrypoint.s…" 11 hours ago Up 11 hours 0.0.0.0:3306->3306/tcp, 33060/tcp mysqltest
c369ae97f198 oracleinanutshell/oracle-xe-11g "/bin/sh -c '/usr/sb…" 14 hours ago Up 14 hours 22/tcp, 8080/tcp, 0.0.0.0:1521->1521/tcp oracle11g
c69fec9ed79d postgres "docker-entrypoint.s…" 14 hours ago Up 14 hours 0.0.0.0:5432->5432/tcp postgrestest
[root@docker ~]# docker exec -it sqlserver2017 bash
root@0cfc3508a253:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Passw0rd
1> use master
2> go
Changed database context to 'master'.
1> create table mytable (id int primary key,info varchar(200))
2> go
1> insert into mytable values(1,'hello tds_fdw');
2> go
(1 rows affected)
1> select * from dbo.mytable;
2> go
id info
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 hello tds_fdw
(1 rows affected)
1>
源端安装FreeTDS驱动
tds_fdw依赖于FreeTDS驱动可以实现在 Linux 系统下访问微软的 SQL 数据库
#安装EPEL扩展Yum源，安装更多软件包
yum install -y epel-release
#安装freetds freetds-devel软件包
yum install -y freetds freetds-devel
操作如下
[root@pg14 ~]# yum install -y epel-release
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
mysql-connectors-community | 2.6 kB 00:00:00
mysql-tools-community | 2.6 kB 00:00:00
mysql80-community | 2.6 kB 00:00:00
updates | 2.9 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:7-11 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================================================
Installing:
epel-release noarch 7-11 extras 15 k
Transaction Summary
=============================================================================================================================================================================================================================================================================
Install 1 Package
Total download size: 15 k
Installed size: 24 k
Downloading packages:
epel-release-7-11.noarch.rpm | 15 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : epel-release-7-11.noarch 1/1
Verifying : epel-release-7-11.noarch 1/1
Installed:
epel-release.noarch 0:7-11
Complete!
[root@pg14 ~]# yum install -y freetds freetds-devel
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
epel/x86_64/metalink | 8.1 kB 00:00:00
* epel: mirrors.bfsu.edu.cn
epel | 4.7 kB 00:00:00
(1/3): epel/x86_64/group_gz | 99 kB 00:00:00
(2/3): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(3/3): epel/x86_64/primary_db | 7.0 MB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package freetds.x86_64 0:1.3.3-1.el7 will be installed
--> Processing Dependency: freetds-libs(x86-64) = 1.3.3-1.el7 for package: freetds-1.3.3-1.el7.x86_64
--> Processing Dependency: libodbc.so.2()(64bit) for package: freetds-1.3.3-1.el7.x86_64
--> Processing Dependency: libsybdb.so.5()(64bit) for package: freetds-1.3.3-1.el7.x86_64
---> Package freetds-devel.x86_64 0:1.3.3-1.el7 will be installed
--> Running transaction check
---> Package freetds-libs.x86_64 0:1.3.3-1.el7 will be installed
---> Package unixODBC.x86_64 0:2.3.1-14.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================================================
Installing:
freetds x86_64 1.3.3-1.el7 epel 291 k
freetds-devel x86_64 1.3.3-1.el7 epel 52 k
Installing for dependencies:
freetds-libs x86_64 1.3.3-1.el7 epel 374 k
unixODBC x86_64 2.3.1-14.el7 base 413 k
Transaction Summary
=============================================================================================================================================================================================================================================================================
Install 2 Packages (+2 Dependent packages)
Total download size: 1.1 M
Installed size: 3.5 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/epel/packages/freetds-1.3.3-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Public key for freetds-1.3.3-1.el7.x86_64.rpm is not installed
(1/4): freetds-1.3.3-1.el7.x86_64.rpm | 291 kB 00:00:00
(2/4): freetds-devel-1.3.3-1.el7.x86_64.rpm | 52 kB 00:00:00
(3/4): freetds-libs-1.3.3-1.el7.x86_64.rpm | 374 kB 00:00:00
(4/4): unixODBC-2.3.1-14.el7.x86_64.rpm | 413 kB 00:00:00
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 863 kB/s | 1.1 MB 00:00:01
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Importing GPG key 0x352C64E5:
Userid : "Fedora EPEL (7) <epel@fedoraproject.org>"
Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5
Package : epel-release-7-11.noarch (@extras)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : unixODBC-2.3.1-14.el7.x86_64 1/4
Installing : freetds-1.3.3-1.el7.x86_64 2/4
Installing : freetds-libs-1.3.3-1.el7.x86_64 3/4
Installing : freetds-devel-1.3.3-1.el7.x86_64 4/4
Verifying : freetds-libs-1.3.3-1.el7.x86_64 1/4
Verifying : freetds-1.3.3-1.el7.x86_64 2/4
Verifying : freetds-devel-1.3.3-1.el7.x86_64 3/4
Verifying : unixODBC-2.3.1-14.el7.x86_64 4/4
Installed:
freetds.x86_64 0:1.3.3-1.el7 freetds-devel.x86_64 0:1.3.3-1.el7
Dependency Installed:
freetds-libs.x86_64 0:1.3.3-1.el7 unixODBC.x86_64 0:2.3.1-14.el7
Complete!
[root@pg14 ~]#
源码安装tds_fdw
官网下载源码安装包tds_fdw-2.0.3.tar.gz，上传到/home/postgres目录下
#切换postgres用户
su - postgres
#查看tds_fdw-2.0.3.tar.gz源码包
ll
#解压tds_fdw-2.0.3.tar.gz
tar xvf tds_fdw-2.0.3.tar.gz
#进入源码安装目录
cd tds_fdw-2.0.3/
#编译安装
make USE_PGXS=1
make USE_PGXS=1 install
操作如下
[root@pg14 postgres]# su - postgres
Last login: Mon Sep 25 08:50:49 CST 2023 on pts/1
[postgres@pg14 ~]$ ll
total 28852
drwxrwxr-x 4 postgres postgres 4096 Sep 25 08:51 mysql_fdw-REL-2_9_1
-rw-r--r-- 1 postgres postgres 168896 Sep 25 00:15 mysql_fdw-REL-2_9_1.tar.gz
drwxrwxr-x 5 postgres postgres 4096 Sep 24 22:04 oracle_fdw-ORACLE_FDW_2_6_0
-rw-r--r-- 1 postgres postgres 135949 Sep 24 21:05 oracle_fdw-ORACLE_FDW_2_6_0.tar.gz
drwx------ 3 postgres postgres 18 Sep 24 22:12 oradiag_postgres
drwxrwxr-x 6 postgres postgres 4096 Sep 24 16:46 postgresql-14.9
-rw-r--r-- 1 postgres postgres 29143610 Aug 21 10:06 postgresql-14.9.tar.gz
-rw-r--r-- 1 postgres postgres 72527 Sep 25 10:39 tds_fdw-2.0.3.tar.gz
[postgres@pg14 ~]$ tar xvf tds_fdw-2.0.3.tar.gz
tds_fdw-2.0.3/
tds_fdw-2.0.3/.gitattributes
tds_fdw-2.0.3/.github/
tds_fdw-2.0.3/.github/ISSUE_TEMPLATE.md
tds_fdw-2.0.3/.gitignore
tds_fdw-2.0.3/ForeignSchemaImporting.md
tds_fdw-2.0.3/ForeignServerCreation.md
tds_fdw-2.0.3/ForeignTableCreation.md
tds_fdw-2.0.3/InstallAlpine.md
tds_fdw-2.0.3/InstallDebian.md
tds_fdw-2.0.3/InstallOSX.md
tds_fdw-2.0.3/InstallRHELandClones.md
tds_fdw-2.0.3/InstallUbuntu.md
tds_fdw-2.0.3/InstallopenSUSE.md
tds_fdw-2.0.3/LICENSE
tds_fdw-2.0.3/META.json
tds_fdw-2.0.3/Makefile
tds_fdw-2.0.3/README.md
tds_fdw-2.0.3/UserMappingCreation.md
tds_fdw-2.0.3/Variables.md
tds_fdw-2.0.3/include/
tds_fdw-2.0.3/include/deparse.h
tds_fdw-2.0.3/include/options.h
tds_fdw-2.0.3/include/tds_fdw.h
tds_fdw-2.0.3/include/visibility.h
tds_fdw-2.0.3/logo/
tds_fdw-2.0.3/logo/tds_fdw.svg
tds_fdw-2.0.3/sql/
tds_fdw-2.0.3/sql/tds_fdw.sql
tds_fdw-2.0.3/src/
tds_fdw-2.0.3/src/deparse.c
tds_fdw-2.0.3/src/options.c
tds_fdw-2.0.3/src/tds_fdw.c
tds_fdw-2.0.3/tds_fdw.control
tds_fdw-2.0.3/tests/
tds_fdw-2.0.3/tests/.gitignore
tds_fdw-2.0.3/tests/README.md
tds_fdw-2.0.3/tests/lib/
tds_fdw-2.0.3/tests/lib/__init__.py
tds_fdw-2.0.3/tests/lib/messages.py
tds_fdw-2.0.3/tests/lib/tests.py
tds_fdw-2.0.3/tests/mssql-tests.py
tds_fdw-2.0.3/tests/postgresql-tests.py
tds_fdw-2.0.3/tests/tests/
tds_fdw-2.0.3/tests/tests/mssql/
tds_fdw-2.0.3/tests/tests/mssql/000_create_schema.json
tds_fdw-2.0.3/tests/tests/mssql/000_create_schema.sql
tds_fdw-2.0.3/tests/tests/mssql/001_create_tinyint_min_table.json
tds_fdw-2.0.3/tests/tests/mssql/001_create_tinyint_min_table.sql
tds_fdw-2.0.3/tests/tests/mssql/002_create_tinyint_max_table.json
tds_fdw-2.0.3/tests/tests/mssql/002_create_tinyint_max_table.sql
tds_fdw-2.0.3/tests/tests/mssql/003_create_smallint_min_table.json
tds_fdw-2.0.3/tests/tests/mssql/003_create_smallint_min_table.sql
tds_fdw-2.0.3/tests/tests/mssql/004_create_smallint_max_table.json
tds_fdw-2.0.3/tests/tests/mssql/004_create_smallint_max_table.sql
tds_fdw-2.0.3/tests/tests/mssql/005_create_int_min_table.json
tds_fdw-2.0.3/tests/tests/mssql/005_create_int_min_table.sql
tds_fdw-2.0.3/tests/tests/mssql/006_create_int_max_table.json
tds_fdw-2.0.3/tests/tests/mssql/006_create_int_max_table.sql
tds_fdw-2.0.3/tests/tests/mssql/007_create_bigint_min_table.json
tds_fdw-2.0.3/tests/tests/mssql/007_create_bigint_min_table.sql
tds_fdw-2.0.3/tests/tests/mssql/008_create_bigint_max_table.json
tds_fdw-2.0.3/tests/tests/mssql/008_create_bigint_max_table.sql
tds_fdw-2.0.3/tests/tests/mssql/009_create_decimal_table.json
tds_fdw-2.0.3/tests/tests/mssql/009_create_decimal_table.sql
tds_fdw-2.0.3/tests/tests/mssql/010_create_float4_table.json
tds_fdw-2.0.3/tests/tests/mssql/010_create_float4_table.sql
tds_fdw-2.0.3/tests/tests/mssql/011_create_float8_table.json
tds_fdw-2.0.3/tests/tests/mssql/011_create_float8_table.sql
tds_fdw-2.0.3/tests/tests/mssql/012_create_date_table.json
tds_fdw-2.0.3/tests/tests/mssql/012_create_date_table.sql
tds_fdw-2.0.3/tests/tests/mssql/013_create_time_table.json
tds_fdw-2.0.3/tests/tests/mssql/013_create_time_table.sql
tds_fdw-2.0.3/tests/tests/mssql/014_create_datetime_table.json
tds_fdw-2.0.3/tests/tests/mssql/014_create_datetime_table.sql
tds_fdw-2.0.3/tests/tests/mssql/015_create_datetime2_table.json
tds_fdw-2.0.3/tests/tests/mssql/015_create_datetime2_table.sql
tds_fdw-2.0.3/tests/tests/mssql/016_create_datetimeoffset_table.json
tds_fdw-2.0.3/tests/tests/mssql/016_create_datetimeoffset_table.sql
tds_fdw-2.0.3/tests/tests/mssql/017_create_char_table.json
tds_fdw-2.0.3/tests/tests/mssql/017_create_char_table.sql
tds_fdw-2.0.3/tests/tests/mssql/018_create_varchar_table.json
tds_fdw-2.0.3/tests/tests/mssql/018_create_varchar_table.sql
tds_fdw-2.0.3/tests/tests/mssql/019_create_varcharmax_table.json
tds_fdw-2.0.3/tests/tests/mssql/019_create_varcharmax_table.sql
tds_fdw-2.0.3/tests/tests/mssql/020_create_binary4_table.json
tds_fdw-2.0.3/tests/tests/mssql/020_create_binary4_table.sql
tds_fdw-2.0.3/tests/tests/mssql/021_create_varbinary4_table.json
tds_fdw-2.0.3/tests/tests/mssql/021_create_varbinary4_table.sql
tds_fdw-2.0.3/tests/tests/mssql/022_create_varbinarymax_table.json
tds_fdw-2.0.3/tests/tests/mssql/022_create_varbinarymax_table.sql
tds_fdw-2.0.3/tests/tests/mssql/023_create_null_datetime_table.json
tds_fdw-2.0.3/tests/tests/mssql/023_create_null_datetime_table.sql
tds_fdw-2.0.3/tests/tests/mssql/024_create_null_datetime2_table.json
tds_fdw-2.0.3/tests/tests/mssql/024_create_null_datetime2_table.sql
tds_fdw-2.0.3/tests/tests/mssql/025_create_match_column_table.json
tds_fdw-2.0.3/tests/tests/mssql/025_create_match_column_table.sql
tds_fdw-2.0.3/tests/tests/mssql/026_create_column_name_table.json
tds_fdw-2.0.3/tests/tests/mssql/026_create_column_name_table.sql
tds_fdw-2.0.3/tests/tests/mssql/027_create_query_option_table.json
tds_fdw-2.0.3/tests/tests/mssql/027_create_query_option_table.sql
tds_fdw-2.0.3/tests/tests/mssql/028_create_view_simple_prerequisites.json
tds_fdw-2.0.3/tests/tests/mssql/028_create_view_simple_prerequisites.sql
tds_fdw-2.0.3/tests/tests/mssql/029_create_view_simple.json
tds_fdw-2.0.3/tests/tests/mssql/029_create_view_simple.sql
tds_fdw-2.0.3/tests/tests/postgresql/
tds_fdw-2.0.3/tests/tests/postgresql/000_create_schema.json
tds_fdw-2.0.3/tests/tests/postgresql/000_create_schema.sql
tds_fdw-2.0.3/tests/tests/postgresql/001_create_server.json
tds_fdw-2.0.3/tests/tests/postgresql/001_create_server.sql
tds_fdw-2.0.3/tests/tests/postgresql/002_create_user_mapping.json
tds_fdw-2.0.3/tests/tests/postgresql/002_create_user_mapping.sql
tds_fdw-2.0.3/tests/tests/postgresql/003_import_schema.json
tds_fdw-2.0.3/tests/tests/postgresql/003_import_schema.sql
tds_fdw-2.0.3/tests/tests/postgresql/004_tinyintmin.json
tds_fdw-2.0.3/tests/tests/postgresql/004_tinyintmin.sql
tds_fdw-2.0.3/tests/tests/postgresql/005_tinyintmax.json
tds_fdw-2.0.3/tests/tests/postgresql/005_tinyintmax.sql
tds_fdw-2.0.3/tests/tests/postgresql/006_smallintmin.json
tds_fdw-2.0.3/tests/tests/postgresql/006_smallintmin.sql
tds_fdw-2.0.3/tests/tests/postgresql/007_smallintmax.json
tds_fdw-2.0.3/tests/tests/postgresql/007_smallintmax.sql
tds_fdw-2.0.3/tests/tests/postgresql/008_intmin.json
tds_fdw-2.0.3/tests/tests/postgresql/008_intmin.sql
tds_fdw-2.0.3/tests/tests/postgresql/009_intmax.json
tds_fdw-2.0.3/tests/tests/postgresql/009_intmax.sql
tds_fdw-2.0.3/tests/tests/postgresql/010_bigintmin.json
tds_fdw-2.0.3/tests/tests/postgresql/010_bigintmin.sql
tds_fdw-2.0.3/tests/tests/postgresql/011_bigintmax.json
tds_fdw-2.0.3/tests/tests/postgresql/011_bigintmax.sql
tds_fdw-2.0.3/tests/tests/postgresql/012_decimal.json
tds_fdw-2.0.3/tests/tests/postgresql/012_decimal.sql
tds_fdw-2.0.3/tests/tests/postgresql/013_float4.json
tds_fdw-2.0.3/tests/tests/postgresql/013_float4.sql
tds_fdw-2.0.3/tests/tests/postgresql/014_float8.json
tds_fdw-2.0.3/tests/tests/postgresql/014_float8.sql
tds_fdw-2.0.3/tests/tests/postgresql/015_date.json
tds_fdw-2.0.3/tests/tests/postgresql/015_date.sql
tds_fdw-2.0.3/tests/tests/postgresql/016_time.json
tds_fdw-2.0.3/tests/tests/postgresql/016_time.sql
tds_fdw-2.0.3/tests/tests/postgresql/017_datetime.json
tds_fdw-2.0.3/tests/tests/postgresql/017_datetime.sql
tds_fdw-2.0.3/tests/tests/postgresql/018_datetime2.json
tds_fdw-2.0.3/tests/tests/postgresql/018_datetime2.sql
tds_fdw-2.0.3/tests/tests/postgresql/019_datetimeoffset.json
tds_fdw-2.0.3/tests/tests/postgresql/019_datetimeoffset.sql
tds_fdw-2.0.3/tests/tests/postgresql/020_char.json
tds_fdw-2.0.3/tests/tests/postgresql/020_char.sql
tds_fdw-2.0.3/tests/tests/postgresql/021_varchar.json
tds_fdw-2.0.3/tests/tests/postgresql/021_varchar.sql
tds_fdw-2.0.3/tests/tests/postgresql/022_varcharmax.json
tds_fdw-2.0.3/tests/tests/postgresql/022_varcharmax.sql
tds_fdw-2.0.3/tests/tests/postgresql/023_binary4.json
tds_fdw-2.0.3/tests/tests/postgresql/023_binary4.sql
tds_fdw-2.0.3/tests/tests/postgresql/024_varbinary4.json
tds_fdw-2.0.3/tests/tests/postgresql/024_varbinary4.sql
tds_fdw-2.0.3/tests/tests/postgresql/025_varbinarymax.json
tds_fdw-2.0.3/tests/tests/postgresql/025_varbinarymax.sql
tds_fdw-2.0.3/tests/tests/postgresql/026_null_datetime.json
tds_fdw-2.0.3/tests/tests/postgresql/026_null_datetime.sql
tds_fdw-2.0.3/tests/tests/postgresql/027_null_datetime2.json
tds_fdw-2.0.3/tests/tests/postgresql/027_null_datetime2.sql
tds_fdw-2.0.3/tests/tests/postgresql/028_column_match_enabled.json
tds_fdw-2.0.3/tests/tests/postgresql/028_column_match_enabled.sql
tds_fdw-2.0.3/tests/tests/postgresql/029_column_match_disabled.json
tds_fdw-2.0.3/tests/tests/postgresql/029_column_match_disabled.sql
tds_fdw-2.0.3/tests/tests/postgresql/030_column_name.json
tds_fdw-2.0.3/tests/tests/postgresql/030_column_name.sql
tds_fdw-2.0.3/tests/tests/postgresql/031_query_option_column_match_enabled.json
tds_fdw-2.0.3/tests/tests/postgresql/031_query_option_column_match_enabled.sql
tds_fdw-2.0.3/tests/tests/postgresql/032_query_option_column_match_disabled.json
tds_fdw-2.0.3/tests/tests/postgresql/032_query_option_column_match_disabled.sql
tds_fdw-2.0.3/tests/tests/postgresql/033_view_simple.json
tds_fdw-2.0.3/tests/tests/postgresql/033_view_simple.sql
tds_fdw-2.0.3/tests/tests/postgresql/034_explain.json
tds_fdw-2.0.3/tests/tests/postgresql/034_explain.sql
tds_fdw-2.0.3/tests/tests/postgresql/035_rescan.json
tds_fdw-2.0.3/tests/tests/postgresql/035_rescan.sql
tds_fdw-2.0.3/tests/validate-test-json
[postgres@pg14 ~]$ cd tds_fdw-2.0.3/
[postgres@pg14 tds_fdw-2.0.3]$ make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I./include/ -fvisibility=hidden -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o src/tds_fdw.o src/tds_fdw.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I./include/ -fvisibility=hidden -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o src/options.o src/options.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I./include/ -fvisibility=hidden -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o src/deparse.o src/deparse.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o tds_fdw.so src/tds_fdw.o src/options.o src/deparse.o -L/opt/pg14/lib -Wl,--as-needed -Wl,-rpath,'/opt/pg14/lib',--enable-new-dtags -lsybdb
cp sql/tds_fdw.sql sql/tds_fdw--2.0.3.sql
cp README.md README.tds_fdw.md
[postgres@pg14 tds_fdw-2.0.3]$ make USE_PGXS=1 install
/bin/mkdir -p '/opt/pg14/lib/postgresql'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/mkdir -p '/opt/pg14/share/doc/postgresql/extension'
/bin/install -c -m 755 tds_fdw.so '/opt/pg14/lib/postgresql/tds_fdw.so'
/bin/install -c -m 644 .//tds_fdw.control '/opt/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//sql/tds_fdw--2.0.3.sql '/opt/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//README.tds_fdw.md '/opt/pg14/share/doc/postgresql/extension/'
[postgres@pg14 tds_fdw-2.0.3]$
源端创建扩展访问目标数据库
#psql登录源端数据库
psql
#创建tds_fdw插件
CREATE EXTENSION tds_fdw;
#创建mssql_svr外部服务器，输入ip，端口，数据库名，tds_version版本
CREATE SERVER mssql_svr FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.17.8', port '1433', database 'master',tds_version '7.4');
#创建用户映射本地用户postgres，输入SQL SERVER目标端用户和密码
CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username 'sa', password 'Passw0rd');
#创建外部表注意表结构和目标端一致性，输入目标端表名和执行方式
CREATE FOREIGN TABLE mssql_table (id int,info text) SERVER mssql_svr OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');
#查询记录，注意当前版本插件不支持远程DML操作
select * from mssql_table;
操作如下
[postgres@pg14 tds_fdw-2.0.3]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE EXTENSION tds_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER mssql_svr FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.17.8', port '1433', database 'master',tds_version '7.4');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username 'sa', password 'Passw0rd');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE mssql_table (id int,info text) SERVER mssql_svr OPTIONS (table_name 'dbo.mytable', row_estimate_method 'showplan_all');
CREATE FOREIGN TABLE
postgres=# select * from mssql_table;
id | info
----+---------------
1 | hello tds_fdw
(1 row)
postgres=# insert into mssql_table values(2,'add by postgres tds_fdw');
ERROR: cannot insert into foreign table "mssql_table"
postgres=#
mongo_fdw
使用mongo_fdw插件访问mongodb文档数据库。详见官网
快速准备目标MongoDB环境
#拉取官方镜像
docker pull mongo
#创建MongoDB容器mongo-test，映射宿主机端口27017，设置auth用户名密码验证
docker run -itd --name mongo-test -p 27017:27017 mongo --auth
#查看容器运行状态
docker ps
#进入mongo-test容器，构建测试数据
docker exec -it mongo-test mongosh
#切换到admin数据库
use admin
#创建auth用户名和密码
db.createUser({user:"root",pwd:"123456",roles:[{role:"root",db:"admin"}]});
#添加auth用户
db.auth('root','123456');
#查看集合
show collections
#创建mongotab测试表，插入测试数据
db.mongotab.insertMany([{name:"hi sunying"},{name:"hello mongo_fdw"}])
#查询测试数据
db.mongotab.find()
操作如下
[root@docker ~]# docker pull mongo
Using default tag: latest
latest: Pulling from library/mongo
7b1a6ab2e44d: Pull complete
90eb44ebc60b: Pull complete
5085b59f2efb: Pull complete
c7499923d022: Pull complete
019496b6c44a: Pull complete
c0df4f407f69: Pull complete
351daa315b6c: Pull complete
a233e6240acc: Pull complete
a3f57d6be64f: Pull complete
dd1b5b345323: Pull complete
Digest: sha256:5be752bc5f2ac4182252d0f15d74df080923aba39700905cb26d9f70f39e9702
Status: Downloaded newer image for mongo:latest
docker.io/library/mongo:latest
[root@docker ~]# docker run -itd --name mongo-test -p 27017:27017 mongo --auth
306ab12aabe132ae60af07905e1d27ad410f358753bc6c220daaae90701f60fe
[root@docker ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
306ab12aabe1 mongo "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:27017->27017/tcp mongo-test
0cfc3508a253 mcr.microsoft.com/mssql/server:2017-latest "/opt/mssql/bin/nonr…" 2 hours ago Up 2 hours 0.0.0.0:1433->1433/tcp sqlserver2017
352a58baea12 mysql:latest "docker-entrypoint.s…" 13 hours ago Up 13 hours 0.0.0.0:3306->3306/tcp, 33060/tcp mysqltest
c369ae97f198 oracleinanutshell/oracle-xe-11g "/bin/sh -c '/usr/sb…" 16 hours ago Up 16 hours 22/tcp, 8080/tcp, 0.0.0.0:1521->1521/tcp oracle11g
c69fec9ed79d postgres "docker-entrypoint.s…" 16 hours ago Up 16 hours 0.0.0.0:5432->5432/tcp postgrestest
[root@docker ~]# docker exec -it mongo-test mongosh
Current Mongosh Log ID: 6511083177a5fd783a50a95a
Connecting to: mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000
Using MongoDB: 5.0.5
Using Mongosh: 1.1.6
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.
test> use admin
switched to db admin
admin> db.createUser(
... {
..... user:"root",
..... pwd:"123456",
..... roles:[{role:"root",db:"admin"}]
..... }
... );
{ ok: 1 }
admin> db.auth('root','123456');
{ ok: 1 }
admin> show collections
system.users
system.version
admin> show dbs
admin 135 kB
config 12.3 kB
local 41 kB
admin> db.mongotab.insertMany([{name:"hi sunying"},{name:"hello mongo_fdw"}])
{
acknowledged: true,
insertedIds: {
'0': ObjectId("65110d0e1ff890f2fcd48c27"),
'1': ObjectId("65110d0e1ff890f2fcd48c28")
}
}
admin> db.mongotab.find()
[
{ _id: ObjectId("65110d0e1ff890f2fcd48c27"), name: 'hi sunying' },
{
_id: ObjectId("65110d0e1ff890f2fcd48c28"),
name: 'hello mongo_fdw'
}
]
admin> show collections
mongotab
system.users
system.version
admin>
源码安装mongo_fdw
下载已经包含mongo-c和json-c库依赖的源码包mongo_fdw.tar.gz，在百度网盘提取码: dnyq，将文件上传到/home/postgres目录下
#切换到postgres用户
su - postgres
#解压文件mongo_fdw.tar.gz
tar -zxf mongo_fdw.tar.gz
#进入已经包含mongo-c和json-c库依赖的源码目录
cd mongo_fdw-REL-5_5_1/
#查看mongo-c-driver和json-c目录是否已经存在
ll
#设置环境变量PKG_CONFIG_PATH和LD_LIBRARY_PATH，确保libmongoc-1.0.so和libbson-1.0.so库文件被找到
export PKG_CONFIG_PATH=/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libmongoc/src:/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libbson/src
export LD_LIBRARY_PATH=/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libmongoc:/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libbson:/opt/pgsql/lib
#由于已经源码编译过了。直接安装即可
make USE_PGXS=1
make USE_PGXS=1 install
#切换到root用户下
su - root
#加载mongodb动态链接库libmongoc-1.0.so和libbson-1.0.so库文件
echo "/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libmongoc" > /etc/ld.so.conf.d/mongo.conf
echo "/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libbson" >> /etc/ld.so.conf.d/mongo.conf
ldconfig
操作如下
[root@pg14 ~]# su - postgres
Last login: Mon Sep 25 14:55:01 CST 2023 on pts/0
[postgres@pg14 ~]$ tar -zxf mongo_fdw.tar.gz
[postgres@pg14 ~]$ cd mongo_fdw-REL-5_5_1/
[postgres@pg14 mongo_fdw-REL-5_5_1]$ ll
total 696
-rwxrwxr-x 1 postgres postgres 3330 Jul 14 18:00 autogen.sh
-rw-r--r-- 1 postgres postgres 45 Sep 21 22:09 config.h
-rw-rw-r-- 1 postgres postgres 6447 Jul 14 18:00 connection.c
-rw-rw-r-- 1 postgres postgres 5680 Sep 21 22:14 connection.o
-rw-rw-r-- 1 postgres postgres 2788 Jul 14 18:00 CONTRIBUTING.md
drwxrwxr-x 2 postgres postgres 86 Jul 14 18:00 data
-rw-rw-r-- 1 postgres postgres 18348 Jul 14 18:00 deparse.c
-rw-rw-r-- 1 postgres postgres 11984 Sep 21 22:14 deparse.o
drwxrwxr-x 2 postgres postgres 4096 Jul 14 18:00 expected
drwxrwxr-x 11 postgres postgres 4096 Sep 21 22:14 json-c
-rw-rw-r-- 1 postgres postgres 7632 Jul 14 18:00 LICENSE
-rw-rw-r-- 1 postgres postgres 1722 Sep 21 22:09 Makefile
-rw-rw-r-- 1 postgres postgres 1804 Jul 14 18:00 Makefile.legacy
-rw-rw-r-- 1 postgres postgres 1722 Jul 14 18:00 Makefile.meta
drwxr-xr-x 7 postgres postgres 4096 Sep 21 22:09 mongo-c-driver
-rwxrwxr-x 1 postgres postgres 1334 Jul 14 18:00 mongodb_init.sh
-rw-rw-r-- 1 postgres postgres 157 Jul 14 18:00 mongo_fdw--1.0--1.1.sql
-rw-rw-r-- 1 postgres postgres 593 Jul 14 18:00 mongo_fdw--1.0.sql
-rw-rw-r-- 1 postgres postgres 709 Jul 14 18:00 mongo_fdw--1.1.sql
-rw-rw-r-- 1 postgres postgres 140275 Jul 14 18:00 mongo_fdw.c
-rw-rw-r-- 1 postgres postgres 274 Jul 14 18:00 mongo_fdw.control
-rw-rw-r-- 1 postgres postgres 18259 Jul 14 18:00 mongo_fdw.h
-rw-rw-r-- 1 postgres postgres 55976 Sep 21 22:14 mongo_fdw.o
-rwxrwxr-x 1 postgres postgres 173704 Sep 21 22:14 mongo_fdw.so
-rw-rw-r-- 1 postgres postgres 58735 Jul 14 18:00 mongo_query.c
-rw-rw-r-- 1 postgres postgres 4433 Jul 14 18:00 mongo_query.h
-rw-rw-r-- 1 postgres postgres 23032 Sep 21 22:14 mongo_query.o
-rw-rw-r-- 1 postgres postgres 9784 Jul 14 18:00 mongo_wrapper.c
-rw-rw-r-- 1 postgres postgres 4038 Jul 14 18:00 mongo_wrapper.h
-rw-rw-r-- 1 postgres postgres 16946 Jul 14 18:00 mongo_wrapper_meta.c
-rw-rw-r-- 1 postgres postgres 23688 Sep 21 22:14 mongo_wrapper_meta.o
-rw-rw-r-- 1 postgres postgres 8686 Jul 14 18:00 option.c
-rw-rw-r-- 1 postgres postgres 10504 Sep 21 22:14 option.o
-rw-rw-r-- 1 postgres postgres 17782 Jul 14 18:00 README.md
drwxrwxr-x 2 postgres postgres 206 Jul 14 18:00 sql
[postgres@pg14 mongo_fdw-REL-5_5_1]$ export PKG_CONFIG_PATH=/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libmongoc/src:/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libbson/src
[postgres@pg14 mongo_fdw-REL-5_5_1]$ export LD_LIBRARY_PATH=/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libmongoc:/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libbson:/opt/pgsql/lib
[postgres@pg14 mongo_fdw-REL-5_5_1]$ make USE_PGXS=1
make: Nothing to be done for `all'.
[postgres@pg14 mongo_fdw-REL-5_5_1]$ make USE_PGXS=1 install
/bin/mkdir -p '/opt/pg14/lib/postgresql'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/install -c -m 755 mongo_fdw.so '/opt/pg14/lib/postgresql/mongo_fdw.so'
/bin/install -c -m 644 .//mongo_fdw.control '/opt/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//mongo_fdw--1.0.sql .//mongo_fdw--1.1.sql .//mongo_fdw--1.0--1.1.sql '/opt/pg14/share/postgresql/extension/'
[postgres@pg14 mongo_fdw-REL-5_5_1]$ su - root
Password:
Last login: Mon Sep 25 15:24:28 CST 2023 on pts/0
[root@pg14 ~]# echo "/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libmongoc" > /etc/ld.so.conf.d/mongo.conf
[root@pg14 ~]# echo "/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libbson" >> /etc/ld.so.conf.d/mongo.conf
[root@pg14 ~]# ll /etc/ld.so.conf.d/mongo.conf
-rw-r--r-- 1 root root 126 Sep 25 15:23 /etc/ld.so.conf.d/mongo.conf
[root@pg14 ~]# cat /etc/ld.so.conf.d/mongo.conf
/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libmongoc
/home/postgres/mongo_fdw-REL-5_5_1/mongo-c-driver/src/libbson
[root@pg14 ~]# ldconfig
[root@pg14 ~]#
源端创建扩展访问目标数据库
#psql登录源端数据库
psql
#创建mongo_fdw插件
CREATE EXTENSION mongo_fdw;
#创建mongo_server外部服务器，输入ip，端口
CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address '192.168.17.8', port '27017');
#创建用户映射本地用户postgres，输入MongoDB目标端用户和密码
CREATE USER MAPPING FOR postgres SERVER mongo_server OPTIONS (username 'root', password '123456');
#创建外部表注意表结构和目标端一致性，输入目标端数据库名和表名
CREATE FOREIGN TABLE mongo_table(_id name,name text) SERVER mongo_server OPTIONS (database 'admin', collection 'mongotab');
#查询文档集合
select * from mongo_table;
#插入集合
insert into mongo_table(name) values('add by mongo_fdw');
#查询最终集合
select * from mongo_table;
操作如下
[root@pg14 ~]# su - postgres
Last login: Mon Sep 25 15:24:14 CST 2023 on pts/0
[postgres@pg14 ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE EXTENSION mongo_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address '192.168.17.8', port '27017');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER mongo_server OPTIONS (username 'root', password '123456');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE mongo_table(_id name,name text) SERVER mongo_server OPTIONS (database 'admin', collection 'mongotab');
CREATE FOREIGN TABLE
postgres=# select * from mongo_table;
_id | name
--------------------------+-----------------
65110d0e1ff890f2fcd48c27 | hi sunying
65110d0e1ff890f2fcd48c28 | hello mongo_fdw
(2 rows)
postgres=# insert into mongo_table(name) values('add by mongo_fdw');
INSERT 0 1
postgres=# select * from mongo_table;
_id | name
--------------------------+------------------
65110d0e1ff890f2fcd48c27 | hi sunying
65110d0e1ff890f2fcd48c28 | hello mongo_fdw
651138c407659276cb1424a2 | add by mongo_fdw
(3 rows)
postgres=#
redis_fdw
使用redis_fdw插件访问Redis数据库。详见官网
快速准备目标Redis环境
docker pull redis
docker run --name myredis -p 6379:6379 -d redis --requirepass "123456"
docker ps
docker exec -it myredis bash
AUTH 123456
CONFIG GET requirepass
MSET name "hello redis_fdw" database "redis db" ipaddress "192.168.17.8"
操作如下
[root@docker ~]# docker pull redis
Using default tag: latest
latest: Pulling from library/redis
a2abf6c4d29d: Already exists
c7a4e4382001: Pull complete
4044b9ba67c9: Pull complete
c8388a79482f: Pull complete
413c8bb60be2: Pull complete
1abfd3011519: Pull complete
Digest: sha256:db485f2e245b5b3329fdc7eff4eb00f913e09d8feb9ca720788059fdc2ed8339
Status: Downloaded newer image for redis:latest
docker.io/library/redis:latest
[root@docker ~]# docker run --name myredis -p 6379:6379 -d redis --requirepass "123456"
f45f8a0586b5eca630b36e8f285b77826438f8041a9837a3c185bc4df32007ea
[root@docker ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
f45f8a0586b5 redis "docker-entrypoint.s…" 9 seconds ago Up 9 seconds 0.0.0.0:6379->6379/tcp myredis
306ab12aabe1 mongo "docker-entrypoint.s…" 5 hours ago Up 5 hours 0.0.0.0:27017->27017/tcp mongo-test
0cfc3508a253 mcr.microsoft.com/mssql/server:2017-latest "/opt/mssql/bin/nonr…" 7 hours ago Up 7 hours 0.0.0.0:1433->1433/tcp sqlserver2017
352a58baea12 mysql:latest "docker-entrypoint.s…" 18 hours ago Up 18 hours 0.0.0.0:3306->3306/tcp, 33060/tcp mysqltest
c369ae97f198 oracleinanutshell/oracle-xe-11g "/bin/sh -c '/usr/sb…" 21 hours ago Up 21 hours 22/tcp, 8080/tcp, 0.0.0.0:1521->1521/tcp oracle11g
c69fec9ed79d postgres "docker-entrypoint.s…" 21 hours ago Up 21 hours 0.0.0.0:5432->5432/tcp postgrestest
[root@docker ~]# docker exec -it myredis bash
root@f45f8a0586b5:/data# redis-cli
127.0.0.1:6379> AUTH 123456
OK
127.0.0.1:6379> CONFIG GET requirepass
1) "requirepass"
2) "123456"
127.0.0.1:6379> MSET name "hello redis_fdw" database "redis db" ipaddress "192.168.17.8"
OK
127.0.0.1:6379>
源码安装redis_fdw
redis_fdw依赖于hiredis包，首先操作系统使用YUM安装依赖。下载PostgreSQL14版本对应REL_14_STABLE源码包redis_fdw-REL_14_STABLE.tar.gz，将文件上传到/home/postgres目录下
#安装EPEL扩展Yum源，安装更多软件包
yum install -y epel-release
#安装hiredis-devel软件包
yum install -y hiredis-devel
#切换到postgres用户
su - postgres
#解压源码安装包
tar -xzf redis_fdw-REL_14_STABLE.tar.gz
#查看源码安装目录
ll
#进入源码安装目录
cd redis_fdw-REL_14_STABLE/
#源码编译安装
make USE_PGXS=1
make USE_PGXS=1 install
操作如下
[root@pg14 ~]# yum install -y epel-release
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:7-11 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================================================
Installing:
epel-release noarch 7-11 extras 15 k
Transaction Summary
=============================================================================================================================================================================================================================================================================
Install 1 Package
Total download size: 15 k
Installed size: 24 k
Downloading packages:
epel-release-7-11.noarch.rpm | 15 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : epel-release-7-11.noarch 1/1
Verifying : epel-release-7-11.noarch 1/1
Installed:
epel-release.noarch 0:7-11
Complete!
[root@pg14 ~]# yum install -y hiredis-devel
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
epel/x86_64/metalink | 7.9 kB 00:00:00
* epel: mirrors.tuna.tsinghua.edu.cn
epel | 4.7 kB 00:00:00
(1/3): epel/x86_64/group_gz | 99 kB 00:00:00
(2/3): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(3/3): epel/x86_64/primary_db | 7.0 MB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package hiredis-devel.x86_64 0:0.12.1-2.el7 will be installed
--> Processing Dependency: hiredis(x86-64) = 0.12.1-2.el7 for package: hiredis-devel-0.12.1-2.el7.x86_64
--> Processing Dependency: libhiredis.so.0.12()(64bit) for package: hiredis-devel-0.12.1-2.el7.x86_64
--> Running transaction check
---> Package hiredis.x86_64 0:0.12.1-2.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================================================
Installing:
hiredis-devel x86_64 0.12.1-2.el7 epel 22 k
Installing for dependencies:
hiredis x86_64 0.12.1-2.el7 epel 30 k
Transaction Summary
=============================================================================================================================================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 51 k
Installed size: 113 k
Downloading packages:
warning: /var/cache/yum/x86_64/7/epel/packages/hiredis-0.12.1-2.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Public key for hiredis-0.12.1-2.el7.x86_64.rpm is not installed
(1/2): hiredis-0.12.1-2.el7.x86_64.rpm | 30 kB 00:00:00
(2/2): hiredis-devel-0.12.1-2.el7.x86_64.rpm | 22 kB 00:00:00
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 172 kB/s | 51 kB 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Importing GPG key 0x352C64E5:
Userid : "Fedora EPEL (7) <epel@fedoraproject.org>"
Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5
Package : epel-release-7-11.noarch (@extras)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : hiredis-0.12.1-2.el7.x86_64 1/2
Installing : hiredis-devel-0.12.1-2.el7.x86_64 2/2
Verifying : hiredis-devel-0.12.1-2.el7.x86_64 1/2
Verifying : hiredis-0.12.1-2.el7.x86_64 2/2
Installed:
hiredis-devel.x86_64 0:0.12.1-2.el7
Dependency Installed:
hiredis.x86_64 0:0.12.1-2.el7
Complete!
[root@pg14 ~]# su - postgres
Last login: Mon Sep 25 17:47:41 CST 2023 on pts/0
[postgres@pg14 ~]$ tar -xzf redis_fdw-REL_14_STABLE.tar.gz
[postgres@pg14 ~]$ ll
total 55900
drwxrwxr-x 7 postgres postgres 4096 Sep 21 22:14 mongo_fdw-REL-5_5_1
-rw-rw-r-- 1 postgres postgres 28021996 Sep 25 14:18 mongo_fdw.tar.gz
drwxrwxr-x 6 postgres postgres 4096 Sep 25 14:50 postgresql-14.9
-rw-r--r-- 1 postgres postgres 29143610 Sep 25 14:48 postgresql-14.9.tar.gz
drwxrwxr-x 3 postgres postgres 200 Aug 17 00:18 redis_fdw-master
-rw-rw-r-- 1 postgres postgres 31728 Sep 25 17:44 redis_fdw-master.zip
drwxrwxr-x 3 postgres postgres 200 Jun 29 2021 redis_fdw-REL_14_STABLE
-rw-r--r-- 1 postgres postgres 27932 Sep 25 17:49 redis_fdw-REL_14_STABLE.tar.gz
[postgres@pg14 ~]$ cd redis_fdw-REL_14_STABLE/
[postgres@pg14 redis_fdw-REL_14_STABLE]$ make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o redis_fdw.o redis_fdw.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o redis_fdw.so redis_fdw.o -L/opt/pg14/lib -Wl,--as-needed -Wl,-rpath,'/opt/pg14/lib',--enable-new-dtags -lhiredis
[postgres@pg14 redis_fdw-REL_14_STABLE]$ make USE_PGXS=1 install
/bin/mkdir -p '/opt/pg14/lib/postgresql'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/install -c -m 755 redis_fdw.so '/opt/pg14/lib/postgresql/redis_fdw.so'
/bin/install -c -m 644 .//redis_fdw.control '/opt/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//redis_fdw--1.0.sql '/opt/pg14/share/postgresql/extension/'
[postgres@pg14 redis_fdw-REL_14_STABLE]$
源端创建扩展访问目标数据库
#psql登录源端数据库
psql
#创建redis_fdw插件
CREATE EXTENSION redis_fdw;
#创建redis_server外部服务器，输入ip，端口
CREATE SERVER redis_server FOREIGN DATA WRAPPER redis_fdw OPTIONS (address '192.168.17.8', port '6379');
#创建用户映射本地用户postgres，输入Redis目标端AUTH密码
CREATE USER MAPPING FOR postgres SERVER redis_server OPTIONS (password '123456');
#创建外部表注意表结构键值数据库特殊性，输入目标端数据库名
CREATE FOREIGN TABLE redis_db0 (key text, val text) SERVER redis_server OPTIONS (database '0');
#查询键值
select * from redis_db0;
#插入一对键值
insert into redis_db0 values('football team','Arsenal FC');
#查询最终键值
select * from redis_db0;
操作如下
[postgres@pg14 ~]$ psql
psql (14.9)
Type "help" for help.
postgres=# CREATE EXTENSION redis_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER redis_server FOREIGN DATA WRAPPER redis_fdw OPTIONS (address '192.168.17.8', port '6379');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER redis_server OPTIONS (password '123456');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE redis_db0 (key text, val text) SERVER redis_server OPTIONS (database '0');
CREATE FOREIGN TABLE
postgres=# select * from redis_db0;
key | val
-----------+-----------------
ipaddress | 192.168.17.8
database | redis db
name | hello redis_fdw
(3 rows)
postgres=# insert into redis_db0 values('football team','Arsenal FC');
INSERT 0 1
postgres=# select * from redis_db0;
key | val
---------------+-----------------
ipaddress | 192.168.17.8
database | redis db
name | hello redis_fdw
football team | Arsenal FC
(4 rows)
postgres=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
