將PostgreSQL遷移到TeleDB
更新時間 2025-02-05 09:36:25
最近更新時間: 2025-02-05 09:36:25
分享文章
本頁介紹天翼云TeleDB數據庫將PostgreSQL遷移至TeleDB。
支持的源和目標數據庫
支持的源和目標數據庫如下表：
| 源數據庫 | 目標數據庫 |
|---|---|
| PostgresSQL 12, 13, 14, 15 | TeleDB |
支持的遷移對象及SQL
遷移對象
- 結構遷移支持的對象：模式、表、索引、約束（外鍵、唯一、排他）、視圖、物化視圖、序列、存儲過程、函數、規則、觸發器、用戶自定義類型、域。
- 支持的字段類型：數字類型、貨幣類型、字符類型、二進制數據類型、日期/時間類型、布爾類型、枚舉類型、幾何類型、網絡地址類型、位串類型、文本搜索類型、UUID類型、JSON類型、復合類型、范圍類型。
注意事項
- 每次至多同步一個庫（database），同步多個庫需要創建多個DTS任務。
- 模式：不支持pg_toast，pg_temp_1，pg_toast_temp_1，pg_catalog，information_schema等系統模式的遷移。
- 表：不支持臨時表的遷移，表的索引、約束會一起遷移，表的觸發器、規則在全量完成之后遷移。
- 序列：待遷移的表中有引用序列時，必須同時遷移相應的序列。
- 映射規則：
- 不包含增量時，可以對庫、表、列名進行映射，若對表的列進行映射，則表中涉及到該列的約束將不會遷移。
- 包含增量時，不支持列名映射。
- 視圖、存儲過程、函數、域、自定義類型等對象依賴的表不支持做表名映射，否則視圖、存儲過程、函數將會失效。
增量數據遷移支持的SQL操作
- DML INSERT、UPDATE、DELETE。
- DDL 增量遷移的DDL操作僅支持CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE SEQUENCE、ALTER SEQUENCE、DROP SEQUENCE、CREATE VIEW、ALTER VIEW、DROP VIEW、CREATE INDEX、ALTER INDEX、DROP INDEX。
注意
暫不支持 CREATE TABLE 表名 AS SELECT 語句。
RENAME表名之后，向更改名稱后的表插入新的數據時，DTS不會同步新的數據到目標庫，因可能會導致任務中斷異常或數據不一致。
暫不支持以注釋開頭的DDL語句的同步。
數據庫賬號及權限
| 數據庫 | 所需權限 | 參考賦權語句 |
|---|---|---|
| 源庫 | 數據庫的CONNECT權限， 模式的USAGE權限， 表的SELECT權限， 序列的SELECT權限存在增量 REPLICATION連接權限 對模式public的USAGE和CREATE權限 用于 增量抓取ddl ，這里也可以用戶提前創建好 |
CREATE USER和GRANT語法。 -- 創建同步用戶 (dts)CREATE USER dts WITH PASSWORD 'xxx'; -- 授權同步用戶 (dts) 需要同步的 SCHEMA (public) 權限 GRANT SELECT,REFERENCES,TRIGGER ON ALL TABLES IN SCHEMA public TO dts; grant SELECT on ALL SEQUENCES IN SCHEMA public to dts; grant usage on schema public to dts; -- 開啟同步用戶 (dts) 的 REPLICATIONALTER USER dts REPLICATION; grant pg_read_all_settings to dts; |
| 目標庫 | 存在結構 具有模式所在庫的CONNECT權限、 模式所在庫上的CREATE權限、 對象所在模式的USAGE權限、 對象所在模式上的CREATE權限 不存在結構 遷移庫表的INSERT, UPDATE, DELETE, DDL 權限 |
-- 創建同步用戶 (dts)CREATE USER dts WITH PASSWORD 'xxx'; grant usage on schema public to dts; grant ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dts; grant CREATE on DATABASE public to dts; grant CREATE on schema public to dts |
操作須知
DTS遷移過程一般包含四個階段：預檢查階段、結構遷移階段、全量階段、增量階段。為了確保數據遷移各個階段的平順，在創建遷移任務前，請務必閱讀以下使用須知。
任務開始前
源庫要求
- 源數據庫的分區表觸發器不可以設置為disable。
- 全量同步支持源庫備機狀態，但需要設置hot_standby_feedback為on；增量同步不支持源庫備機狀態。
- 同步對象依賴和關聯的對象也須一起同步，否則可能導致任務失敗。
- 若要做增量同步，源數據庫的“pg_hba.conf” 文件中包含如下的配置：
- host replication all 0.0.0.0/0 md5
- 源數據庫參數wal_level必須配置為logical；
- 源數據庫需提前安裝Decoderbufs插件；
- 源數據庫中無主鍵表的replica identity屬性必須為full；
- 源數據庫的max_replication_slots參數值必須大于當前已使用的復制槽數量；
- 源數據庫的max_wal_senders參數值必須等于或大于max_replication_slots參數值；
- 源數據庫中表的主鍵列toast屬性為main、external、extended時，其replica identity屬性必須為full。
- 同步對象依賴和關聯的對象也須一起同步，否則可能導致任務失敗。
目標庫要求
- 目標數據庫的block_size參數值必須大于或等于源庫中的對應參數值。
- 如果存在money類型的字段，需要目標數據庫和源數據庫的lc_monetary參數值一致。
- 若要做增量同步，且同步對象包含外鍵、觸發器或事件觸發器，則目標數據庫的session_replication_role參數必須設置為replica，同步結束后，此參數需改為origin。
- 目標庫不可以包含與待同步對象類型相同且名稱相同的對象，包括模式、表、序列等，否則任務可能出差。系統庫、系統模式、系統表等除外。
- 選擇表級對象遷移時，增量遷移過程中不建議對表進行重命名操作。
- 對于全量+增量和增量任務，啟動前請確保源庫中未啟動長事務，啟動長事務會阻塞邏輯復制槽的創建，進而引發任務失敗。
- 若選擇同步DDL，須注意源庫執行DDL時，確保在目標庫上是兼容的。
- 目標數據庫關聯TeleDB實例必須有足夠的磁盤空間，磁盤大小建議取以下兩種中的最小值:
- 源庫待遷移數據量大小的1.5倍。
- 源庫待遷移數據量大小加200GB。
結構、全量過程中
- 請勿修改源庫和目標庫的端口號，請勿修改、刪除源庫和目標庫連接用戶的密碼、權限，否則可能導致任務失敗。
- 請勿在源庫執行任何DDL，否則可能導致數據不一致或任務失敗。
- 請勿在目標庫做寫入操作，否則可能導致數據不一致。
增量過程中
- 請勿修改源庫和目標庫的端口號，請勿修改、刪除源庫和目標庫連接用戶的密碼、權限，否則可能導致任務失敗。
- 請勿修改源數據庫表的主鍵或者唯一鍵（主鍵不存在時），否則可能導致增量數據不一致或任務失敗。
- 請勿修改源數據庫中表的replica identity屬性，否則可能導致增量數據不一致或任務失敗。
- 請勿在目標庫做寫入操作，否則可能導致數據不一致。
- 庫級同步時，源庫新增無主鍵表時，請務必同時將該無主鍵表的replica identity屬性設置為full，然后再寫入數據，否則可能導致數據不一致或任務失敗。
- 庫級同步時，源庫新增主鍵表時，如果主鍵列toast屬性為main、external、extended時，請務必同時將該表的replica identity屬性設置為full，然后再寫入數據，否則可能導致數據不一致或任務失敗。
- 若選擇同步DDL，須注意源庫執行DDL時，確保在目標庫上是兼容的。
數據稽核
- 建議在源庫的業務低峰期進行數據比對，防止誤報不一致數據，以及減少對源庫和DTS任務的沖擊。
- 在增量同步過程中做對比時，源庫若存在寫入，則對比結果可能不一致。
數據類型映射說明
| PostgreSQL類型 | Decoderbuf字段 |
|---|---|
| BOOLOID | datum_boolean |
| INT2OID | datum_int32 |
| INT4OID | datum_int32 |
| INT8OID | datum_int64 |
| OIDOID | datum_int64 |
| FLOAT4OID | datum_float |
| FLOAT8OID | datum_double |
| NUMERICOID | datum_double |
| CHAROID | datum_string |
| VARCHAROID | datum_string |
| BPCHAROID | datum_string |
| TEXTOID | datum_string |
| JSONOID | datum_string |
| XMLOID | datum_string |
| UUIDOID | datum_string |
| TIMESTAMPOID | datum_string |
| TIMESTAMPTZOID | datum_string |
| BYTEAOID | datum_bytes |
| POINTOID | datum_point |
| PostGIS geometry | datum_point |
| PostGIS geography | datum_point |
準備工作
- 登錄自建PostgreSQL所屬的服務器。
- 如果需要進行增量遷移，需要將配置文件中的wal_level設置為logical。
- 將DTS的IP地址加入至自建PostgreSQL的配置文件pg_hba.conf中。如下圖：
如果您已將信任地址配置為0.0.0.0/0，可跳過本步驟。
- 如果任務包含增量遷移，需安裝PostgreSQL的邏輯解碼器輸出插件Decoderbufs，建議安裝v2.1.1.Final以上版本，低版本可能會導致PostgreSQL數據庫出現coredump，詳細的步驟可參考如下內容或PostgreSQL官網文檔。
(1) 請自行前往github倉庫獲取Decoderbufs插件。
(2) 進行插件的配置。
① 進行插件的編譯。
export PATH=/usr/lib/postgresql/9.6/bin:$PATH make make install
② 在postgresql.conf中配置邏輯復制。
MODULES shared_preload_libraries = 'decoderbufs' # 解碼插件配置 REPLICATION wal_level = logical ????????????# minimal, archive, hot_standby, or logical (change requires restart) max_wal_senders = 100 ????????????# max number of walsender processes (change requires restart) wal_keep_segments = 4 ??????????# in logfile segments, 16MB each; 0 disables #wal_sender_timeout = 60s ?????# in milliseconds; 0 disables max_replication_slots = 100 ??????# max number of replication slots (change requires restart)
③ 重啟PostgreSQL。
④ 驗證配置是否生效。
#創建邏輯復制，使用邏輯復制插件decoderbufs select * from pg_create_logical_replication_slot('decoderbufs_demo', 'decoderbufs'); #對相關表進行數據操作 #使用decoderbufs調試模式 SELECT data FROM pg_logical_slot_peek_changes('decoderbufs_demo', NULL, NULL, 'debug-mode', '1'); #使用decoderbufs獲取wal變更，更新wal位置 SELECT data FROM pg_logical_slot_get_changes('decoderbufs_demo', NULL, NULL, 'debug-mode', '1'); #查看邏輯復制的wal位置 SELECT * FROM pg_replication_slots WHERE slot_type = 'logical'; #刪除邏輯復制 select pg_drop_replication_slot("decoderbufs_demo");
- 創建源庫DTS用戶
如果僅做測試用，可以直接使用PostgreSQL的超級用戶作為遷移用戶，一般用戶名為postgres。
如果需要授予精準的操作權限，則按照以下操作進行授權：
CREATE USER和GRANT語法。 -- 創建同步用戶 (dts) CREATE USER dts WITH PASSWORD 'xxx'; -- 授權同步用戶 (dts) 需要同步的 SCHEMA (public) 權限 GRANT SELECT,REFERENCES,TRIGGER ON ALL TABLES IN SCHEMA public TO dts; ? grant SELECT on ALL SEQUENCES IN SCHEMA public to dts; grant usage on schema public to dts; -- 開啟同步用戶 (dts) 的 REPLICATION ALTER USER dts REPLICATION; grant pg_read_all_settings to dts;
- 創建目標庫DTS用戶
如果僅做測試用，可以直接使用TeleDB的root用戶作為遷移用戶。
如果需要TeleDB的精準遷移權限，可按照如下配置操作：
CREATE USER dts WITH PASSWORD 'xxx'; grant usage on schema public to dts; grant ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dts; grant CREATE on DATABASE public to dts; grant CREATE on schema public to dts;如果要做增量遷移且遷移的表包含外鍵，那么除了上述權限外，還要保證該用戶具有在會話級別執行SET session_replication_role = 'replica'的權限。可登錄TeleDB控制臺，將該參數設置并重啟。
- 如果需要做增量DDL，則需要手動在源庫創建用于增量DDL的元數據結構
執行如下語句，創建存儲DDL信息的表。勾選增量DDL時，遷移對象中必須包含這張表以及它的序列。
DROPTABLE IF EXISTS public.dts_ddl_info; DROP SEQUENCE IF EXISTS public.dts_ddl_info_id_seq; CREATETABLE public.dts_ddl_info( id bigserial primary key, ddl text, username varchar(64) default current_user, txid varchar(16) default txid_current()::varchar(16), tag varchar(64), database varchar(64) default current_database(), schema varchar(64) default current_schema, client_address varchar(64) default inet_client_addr(), client_port integer default inet_client_port(), event_time timestamp default current_timestamp );
- 執行如下語句，創建函數。
CREATEOR REPLACE FUNCTION public.dts_capture_ddl() RETURNS event_trigger LANGUAGE plpgsql SECURITY INVOKER AS $BODY$ declare ddl text; declare real_num int; declare max_num int:=50000; begin if (tg_tag in('CREATE TABLE','ALTER TABLE','DROP TABLE','CREATE SEQUENCE','ALTER SEQUENCE','DROP SEQUENCE','CREATE VIEW','ALTER VIEW','DROP VIEW','CREATE INDEX','ALTER INDEX','DROP INDEX')) then select current_query()into ddl; insertinto public.dts_ddl_info(ddl, username, txid, tag, database, schema, client_address, client_port, event_time) values(ddl, current_user, cast(txid_current()asvarchar(16)), tg_tag, current_database(), current_schema, inet_client_addr(), inet_client_port(), current_timestamp); selectcount(id)into real_num from public.dts_ddl_info; if real_num > max_num then if current_setting('server_version_num')::int<100000 then deletefrom public.dts_ddl_info where id<(select min(id)+1000from public.dts_ddl_info)andnot exists (select0from pg_locks l join pg_database d on l.database=d.oid where d.datname=current_catalog and pid<>pg_backend_pid()and locktype='relation'and relation=to_regclass('public.dts_ddl_info_pkey')::oid and mode='RowExclusiveLock'); else deletefrom public.dts_ddl_info where id<(select min(id)+1000from public.dts_ddl_info)and(xmax=0or coalesce(txid_status(xmax::text::bigint),'')<>'in progress'); end if; end if; end if; end; $BODY$;
將剛創建的函數的所有者修改為DTS連接源庫的賬號，以postgresql為例。
ALTER FUNCTION public.dts_capture_ddl() OWNER TO postgres;
執行下述命令，創建全局事件觸發器。
CREATE EVENT TRIGGER dts_ddl_event ON ddl_command_end EXECUTE PROCEDURE public.dts_capture_ddl();
執行以下語句，將創建的事件觸發器設置為enable。
ALTER EVENT TRIGGER dts_ddl_event ENABLE ALWAYS;
- 確認待遷移對象中是否包含觸發器
如果將觸發器作為結構的一部分，在DTS的調度邏輯上，觸發器會先于全量遷移被遷移到TeleDB。這樣可能會影響到全量遷移，導致數據不一致。
建議將觸發器放到全量遷移之后，再新建一個遷移任務進行遷移。
- 數據及業務信息統計
如果為非測試任務，需要在遷移之前統計業務以及遷移信息，方便進行遷移任務規劃。
| 資源信息 | 源庫規格信息 | 例：4C8G + 500G + SSD |
|---|---|---|
| 目標庫磁盤信息 | 例：4C8G + 500G + SSD | |
| 網絡情況 | 例：為測試環境純內網傳輸，無復雜的網絡拓補結構 | |
| 數據信息 | 總數據量 | 例：30GB |
| 總庫表數量 | 例：40個庫，8000張表，2000個視圖，5個觸發器 | |
| 每日新增數據量級 | 例：一天的WAL日志新增大概100GB | |
| 是否所有表都有主鍵 | 建議遷移前完善主鍵，提高性能，方便運維。如果存在無主鍵的表，則增量階段源端對應表的增、刪、改操作不會同步至目標端，可能導致數據不一致，請謹慎評估。 | |
| 業務類 | 規劃遷移批次 | 例：規劃分3次進行遷移，遷移時間每天晚上20:00-06:00，日期為2023-09-18至2023-09-20。實際遷移操作請與數據庫全量備份操作錯開，以免交叉影響，否則可能會導致備份失敗，同時影響遷移效率。 |
| 數據遷移是否可停業務 | 例：能/不能 | |
| 增量遷移情況 | 例：開啟增量遷移，持續時間5天 | |
| 可停業務時間長度 | 例：服務停機時間預計48小時 | |
| 業務中是否存在百萬級別的大事務 | 例：存在，涉及對表CLOUD.LOGS進行大事務操作，存在一個存儲過程用不帶where條件的delete語句定期清理該表。 |
操作步驟
-
訂購DTS數據遷移實例。
-
進入實例配置頁面。
- DTS實例創建成功后，進入【數據遷移】實例列表頁面，上一步驟購買成功的實例在實例列表中顯示狀態為“待配置”，進入實例配置頁面進行配置：
- DTS實例創建成功后，進入【數據遷移】實例列表頁面，上一步驟購買成功的實例在實例列表中顯示狀態為“待配置”，進入實例配置頁面進行配置：
-
配置源庫及目標庫信息。
- 進入實例配置第一個步驟的【配置源庫及目標庫信息】頁面，填入源庫與目標庫的相關配置信息，包括數據庫類型、IP地址端口、數據庫賬號、數據庫密碼等信息。
- 完成上述信息的填寫后，單擊源數據庫和目標數據庫的“測試連接”按鈕進行數據庫連接測試，檢查數據庫能否正常連接。
-
配置遷移對象及高級配置。
- 源庫和目標庫連通性測試成功后，點下一步按鈕，進入實例配置第二個步驟的【配置遷移對象及高級配置】頁面，在“源庫對象”中選擇要遷移的源庫對象，包含：庫、TABLE、VIEW、FUNCTION等，選中后單擊“>”按鈕，將待遷移對象移動到“已選擇對象”中。
- 遷移對象配置說明：
配置 說明 任務步驟 如果只需要進行全量遷移，請同時勾選庫表結構遷移和全量遷移。 如果需要進行不停機遷移，請同時勾選庫表結構遷移、全量遷移和增量遷移。 注意：如果未選擇增量遷移，為保障數據一致性，數據遷移期間請勿在源實例中執行DML和DDL操作。 遷移對象 源庫為PostgreSQL的情況下，支持表、視圖、函數、存儲過程、物化視圖、規則、觸發器，域、自定義類型等對象的遷移。 在遷移對象框中單擊待遷移的對象，然后單擊將其移動到已選擇對象框；已選擇對象可以通過單擊將對象回退。 選擇遷移對象時，如不展開庫的詳細信息，則表示整庫遷移，后續在增量任務過程中，可在源庫創建新表，其他類型暫不支持。 映射名稱更改 支持庫表列三級名稱映射，如需更改單個遷移對象在目標實例中的庫名、表名和列名，選擇對象，然后單擊編輯按鈕。 如需批量更改遷移對象在目標實例中的庫名、表名，請單擊已選擇對象方框右上方的“批量編輯”。 若遷移任務僅包含結構遷移和全量遷移，支持列映射名稱更改，若遷移任務包含增量遷移，不允許列映射名稱更改。 庫表名僅支持字母、數字和下劃線，長度不超過64個字符。 注意：整庫遷移時不建議做庫表名映射。 過濾待遷移數據 支持設置where條件過濾數據，過濾條件不允許;和--字符，如需使用引號，請使用單引號(')，只有滿足where條件的數據才會遷移到目標庫。 增量遷移的DML 選擇增量遷移DML操作，選中遷移對象，單擊“編輯”，在彈跳框中選擇所需增量遷移的DML操作。若在數據庫級別和表級別都指定了DML操作，則表級別的設置會覆蓋庫級別的。 增量遷移的DDL 增量任務可選擇是否遷移增量DDL。 是否定時開始任務 可選擇任務開始的時間，默認單擊開始任務后立即啟動遷移任務。 注意任務中存在整庫遷移的情況下，則必須勾選增量DDL同步。
非整庫遷移的情況下，若選擇DDL同步，則只遷移待遷移對象的DDL語句。
非整庫遷移的情況下，若未選擇增量DDL同步，則增量階段不會同步任何DDL語句。
-
預檢查和啟動遷移。
完成遷移對象和高級配置后，單擊“下一步預檢查”，進入實例配置第三個步驟的【預檢查】頁面。預檢查會檢查如下列表信息，并給出檢查結果，用戶可以依據檢查結果進行下一步操作。
| 檢查項 | 檢查內容 |
|---|---|
| lc_monetary參數配置一致性檢查 | 檢查源庫與目標庫的lc_monetary參數配置是否一致。 |
| pg版本檢查 | 檢查源庫和目標庫的pg版本是不是符合要求。 |
| 同名對象存在性檢查 | 檢查目標庫中是否存在和待遷移庫同名的待遷移對象。 |
| 擴展插件兼容性檢查 | 檢查源庫中安裝的擴展插件，在目標庫是否存在。 |
| 約束完整性檢查 | 檢查待遷移對象中子表依賴的父表是否被選中。 |
| 檢查hot_standby_feedback參數配置 | 全量遷移源庫pg為備節點時，hot_standby_feedback參數需配置為ON。 |
| 源庫用戶權限檢查 | 檢查源庫用于DTS任務的用戶是否具有相應的權限。 |
| 源庫連通性檢查 | 檢查數據傳輸服務器能否連通源數據庫。 |
| 源數據庫的模式名表名是否合法 | 檢查源數據庫的模式名表名是否合法，名稱不可以包含+"%'<>,.字符。 |
| 源庫參數wal_level是否為logical | 勾選增量遷移時，檢查源庫wal_level參數值是否為logical。 |
| 目標庫用戶權限檢查 | 檢查目標庫用于DTS任務的用戶是否具有相應的權限。 |
| 目標庫連通性檢查 | 檢查數據傳輸服務器能否連通目標數據庫。 |
| 源庫參數max_replication_slots校驗 | 勾選增量遷移時，檢查源庫max_replication_slots參數值是否大于當前已使用的復制槽數量。 |
| 源庫參數max_wal_senders校驗 | 勾選增量遷移時，檢查源庫max_wal_senders參數值是否大于當前已使用的復制槽數量。 |
| 邏輯解碼插件安裝校驗 | 勾選增量遷移時，檢查源庫是否安裝邏輯解碼插件decoderbufs。 |
| 待遷移表是否存在主鍵檢查 | 勾選增量遷移時，檢查待遷移表中是否存在無主鍵的表。 |
| 無日志表存在性檢查 | 勾選增量遷移時，檢查當前選擇的待同步對象中的表是否存在無日志表。 |
| 源庫主備狀態檢查 | 勾選增量遷移時，檢查源庫是否為集群中的主庫。 |
| ddl同步元數據檢查 | 勾選ddl同步時，檢查源庫是否有ddl同步所需的元數據對象。 |
如果預檢查通過，可單擊【預檢查】頁面底部的啟動遷移按鈕，開始遷移任務。或者單擊數據遷移按鈕，返回任務列表，列表中的任務將會顯示為 未啟動 。
- 勾選該任務，單擊 開始任務 ，任務將顯示為 運行中 ，直到該任務全量遷移完成，或者處于增量遷移狀態。
