利用FDW進行ORACLE到Postgresql的數據遷移

IT那活兒發布于2023-01-11 13:19 / 2799人閱讀

點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！

一

FDW插件

隨著開源數據庫技術的發展和去“O”工作的推進，越來越多企業生產系統選擇使用Postgresql數據庫。Pgsql采用多進程結構，其存儲過程、函數的支持好于mysql。個人認為pgsql是oracle的最佳替代產品。

但是pgsql作為一款開源數據庫，其計算能力和穩定性還是無法與Oracle相提并論，所有當企業要使用PG替代oracle時，還是得從數據庫的拆分和架構上下功夫。使用PG去“O”的核心步驟之一是數據遷移，遷移方案有多種，如ETL、ORACLE_FDW等，本文將重點介紹ORACLE_FDW的遷移過程。FDW是PG的一個開源插件，可以通過在github上下載編譯安裝，以實現類似ORACLEDBLINK的功能。

FDW優點是配置簡單，使用方便，缺點是不能實現增量遷移，所以只適合數據量較小或者停機時間較長的情況。下面開始本次分享。

二

FDW插件安裝

2.1 PG軟件安裝

一般選用源碼進行編譯安裝，此處不對安裝過程進行詳細描述（相信大家都是老司機哈）。

2.2 下載精簡版Oracle客戶端instantclient-*.zip

包含basic、sdk和sqlplus三個文件并解壓。

2.3 配置好用戶postgres的環境變量

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/postgres/instantclient_11_2:/usr/local/pgsql/lib
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/pgsql/bin
ORACLE_HOME=/home/postgres/instantclient_11_2
PGDATA=/data
export PATH ORACLE_HOME LD_LIBRARY_PATH PGDATA

2.4 插件編譯安裝

$ make
$ make install

2.5 驗證是否安裝成功

三

ORACLE_FDW配置和使用

3.1 創建extension

create extension oracle_fdw;
postgres=# dx
                        List of installed extensions
    Name | Version |   Schema | Description
------------+---------+------------+----------------------------------------
 oracle_fdw | 1.1     | public | foreign data wrapper for Oracle access -->說明創建成功
 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

3.2 創建server,使pg通過創建外部表連接oracle數據庫

CREATE SERVER spclora FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 10.26.5*.**:1521/spcltbk);
--創建server spclora 連接到oracle數據庫
GRANT USAGE ON FOREIGN SERVER spclora TO userinfo_prod;
--將server授權給用戶userinfo_prod
c userinfodb userinfo_prod
--使用userinfo_prod切換到userinfodb
CREATE USER MAPPING FOR userinfo_prod SERVER spclora OPTIONS (user pgsync, password oracle);
--創建到oracle的映射，pgsync/oracle是oracle數據庫的用戶名和密碼

3.3 創建外部表

create FOREIGN table t_***_userinfo_fdw
(
  phone*** VARCHAR(24) not null,
  ser***tus int4 not null,
  createtime timestamp with time zone default now() not null,
  ups***time  timestamp with time zone default now() not null,
  op**a*or      int4 not null,
  che**me timestamp with time zone,
  lo**id       int4,
  modu**code VARCHAR(20),
  modi***time  timestamp with time zone default now() not null
)SERVER spclora OPTIONS (schema SP***DP, table T_USERINFO_MV,prefetch 10240);

至此，通過訪問外部表，即可訪問ORACLE數據庫對應表，上例建表語句中的options表示oracle的表信息SP***DP.T_USERINFO_MV。pretetch，表示從oracle預取的行數，默認是200,建議配置到最大10240,根據我們的實戰經驗，配置該參數后，數據遷移的速度至少提高50%。

四

數據遷移

在PG側創建表，然后使用insertinto pg_table select * from foreign_table即可實現數據遷移。

遷移案例

某客戶項目一重要系統原數據庫使用Oracle11.2.0.4,架構為HA架構，數據量約1TB，進行去“O”改造。考慮到去O后的效率以及數據增長等問題，架構上做了數據“對癥下藥”。數據庫層面進行了拆分，日志數據存放到ES上，分發數據存放到Mongodb上，核心交易數據存放到PG中，日志數據無需遷移，分發數據可以在業務重建之后，從其他業務系統請求重新分發，也無需遷移，只需要遷移核心交易數據到PG中，需遷移的數據量大大減少。為提升PG數據庫效率，PG架構使用一主兩從加pgpool的讀寫分離架構。

根據業務的特點以及確保遷移影響降到最低，遷移方案采用數據按省份進行割接的輪動方式。每個省份的數據遷移，只有15分鐘的停機時間。停機時間短，我們考慮過使用OGG進行增量遷移，使用過OGG的同學都是，這玩意就像有錢人家的大小姐，性子琢磨不透，純“根據心情”，經常出現莫名其妙的岔子。再加上異構環境下，估計使用OGG，岔子更多，所以我們選擇ORACLE_FDW作為遷移方案。

為了在規定的時間內完成數據遷移，我們采取了以下手段：

由于在原表中以省份ID區分各省數據，列數據選擇性較低，以省份過濾查詢時，部分省份數據無法使用索引，全表掃描會導致遷移的時間延長；而且本次遷移，業務上也進行了更改，所以遷移數據時只需要原表的部分列。鑒于此情況，我們在源端使用了物化視圖對原表數據進行裁剪，PG外部表與物化視圖進行對應，這樣遷移時可以減少無效的讀取IO，縮短遷移時間。
修改外部表的prefetch參數為10240。
將多個表數據遷移編寫腳本實現手工并行。

按照本篇介紹及手段方法，即可完成數據量1T左右的遷移工作，希望對你的工作有所啟示和幫助。

本文作者：劉運彬（上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

GPU云服務器云服務器 oracle數據庫遷移到mysql oracle數據遷移到db2 PPAS oracle數據遷移到db2 oracle fdw

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129217.html

新書推薦 |《PostgreSQL實戰》出版(提供樣章下載)

摘要：作者譚峰張文升出版日期年月頁數頁定價元本書特色中國開源軟件推進聯盟分會特聘專家撰寫，國內多位開源數據庫專家鼎力推薦。張文升中國開源軟件推進聯盟分會核心成員之一。很高興《PostgreSQL實戰》一書終于出版，本書大體上系統總結了筆者 PostgreSQL DBA 職業生涯的經驗總結，本書的另一位作者張文升擁有豐富的PostgreSQL運維經驗，目前就職于探探科技任首席PostgreS...

Martin91 2019-07-25 12:36 評論0 收藏0
PostgreSQL對接SequoiaDB

摘要：是一款開源的數據庫，支持標準，用戶可以通過驅動連接進行應用程序開發。本文就針對如何擴展功能，實現對接進行介紹。直接在中修改配置文件，只能在當前中生效，重新登錄需要重新設置。 PostgreSQL是一款開源的SQL數據庫，支持標準SQL，用戶可以通過JDBC驅動連接PostgreSQL進行應用程序開發。用戶通過擴展PostgreSQL功能，讓開發者可以使用SQL語句訪問SequoiaDB...

TZLLOG 2019-07-25 12:30 評論0 收藏0
阿里云如何打破Oracle遷移上云的壁壘

摘要：摘要第九屆中國數據庫技術大會，阿里云數據庫產品專家蕭少聰帶來以阿里云如何打破遷移上云的壁壘為題的演講。于是，阿里云給出了上面的解決方案。摘要： 2018第九屆中國數據庫技術大會，阿里云數據庫產品專家蕭少聰帶來以阿里云如何打破Oracle遷移上云的壁壘為題的演講。Oracle是指數據庫管理系統，面對Oracle遷移上云的壁壘，阿里云如何能夠打破它呢？本文提出了Oracle 到云數據庫P...

chavesgu 2019-06-25 18:39 評論0 收藏0