tencent cloud

PrevNext

文档反馈

请输入关键字

Recent Pages

文档
云数据库 PostgreSQL
    文档
    Download PDF

    跨库访问

    最后更新时间：2024-03-20 11:01:39
    下载PDF
      云数据库 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)

      参考链接

      postgres_fdw 官方介绍
      9.3 版本 SERVER 创建
      9.5 版本 SERVER 创建
      10 版本 SERVER 创建
      11 版本 SERVER 创建
      12 版本 SERVER 创建
      13 版本 SERVER 创建
      14 版本 SERVER 创建

      使用 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 电话支持
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      隐私条款服务条款缓存条款