tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TencentDB for PostgreSQL
    Documentation
    Download PDF

    Read-Write Separation with starocks_fdw

    Last updated: 2024-10-28 14:57:24
    Download PDF
      TencentDB for PostgreSQL supports the self-developed starrocks_fdw plugin, enabling you to use tables in StarRocks as foreign tables of PostgreSQL to achieve separation of hot and cold storage. This plugin is compatible with the mysql_fdw plugin. Below is an example of its usage. For more detailed parameters and usage, refer to the mysql_fdw official website. The premise of this document is that you have already deployed StarRocks and can access it. Additionally, you have purchased a TencentDB for PostgreSQL instance.
      In this document, a TencentDB for PostgreSQL instance has already been purchased, and a StarRocks database has been self-built on a CVM in the same VPC. The operation process will be described for you below.
      Note:
      The starocks_fdw plugin currently only supports PG13 versions 13.14_r1.17 and above.

      Preparing Data on StarRocks

      Create a new table sr_m1:
      mysql> CREATE TABLE sr_m1 (
          ->     id int not null,
          ->     str INT
          -> );
      Query OK, 0 rows affected (0.02 sec)
      Insert data:
      mysql> insert into sr_m1(id,str)values(1,1),(2,2),(3,3);
      Query OK, 3 rows affected (0.48 sec)
      {'label':'insert_61cc249a-758a-11ef-a99a-5254006f765d', 'status':'VISIBLE', 'txnId':'1024'}
      Check if data insertion is successful:
      mysql> select * from sr_m1;
      +------+------+
      | id   | str  |
      +------+------+
      |    1 |    1 |
      |    3 |    3 |
      |    2 |    2 |
      +------+------+
      3 rows in set (0.01 sec)

      Creating a Plugin on TencentDB for PostgreSQL

      Create a new database and switch to it; if you already have a database, you can skip this step:
      postgres=> create database am_sr;
      CREATE DATABASE
      postgres=> \\c am_sr
      psql (14.11, server 13.14)
      You are now connected to database "am_sr" as user "dbadmin".
      am_sr=>
      Create a plugin:
      am_sr=> create extension starrocks_fdw;
      CREATE EXTENSION

      Creating External Services and User Mapping on TencentDB for PostgreSQL

      Create an external server: Refer to the plugin parameter document for its parameters.
      am_sr=> CREATE SERVER server_sr FOREIGN DATA WRAPPER starrocks_fdw OPTIONS (host '10.21.0.11', port '9030',instanceid 'ins-xxx3tfi5',access_type '2',region 'ap-guangzhou', uin '100033123456', own_uin '100033456789', vpcid 'vpc-fqxagfew', subnetid 'vpc-fqxawe23');
      CREATE SERVER
      Create user mapping: Assuming the user of TencentDB for PostgreSQL is dbadmin and you want to use the username root and password 123456 to connect to StarRocks, the command would be:
      am_sr=> CREATE USER MAPPING for dbadmin SERVER server_sr OPTIONS (password '123456', username 'root');
      CREATE USER MAPPING
      You can use the following command to view the currently created external services:
      am_sr-> FROM pg_foreign_server;
        srvname  | srvowner | srvfdw | srvtype | srvversion |                                                                  
                       srvoptions                                                                                    
      -----------+----------+--------+---------+------------+------------------------------------------------------------------
      ---------------------------------------------------------------------------------------------------------------
       server_sr |    16385 |  16495 |         |            | {host=10.21.0.11,port=9030,instanceid=ins-xxx3tfi5,access_type=2
      ,region=ap-guangzhou,uin=100033123456,own_uin=100033456789,vpcid=vpc-fqxagfew,subnetid=vpc-fqxawe23,linkid=352}
      (1 row)
      You can also use the following command to view the currently established user mapping:
      am_sr=> SELECT um.umid, um.srvid, fs.srvname, um.umuser, r.rolname AS usename, um.umoptions FROM pg_user_mappings um JOIN pg_foreign_server fs ON um.srvid = fs.oid JOIN pg_roles r ON um.umuser = r.oid;
       umid  | srvid |  srvname  | umuser | usename |            umoptions            
      -------+-------+-----------+--------+---------+---------------------------------
       16499 | 16498 | server_sr |  16385 | dbadmin | {password=123456,username=root}
      (1 row)

      Creating a Foreign Table in TencentDB for PostgreSQL

      Create a table:
      am_sr=> CREATE TABLE sr (id INT NOT NULL,str INT) PARTITION BY RANGE (id);
      CREATE TABLE
      Create a foreign table:
      am_sr=> CREATE foreign TABLE sr_m PARTITION OF sr FOR VALUES FROM (1) TO (10) SERVER server_sr OPTIONS (dbname 'my_database', table_name 'sr_m1');
      CREATE FOREIGN TABLE
      Query data:
      am_sr=> select * from sr;
       id | str 
      ----+-----
        1 |   1
        3 |   3
        2 |   2
      (3 rows)
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy