postgresql 跨库查询

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