Cross-database access refers to the process of performing data reading, writing, and joint operations on data in other libraries within this instance or in other instances. The target objects of cross-database access are collectively referred to as external data sources.
TencentDB for PostgreSQL provides a plug-in for accessing external data sources, which is used to implement and enhance cross-database access capabilities. There are two kinds of external access plug-ins:
|
Isomorphic cross-database access extension | dblink,postgresql_fdw |
Heterogeneous cross-database access extension | mysql_fdw,cos_fdw |
Directions for cross-database access
1. Use the "CREATE EXTENSION plug-in name;" statement to install a plug-in.
2. Create an external server object for each remote database they want to connect to and create a link mapping.
3. Use the corresponding command to access external tables to retrieve data.
Additionally, without constraints, the powerful access capability of the cross-database access extension may bring certain potential security risks. Therefore, TencentDB for PostgreSQL has optimized permission control and manages classification according to the environment where the target instance is located. Additional auxiliary parameters have been added on the basis of the open-source version to verify user identity and adjust network policies. For details, reference Plugin Auxiliary Parameters. Note:
The dblink plug-in is only supported in the kernel of TencentDB for PostgreSQL with a major version of 10 or higher. Please be informed.
Plugin Auxiliary Parameters
host
Required items for performing cross-instance access. The IP address of the target instance.
port
Required items for performing cross-instance access. The port number of the target instance.
instanceid
Instance ID
Cross-instance access between TencentDB for PostgreSQL is a required item. The format is similar to postgres-xxxxxx, pgro-xxxxxx, which can be viewed in the instance list on the console.
If the target instance is on Tencent Cloud CVM, fill in the instance ID of the CVM instance, with a format similar to ins-xxxxx.
dbname
database name. Fill in the database name of the remote PostgreSQL service you need to access. If you do not perform cross-instance access and only perform cross-database access within the same instance, you only need to configure this parameter, and other parameters can be left blank.
access_type
Optional. The types that the target instance belongs to are as follows:
Value is 1: The target instance is a TencentDB instance, including TencentDB for PostgreSQL, TencentDB for MySQL, etc. If not specified, this item is selected by default.
Value is 2: The target instance is on a Tencent Cloud CVM machine.
Value is 3: The target instance is self-built on the public network.
Value is 4: The target instance is a connected instance of Cloud VPN.
Value is 5: The target instance is accessed through a self-built VPN.
Value is 6: The target instance is a Direct Connect-enabled instance.
uin
Required. The account ID to which the instance belongs. User permissions are identified through this information. See Query uin. own_uin
Not required. The root account ID to which the instance belongs also requires this information for user permission identification.
vpcid
Optional. VPC ID. If the target instance is in the VPC network of Tencent Cloud CVM, this parameter is required. It can be viewed in the VPC console.
subnetid
Optional. The subnet ID of the VPC. If the target instance is in the VPC network of Tencent Cloud CVM, this parameter is required. It can be viewed in the subnet of the VPC console . dcgid
Optional. Dedicated line ID. If the target instance needs to connect via a dedicated network, this parameter value is required.
vpngwid
Not required. VPN gateway ID. If the target instance needs to connect through a VPN, this parameter value is required.
region
Optional. The region where the target instance is located. For example, "ap-guangzhou" refers to Guangzhou. If cross-region data access is required, this parameter value is needed.
Using Postgres_fdw Example
The postgres_fdw plug-in can be used to access data from other databases in this instance or from other PostgreSQL instances.
Step 1: Prerequisite
1. Create a test database in this instance.
postgres=>create role user1 with LOGIN CREATEDB tencentdb_superuser PASSWORD 'password1';
CREATE ROLE
postgres=>create database testdb1;
CREATE DATABASE
Note:
If an error occurs while creating a plugin, submit a ticket to contact Tencent Cloud After-sales Service for assistance. 2. Create test data in the target instance.
postgres=>create role user2 with LOGIN CREATEDB PASSWORD 'password2';
postgres=> create database testdb2;
CREATE DATABASE
postgres=
GRANT
postgres=> \\c testdb2 user2
You are now connected to database "testdb2" as user "user2".
Testdb2=> create schema test_schema;
CREATE SCHEMA
testdb2=> create table test_schema.test_table2(id integer);
CREATE TABLE
testdb2=> insert into test_schema.test_table2 values (1);
INSERT 0 12
Step 2: Create the Postgres_fdw Plug-In
Note:
If a prompt indicates that the plug-in does not exist or there are insufficient permissions when creating a plug-in, submit a ticket for handling.
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.1 | public | foreign-data wrapper for remote PostgreSQL servers
(2 rows)
Step 3: Creating a SERVER
Note:
Only the Linux kernel versions v10.17_r1.2, v11.12_r1.2, v12.7_r1.2, v13.3_r1.2, v14.2_r1.0 and later support access across instances.
Cross-instance access.
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
Access across databases without cross-instance access, just need to fill in the dbname parameter.
create server srv_test1 foreign data wrapper postgres_fdw options (dbname 'testdb2');
The target instance is on a Tencent Cloud CVM with a basic network type.
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
The target instance is on a Tencent Cloud CVM, and the network type is a private network.
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
The target instance is self-built on the public network.
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
The target instance is a connected instance of Tencent Cloud 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');
The target instance is in a self-built VPN access instance.
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');
The target instance is an instance connected to Tencent Cloud DC.
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
Step 4: Creating User Mapping
testdb1=> create user mapping for user1 server srv_test1 options (user 'user2',password 'password2');
CREATE USER MAPPING
Step 5: Creating an External Table
testdb1=> create foreign table foreign_table1(id integer) server srv_test1 options(schema_name ‘test_schema’,table_name'test_table2');
CREATE FOREIGN TABLE
Note:
For cross-database access within the same instance, just fill in the name of the target table (table_name). No need to specify the schema_name parameter.
Step 6: Accessing External Data
testdb1=> select * from foreign_table1;
id
----
1
(1 row)
Reference Link
Using a Dblink Example
Step 1: Create a Dblink Plug-In
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)
Step Two: Creating a Dblink Link
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)
Step 3: Accessing External Data
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)
Reference Link
Using Mysql_fdw Example
Step 1: Create a Mysql_fdw Plug-In
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)
Step Two: Creating a 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
Step Three: Creating External User Map
postgres=> CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'fdw_user',password 'Secret!123');
CREATE USER MAPPING
Step Four: Accessing External Data
Note:
The mysql database that this instance wants to connect to must have at least one table before the table structure can be imported through IMPORT FOREIGN SCHEMA.
postgres=> IMPORT FOREIGN SCHEMA hrdb FROM SERVER mysql_svr INTO public;
Reference Link
Using Cos_fdw Example
Usage Notes
Target instance. Pay attention to the following points.
1. Require lifting PostgreSQL's hba restrictions and allowing created mapped users (such as user2) to access using the MD5 method. For hba modification, refer to PostgreSQL official documentation. 2. If the target instance is not a TencentDB instance and has a Hot Standby Mode set up, after a primary/replica switch, you need to manually update the server connection address or recreate the server.