select * from pg_extension;

创建插件

create extension if not exists dblink;

postgres=> select * from pg_extension;

-------+-------------+----------+--------------+----------------+------------+-----------+--------------

14173 | plpgsql | 10 | 11 | f | 1.0 | |

16567 | pgstattuple | 10 | 2200 | t | 1.5 | |

24759 | dblink | 10 | 2200 | t | 1.2 | |

(3 rows)

postgres=> \c

You are now connected to database "postgres" as user "tt".

postgres=> \du

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

tt | | {}

zc | Superuser | {}

开启dblink ：

postgres=# select dblink_connect('dblinktest','host=192.168.75.2 dbname=zc user=zc password=zc port=5432');

dblink_connect

----------------

OK

(1 row)

通过dblink 查询：

postgres=# select * from dblink('dblinktest', 'select * from t2') as t(id int, name varchar(32));

id | name

----+------

1 | zc

(1 row)

关闭dblink ：

postgres=# SELECT dblink_disconnect('dblinktest');

dblink_disconnect

-------------------

OK

(1 row)

再次查询

postgres=# select * from dblink('dblinktest', 'select * from t2') as t(id int, name varchar(32));

ERROR: could not establish connection

DETAIL: missing "=" after "dblinktest" in connection info string

授予tt用户创建不安全的链接权限

GRANT all ON FUNCTION dblink_connect_u(text) TO tt;

GRANT all ON FUNCTION dblink_connect_u(text, text) TO tt;

进程测试

postgres=> SELECT dblink_connect_u('myconn', 'dbname=zc options=-csearch_path=');

dblink_connect_u

------------------

OK

(1 row)

postgres=> select * from dblink('myconn', 'select * from t2') as t(id int, name varchar(32));

ERROR: relation "t2" does not exist

CONTEXT: while executing query on dblink connection named "myconn"

postgres=> select * from dblink('myconn', 'select * from public.t2') as t(id int, name varchar(32));

id | name

----+------

1 | zc

(1 row)

可以发现不用密码就能查询了。

超级用户使用安全连接可以不用密码查询，但是普通用户不行

超级用户：

postgres=# SELECT dblink_connect('myconn1', 'dbname=zc options=-csearch_path=');

dblink_connect

----------------

OK

(1 row)

postgres=#

postgres=# select * from dblink('myconn1', 'select * from public.t2') as t(id int, name varchar(32));

id | name

----+------

1 | zc

(1 row)

普通用户：

无法创建无密码的链接。

postgres=> SELECT dblink_connect('myconn1', 'dbname=zc options=-csearch_path=');

ERROR: password is required

DETAIL: Non-superusers must provide a password in the connection string.

postgres=>

dblink — 在一个远程数据库中执行一个查询

当给定两个text参数时，第一个被首先作为一个持久连接的名称进行查找；如果找到，该命令会在该连接上被执行。

如果没有找到，第一个参数被视作一个用于dblink_connect的连接信息字符串，并且被指出的连接只是在这个命令的持续期间被建立。

普通用户需要输入密码，root可以不用密码：

普通用户：

postgres=> SELECT *

postgres-> FROM dblink('dbname=zc options=-csearch_path=',

postgres(> 'select proname, prosrc from pg_proc')

postgres-> AS t1(proname name, prosrc text)

postgres-> WHERE proname LIKE 'bytea%';

ERROR: password is required

DETAIL: Non-superusers must provide a password in the connection string.

postgres=>

超级用户：

zc=# SELECT *

zc-# FROM dblink('dbname=zc options=-csearch_path=',

zc(# 'select proname, prosrc from pg_proc')

zc-# AS t1(proname name, prosrc text)

zc-# WHERE proname LIKE 'bytea%';

proname | prosrc

--------------------------+--------------------------

byteain | byteain

byteaout | byteaout

bytea_string_agg_transfn | bytea_string_agg_transfn

bytea_string_agg_finalfn | bytea_string_agg_finalfn

byteaeq | byteaeq

bytealt | bytealt

byteale | byteale

byteagt | byteagt

byteage | byteage

byteane | byteane

byteacmp | byteacmp

bytea_sortsupport | bytea_sortsupport

bytealike | bytealike

byteanlike | byteanlike

byteacat | byteacat

bytearecv | bytearecv

byteasend | byteasend

普通用户使用dblink 命令的两个方式：

方式1：

SELECT *

FROM dblink('host=192.168.75.2 dbname=zc user=zc password=zc port=5432',

'select proname, prosrc from pg_proc')

AS t1(proname name, prosrc text)

WHERE proname LIKE 'bytea%';

普通用户使用上面的sql：

postgres=> SELECT *

postgres-> FROM dblink('host=192.168.75.2 dbname=zc user=zc password=zc port=5432',

postgres(> 'select proname, prosrc from pg_proc')

postgres-> AS t1(proname name, prosrc text)

postgres-> WHERE proname LIKE 'bytea%';

proname | prosrc

--------------------------+--------------------------

byteain | byteain

byteaout | byteaout

bytea_string_agg_transfn | bytea_string_agg_transfn

bytea_string_agg_finalfn | bytea_string_agg_finalfn

byteaeq | byteaeq

bytealt | bytealt

byteale | byteale

byteagt | byteagt

byteage | byteage

byteane | byteane

方式2：

SELECT *

FROM dblink('dblinktest',

'select proname, prosrc from pg_proc')

AS t1(proname name, prosrc text)

WHERE proname LIKE 'bytea%';

postgres=> select dblink_connect('dblinktest','host=192.168.75.2 dbname=zc user=zc password=zc port=5432');

dblink_connect

----------------

OK

(1 row)

postgres=> SELECT *

postgres-> FROM dblink('dblinktest',

postgres(> 'select proname, prosrc from pg_proc')

postgres-> AS t1(proname name, prosrc text)

postgres-> WHERE proname LIKE 'bytea%';

proname | prosrc

--------------------------+--------------------------

byteain | byteain

byteaout | byteaout

bytea_string_agg_transfn | bytea_string_agg_transfn

bytea_string_agg_finalfn | bytea_string_agg_finalfn

dblink_exec 进行增删改

postgres=> select dblink_exec('dblinktest', 'insert into t1 (id) values (4)');

dblink_exec

-------------

INSERT 0 1

(1 row)

postgres=> select * from dblink('dblinktest', 'select * from t1') as t(id int);

id

----

4

2

3

(3 rows)

dblink_open 打开游标

SELECT dblink_open('dblinktest', 'select * from t1');

dblink_fetch从一个之前由dblink_open建立的游标中取得行。

SELECT dblink_open('dblinktest', 'select * from public.t1');

SELECT * FROM dblink_fetch('dblinktest', 3) AS (id int);

超级用户测试通过，

postgres=# SELECT dblink_open('dblinktest', 'select * from public.t1');

dblink_open

-------------

OK

(1 row)

postgres=# SELECT * FROM dblink_fetch('dblinktest', 3) AS (id int);

id

----

4

(3 rows)

关闭游标

SELECT dblink_close('dblinktest');

普通用户报错：

postgres=> SELECT dblink_open('dblinktest', 'select * from public.t1');

ERROR: connection not available

怀疑权限问题，没法打开游标

查询当前的dblink

SELECT dblink_get_connections();

postgres=# SELECT dblink_get_connections();

dblink_get_connections

----------------------------------

{myconn1,dblinktest,dblinktest1}

异步查询：

SELECT dblink_send_query('dblinktest', 'SELECT * FROM t1');

postgres=# SELECT dblink_send_query('dblinktest', 'SELECT * FROM t1');

dblink_send_query

-------------------

1

(1 row)

如果查询被成功地派送返回 1，否则返回 0。

dblink_is_busy — 检查连接是否正在忙于一个异步查询

SELECT dblink_is_busy('dblinktest');

postgres=# SELECT dblink_is_busy('dblinktest');

dblink_is_busy

----------------

0

dblink_get_result — 得到一个异步查询结果

SELECT * FROM dblink_get_result('dblinktest') AS t1(id int);

postgres=# SELECT * FROM dblink_get_result('dblinktest') AS t1(id int);

id

----

4

2

3

dblink_cancel_query — 在命名连接上取消任何活动查询

SELECT dblink_cancel_query('dblinktest');

postgres=# SELECT dblink_cancel_query('dblinktest');

dblink_cancel_query

---------------------

OK

(1 row)

ITPub博客

dblink 测试

最新文章