873
PostgreSQL 的表傳輸功能
標簽
PostgreSQL , transfer table , 表傳輸
背景
表傳輸的功能很有意思，比如一些企業會有中心數據庫，邊緣數據庫。邊緣數據庫的數據需要周期性的導入到中心庫進行匯總，例如每個工廠的日流水數據，每天導入到總部的中心數據庫。
從邊緣庫導入到中心庫，大家可能會想到使用ETL工具，或者是數據訂閱（同步）的方式，但是大家有沒有想過，這些方式都需要數據重新來一遍insert或者copy。
insert, copy是數據庫的標準寫入接口，沒什麼不好的，隻不過當邊緣數據庫很多，數據量很大時，寫入可能成為瓶頸（雖然PG已經是堆表，寫入通常可以達到單機幾百萬行/s的速度）。如果有索引的話，更慢。
那麼有沒有效率更高的數據傳輸方法呢？
表傳輸應運而生，表傳輸可以理解為數據文件的拷貝，沒有了BUILD INDEX，forming tuple，alloc extend的消耗，速度大幅提升。
pg_transfer插件是postgrespro 企業版本的一個插件，可以用來實現表傳輸。
pg_transfer用法
表傳輸前提
因為表傳輸是拷貝文件的方式傳輸數據，所以必須要求源、目標數據庫具有物理文件兼容性。例如
1、數據庫版本一致。
2、數據庫所在操作係統架構一致（CPU架構、操作係統架構）。
3、數據庫某些涉及物理格式的編譯參數一致（塊大小、是否開啟CHECKSUM、數據文件段大小（涉及到文件尋址））。
準備步驟
源和目標都必須安裝pg_transfer插件
create extension pg_transfer;
將表置為隻讀
ALTER TABLE table_name SET CONSTANT;
收集統計信息
VACUUM (ANALYZE) table_name;
遷移表定義
pg_dump database -t table_name --schema-only -f transfer_dir/archive.out
pg_restore -d database --schema-only transfer_dir/archive.out
獲取目標庫被遷移表的toast relid，備用。
psql target_database -c select reltoastrelid from pg_class where relname='table_name'
遷移表、索引、TOAST數據
將表的數據刷盤，確保shared buffer中沒有表的髒頁。
同時需要輸入前一步獲得的目標庫生成的TOAST relid。
psql -d database -c select pg_transfer_freeze('table_name'::regclass::oid, reltoastrelid::oid);
導出表、索引、TOAST的數據文件
pg_dump database -Fc -t table_name --copy-mode-transfer --transfer-dir transfer_dir/ -f transfer_dir/archive.out
將數據文件導入目標庫，並掛接filenode。
pg_restore -d target_database --data-only --transfer-dir transfer_dir/ transfer_dir/archive.out
注意事項
如果源和目標在同一個文件係統中，那麼遷移過程中pg_dump或pg_restore 二選一，必須使用 --copy-mode-transfer 開關 。
如果目標庫有slave，並且希望將表傳輸的數據通過WAL同步到目標庫的slave，那麼使用pg_restore時，必須加上--generate-wa選項，以產生WAL。
參考
https://postgrespro.com/docs/postgresproee/9.6/pgtransfer.html
最後更新：2017-06-03 16:53:21