你不知道的PostgreSQL數據庫安裝及實現跨庫查詢PG和Oracle

PG作為近幾年最火熱的關係型數據，已經被很多開發者所使用，尤其是5G網絡普及完畢後，IOT和AI的應用場景下，數據的讀寫速度要求非常高，MYSQL已經開始不能滿足高強度的數據吞吐（這裡有爭議，這裡只是說單臺服務的情況，使用負載的不在此討論範圍），ORACLE數據由於其不是開源項目，導致很多開發者在進行技術選型的時候，不會優先選擇ORACLE作為第一選擇，這時候PG作為一款可以媲美ORACLE的開源關係型數據庫的優勢就很明顯了。下面我來介紹一下在CentOS下安裝PG和使用PG進行跨庫查詢PG和ORACLE。

環境準備

一臺CentOS7的Lunix服務器。

PostgreSQL 10.10, 64-bit，官網 https://www.postgresql.org/進行下載（window或者lunix都可以直接下載，我們這裡使用yum進行鏡像下載）

PG安裝步驟如下：

1.安裝rpm文件

<code>yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm/<code>

2.安裝PG客戶端

<code>yum install postgresql10/<code>

3.安裝服務端

<code>yum install postgresql10-server/<code>

4.初始化PG

<code>/usr/pgsql-10/bin/postgresql-10-setup initdb/<code>

5、設置自動啟動並且啟動postgresql服務

<code>systemctl enable postgresql-10
systemctl start postgresql-10/<code>

下面進行PG數據庫初始化，創建用戶和數據庫，步驟如下：

1.切換用戶到postgres（PG安裝成功後會自動創建postgres，初始化沒有密碼）

<code>su - postgres/<code>

2.登陸postgresql數據庫

3.創建用戶和數據庫

<code>create user admin with password 'admin';            // 創建用戶
create database iot_admin owner admin;                 // 創建數據庫
grant all privileges on database iot_admin to admin;   // 授權/<code>

4、退出psql

<code>\q/<code>

PG在安裝之後，默認不開啟遠程訪問的，需要手動開啟遠程訪問，步驟如下

1、修改/var/lib/pgsql/10/data/postgresql.conf文件，取消 listen_addresses 的註釋，將參數值改為“*”

2、修改/var/lib/pgsql/10/data/pg_hba.conf文件，增加下圖紅框部分內容

3.切換root用戶後，重啟PG服

<code>su - root
systemctl restart postgresql-10.service/<code>

<code>/<code>

4.使用Navicat連接PG

到這裡PG已經正常安裝到CentOS上，並且已經正常連接了。

下面我們來介紹PG最為強大的fdw功能

什麼是fdw，我們來說下業務場景，比如我想做獲取其他服務器上的的數據，一般情況我們的獲取方式都是使用webservice或者rest-api接口的方式進行獲取，有了fdw之後，我們可以直接將服務器上數據庫的表映射（可以理解為一種特殊的外部數據接口），這樣我們就可以直接讀取我們自己庫上映射的表進行數據構建，這裡可能會有人認為接口也可以實現這種功能，我想說的是，接口總時有一定的延遲性的，而且映射過來的表我們是可以使用join來進行數據關聯的，這也極大的方便了我們的開發和應用。

下面我來介紹使用Postgreslq_fdw來實現跨庫查詢Postgresql和使用Oracle_fdw來查詢Oracle

使用Postgresql_fdw實現跨庫查詢Postgreql庫(使用Navicat進行操作)

根據上文，我們已經創建了一個數據庫iot_admin

1.我們新建一個數據庫iot_all

2.這裡我們在iot_admin中新建一張表base_user,

然後將base_user的數據同步到iot_all中。

iot_admin的建表SQL語句如下：

CREATE TABLE "public"."base_user" (

"id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,

"username" varchar(255) COLLATE "pg_catalog"."default",

"password" varchar(255) COLLATE "pg_catalog"."default",

"name" varchar(255) COLLATE "pg_catalog"."default",

"birthday" varchar(255) COLLATE "pg_catalog"."default",

"address" varchar(255) COLLATE "pg_catalog"."default",

"mobile_phone" varchar(255) COLLATE "pg_catalog"."default",

"tel_phone" varchar(255) COLLATE "pg_catalog"."default",

"email" varchar(255) COLLATE "pg_catalog"."default",

"sex" varchar(1) COLLATE "pg_catalog"."default",

"status" varchar(1) COLLATE "pg_catalog"."default",

"description" varchar(255) COLLATE "pg_catalog"."default",

"crt_time" timestamp(6),

"crt_user" varchar(255) COLLATE "pg_catalog"."default",

"crt_name" varchar(255) COLLATE "pg_catalog"."default",

"crt_host" varchar(255) COLLATE "pg_catalog"."default",

"upd_time" timestamp(6),

"upd_user" varchar(255) COLLATE "pg_catalog"."default",

"upd_name" varchar(255) COLLATE "pg_catalog"."default",

"upd_host" varchar(255) COLLATE "pg_catalog"."default",

"attr1" varchar(255) COLLATE "pg_catalog"."default",

"attr2" varchar(255) COLLATE "pg_catalog"."default",

"attr3" varchar(255) COLLATE "pg_catalog"."default",

"attr4" varchar(255) COLLATE "pg_catalog"."default",

"attr5" varchar(255) COLLATE "pg_catalog"."default",

"attr6" varchar(255) COLLATE "pg_catalog"."default",

"attr7" varchar(255) COLLATE "pg_catalog"."default",

"attr8" varchar(255) COLLATE "pg_catalog"."default",

"delete_flag" int2 NOT NULL DEFAULT 0,

"user_type" int2 NOT NULL DEFAULT 0,

"office_id" varchar(64) COLLATE "pg_catalog"."default",

"company_id" varchar(64) COLLATE "pg_catalog"."default",

"is_frist_login" varchar(10) COLLATE "pg_catalog"."default"

)

;

ALTER TABLE "public"."base_user" ADD CONSTRAINT "pk_base_user" PRIMARY KEY ("id");

3.在iot_all中安裝FDW插件

SQL如下：

CREATE EXTENSION postgres_fdw;

4.在iot_all中創建iot_admin的遠程服務

CREATE SERVER admin_server

FOREIGN DATA WRAPPER postgres_fdw

OPTIONS (host '10.100.2.156', port '5432', dbname 'iot_admin');

數據庫ip 端口 數據庫名稱

5.驗證服務創建成功

SELECT * from pg_foreign_server;

6.創建user mapping 相當於用戶數據映射，將服務的權限給用戶，沒有給權限的用戶無法使用相應的服務，這裡使用初始化用戶posgres

CREATE USER MAPPING FOR postgres

SERVER admin_server

OPTIONS (user 'postgres', password 'xgit_123');

用戶名 密碼

7.在iot_all中創建外部表，映射iot_admin中的base_user;

CREATE FOREIGN TABLE "public"."base_user" (

"id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,

"username" varchar(255) COLLATE "pg_catalog"."default",

"password" varchar(255) COLLATE "pg_catalog"."default",

"name" varchar(255) COLLATE "pg_catalog"."default",

"birthday" varchar(255) COLLATE "pg_catalog"."default",

"address" varchar(255) COLLATE "pg_catalog"."default",

"mobile_phone" varchar(255) COLLATE "pg_catalog"."default",

"tel_phone" varchar(255) COLLATE "pg_catalog"."default",

"email" varchar(255) COLLATE "pg_catalog"."default",

"sex" varchar(1) COLLATE "pg_catalog"."default",

"status" varchar(1) COLLATE "pg_catalog"."default",

"description" varchar(255) COLLATE "pg_catalog"."default",

"crt_time" timestamp(6),

"crt_user" varchar(255) COLLATE "pg_catalog"."default",

"crt_name" varchar(255) COLLATE "pg_catalog"."default",

"crt_host" varchar(255) COLLATE "pg_catalog"."default",

"upd_time" timestamp(6),

"upd_user" varchar(255) COLLATE "pg_catalog"."default",

"upd_name" varchar(255) COLLATE "pg_catalog"."default",

"upd_host" varchar(255) COLLATE "pg_catalog"."default",

"attr1" varchar(255) COLLATE "pg_catalog"."default",

"attr2" varchar(255) COLLATE "pg_catalog"."default",

"attr3" varchar(255) COLLATE "pg_catalog"."default",

"attr4" varchar(255) COLLATE "pg_catalog"."default",

"attr5" varchar(255) COLLATE "pg_catalog"."default",

"attr6" varchar(255) COLLATE "pg_catalog"."default",

"attr7" varchar(255) COLLATE "pg_catalog"."default",

"attr8" varchar(255) COLLATE "pg_catalog"."default",

"delete_flag" int2 NOT NULL DEFAULT 0,

"user_type" int2 NOT NULL DEFAULT 0,

"office_id" varchar(64) COLLATE "pg_catalog"."default",

"company_id" varchar(64) COLLATE "pg_catalog"."default",

"is_frist_login" varchar(10) COLLATE "pg_catalog"."default"

) Server admin_server options (table_name 'base_user');

服務名 映射的表名稱

6.驗證同步

查看iot_admin中base_user的數據

查看iot_all中的base_user的數據

這裡需要重點說明的一個問題是，外部表映射僅僅可以對錶的增刪改查進行操作，也就是對錶數據進行操作，但是不能對錶結構進行操作，也就是all裡面字段的改變不影響admin的改變，同時ALL中的字段是可以少於admin中，但是不能多於admin中，或者存在admin中不存在的字段

使用Oracle_fdw實現跨庫查詢Oracle庫

1.安裝Oracle_fdw

官方地址：http://pgxn.org/dist/oracle_fdw/ ，選擇一個版本下載。

2.安裝Oracle Instant Client

從oralce官網下載 'Basic' and 'SDK'，假如下載後文件所在位置在/opt/oracle中。

<code>cd /opt/oracle
unzip instantclient-basic-linux.x64-12.2.0.1.0.zip
unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip
mv instantclient_12_2 instantclient
cd instantclient
ln -s libclntsh.so.12.1 libclntsh.so
vi /etc/profile
export ORACLE_HOME=/opt/oracle/instantclient
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
source /etc/profile/<code>

3.啟動用戶變量

<code>source /home/postgres/.bashrc/<code>

4.安裝oracle_fdw

<code>unzip oracle_fdw-1.5.0.zip 
cd oracle_fdw-1.5.0
make
make install
/<code>

下面是創建FDW的擴展

同樣是iot_all數據庫

1.安裝oracle_fdw擴展

<code>create extension oracle_fdw;/<code>

2.創建server

<code>create server oradb_test foreign data wrapper oracle_fdw options(dbserver '192.168.0.215:1521/ora_db');
/<code>

這裡和PG互相跨庫查詢略有不同

4.服務授權

<code>grant usage on foreign server oradb_test to postgres;/<code>

5.創建服務映射給用戶

<code>create user mapping for postgres server oradb_test  options(user 'MG_APP',password 'QWERasdf');/<code>

下面進行表的創建和做映射，可以參照PG實現跨庫，步驟相同。

結語

PG作為一款關係型數據庫，可以實現如此多的功能擴展，可以是一款全棧型數據庫也不為過。