PostgreSQL FDW的技術(shù)原因及應(yīng)用

1、 揚帆起航PostgreSQL FDW的技術(shù)原因及應(yīng)用前生今世走馬觀花實例：file_fdw實例：postgres_fdw工作原理FDW 意義FDWFDWFDWFDWFDWFDW如何編寫大綱FDW 的意義各種強大的數(shù)據(jù)組件層出不窮應(yīng)用成為各種數(shù)據(jù)系統(tǒng)間的搬運工 應(yīng)對各種異構(gòu)的接口用于構(gòu)建數(shù)據(jù)系統(tǒng)的工具正在變得越來越好，但還缺少一個重要的組件：與Unixshell類似的數(shù)據(jù)庫集成設(shè)施。即一種聲明式的，簡單的，用于組裝數(shù)據(jù)系統(tǒng)的高級語言，數(shù)據(jù)庫世界的巴別塔PostgreSQL FDW提供了組裝這類數(shù)據(jù)系統(tǒng)的基礎(chǔ)設(shè)施，是一項很有趣的技術(shù)postgresql | elasticsearch自動創(chuàng)建pg

2、sql中表的全文索引？SQL更像是數(shù)據(jù)系統(tǒng)世界的 “世界通用語”INSERT INTO redis.daily_summary SELECT * FROM pgsql.metadata mtJOIN mysql.realtime_counters USING(id) JOIN mongo.history_counters USING(id)FDW 愿景展望ONE DATABASE RULE THEM ALL!FDWFDW 愿景展望Federation Database / PolystorePrestoAWS AthenaFDW 前生今世實現(xiàn)者寥寥無幾S Q L / M E D :M a n

3、a g e m e n t o f E x t e r n a l D a t aS Q L S t a n d a r d I S O / I E C 9 0 7 5 - 9 : 2 0 08P o s t g r e S Q L I B M D B 2M a r i a D B FDW 基本模型FDW 前生今世FDW 走馬觀花CREATE FOREIGN DATA WRAPPER file_fdw;CREATE SERVER IF NOT EXISTS pg_log FOREIGN DATA WRAPPER file_fdw;CREATE USER MAPPING FOR postgres

4、 SERVER pg_log;CREATE FOREIGN TABLE pg_log_monday (log_time user_nametimestamptz, text,text, integer,location application_nametext, text) SERVER pg_log OPTIONS (filename /pg/data/log/postgresql-Mon.csv, format csv);SELECT * FROM pg_log_monday;CREATE FORIEIGN DATA WRAPPER database_nameprocess_idCREAT

5、E SERVER CREATE USER MAPPING CREATE FOREIGN TABLEFDW 走馬觀花/wiki/Foreign_data_wrappersSQL的NoSQL的BigData的LocalFile的Cloud的百花齊放FDW實例file_fdwpostgres_fdw原裝品質(zhì)，由PostgreSQL全球開發(fā)組維護 原版自帶，PostgreSQL默認(rèn)自帶這兩個擴展File FDW訪問外部表（文件，如CSV）讀取任意程序的輸出并將其轉(zhuǎn)換為數(shù)據(jù)表 只讀訪問CSV格式的PG日志與外部軟件（Excel，其他數(shù)據(jù)庫） 交換數(shù)據(jù)CREATE FOREIGN TABLE etc_pa

6、ssword (username TEXT,password TEXT,user_id INTEGER,group_id INTEGER,user_info TEXT,home_dir TEXT, shellTEXT) SERVER fs OPTIONS (PROGRAM $awk -F: NF & !/:space:*#/ print $1,$2,$3,$4,$5,$6,$7 OFS=037 /etc/passwd$, FORMAT csv, DELIMITER E037);除了CSV文件，任何輸出csv的程序都可以作為輸入執(zhí)行任意的Shell命令獲取輸出比如打印出 /etc/passwd或

7、者通過ssh/curl/wget從網(wǎng)絡(luò)獲取數(shù)據(jù)postgres_fdw原裝品質(zhì)，可讀可寫可下推ETL / Sharding / CMDBpostgres_fdw Quick Start- execute on node 1SELECT * FROM users LIMIT 200;postgres_fdw pushdown下推是FDW性能的決定性因素謂詞（Where條件）， 聚合函數(shù)（avg, max）， 連接子句 （JOIN）TopN （ORDER x LIMIT x）在本地判斷需要取回大量數(shù)據(jù)下推至遠(yuǎn)程執(zhí)行，只取回極少量需要的結(jié)果postgres_fdw pushdownPostgresF

8、DW支持很多下推postgres_fdwbased sharding利用外部表機制與分區(qū)表機制，優(yōu)雅地實現(xiàn)分片每個實例上都有一張分區(qū)表，其中一或N個分區(qū)為真實存儲數(shù)據(jù)的本地物理表。 其他分區(qū)為外部表，映射到對應(yīng)真實表所在的Server分片樣例- 插入10000條測試數(shù)據(jù)并查看分布INSERT INTO users (id,username) SELECT i, user # | i FROM generate_series(1, 10000) i; SELECT tableoid:RegClass, array_agg(id) FROM users GROUP BY tableoid;- 當(dāng)查

9、詢落在外部分片上的數(shù)據(jù)時，會自動取回相應(yīng)結(jié)果test=# EXPLAIN VERBOSE SELECT * FROM users WHERE id = 3;QUERY PLAN-Foreign Scan on public.users_1 (cost=100.00.121.21 rows=4 width=68) Output: users_1.id, users_1.username, users_1.passwordRemote SQL: SELECT id, username, password FROM public.users_1 WHERE (id = 3)- 當(dāng)掃描整個分區(qū)表時，查

10、詢會自動路由至本地分區(qū)和遠(yuǎn)程分區(qū)FDW sharding FAQ外部表的約束問題外部表的死鎖檢測問題 外部表的下推與性能問題外部表與主從FailoverFDW的連接復(fù)用FDW的search_path例如：想知道某一個查詢在主從離線三套庫上各自的執(zhí)行時長，平均執(zhí)行時長，總執(zhí)行時長可以便利地從一個中心點實時查詢所有線上數(shù)據(jù)庫的元數(shù)據(jù)聚合postgres_fdw for CMDBpostgres_fdw 可以讀取遠(yuǎn)程PostgreSQL的數(shù)據(jù)，自然也可以讀取遠(yuǎn)程數(shù)據(jù)庫實例的元數(shù)據(jù)（catalog）當(dāng)使用postgres_fdw導(dǎo)入遠(yuǎn)程表時，如果遠(yuǎn)端表有字段使用了系 統(tǒng)保留字段名，是沒有辦法導(dǎo)入該字段

11、的，只能手工創(chuàng)建去除該 字段的外部表定義，作為曲線救國的方式例如pg_catalog.pg_replication_slots的xmin字段mysql_fdwCREATE EXTENSION mysql_fdw;CREATE SERVER mysql_serverFOREIGN DATA WRAPPER mysql_fdw OPTIONS (host , port 3306);CREATE USER MAPPING FOR postgresSERVER mysql_serverOPTIONS (username foo, password bar);CREATE FOREIGN TABLE warehouse( warehouse_id int,warehouse_name text, warehouse_created datetime)SERVER mysql_serverOPTIONS (dbname db, table_name warehouse);/EnterpriseDB/mysql_fdwmysql_fdw與mongo_fdw均由EDB提供支持連接mysql時，mysql_fdw實際會使用mysql的C語言驅(qū)動libmysqlclientMongoFDW/EnterpriseDB/mongo_fdwMongoFDW Mong