PostgreSQL 的表传输功能
背景
表传输的功能很有意思,比如一些企业会有中心数据库,边缘数据库。边缘数据库的数据需要周期性的导入到中心库进行汇总,例如每个工厂的日流水数据,每天导入到总部的中心数据库。
从边缘库导入到中心库,大家可能会想到使用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