Oracle資料庫遷移PostgreSQL資料庫問題及解決
目錄
- 如何計劃遷移資料庫(現狀及問題分析)
- 統計系統表及表功能
- 解耦公共表
- 建立資料庫
- 遷移表結構
- 匯入表資料
- 改SQL語法
- 保證資料時效性和完整性
- 其他（優化SQL等）
1.如何計劃遷移資料庫
將資料庫從Oracle遷移至PostgreSQL資料庫，需要考慮的有很多， 但是有一點是不變的，或者說是目的：要保證資料庫遷移后，系統功能能夠正常使用，或對業務邏輯盡可能少的修改（修改業務邏輯可能會出現意想不到的連鎖問題）， 那么，就需要想辦法將原有的Oracle資料庫整體結構，盡可能的在PostgreSQL上還原，這樣才能保證不必大量調整業務邏輯， 明確目的之后，可以具體分析系統現狀了： 該系統所使用的是Oracle資料庫，但Orale資料庫是一個總庫，其他系統使用的表也在該庫中，使用屬主區分表，所以該系統除了自身使用的表之外，還使用了一部分公共表， 這樣一來，在遷移資料庫之前，除了統計系統本身的表外，還要進行公共表解耦， 其次，如果要遷移到PostgreSQL資料庫，那么建表陳述句就必須按照PostgreSQL的語法寫，即調整建表腳本， 除了建表腳本，原Oracle資料庫還有大量的序列、索引、存盤程序等，這些也需要移植過去， 然后就是如何保證資料的完整性和時效性？ 總結以上的問題要點： 1.遷移資料庫之前，統計系統本身的表清單和表功能清單等； 2.遷移資料庫之前，進行公共表解耦； 3.調整建表腳本； 4.移植序列、索引、存盤程序等； 5.匯入原有的表資料； 6.調整系統原本的SQL陳述句； 7.生產環境保證資料的完整性和時效性； 8.其他問題及解決；
2.統計系統表及表功能
統計系統的使用的表和表功能，主要是為了明確需要遷移的表，以及表所負責的功能（方便資料遷移時，確定資料匯入的先后順序）， 這一步很簡單，但是比較耗費時間，需要耐心和細心， 最好能夠一起統計表使用到的序列和索引等，方便之后的遷移操作，
3.解耦公共表
之所以需要解耦公共表，原因有二： 1.如果把使用到的公共表（表屬主并不是該遷移的系統）遷移到PostgreSQL資料庫，那么其他使用該表的系統就無法正常使用； 2.公司規定，不是自己系統的表，一般是只進行讀操作，不會進行寫入或修改操作，更不會遷移其他系統的表； 根據以上原因，最終采用的方案是： 由表屬主的系統，提供給我們這個遷移系統所需要的介面，我們由原來的直接查詢其他系統的表，轉為呼叫其他系統提供的介面來查詢需要的資料； 這么做的好處就是，將原來的公共表與本系統進行解耦，同時當該系統進行資料庫遷移后（其他系統仍是Oracle資料庫），還能夠查詢到需要的資料，不會影響其他系統運行， 需要注意的是： 在進行表解耦之前，需要先統計該系統使用的外部表，包括本系統使用外部表的哪些關聯欄位、外部表在本系統中的作用等資訊， 然后根據這些資訊，與其他系統負責人溝通并開發介面、聯調介面等，
4.建立資料庫
在各環境的資料庫正式遷移之前，可以先在本地的PostgreSQL資料庫，建立一個新的系統資料庫用于本地系統功能和業務測驗， 當本地測驗的差不多了，就可以把腳本提供給DBA，交由DBA去創建各環境的PostgreSQL資料庫， 至于怎么在本地安裝PostgreSQL資料庫，這里就不贅述了，5.遷移表結構（包括序列、索引等）
遷移表結構涉及的東西比較多，可以參考以下的遷移步驟： 1.撰寫新的CREATE TABLE腳本（按照PostgreSQL的語法）; 2.撰寫新的INDEX、SEQUENCE等腳本（索引、序列的各種名稱、引數都和Oracle的一致，盡量只調整語法差異）；5.1 CREATE TABLE腳本
撰寫CREATE TABLE腳本的時候，除了需要注意建表的語法之外，還有Oracle和PostgreSQL兩種資料庫不同欄位型別的替換，
例如：
varchar型別和character varying型別
Oracle中，定長的char型別最大長度是2000，變長的varchar型別長度最大是2000、varchar2型別最大長度是4000，
| 欄位型別 | 長度是否可變 | 最大長度 | 補充 |
|---|---|---|---|
| char | 否 | 2000 | 存盤長度為指定的長度，即char(20)存'abc'，占20位元組；比varchar占空間多，但效率稍高； |
| varchar | 是 | 2000 | 存盤長度為實際長度，即varchar(20)存'abc',占3位元組；比char省空間，但效率稍低；存中文2位元組，英文1位元組；對空串不處理； |
| varchar2 | 是 | 4000 | Oracle特有，存盤特點和varchar一致，但varchar2存中英文都是2位元組；最大長度限制為varcahr的2倍；會將空串處理為null； |
PostgreSQL中，定長的char型別和變長的varchar型別的最大長度都是1G（10485760），變長的text型別則沒有長度限制，
| 欄位型別 | 長度是否可變 | 最大長度 | 補充 |
|---|---|---|---|
| text | 是 | 無限制 | 性能和varchar幾乎相同，但存盤結構不同； |
| character,char | 否 | 1G（10485760） | 定長；默認長度為1G；不足部分會補空白； |
| character varying,varchar | 是 | 1G（10485760） | 變長；默認長度為1；占用位元組為實際存盤長度； |
在PostgreSQL中，text、char和varcahr的性能是沒有區別的，大多數情況下使用text和varchar比較好； character是全稱，char是別名（簡稱）；character varying是全稱，varchar是別名（簡稱）; Oracle中較長的欄位，可以用PostgreSQL中的character varying替代，長度是沒問題的
number型別和numeric型別 Oracle中，使用number型別來保存對精度要求高的數值，比如貨幣、金額等 對精度要求不高或沒有要求的盡量不要用numeric型別，因為它的效率很低| 欄位型別 | 范圍 | 說明 | 例子 |
|---|---|---|---|
| number(precision,scale) | percision:1到38;scale：-78到124 | 不指定percision,默認為38； | number(8,3) |
PostgreSQL中,與number型別對應的是numeric型別
| 欄位型別 | 范圍 | 說明 | 例子 |
|---|---|---|---|
| numeric(precision,scale) | percision:最大131072個數字;scale:最大16383個數字 | 變數；percision為總精度，scale為小數位位數；若小數位超過指定位數，則進行四舍五入；兩引數同時省略時，可存任何不超上限精度的數字；可存特殊值NaN（NaN與任何值都不等，包括自身）; | numeric(8,3) |
| decimal(precision,scale) | 同上 | 同上（只是語法支持，因為其底層就是numeric型別） | decimal(8,3)或decimal(8,-2) |
| 欄位型別 | 格式 | 說明 |
|---|---|---|
| date | 標準：DD-MON-YY | 存盤的時間格式可定義，如“yyyy-mm-dd”或“yyyy-mm-dd hh24:mi:ss” |
PostgreSQL中,與date型別對應的是timestamp型別
| 欄位型別 | 格式 | 說明 |
|---|---|---|
| timestamp | 存時間日期 | |
| time | 存時間 | |
| date | 存日期 | |
| timestampz | 存timestamp和時區 | |
| interval | 存一段時間 |
其他資料型別不再一一介紹，可以通過查閱PostgreSQl輕松學來獲取相關知識，
建表陳述句例子：
-- 刪減了一部分關鍵欄位，只保留了基本的欄位，同表一起創建的還有該表使用的索引
CREATE TABLE cp_opr.user_role
(
id varchar(20) COLLATE pg."default" NOT NULL,
role_id varchar(50) COLLATE pg."default" NOT NULL,
department_chinese_name varchar(255) COLLATE pg."default" NOT NULL,
user_name varchar(30) COLLATE pg."default" NOT NULL,
department_code varchar(30) COLLATE pg."default",
user_id varchar(30) COLLATE pg."default" NOT NULL,
state varchar(2) COLLATE pg."default" NOT NULL,
flag varchar(2) COLLATE pg."default" DEFAULT '1'::varchar,
manager varchar(30) COLLATE pg."default",
create_by varchar(100) COLLATE pg."default" DEFAULT 'complaint'::varchar,
create_date timestamp(0) without time zone NOT NULL DEFAULT now(),
update_by varchar(100) COLLATE pg."default",
update_date timestamp(0) without time zone,
CONSTRAINT user_role PRIMARY KEY (id)
);
COMMENT ON TABLE cp_opr.user_role
IS '用戶角色表';
COMMENT ON COLUMN cp_opr.user_role.id
IS 'ID';
COMMENT ON COLUMN cp_opr.user_role.role_id
IS '角色ID';
COMMENT ON COLUMN cp_opr.user_role.department_chinese_name
IS '機構名稱';
COMMENT ON COLUMN cp_opr.user_role.user_name
IS '用戶名稱';
COMMENT ON COLUMN cp_opr.user_role.department_code
IS '機構';
COMMENT ON COLUMN cp_opr.user_role.user_id
IS '用戶ID';
COMMENT ON COLUMN cp_opr.user_role.state
IS '狀態';
COMMENT ON COLUMN cp_opr.user_role.flag
IS '有效標志';
COMMENT ON COLUMN cp_opr.user_role.manager
IS '角色的管理員';
COMMENT ON COLUMN cp_opr.user_role.create_by
IS '創建人';
COMMENT ON COLUMN cp_opr.user_role.create_date
IS '創建日期';
COMMENT ON COLUMN cp_opr.user_role.update_by
IS '修改人';
COMMENT ON COLUMN cp_opr.user_role.update_date
IS '修改日期';
-- 不能創建與表同名的索引
-- CREATE INDEX
CREATE INDEX index_role_dep_code ON cp_opr.user_role USING btree(department_code) ;
CREATE INDEX index_role_id ON cp_opr.user_role USING btree(role_id) ;
CREATE INDEX index_role_user_id ON cp_opr.user_role USING btree(user_id) ;
5.2 移植SEQUENCE序列
Oracle和PostgreSQl的序列創建方法大致上一樣，但是會有一些細微的差別，
需要注意的是，新的序列在創建的時候，高速快取值最好和原序列的一致，
否則可能會發生序列跳躍（Oracle的最低可為0，PostgreSQl的最低為1），
-- 以下是三個序列的創建陳述句，當前設定的初始值為1，當遷移后需要查詢原序列的當前值，并修改現在的初始值1
-- CREATE SEQUENCE
create sequence cp_opr.sq_tauditdetail_id
minvalue 1
maxvalue 9223372036854775807
start with 1
increment by 1
cache 10;
-- CREATE SEQUENCE
create sequence cp_opr.seq_complaint_email_log_id
minvalue 1
maxvalue 9223372036854775807
start with 1
increment by 1
cache 10;
-- CREATE SEQUENCE
create sequence cp_opr.seq_t_complaint_mobilesms_log
minvalue 1
maxvalue 9223372036854775807
start with 1
increment by 1
cache 10;
6.匯入原有的表資料
匯入Oracle資料庫的歷史資料，我們采用的方案是寫批處理，然后跑批導資料， 因為是本地和sit環境，所以資料量大的表可以適當刪掉些無用的舊資料，以提升匯入速度，方便測驗，這里就不上批處理的代碼了， 匯入資料的時候，需要注意： 資料匯入的先后順序，因為有的表資料通過邏輯外鍵關聯，這些邏輯外鍵不允許為空，如果順序亂了，則可能匯入資料的時候報錯，7.改SQL語法
這一步主要改的是系統中的SQL陳述句，因為PostgreSQl的語法和Oracle的差異還是不小的，所以必須要進行調整， 調整的內容可以分為2部分： 1.SQL語法（）； 2.SQL函式；7.1 SQL語法
語法需要注意的地方，主要是欄位型別的轉換，這是因為PostgreSQL的語法要求的，
欄位型別的指定（轉換）需要用“::”符號，例如：
-- 不僅可以指定WHERE條件中的子型別別，還可以指定SELECT查詢欄位的型別
SELECT T.BRANCH_ID::VARCHAR
FROM T_xxx T
WHERE T.BRANCH_ID <> 1 ]]>
AND T.PARENT_ID = 1
AND T.BRANCH_ID::VARCHAR
<iterate prepend="in" conjunction="," open="(" close=")">
#deplist[]#
</iterate>
-- INSERT陳述句用的最為頻繁
INSERT INTO T_C_C
(CREATED_BY,
CREATED_DATE,
UPDATED_BY,
UPDATED_DATE,
CALENDAR_ID,
CALENDAR_DATE,
IS_WORKING_DATE,
YEAR,
MONTH,
DAY,
QUARTER,
WEEK)
VALUES(#createdBy:VARCHAR#,
now(),
#updatedBy:VARCHAR#,
now(),
#calendarId:VARCHAR#,
#calendarDate#::timestamp,
#isWorkingDate:VARCHAR#,
substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),1,4),
substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),6,2),
substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),8,2),
substr(to_char(#calendarDate#::timestamp,'YYYYQMMDD'),5,1),
to_char(#calendarDate#::timestamp,'ww'));
-- UPDATE陳述句中，不能夠使用表別名
-- 特殊的時間加減法運算
select CURRENT_TIMESTAMP::TIMESTAMP + INTERVAL '5 day';
-- 分頁查詢語法
SELECT * FROM T_xxx LIMIT 20 OFFSET 0;
-- 左右連接查詢 需要使用LEFT OUTER JOIN 或 RIGHT OUTER JOIN
SELECT * FROM T_A a LEFT OUTER JOIN T_B b WHERE a.id=b.id;
-- 子查詢必須要有別名
SELECT a.id,a.name,a.phone FROM(SELECT * FROM T_XXX WHERE ID in('1','2','5'))a;
-- 插入空值NULL的時候，必須明確指定為NULL，而不能和Oracle一樣是''
7.2 SQL函式
以下列舉一部分，更多的函式可以自行百度
| Oracle函式 | PostgreSQL函式 |
|---|---|
| Decode | case xxx when |
| Nvl | Coalesce |
| Instr | Strops |
| Nlssort | convert_to |
| Trunc | date_trunc |
8.保證資料時效性和完整性
8.1 資料完整性
資料的完整性，主要依靠的是： 1.一開始系統使用表統計，是否統計的足夠完整； 2.匯入資料的時候順序； 測驗匯入后的資料是否完整也比較簡單： 1.觀察資料匯入時是否有日志例外； 2.將業務從頭到尾走一遍，看看資料是否查不到或者流程走不通；8.2 資料時效性
這個主要涉及的是，當生產環境資料庫由Oracle切換到PostgreSQL的時候，需要一定的時間去匯入原有的資料， 那么匯入資料的這段時間中，產生的新資料該如何匯入新資料庫？ 如果直接匯入，會不會與原有的資料關聯不上？除此之外，還會有什么意想不到的問題？ 比較遺憾的是，因為本次遷移的系統屬于比較邊緣的系統，所以最終采用的方案是： 生產資料庫切換的時候，將服務器關閉一段時間，阻止新資料的產生，直到舊資料遷移完畢再重啟服務器， 除了本次遷移的系統，還有很多系統也需要遷移，如果那些系統遷移的時候遇到了這個問題，并有了更好的方案，再來更新，9.其他
暫無，如果發現了再來補充~ ------------------------------------------2020.7.31------------------------------------------------- 前幾天這個切換生產資料庫的專案正式上線了，還是在深夜進行切換的， 之所以是深夜進行生產資料庫的切換，主要考慮的問題是： 因為我們系統的生產資料庫在切換的時候，沒有很好的關于切換時產生的新資料如何匯入新資料庫的解決方案，所以需要對這個問題進行一些妥協， 最后采用的方案是：將生產資料庫切換時間定在夜里十點到凌晨三點（通過資料分析，發現這個時段產生的新資料比較少），凌晨三點后發布切換資料庫的新版本系統， 而在切換資料庫時段產生的新資料，則由專門的人員進行手動記錄案件，等到新版本發布之后，再將資料手動錄入到系統中， 比較有趣的是，原計劃需要用5個小時完成資料庫的切換作業，實際上只用了大概三個小時就搞定了， 之所以這么快，一方面是因為在正式的切換資料庫之前，已經在預生產環境做過兩次模擬切換了，大家對于切換流程都比較熟悉了，另一方面就是大家都困，都想早點回去睡....... 不過值得總結的是，在對生產資料表導完資料的時候，需要對系統中重要的資料表的資料量進行比較，也就是看看Oracle資料庫的資料在匯入pgSQL資料庫之后有沒有丟失資料， 還有就是在資料量檢測完成，發布版本之后，還需要匯入一些新案件進行流程測驗，檢測是否有BUG出現， 雖然當天夜里已經對系統進行了一些測驗，但是第二天白天拉取日志的時候，還是有一部分例外資訊，主要就是一兩條很隱蔽、難以測驗的SQL陳述句型別轉換例外， 將這些問題處理之后，第二天下班之后又重新發了一個緊急版本， 至此，資料庫遷移的作業算是圓滿結束了， ------------------------------------------2020.9.2-------------------------------------------------轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1166.html
標籤：PostgreSQL