跨库访问:PostgreSQL数据库的高级技巧

发表时间: 2020-02-27 11:25

跨库访问常用的三种方式:schema,dblink,postgres_fdw

第一种:schema(架构方式)

其实还是在一个数据库中,只不过把不同数据保存在不同的架构中,创建与public并行的架构


访问其他架构下面的需要带上架构名称.表名,如下格式

select * from public.test;

select * from test.test1;

如果认为输入框架名称太麻烦,可以设置

set search_path to "$user",public,test; 前面是固定格式,后面是对应要访问的两个框架,第一个参数为本框架,另一个为要访问的框架,设置完成后访问直接可以访问表名,不需要加架构名称

在public访问test框架中的test1表:select * from test1;

第二种,dblink

跨库查询必须使用dblink,无法像Oracle那样,授权然后直接访问

首先创建扩展

create extension

dblink;

再去查询

select * from dblink('host=127.0.0.1 dbname=postgres user=postgres password=123456','select * from account') as t(id int,name varchar(10));

如果要查询别的数据库的东西,可以创建视图,然后从视图中查询

第三种 postgres_fdw

首先安装该扩展

CREATE EXTENSION postgres_fdw;

然后使用CREATE SERVER创建一个外部服务器。在这个示例中,我们希望连接到主机为192.83.123.89 的PostgreSQL服务器,监听端口为5432。远程服务器上做连接的数据库名为situation

CREATE SERVER foreign_server

FOREIGN DATA WRAPPER postgres_fdw

OPTIONS (host '192.168.6.168', port '5432', dbname 'situation');

也需要一个使用CREATE USER MAPPING定义的用户映射,辨认将要在远程服务器上使用的角色:

CREATE USER MAPPING FOR postgres

SERVER foreign_server

OPTIONS (user 'postgres', password 'admin@110');

现在可以用CREATE FOREIGN TABLE创建一个外部表了。在这个示例中,我们希望访问远程服务器上名为some_schema.some_table 的表。它的本地名为foreign_table:chatgroup为远程表名

CREATE FOREIGN TABLE foreign_table (

id text NOT NULL,

groupid text,

userid text,

createuserid text,

createtime timestamp

)

SERVER foreign_server

OPTIONS (schema_name 'public', table_name 'chatgroup');

查询数据

select * from foreign_table;