Postgres Foreign Table简单尝试

最近在看PostgreSQL分区文档时无意间看到有支持Foreign Table这个功能，这是我第一次听说这个功能，感觉蛮有意思的，它可以让一些数据保存在不同的机器上，我想这个对数据冷热分区应该很有帮助，比如把热数据放到性能比较好的机器上，把冷数据放到性能比较差的机器上。相较于用代码控制数据访问哪台db的方式，foreign table更便捷，访问方式的一致性也更好。下面简单尝试下功能。

开启扩展

PostgreSQL默认支持的外部数据源有：file | postgres等（可以通过extension目录下查找fdw来找到有哪些扩展：ls /usr/local/share/postgresql/extension/ | grep fdw），也可以找到第三方的数据源扩展，比如mysql或者其它db。这里我们直接用官方的postgres来试下。

CREATE extension postgres_fdw;

如果报错，请确认下你postgres的版本是不是太低了

创建外部服务器

CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', dbname 'db2', port '5432');

这里表示用postgres_fdw这个扩展来绑定外部的postgres数据库，简单起见我们用同一台机器（127.0.0.1）上的另外一个数据库（db2）来测试，我们为这个server起的名称是foreign_server

然后我们在db2中创建一个测试表，然后插入一些数据

CREATE TABLE public.foreign_part_table_test
(
    id serial NOT NULL,
    name character varying NOT NULL,
    CONSTRAINT foreign_part_table_test_pkey PRIMARY KEY (id)
);

INSERT INTO public.foreign_part_table_test(
	id, name)
	VALUES (1, 'aaaaa');
INSERT INTO public.foreign_part_table_test(
	id, name)
	VALUES (2, 'bbbbb');

创建外部表

CREATE FOREIGN TABLE foreign_test_table (
    id SERIAL NOT NULL,
    name character varying
)
SERVER foreign_server
OPTIONS(table_name 'foreign_part_table_test', schema_name 'public');

我们在本地创建一个table：foreign_test_table来和db2中的foreign_part_table_test建立绑定关系，他们的列的类型和名称要一致。额外的我在本地表上面加了自增id（用来后面测试）。

数据测试

通过外部表查询数据

select * from foreign_test_table;

通过自增id插入数据

INSERT INTO foreign_test_table(name) VALUES ('uuuuu');

报错了：

ERROR: duplicate key value violates unique constraint "foreign_part_table_test_pkey" DETAIL: Key (id)=(2) already exists.

我们之前在外部表上加了自增序列，但是这个序列自增是在本地自增过后再向db2的对应表插入的，但是db2中的表已经有数据了，因此这种情况要注意，要么db2的数据也是空数据，要么调一下自增序列的起始值。

更新数据

update foreign_test_table set name='ccccc' where id=1;

更新成功

表连接查询

我们先创建一个连接测试table，然后插入一些测试数据

CREATE TABLE public.test_table
(
    id integer,
    memo text
);

left join查询出memo数据

select foreign_test_table.*,test_table.memo from foreign_test_table left join test_table on test_table.id=foreign_test_table.id;

居然表连接也可以

虽然数据是对了，但是个人认为性能很差，尽量不要做跨数据库表连接，就单表用用吧

删除数据

delete from foreign_test_table where id=2;

DELETE 1 Query returned successfully in 129 msec.

好了，测试完毕，基本功能都是可用的。目前还没在生产环境使用，不知道有没有什么坑还没踩，希望有用过的大神提醒下，非常感谢