[toc]

准备工作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
=== 远端数据库 ==
create database testdb;
\c testdb
create user testdblink password 'Enmotech@123';
grant all on database testdb to testdblink ;
\c testdb testdblink
create table testdblink.testdata as select generate_series(1,100) as id ;
=== 本地数据库
create database liups;
\c liups
create user liups password 'Password123';
grant all on database liups to liups ;
create user liups1 password 'Password123';
grant all on database liups to liups1 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
=== 远端数据库
MogDB=# create database testdb;
CREATE DATABASE
MogDB=# \c testdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "fomm".
testdb=# create user testdblink password 'Enmotech@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
testdb=# grant all on database testdb to testdblink
testdb-# \c testdb testdblink
Password for user testdblink:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "testdblink".
testdb=> create table testdblink.testdata as select generate_series(1,100) as id ;
INSERT 0 100

==本地数据库
MogDB=# create database liups;
CREATE DATABASE
MogDB=# \c liups
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "liups" as user "fomm".
liups=# create user liups password 'Password123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
liups=# grant all on database liups to liups ;
GRANT
liups=#

pg_hba.conf 配置允许远程登录，这里在 配置文件新增如下：

1
host all all 0.0.0.0/0 md5

验证远端数据库可以远程登录

1
2
3
4
5
6
7
8
9
[fomm@cnwww ~]$ gsql -h172.20.62.166 -Utestdblink testdb
Password for user testdblink:
gsql ((MogDB 5.0.8 build 2ede45c9) compiled at 2024-06-30 12:43:48 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testdb=> \conninfo
You are connected to database "testdb" as user "testdblink" on host "172.20.62.166" at port "27000".
testdb=>

安装和创建对应插件(create extension)

postgres_fdw 插件默认包含在安装包中，因此无需专门安装，只需要创建出来即可：

1
2
===本地数据库===
create extension postgres_fdw;

注意事项：创建插件需要使用管理员权限的账号创建：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
liups=> create extension postgres_fdw;
ERROR:  permission denied to create extension "postgres_fdw"
HINT:  Must be system admin to create this extension.
liups=> \c liups fomm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "liups" as user "fomm".
liups=# create extension postgres_fdw;
CREATE EXTENSION
liups=# \dx
                                    List of installed extensions
      Name       | Version |   Schema   |                        Description
-----------------+---------+------------+-------------------------------------------------
 dist_fdw        | 1.0     | pg_catalog | foreign-data wrapper for distfs access
 file_fdw        | 1.0     | pg_catalog | foreign-data wrapper for flat file access
 hstore          | 1.1     | pg_catalog | data type for storing sets of (key, value) pairs
 log_fdw         | 1.0     | pg_catalog | Foreign Data Wrapper for accessing logging data
 plpgsql         | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw    | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
 security_plugin | 1.0     | pg_catalog | provides security functionality
 tidrangescan    | 1.0     | pg_catalog | example implementation for custom-scan-provider interface
(8 rows)
liups=#

创建远程数据库链接(create server)，指定对端数据库的物理信息

1
2
===本地数据库===
create server testDBLink_mogdb1 foreign data wrapper postgres_fdw options(host '172.20.62.166', port '27000',dbname 'testdb');
1
2
3
4
5
liups=> create server testDBLink_mogdb1 foreign data wrapper postgres_fdw options(host '172.20.62.166', port '27000',dbname 'testdb');
ERROR:  permission denied for foreign-data wrapper postgres_fdw
DETAIL:  N/A
liups=> Connection to cn.liups.com closed by remote host.
Connection to cn.liups.com closed.

注意事项：创建远程数据库链接(create server)可以通过管理员进行创建，或者授予 ：GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO liups; 权限来创建。

1
2
3
4
5
6
7
8
9
liups=# GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO liups;
GRANT
liups=# \c liups liups
Password for user liups:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "liups" as user "liups".
liups=> create server testDBLink_mogdb1 foreign data wrapper postgres_fdw options(host '172.20.62.166', port '27000',dbname 'testdb');
CREATE SERVER
liups=>

目前测试给 USAGE 权限即可，当然如果给 ALL PRIVILEGES 那更可以。

1
GRANT ALL PRIVILEGES on FOREIGN DATA WRAPPER postgres_fdw TO testuser;

创建用户映射关系(create user mapping)，指定对端数据库的认证信息

注意，在做这一步之前，如果之前没有创建过针对usermapping的密码保护，则需要执行以下语句

1
2
3
4
5
6
7
8
9
10
11
[root@cnwww ~]# su - fomm
Last login: Wed Jul  3 10:04:55 CST 2024 on pts/0
[fomm@cnwww ~]$ ls -l $GAUSSHOME/bin/user*
ls: cannot access /fopt/mogdb/app/bin/user*: No such file or directory
[fomm@cnwww ~]$ gs_guc generate -o usermapping -S default -D $GAUSSHOME/bin
The gs_guc run with the following arguments: [gs_guc -o usermapping -S ******* -D /fopt/mogdb/app/bin generate ].
gs_guc generate -S ***
[fomm@cnwww ~]$ ls -l $GAUSSHOME/bin/user*
-rw------- 1 fomm fomm 56 Jul  3 10:07 /fopt/mogdb/app/bin/usermapping.key.cipher
-rw------- 1 fomm fomm 24 Jul  3 10:07 /fopt/mogdb/app/bin/usermapping.key.rand
[fomm@cnwww ~]$
1
2
===本地数据库的操作系统用户下===
gs_guc generate -o usermapping -S default -D $GAUSSHOME/bin

创建用户映射关系：

1
create user mapping for public server testDBLink_mogdb1 options(user 'testdblink',password 'Enmotech@123');
1
2
3
liups=> create user mapping for public server testDBLink_mogdb1 options(user 'testdblink',password 'Enmotech@123');
CREATE USER MAPPING
liups=>

这里的user ‘testdblink’,password 'Enmotech@123’对应远端数据库用户名密码。同时，为了简化，直接用了public，其实也可以改为特定的本地用户(for username)，改了之后只有指定用户才能用这个用户密码连接到远程数据库。

开始使用

1
2
3
4
5
6
7
liups=> select * from testdblink.testdata@testDBLink_mogdb1;
 id
-----
 1
 2
 3
 …………

注意事项：这里需要 添加 schema. 否则会报：remote table "xxx" does not exist

1
2
liups=> select * from  testdata@testDBLink_mogdb1;
ERROR:  remote table "testdata" does not exist

原文作者: Hi.MogDB

原文链接: https://hi.mogdb.org/posts/4dfd7462/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议