1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL: Using pgsql_fdw connect remote PostgteSQL DB

• 数据库

今天在从 德哥 blog 中发现 PostgreSQL9.1 外部表支持另一个模块，即 pgsql_fdw ，使用这个模块可以方便地在一个 PG 库中访问远程 PG 库中的表，当然是以创建外部表的形式， 这种方法可以近似地理解成 dblink ，关于PostgreSQL 中的 dblink 可以参考以下文章：

接下来演示下 pgsql_fdw 的实验过程。

pgsql_fdw 安装

1.1 Requirement
PostgreSQL 9.1 or later

1.2 下载
https://build.opensuse.org/package/files?package=pgsql_fdw&project=home%3Adeadpoint

1.3 解压

1
 
[postgres@pgb extension]$ tar zxvf pgsql_fdw-1.0.tar.gz

1.4 编译

1
2
3
4
5
6
7
 
[postgres@pgb pgsql_fdw-1.0]$ make USE_PGXS=1  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o pgsql_fdw.o pgsql_fdw.c  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o option.o option.c  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o deparse.o deparse.c  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o connection.o connection.c  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I/opt/pgsql9.1/include -I. -I. -I/opt/pgsql9.1/include/server -I/opt/pgsql9.1/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o ruleutils.o ruleutils.c  
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o pgsql_fdw.so pgsql_fdw.o option.o deparse.o connection.o ruleutils.o -L/opt/pgsql9.1/lib -L/usr/lib -Wl,-rpath,'/opt/pgsql9.1/lib',--enable-new-dtags -L/opt/pgsql9.1/lib -lpq

1.5 安装

1
2
3
4
5
 
[postgres@pgb pgsql_fdw-1.0]$ make USE_PGXS=1 install/bin/mkdir -p '/opt/pgsql9.1/lib'  
/bin/mkdir -p '/opt/pgsql9.1/share/extension'  
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 pgsql_fdw.so '/opt/pgsql9.1/lib/pgsql_fdw.so'  
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./pgsql_fdw.control '/opt/pgsql9.1/share/extension/'  
/bin/sh /opt/pgsql9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./pgsql_fdw--1.0.sql '/opt/pgsql9.1/share/extension/'

pgsql_fdw 演示

准备访问同一台 PostgreSQLServer中的另外一个库的表。

源库: 127.0.0.1/1921 mydb
目标库 127.0.0.1/1921 skytf

2.1 创建 extension

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
[postgres@pgb pgsql_fdw-1.0]$ psql skytf postgres  
psql (9.1.0)  
Type "help" for help.
skytf=# create extension pgsql_fdw;  
CREATE EXTENSION

skytf=# /dx  
 List of installed extensions  
 Name | Version | Schema | Description  
-----------+---------+------------+----------------------------------------------------  
file_fdw | 1.0 | public | foreign-data wrapper for flat file access  
mysql_fdw | 1.0 | public | Foreign data wrapper for querying a MySQL server  
pgsql_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers  
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language  
(4 rows)

备注： pgsql_fdw 已创建。

2.2 创建 server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
skytf=# grant usage on foreign data wrapper pgsql_fdw to skytf;  
GRANT

skytf=# /c skytf skytf  
You are now connected to database "skytf" as user "skytf".

CREATE SERVER pgsql_srv FOREIGN DATA WRAPPER pgsql_fdw  
OPTIONS (host '127.0.0.1', port '1923', dbname 'mydb');

skytf=> /des  
 List of foreign servers  
 Name | Owner | Foreign-data wrapper  
--------------+-------+----------------------  
file_srv | skytf | file_fdw  
mysql_svr_25 | skytf | mysql_fdw  
pgsql_srv | skytf | pgsql_fdw  
(3 rows)

备注： pgsql_srv 已创建。

2.3 create mapping user

1
2
3
 
CREATE USER MAPPING FOR public SERVER pgsql_srv  
OPTIONS (user 'mydb', password 'mydb');  
CREATE USER MAPPING

2.4 create foreign table

1
2
3
4
5
6
 
skytf=> CREATE FOREIGN TABLE ft_test (  
skytf(> id integer ,  
skytf(> name character varying(32) )  
skytf-> SERVER pgsql_srv  
skytf-> OPTIONS (nspname 'mydb', relname 'test');  
CREATE FOREIGN TABLE

2.5 查询测试

1
2
3
4
5
 
skytf=> select * from ft_test limit 1;  
 id | name  
------+------  
5024 | AAAA  
(1 row)

2.6 执行计划

1
2
3
4
5
6
7
8
 
skytf=> explain analyze select * from ft_test where id=1;  
 QUERY PLAN  
------------------------------------------------------------------------------------------------------------  
Foreign Scan on ft_test (cost=100.00..111.46 rows=2 width=8) (actual time=2.190..2.190 rows=0 loops=1)  
 Filter: (id = 1)  
 Remote SQL: DECLARE pgsql_fdw_cursor_10 SCROLL CURSOR FOR SELECT id, name FROM mydb.test WHERE (id = 1)  
Total runtime: 3.092 ms  
(4 rows)

备注：根据where 条件查询， pgsql_fdw 会将查询语句发送到目标远程 PG 库，并且根据文档描述，pgsql_fdw 优化了以下： 

1. 减少了远程数据传输； 
2. 将 where 条件子表发送到目标服务器。

统计外部表记录总数，执行计划如下：

1
2
3
4
5
6
7
8
 
skytf=> explain analyze select count(*) from ft_test ;  
 QUERY PLAN  
----------------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=33446.70..33446.71 rows=1 width=0) (actual time=27718.201..27718.206 rows=1 loops=1)  
 -> Foreign Scan on ft_test (cost=100.00..28553.56 rows=1957256 width=0) (actual time=40.259..15705.231 rows=1990000 loops=1)  
 Remote SQL: DECLARE pgsql_fdw_cursor_8 SCROLL CURSOR FOR SELECT NULL, NULL FROM mydb.test  
Total runtime: 27718.583 ms  
(4 rows)

2.7 Connection management

1
2
3
4
5
 
skytf=> select * From pgsql_fdw_connections;  
srvid | srvname | usesysid | usename  
-------+-----------+----------+---------  
74984 | pgsql_srv | 41818 | skytf  
(1 row)

2.8 查看系统进程

1
2
3
 
[postgres@pgb pg_root]$ ps -ef | grep mydb  
postgres 18454 31854 0 10:37 ? 00:00:00 postgres: mydb mydb 127.0.0.1(25949) idle in transaction  
postgres 18514 31854 1 10:55 ? 00:00:00 postgres: mydb mydb 127.0.0.1(46007) idle in transaction

备注：在一个 session 中查看外部表是以开启事务方式进行，直到退出当前 session 或者切换用户，事务才结束。

常见 ERROR

3.1 查询外部表时报错

1
2
3
4
5
6
7
8
 
[postgres@pgb ~]$ psql skytf skytf  
psql (9.1.0)  
Type "help" for help.

skytf=> select * from ft_test limit 1;  
ERROR: password is required  
DETAIL: Non-superuser cannot connect if the server does not request a password.  
HINT: Target server is authentication method must be changed.

备注：上面报错是因为 pg_hba.conf 文件认证问题，本机配置的是 local 连接所有库都是 trust 方式，即不需要密码，而 pgsql_fdw 连接时需要提供用户名和密码。

解决方法
修改 pg_hba.conf，增加红色字体行，并 reload
pg_hba.conf 文件

1
2
3
4
5
6
 
# IPv4 local connections:  
host mydb mydb 127.0.0.1/32 md5  
host all all 127.0.0.1/32 trust
reload 配置文件
[postgres@pgb pg_root]$ pg_ctl reload -D $PGDATA  
server signaled

pgsql_fdw 外部表限制

  1. 外部表只读，其它修改操作不允许，例如 update，delete，INSERT 等，并且外部表也不会被 vacuum。
  2. 当开启 session 查询外部表时， pgsql_fdw 以事务方式开启，即便当前 session 空闲，事务也没有提交，直到退出当前 session 或者切换到其它用户后，事务。

参考

原创文章，作者：6024010，如若转载，请注明出处：https://blog.ytso.com/237867.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论