pg创建dblink访问另外的库下的表

pg版本：10

 

[postgres@localhost ~]$ psql
psql.bin (10.15)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 db_test   | hxl      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/hxl              +
           |          |          |             |             | hxl=CTc/hxl
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# select user;
   user   
----------
 postgres
(1 row)

创建dblink扩展
postgres=# create extension dblink;

查看扩展
postgres=# select * from pg_extension;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql |       10 |           11 | f              | 1.0        |           |
 dblink  |       10 |         2200 | t              | 1.2        |           |
(2 rows)


创建dblink
postgres=# select dblink_connect('test_dblink1','dbname=db_test host=localhost port=5432 user=hxl password=postgres');

这里的test_dblink1是dblink名

postgres=# select * from dblink('test_dblink1','select id,name1 from tb_test limit 10') as t1(id int,name1 varchar);

 

-- The End --