暂无图片
暂无图片
7
暂无图片
暂无图片
暂无图片

PostgreSQL外部数据封装器(FDW)

原创 孙莹 2023-09-26
938

PostgreSQL Foreign Data Wrapper

postgresqlfdw.png

外部数据封装器

外部数据封装器(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_fdwpostgresql_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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论