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

PostgreSQL 9.3Beta1：支持可写的外部表(Writeable Foreign Tables)

• 数据库

PostgreSQL 9.3 新增的一个主要特性是支持可写的外部表 (foreign tables)，而在 9.3 版本之前外部表只读，关于外部表的使用可以参考之前的 blog：

  1. pgsql_fdw
  2. oracle_fdw
  3. mysql_fdw
  4. file_fdw

在之前版本要实现跨库操作，一般通过 dblink 或者 pgsql_fdw ，而 dblink 用起来非常不方便；9.3 版本支持外部表可写，对跨库操作的支持进一步提升，越来越像 oracle 的 dblink 了，接下来演示下：

源库数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
[pg93@redhatB pg_root]$ psql source_db source_db
psql (9.3beta1)
Type "help" for help.
  
source_db=> create table test_1 (id int4 primary key ,name varchar(32),amount numeric);
CREATE TABLE
  
source_db=> insert into test_1 values (1,'a',100);
INSERT 0 1
source_db=> insert into test_1 values (2,'b',200);
INSERT 0 1
source_db=> insert into test_1 values (3,'c',300);
INSERT 0 1
  
source_db=> select * from test_1;
 id | name | amount 
----+------+--------
  1 | a    |    100
  2 | b    |    200
  3 | c    |    300
(3 rows)

安装并配置 postgres_fdw

2.1 安装 postgres_fdw

1
2
 
francs=# create extension postgres_fdw;
CREATE EXTENSION

备注：详细内容参考之前的 blog: PostgreSQL 9.3Beta1: 新增 postgres_fdw 外部模块。

2.2 creater server 并赋权

1
2
3
4
5
6
7
 
source_db=> /c francs postgres
francs=# CREATE SERVER srv_source_db FOREIGN DATA WRAPPER postgres_fdw 
francs-# OPTIONS (host '127.0.0.1', port '1925', dbname 'source_db');
CREATE SERVER
  
francs=# grant usage on foreign server srv_source_db to francs;
GRANT

2.3 CREATE MAPPING USER

1
2
3
 
francs=# CREATE USER MAPPING FOR francs SERVER srv_source_db
francs-# OPTIONS (user 'source_db', password 'source_db');
CREATE USER MAPPING

2.4 创建外部表

1
2
3
4
5
6
7
 
francs=> CREATE FOREIGN TABLE ft_test_1 (
francs(>  id    integer , 
francs(>  name  character varying(32), 
francs(>  amount numeric             ) 
francs->  SERVER srv_source_db
francs->  OPTIONS (schema_name 'source_db',table_name 'test_1');
 CREATE FOREIGN TABLE

2.5 查询测试

1
2
3
4
 
francs=> select * from ft_test_1;
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a password.
HINT:  Target server s authentication method must be changed.

备注：如果遇到这个错误，在 pg_hba.conf 增加一条策略：红色那条为新增，文件内容未全部列出。修改之后执行 pg_ctl reload 命令。 

1
2
3
 
# IPv4 local connections:
host    source_db       source_db       127.0.0.1/32            md5
host    all             all             127.0.0.1/32            trust

2.6 再次查询测试

1
2
3
4
5
6
7
 
 francs=> select * from ft_test_1;
 id | name | amount 
----+------+--------
  1 | a    |    100
  2 | b    |    200
  3 | c    |    300
(3 rows)

备注：以上已经安装并配置好了 postgres_fdw ，接着测试外部表的可写情况。

外部表可写测试

3.1 插入测试
3.1.1 francs 库增加一条数据

1
2
3
4
5
6
7
8
9
10
11
 
francs=> insert into ft_test_1 values (4,'d',400);
INSERT 0 1
  
francs=> select * from ft_test_1;
 id | name | amount 
----+------+--------
  1 | a    |    100
  2 | b    |    200
  3 | c    |    300
  4 | d    |    400
(4 rows)

3.1.2 source_db 库验证

1
2
3
4
5
6
7
8
9
10
11
 
francs=> /c source_db source_db
You are now connected to database "source_db" as user "source_db".
  
source_db=> select * from test_1;
 id | name | amount 
----+------+--------
  1 | a    |    100
  2 | b    |    200
  3 | c    |    300
  4 | d    |    400
(4 rows)

备注：验证 OK。

3.2 删除测试
3.2.1 francs 库删除一条数据

1
2
3
4
5
6
7
8
9
10
 
francs=> delete from ft_test_1 where id=1;
DELETE 1
  
francs=> select * from ft_test_1 ;
 id | name | amount 
----+------+--------
  2 | b    |    200
  3 | c    |    300
  4 | d    |    400
(3 rows)

3.2.2 source_db 库验证

1
2
3
4
5
6
7
8
9
10
 
francs=> /c source_db source_db;
You are now connected to database "source_db" as user "source_db".
  
source_db=> select * from test_1 ;
 id | name | amount 
----+------+--------
  2 | b    |    200
  3 | c    |    300
  4 | d    |    400
(3 rows)

备注：验证 OK。同理 update 测试也正常，这里不介绍了。

事务测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
francs=> begin;
BEGIN
francs=> insert into ft_test_1 values (5,'e',500);
INSERT 0 1
  
francs=> select * from ft_test_1;
 id | name | amount 
----+------+--------
  3 | c    |    300
  4 | d    |    400
  2 | b    |   2000
  5 | e    |    500
(4 rows)
  
francs=> rollback;
ROLLBACK
  
francs=> select * from ft_test_1;
 id | name | amount 
----+------+--------
  3 | c    |    300
  4 | d    |    400
  2 | b    |   2000
(3 rows)

备注：远程操作依然支持事务。

参考

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

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

相关推荐

发表回复

登录后才能评论