dblink 测试

select * from pg_extension; 创建插件 create extension if not exists dblink; postgres=> select * from pg_extension;   oid  |   extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition  -------+-------------+----------+--------------+----------------+------------+-----------+--------------  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   4   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   4   4   2   3 dblink_cancel_query — 在命名连接上取消任何活动查询 SELECT dblink_cancel_query('dblinktest'); postgres=# SELECT dblink_cancel_query('dblinktest');  dblink_cancel_query  ---------------------  OK (1 row)