lightdb 支持语法 @dblink 访问外部数据库 oracle/lightdb

背景

在业务产品中，存在 Oracle 移植过来的 SQL 语句。它们使用 @dblink（表名@外部服务器名） 对外部数据库进行访问(select delete insert)。如 SELECT * FROM database_info@oracle_server_fdw; 查询 oracle 数据库表数据功能。lightdb 23.4 版本对此进行了支持。

lightdb 支持语法 @dblink 访问外部数据库 oracle/lightdb

在 lightdb 23.4 版本以后，lightdb 的 oracle 模式兼容了 oracle 通过 @dblink 语法跨库访问 oracle 数据库。同时 lightdb 的 oracle 模式还支持了通过 @dblink 语法跨库访问 lightdb 数据库。该功能需要使用 oracle_fdw，postgres_fdw 扩展，通过外部表进行访问。以下为使用示例

@dblink方式访问oracle

1.创建oracle_fdw扩展

CREATE EXTENSION oracle_fdw;

2.创建外部服务器

CREATE SERVER oracle_server_fdw FOREIGN DATA WRAPPER oracle_fdw 
OPTIONS (dbserver '//ip:1521/test');

3.创建用户映射（用户名大写）

CREATE USER MAPPING FOR USER server oracle_server_fdw 
OPTIONS(user 'SYSTEM', password 'oracle');

4.设置权限

GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw TO CURRENT_USER;

GRANT USAGE ON FOREIGN SERVER oracle_server_fdw TO CURRENT_USER;

5.创建外部表（SCHEMA TABLE 大写 且已存在）

CREATE FOREIGN TABLE oracle_server_fdw_database_info(
  id INT OPTIONS (key 'true')  NOT NULL
  ) 
  SERVER oracle_server_fdw 
  OPTIONS(SCHEMA 'HR', TABLE 'A');

6.执行查询语句

SELECT * FROM database_info@oracle_server_fdw;

id

1
2
3
4
(4 rows)

@dblink方式访问lightdb

1.创建一个被连接的数据库（pg，mysql，oracle均可）

CREATE DATABASE link_pg_db;

2.进去数据库

\c link_pg_db

3.创建用户

CREATE USER mumu;

4.创建 SCHEMA

CREATE SCHEMA mumu;

5.赋予权限

GRANT ALL ON SCHEMA mumu TO mumu;

6.修改用户密码

ALTER USER mumu WITH password 'mumu123456';

7.设置当前用户

SET ROLE mumu;

8.创建一个被访问的表并加入数据

CREATE TABLE mumu.database_info (
  id INT,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL
);
INSERT INTO mumu.database_info (id, name, age) 
VALUES (1, '张三', 25);
INSERT INTO mumu.database_info (id, name, age) 
VALUES (2, '李四', 125);
INSERT INTO mumu.database_info (id, name, age) 
VALUES (3, '王五', 65);
INSERT INTO mumu.database_info (id, name, age) 
VALUES (4, '赵六', 15);
INSERT INTO mumu.database_info (id, name, age) 
VALUES (5, '陈七', 35);

9.回到原数据库

\c postgresql

10.创建 oracle模式数据库

CREATE DATABASE link_pg_db_oracle
WITH lightdb_syntax_compatible_type = 'oracle' ;

11.进入oracle模式数据库

\c link_pg_db_oracle

12.创建pg扩展

CREATE EXTENSION postgres_fdw;

13.创建外部服务器

连接自己的

CREATE SERVER db100_fdw FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS(dbname 'link_pg_db');

连接指定ip的

CREATE SERVER db100_fdw FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS( host 'ip', dbname 'link_pg_db', port '5432');

14.创建用户映射

CREATE USER MAPPING FOR USER SERVER db100_fdw 
OPTIONS(user 'mumu', password 'mumu123456');

15.创建外部表

CREATE FOREIGN TABLE db100_fdw_database_info(
  id INT,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL
  ) 
  SERVER db100_fdw 
  OPTIONS(schema_name 'mumu',table_name 'database_info');

16.访问外部表

SELECT * FROM database_info@db100_fdw;

id | name | age
----±-----±----
1 | 张三 | 25
2 | 李四 | 125
3 | 王五 | 65
4 | 赵六 | 15
5 | 陈七 | 35
(5 rows)