CentOS 7下PostgreSQL的安装与配置以及Oracle_fdw的安装和使用

        本文主要记录如何在服务器上安装并调试PostgreSQL、安装并使用Oracle_fdw，实现在PostgreSQL中获取Oracle的数据。本文中所使用服务器版本为CentOS 7，所使用到的安装包均可以从官网下载。

1 安装并调试PostgreSQL

        （1）下载安装包：

        从Index of /pub/source/下载所需版本的PostgreSQL的安装包，本文所使用的版本为13.4。

        （2）安装准备：

        #创建用户postgres

        useradd postgres

        #安装gcc相关依赖包

        yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel  python-devel gcc-c++ openssl-devel cmake gcc* readline-devel

        #创建名为postgres文件夹并赋予用户postgres所有权

        mkdir /opt/postgres

        chown -R postgres:postgres /opt/postgres

        #添加环境变量

        #PGDATA即PostgreSQL的data路径

        vi /etc/profile

        export PATH=/opt/postgres/bin:$PATH

        export PGHOME=/opt/postgres

        export PGDATA=/opt/postgres/data/

        export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib/

        export PATH=$PGHOME/bin:$PATH:$HOME/bin

        （3）安装和初始化数据库：

        #cd到安装包所在路径下并解压，本文存放安装包的位置为/usr/local

        #解压完成后进入文件夹并配置

        tar -zxvf postgresql-13.4.tar.gz

        cd  postgresql-13.4

        ./configure --prefix=/opt/postgres/ --with-python --with-libxml --with-libxslt  

        #编译并安装

        make

        make install

        #初始化数据库

        su postgres

        /opt/postgres/bin/initdb -D $PGDATA -E UTF8

        （4）修改数据库配置允许其他服务器连接：

        #修改postgresql.conf，文件位于data路径下，也可以使用find命令查找文件

        #找到listen_addresses=’localhost’，将注释去掉，localhost修改为 *

        #可根据需要修改端口号

        vi postgresql.conf

        listen_addresses = '*'

        port = 5432

        #修改pg_hba.conf，根据所需网段设置放行的ip规则

        #本文为设置host  all  all  0.0.0.0/0  trust

        vi pg_hba.conf

        #IPv4 local connections:

        host  all  all  0.0.0.0/0  trust

        （5）启动PostgreSQL：

        /opt/postgres/bin/pg_ctl -l /opt/postgres/server.log start 

        （6）PostgreSQL常用命令：

        #在xshell中进入数据库

        psql

        #退出psql

        \q

        #查看数据库

        \l

        #查看用户

        \du

        #数据库的启动、停止、重启在环境变量配置好以后

        #可以直接通过以下命令实现

        pg_ctl start

        pg_ctl stop

        （7）遇到的问题和部分解决方案：

        当调试过程出现问题，可以查看/opt/postgres下的server.log，也可以使用systemctl status postgresql、journalctl -xe等命令查看。

2 安装并配置Oracle_fdw

        （1）下载安装包：

        从oracle_fdw: A Foreign Data Wrapper for Oracle / PostgreSQL Extension Network下载Oracle_fdw.zip，注意，需要根据PostgreSQL版本选择Oracle_fdw版本，如果版本对不上，有可能会发生错误导致安装失败。本文使用Oracle_fdw-2.3.1-2020-09-29版本，该版本支持PostgreSQL-13.4。

        （2）下载Oracle database instant client客户端：

        从Instant Client for Linux x86-64 (64-bit) | Oracle 中国上下载，包含三个zip：instantclient-basic-linux.x64-12.2.0.1.0.zip、instantclient-sdk-linux.x64-12.2.0.1.0.zip、instantclient-sqlplus-linux.x64-12.2.0.1.0.zip。

        （3）安装Oracle database instant client客户端：

        #创建Oracle客户端目录

        mkdir /opt/oracle

        #解压三个zip，放到/opt/oracle下

        unzip -o instantclient-basic-linux.x64-12.2.0.1.0.zip

        unzip -o instantclient-sdk-linux.x64-12.2.0.1.0.zip

        unzip -o instantclient-sqlplus-linux.x64-12.2.0.1.0.zip

        #在/opt/oracle下面创建配置文件tnsname.ora

        cd /opt/oracle

        vi tnsname.ora

        #添加内容

        #注意修改主机IP、端口和数据库实例名

        MYDB =

             (DESCRIPTION =

                (ADDRESS_LIST =

                   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.255.13)(PORT=1521))

                  )

               (CONNECT_DATA=(SERVICE_NAME=orcl)(SERVER = orcl))

              ）

        #配置Oracle客户端环境变量

        vi /etc/profile

        #添加内容

        export ORACLE_HOME=/opt/oracle/instantclient_12_2;

        export SQLPATH=/opt/oracle;

        export TNS_ADMIN=/opt/oracle;

        export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH;

        export PATH=$PATH:$ORACLE_HOME;

        #重新加载配置

        source /etc/profile

        #创建Oracle用户并授权

        groupadd oinstall

        useradd -g oinstall oracle

        password oracle

        chown -R oracle:oinstall /opt/oracle

        chmod -R 775 /opt/oracle

        （4）解压并安装Oracle_fdw：

        #进入Oracle_fdw解压后的目录找到Makefile文件

        unzip -o oracle_fdw-2.3.1.zip

        #将其中PG_CONFIG的值修改为$PG_HOME/bin/pg_config

        #然后编译安装

        cd oracle_fdw-2.3.1

        make

        make install

        （5）常见错误及解决方案：

        #如果报错make:G_HOME/bin/pg_config:命令未找到

        yum install -y postgresql-devel

        #然后找到pg_config的路径（可以使用find命令）

        #并将上一步中PG_CONFIG的值修改为pg_config的绝对路径

        #如：PG_CONFIG=/opt/postgres/bin/pg_config

        #然后重新make和make install

        #如果报错cannot find -lclntsh

        #则进入Oracle客户端instantclient_12_2目录建立软链接

        ln -s libclntsh.so.12.1 libclntsh.so

        #然后make和make install

3 使用Oracle_fdw获取数据

        这部分均为SQL语句，可以使用命令行进入数据库完成，也可以使用navaicat远程连接数据库完成。

        （1）安装插件：

        create extension oracle_fdw;

        （2）创建外部服务器：

        - - 本文dbserver写为 //IP:端口/数据库实例，也可以通过配置tnsname.ora的方式实现

        - - oracle_test为自定义外部服务器名称

        create server oracle_test foreign data wrapper oracle_fdw options(dbserver '//*.*.*.*:1521/orcl')

        （3）创建mapping

        - - 其中postgres是数据库中角色名，oracle_test为外部服务器名称

        - - user和password为被连接的Oracle数据库登录名和密码

        create user mapping for postgres server oracle_test options(user 'user', password 'password');

        （4）创建外部表获取数据

        - - test_user_role为自定义外部表，表结构建议和Oracle中表结构相同

        - - oracle_test为外部服务器名称

        - - schema即Oracle中用户名，table为Oracle中该用户下的表名

        - - schema和table必须大写

        create foreign table test_user_role(userid char(20), roleid char(2)) server oracle_test options(schema 'TEST', table 'USER_ROLE');

        （5）常见错误及解决方案：

        #在create extension oracle_fdw时，如果控制台报错***so找不到，就去oracle_fdw-2.3.1文件夹下创建软链接到$PGHOME/lib或直接复制到$PGHOME/lib目录下。软链接写法如下：

        ln -s /opt/oracle/instantclient_12_2/libclntshcore.so.12.1 $PGHOME/lib/libclntshcore.so.12.1

        #出现一些其它的问题优先查看环境变量配置是否正确