PostgreSQL 的表传输功能

less than 1 minute read

背景

表传输的功能很有意思,比如一些企业会有中心数据库,边缘数据库。边缘数据库的数据需要周期性的导入到中心库进行汇总,例如每个工厂的日流水数据,每天导入到总部的中心数据库。

从边缘库导入到中心库,大家可能会想到使用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/enterprise/9.6/pgtransfer.html

Flag Counter

digoal’s 大量PostgreSQL文章入口