在远程数据库上创建新的数据库musician,并在库里创建表man,插入测试数据:
postgres=# create database musician;
CREATE DATABASE
music=# \c musician eric
您现在已经连线到数据库 "musician",用户 "eric".
musician=> create table man(id bigint);
CREATE TABLE
musician=> insert into man select * from generate_series(1,8000);
INSERT 0 8000
musician=> select count(*) from man;
count
-------
8000
(1 行记录)
musician=> \d
关联列表
架构模式 | 名称 | 型别 | 拥有者
----------+------+--------+--------
public | man | 资料表 | eric
(1 行记录)
在本地测试库安装插件postgres_fdw:
postgres=# create extension postgres_fdw;
CREATE EXTENSION
music=> \c music postgres
You are now connected to database "music" as user "postgres".
创建外部服务器对象,需要指定相关信息:
对象名称:musician_fdw_server
包装器类型:postgres_fdw,如果要连接Oracle或者MySQL数据库的话,可用oracle_fdw或mysql_fdw
主机IP:192.168.1.129
数据库名称:musician(刚刚创建的数据库名)
端口号:5432
music=# create server musician_fdw_server foreign data wrapper postgres_fdw options (host '192.168.1.129',dbname 'musician',port '5432');
CREATE SERVER
创建用户映射,相关信息:
本地用户:eric
外部服务器对象:musician_fdw_server
远程数据库用户名密码:eric,gao
music=# create user mapping for eric server musician_fdw_server options (user 'eric',password 'gao');
CREATE USER MAPPING
配置外部表,相关信息:
外部表在本库的名称:manid
外部服务器:musician_fdw_server
外部表名:man
music=> \c music postgres
You are now connected to database "music" as user "postgres".
music=# create foreign table manid(id bigint) server musician_fdw_server options(table_name 'man');
CREATE FOREIGN TABLE
注意:
在远程数据库的pg_hba.conf中修改一下相关的配置:
最终这样修改的:
# IPv4 local connections:
host all all 192.168.1.0/24 md5
因为远程连接的话,PG要求是需要有密码验证的,设置成trust的话会报错。
设置完成之后验证一下查询效果:
music=> \c music eric
You are now connected to database "music" as user "postgres".
music=# select count(*) from manid;
count
-------
8000
(1 row)
数据是可以看到了,性能如何呢?我们来测试一下:
在远程数据库本地执行语句:
musician=> explain analyze select count(*) from man;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Aggregate (cost=136.00..136.01 rows=1 width=0) (actual time=26.128..26.129 rows=1 loops=1)
-> Seq Scan on man (cost=0.00..116.00 rows=8000 width=0) (actual time=0.014..13.068 rows=8000 loops=1)
Planning time: 0.045 ms
Execution time: 26.189 ms
(4 行记录)
在本地数据库本地执行语句:
music=> explain analyze select count(*) from manid;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=220.92..220.93 rows=1 width=0) (actual time=42.804..42.804 rows=1 loops=1)
-> Foreign Scan on manid (cost=100.00..212.39 rows=3413 width=0) (actual time=2.264..41.813 rows=8000 loops=1)
Planning time: 0.067 ms
Execution time: 44.411 ms
(4 rows)
看起来差别不是太大,但是测试的数据量和类型也不复杂,那我们接下来换一条语句:
远程数据库本地执行语句:
musician=> explain analyze select * from man;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on man (cost=0.00..116.00 rows=8000 width=8) (actual time=0.012..10.277 rows=8000 loops=1)
Planning time: 0.036 ms
Execution time: 18.758 ms
(3 行记录)
本地数据库本地执行语句:
music=> explain analyze select * from manid;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Foreign Scan on manid (cost=100.00..186.80 rows=2560 width=8) (actual time=14.445..60.194 rows=8000 loops=1)
Planning time: 12.400 ms
Execution time: 64.936 ms
(3 rows)