跨库访问常用的三种方式: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;