tencent cloud

文档反馈

跨库访问

最后更新时间:2024-03-20 11:01:39
    云数据库 PostgreSQL 提供用于访问外部数据源的一类插件,外部数据源包括本实例其他库中数据或者其他实例的数据。跨库访问插件包含同构的跨库访问插件 dblink、postgresql_fdw,异构的跨库访问插件 mysql_fdw、cos_fdw。跨库访问使用步骤如下:
    1. 使用 “CREATE EXTENSION” 语句安装插件。
    2. 为每个需要连接的远程数据库创建一个外部服务器对象并创建链接映射。
    3. 使用对应的命令访问外部表以获取数据。
    由于跨库访问插件可以直接跨实例访问或在同实例中进行跨 database 访问。云数据库 PostgreSQL 对创建外部服务器对象时进行了权限控制优化,根据目标实例所在环境进行分类管理。在开源版本基础上增加了额外辅助参数,来验证用户身份和调整网络策略。具体请参考下文 插件辅助参数
    说明:
    dblink 插件当前只有大版本为10的云数据库 PostgreSQL 内核支持,请知悉。

    插件辅助参数

    host 跨实例访问时候为必须项。目标实例的 IP 地址。
    port 跨实例访问时候为必须项。目标实例的 port。
    instanceid 实例 ID
    在云数据库 PostgreSQL 间跨实例访问时使用,当跨实例访问时为必选项。格式类似 postgres-xxxxxx、pgro-xxxxxx,可在 控制台 查看。
    如果目标实例在腾讯云 CVM 上,则为 CVM 机器的实例 ID,格式类似 ins-xxxxx。
    dbname database 名,填写需要访问的远端 PostgreSQL 服务的 database 名字。若不跨实例访问,仅在同实例中进行跨库访问,则只需要配置此参数即可,其他参数都可为空。
    access_type 非必须项。目标实例所属类型:
    目标实例为 TencentDB 实例,包括云数据库 PostgreSQL、云数据库 MySQL 等,如果不显示指定,则默认该项。
    目标实例在腾讯云 CVM 机器上。
    目标实例为腾讯云外网自建。
    目标实例为云 VPN 接入的实例。
    目标实例为自建 VPN 接入的实例。
    目标实例为专线接入的实例。
    uin 非必须项。实例所属的账号 ID,通过该信息鉴定用户权限,可参见 查询 uin
    own_uin 非必须项。实例所属的主账号 ID,同样需要该信息鉴定用户权限。
    vpcid 非必须项。私有网络 ID,目标实例如果在腾讯云 CVM 的 VPC 网络中,则需要提供该参数,可在 VPC 控制台 中查看。
    subnetid 非必须项。私有网络子网 ID,目标实例如果在腾讯云 CVM 的 VPC 网络中,则需要提供该参数,可在 VPC 控制台 的子网中查看。
    dcgid 非必须项。专线 ID,目标实例如果需要通过专线网络连接,则需要提供该参数值。
    vpngwid 非必须项。VPN 网关 ID,目标实例如果需要通过 VPN 进行网络连接,则需要提供该参数值。
    region 非必须项。目标实例所在地域,如 “ap-guangzhou” 表示广州。如果需要跨地域访问数据,则需要提供该参数值。

    使用 postgres_fdw 示例

    使用 postgres_fdw 插件可以访问本实例其他库或者其他 postgres 实例的数据。

    步骤1:前置条件

    1. 在本实例中创建测试数据。
    postgres=>create role user1 with LOGIN CREATEDB PASSWORD 'password1';
    postgres=>create database testdb1;
    CREATE DATABASE
    注意:
    若创建插件报错,请 提交工单 联系腾讯云售后协助处理。
    2. 在目标实例中创建测试数据。
    postgres=>create role user2 with LOGIN CREATEDB PASSWORD 'password2';
    postgres=> create database testdb2;
    CREATE DATABASE
    postgres=> \\c testdb2 user2
    You are now connected to database "testdb2" as user "user2".
    testdb2=> create table test_table2(id integer);
    CREATE TABLE
    testdb2=> insert into test_table2 values (1);
    INSERT 0 1

    步骤2:创建 postgres_fdw 插件

    说明:
    若创建插件时,提示插件不存在或权限不足,请 提交工单 处理。
    #创建
    postgres=> \\c testdb1
    You are now connected to database "testdb1" as user "user1".
    testdb1=> create extension postgres_fdw;
    CREATE EXTENSION
    #查看
    testdb1=> \\dx
    List of installed extensions
    Name | Version | Schema | Description
    --------------+---------+------------+----------------------------------------------------
    plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
    postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers
    (2 rows)

    步骤3:创建 SERVER

    注意:
    仅 v10.17_r1.2、v11.12_r1.2、v12.7_r1.2、v13.3_r1.2、v14.2_r1.0 及之后的内核版本支持跨实例访问。
    跨实例访问。
    #从本实例的 testdb1 访问目标实例 testdb2 的数据
    testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', instanceid 'postgres-xxxxx');
    CREATE SERVER
    不跨实例,仅跨 database 访问,仅需要填写 dbname 参数即可。
    #从本实例的 testdb1 访问本实例 testdb2 的数据
    create server srv_test1 foreign data wrapper postgres_fdw options (dbname 'testdb2');
    目标实例在腾讯云 CVM 上,且网络类型为基础网络。
    testdb1=>create server srv_test foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx', dbname 'testdb2', port '5432', instanceid 'ins-xxxxx', access_type '2', region 'ap-guangzhou',uin 'xxxxxx',own_uin 'xxxxxx');
    CREATE SERVER
    目标实例在腾讯云 CVM 上,且网络类型为私有网络。
    testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', instanceid 'ins-xxxxx', access_type '2', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx', vpcid 'vpc-xxxxxx', subnetid 'subnet-xxxxx');
    CREATE SERVER
    目标实例在腾讯云外网自建。
    testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', access_type '3', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx');
    CREATE SERVER
    目标实例在腾讯云 VPN 接入的实例。
    testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', access_type '4', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx', vpngwid 'xxxxxx');
    目标实例在自建 VPN 接入的实例。
    testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', access_type '5', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx', vpngwid 'xxxxxx');
    目标实例在腾讯云专线接入的实例。
    testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', access_type '6', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx', dcgid 'xxxxxx');
    CREATE SERVER

    步骤4:创建用户映射

    说明:
    同实例的跨 database 访问则可跳过此步骤。
    testdb1=> create user mapping for user1 server srv_test1 options (user 'user2',password 'password2');
    CREATE USER MAPPING

    步骤5:创建外部表

    testdb1=> create foreign table foreign_table1(id integer) server srv_test1 options(table_name 'test_table2');
    CREATE FOREIGN TABLE

    步骤6:访问外部数据

    testdb1=> select * from foreign_table1;
    id
    ----
    1
    (1 row)

    参考链接

    使用 dblink 示例

    步骤一:创建 dblink 插件

    postgres=> create extension dblink;
    postgres=> \\dx
    List of installed extensions
    Name | Version | Schema | Description
    --------------------+---------+------------+-------------------------------------------------------------------
    dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
    pg_stat_log | 1.0 | public | track runtime execution statistics of all SQL statements executed
    pg_stat_statements | 1.6 | public | track execution statistics of all SQL statements executed
    plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
    (4 rows)

    步骤二:建立 dblink 链接

    select dblink_connect('yunpg1','host=10.10.10.11 port=5432 instanceid=postgres-2123455r dbname=postgres access_type=1 user=dbadmin password=P302!');
    dblink_connect
    ----------------
    OK
    (1 row)

    步骤三:访问外部数据

    postgres=> select * from dblink('yunpg1','select catalog_name,schema_name,schema_owner from information_schema.schemata') as t(a varchar(50),b varchar(50),c varchar(50));
    a | b | c
    ----------+--------------------+---------
    postgres | pg_toast | user_00
    postgres | pg_temp_1 | user_00
    postgres | pg_toast_temp_1 | user_00
    postgres | pg_catalog | user_00
    postgres | public | user_00
    postgres | information_schema | user_00
    (6 rows)

    参考链接

    使用 mysql_fdw 示例

    步骤一:创建 mysql_fdw 插件

    postgres=> create extension mysql_fdw;
    CREATE EXTENSION
    postgres=> \\dx;
    List of installed extensions
    Name | Version | Schema | Description
    --------------------+---------+------------+------------------------------------------------------------------------
    dblink | 1.2 | public | connect to other PostgreSQL databases from within a database
    mysql_fdw | 1.1 | public | Foreign data wrapper for querying a MySQL server
    pg_stat_log | 1.0 | public | track runtime execution statistics of all SQL statements executed
    pg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executed
    plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
    (5 rows)

    步骤二:创建 SERVER

    postgres=> CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '171.16.10.13',port '3306',instanceid 'cdb-l1d95grp',uin '100026380431');
    CREATE SERVER

    步骤三:创建外部用户映射

    postgres=> CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'fdw_user',password 'Secret!123');
    CREATE USER MAPPING

    步骤四:访问外部数据

    postgres=> IMPORT FOREIGN SCHEMA hrdb FROM SERVER mysql_svr INTO public;

    参考链接

    使用 cos_fdw 示例

    cos_fdw 使用示例请参考文档 通过 cos_fdw 插件支持分级存储能力

    使用注意

    目标实例,需要注意以下几点:
    1. 需要放开 PostgreSQL 的 hba 限制,允许创建的映射用户(如:user2)以 MD5 方式访问。hba 的修改可参考 PostgreSQL 官方说明
    2. 如果目标实例非 TencentDB 实例,且搭建有热备模式,当主备切换后,需要自行更新 server 连接地址或者重新创建 server。
    联系我们

    联系我们,为您的业务提供专属服务。

    技术支持

    如果你想寻求进一步的帮助,通过工单与我们进行联络。我们提供7x24的工单服务。

    7x24 电话支持