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