本节介绍了contrib中的dblink插件,该插件可用于连接远程PG数据库,看起来像是Oracle的DBLink. **安装** 进入dblink目录,make & make install ``` [root@localhost contrib]# cd dblink/ [root@localhost dblink]# ls dblink--1.0--1.1.sql dblink--1.1--1.2.sql dblink--1.2.sql dblink.c dblink.control dblink--unpackaged--1.0.sql expected input Makefile output pg_service.conf sql [root@localhost dblink]# [root@localhost dblink]# make make -C ../../src/backend generated-headers make[1]: Entering directory `/data/source/postgresql-12beta1/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/utils' make[1]: Leaving directory `/data/source/postgresql-12beta1/src/backend' make -C ../../src/interfaces/libpq all make[1]: Entering directory `/data/source/postgresql-12beta1/src/interfaces/libpq' make -C ../../../src/port all make[2]: Entering directory `/data/source/postgresql-12beta1/src/port' make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/data/source/postgresql-12beta1/src/port' make -C ../../../src/common all make[2]: Entering directory `/data/source/postgresql-12beta1/src/common' make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/data/source/postgresql-12beta1/src/common' make[1]: Leaving directory `/data/source/postgresql-12beta1/src/interfaces/libpq' gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I../../src/interfaces/libpq -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o dblink.o dblink.c -MMD -MP -MF .deps/dblink.Po gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -shared -o dblink.so dblink.o -L../../src/port -L../../src/common -L../../src/interfaces/libpq -lpq -Wl,--as-needed -Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags [root@localhost dblink]# make install make -C ../../src/backend generated-headers make[1]: Entering directory `/data/source/postgresql-12beta1/src/backend' make -C catalog distprep generated-header-symlinks make[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/catalog' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/catalog' make -C utils distprep generated-header-symlinks make[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/utils' make[2]: Nothing to be done for `distprep'. make[2]: Nothing to be done for `generated-header-symlinks'. make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/utils' make[1]: Leaving directory `/data/source/postgresql-12beta1/src/backend' make -C ../../src/interfaces/libpq all make[1]: Entering directory `/data/source/postgresql-12beta1/src/interfaces/libpq' make -C ../../../src/port all make[2]: Entering directory `/data/source/postgresql-12beta1/src/port' make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/data/source/postgresql-12beta1/src/port' make -C ../../../src/common all make[2]: Entering directory `/data/source/postgresql-12beta1/src/common' make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/data/source/postgresql-12beta1/src/common' make[1]: Leaving directory `/data/source/postgresql-12beta1/src/interfaces/libpq' /usr/bin/mkdir -p '/appdb/xdb/pg12beta1/lib/postgresql' /usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension' /usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension' /usr/bin/install -c -m 755 dblink.so '/appdb/xdb/pg12beta1/lib/postgresql/dblink.so' /usr/bin/install -c -m 644 ./dblink.control '/appdb/xdb/pg12beta1/share/postgresql/extension/' /usr/bin/install -c -m 644 ./dblink--1.2.sql ./dblink--1.1--1.2.sql ./dblink--1.0--1.1.sql ./dblink--unpackaged--1.0.sql '/appdb/xdb/pg12beta1/share/postgresql/extension/' [root@localhost dblink]# ``` **简介** dblink可以让PG具备在远程数据库上执行查询的能力,其语法如下: ``` dblink(text connname, text sql [, bool fail_on_error]) returns setof record dblink(text connstr, text sql [, bool fail_on_error]) returns setof record dblink(text sql [, bool fail_on_error]) returns setof record ``` 其中,输入参数:connname为连接名称,connstr是连接串,sql为查询语句,fail_on_error标记出现错误是否停止;输出参数是产生的行,Record类型.由于返回没有列信息,因此使用时需指定columns信息. **使用** 远程PG数据库部署在26.26上,希望在PG中查询该26.26实例testdb数据库public模式下的t1表. ``` testdb=# select * from t1; id ---- 1 (1 row) testdb=# \d+ t1 Table "public.t1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id | integer | | | | plain | | testdb=# select * from t1; id ---- 1 (1 row) ``` 在本机执行以下查询: ``` testdb=# create extension dblink; CREATE EXTENSION testdb=# select t1.* from dblink('host=192.168.26.26 port=5432 dbname=testdb user=xdb','select * from t1') as t1(id int); id ---- 1 (1 row) ``` 由于需要显式的指定的返回数据行的元数据,使用起来有些啰嗦. **参考资料** [Using DbLink to access other PostgreSQL Databases and Servers](https://www.postgresonline.com/journal/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html) [Implementing Autonomous Transactions in Postgres](https://www.cybertec-postgresql.com/en/implementing-autonomous-transactions-in-postgres/) [PG doc : dblink](https://www.postgresql.org/docs/11/contrib-dblink-function.html)