外部数据封装器
外部数据封装器（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
米乐app官网下载 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
米乐app官网下载 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) "
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) "
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=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。