postgres_fdw Extension for Cross-database Access

Recent Pages

postgres_fdw Extension for Cross-database Access

Last updated: 2024-01-24 11:16:51

TencentDB for PostgreSQL provides extensions for accessing external data sources, including other databases in the same instance and other instances. The cross-database access extensions include homogeneous extensions (dblink and postgresql_fdw) and heterogeneous extensions (mysql_fdw and cos_fdw). You can enable cross-database access by following steps below:
1. Install the extensions by running CREATE EXTENSION.
2. Create a foreign server object and create link maps for each remote database that needs to be connected.
3. Use the corresponding command to access external tables to get the data.
As the cross-database access extensions can directly access across instances or perform cross-database access in the same instance, TencentDB for PostgreSQL optimizes access control over the creation of foreign server objects and implements categorized management based on the environment of the target instance. Auxiliary parameters are added to the open-source edition to verify user identity and adjust network policies. For more information, see Auxiliary Parameters.
Auxiliary Parameters
host
This parameter is required for cross-instance access. IP address of the target instance
port
This parameter is required for cross-instance access. Port of the target instance.
instanceid
Instance ID
This parameter is required for access across TencentDB for PostgreSQL instances. It is in the format of postgres-xxxxxx or pgro-xxxxxx and can be viewed in the console.
If the target instance is in a CVM instance, this parameter is the ID of the CVM instance in the format of ins-xxxxx.
dbname 
Name of the database in the remote PostgreSQL service to be accessed. For cross-database access in the same instance, you only need to configure this parameter and can leave other parameters empty.
access_type
This parameter is optional.  Target instance types:
The target instance is a TencentDB for PostgreSQL or TencentDB for MySQL instance; if no other types are explicitly specified, this will be the default type.
The target instance is in a CVM instance.
The target instance is a self-built instance with public IP in Tencent Cloud.
The target instance is a Tencent Cloud VPN-based instance.
The target instance is a self-built VPN-based instance.
The target instance is a Direct Connect-based instance.
uin
This parameter is optional. ID of the account to which the instance belongs, which is used to verify user permissions and can be viewed in Account Info.
own_uin
This parameter is optional. ID of the root account to which the instance belongs, which is also needed for verifying user permissions.
vpcid
This parameter is optional. VPC ID. It is required if the target instance is in a CVM instance in a VPC. It can be viewed in the VPC console.
subnetid
This parameter is optional. VPC subnet ID. It is required if the target instance is in a CVM instance in a VPC. It can be viewed in the VPC console.
dcgid
This parameter is optional. Direct Connect connection ID. It is required if the target instance is connected to the network over Direct Connect.
vpngwid
This parameter is optional. VPN gateway ID. It is required if the target instance is connected to the network over VPN.
region
This parameter is optional. It indicates the region where the target instance resides; for example, "ap-guangzhou" represents the Guangzhou region. It is required for cross-region access.
Sample for Using postgres_fdw
The postgres_fdw extension can be used to access data from other databases in the same instance or other instances.
Step 1. Prepare
1. Create test data in the instance.
postgres=>create role user1 with LOGIN  CREATEDB PASSWORD 'password1';
postgres=>create database testdb1;
CREATE DATABASE
Note:
 If an error occurs during creation, submit a ticket 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=> \\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
Step 2. Create the postgres_fdw extension
Note:
If you are prompted that the extension does not exist or you have insufficient permissions during creation, submit a ticket for assistance.
# Create
postgres=> \\c testdb1
You are now connected to database "testdb1" as user "user1".
testdb1=> create extension postgres_fdw;
CREATE EXTENSION
# View
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)
Step 3. Create a server
Note:
Cross-instance access is supported only for kernel v10.17_r1.2, v11.12_r1.2, v12.7_r1.2, v13.3_r1.2, v14.2_r1.0, and later.
Cross-instance access
# Access the data of the target instance's `testdb2` from the current instance's `testdb1`
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
For cross-database access in the same instance, you only need to enter the dbname parameter.
# Access the data of `testdb2` from `testdb1` in the current instance
create server srv_test1 foreign data wrapper postgres_fdw options (dbname 'testdb2');
The target instance is in a CVM instance in the classic network.
  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 in a CVM instance in a VPC.
  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 a self-built instance with public IP in Tencent Cloud.
  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 Tencent Cloud VPN-based instance.
  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 a self-built VPN-based 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 a Direct Connect-based instance.
  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. Create a user mapping
Note:
 You can skip this step for cross-database access in the same instance.
testdb1=> create user mapping for user1 server srv_test1 options (user 'user2',password 'password2');
CREATE USER MAPPING
Step 5. Create a foreign table
testdb1=> create foreign table foreign_table1(id integer) server srv_test1 options(table_name 'test_table2');
CREATE FOREIGN TABLE
Step 6. Access data from foreign table
testdb1=> select * from foreign_table1;
  id
----
   1
(1 row)
References
postgres_fdw Overview
Create a server on v9.3
Create a server on v9.5
Create a server on v10
Create a server on v11
Create a server on v12
Create a server on v13
Create a server on v14
Sample for Using dblink
Step 1. Create the dblink extention
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 2. Create the 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. Access 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)
References
﻿dblink Overview﻿
Sample for Using mysql_fdw
Step 1. Create the mysql_fdw extension
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 2. Create 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 3. Create a user mapping
postgres=> CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'fdw_user',password 'Secret!123');
CREATE USER MAPPING
Step 4. Access external data
postgres=> IMPORT FOREIGN SCHEMA hrdb FROM SERVER mysql_svr INTO public;
References
mysql_fdw Overview
Sample for Using cos_fdw
For samples of using cos_fdw, see Supporting Tiered Storage Based on cos_fdw Extension.
Note
Pay attention to the following for the target instance:
1. The hba in PostgreSQL needs to be modified to allow the created mapped user (e.g., user2) to access via MD5. For more information on how to modify hba, see PostgreSQL's official documentation.
2. If the target instance is not a TencentDB instance and has a hot backup mode configured, after a primary-standby switch, you need to update the server connection address or create a server again.

Contact Us

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

Technical Support

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

7x24 Phone Support

tencent cloud

Recent Pages

postgres_fdw Extension for Cross-database Access

Auxiliary Parameters

Sample for Using `postgres_fdw`

Step 1. Prepare

Step 2. Create the postgres_fdw extension

Step 3. Create a server

Step 4. Create a user mapping

Step 5. Create a foreign table

Step 6. Access data from foreign table

References

Sample for Using `dblink`

Step 1. Create the `dblink` extention

Step 2. Create the `dblink` link

Step 3. Access external data

References

Sample for Using `mysql_fdw`

Step 1. Create the `mysql_fdw` extension

Step 2. Create a server

Step 3. Create a user mapping

Step 4. Access external data

References

Sample for Using `cos_fdw`

Note

Was this page helpful?

Was this page helpful?

tencent cloud

Sign Up

Log in

Recent Pages

postgres_fdw Extension for Cross-database Access

Auxiliary Parameters

Sample for Using postgres_fdw

Step 1. Prepare

Step 2. Create the postgres_fdw extension

Step 3. Create a server

Step 4. Create a user mapping

Step 5. Create a foreign table

Step 6. Access data from foreign table

References

Sample for Using dblink

Step 1. Create the dblink extention

Step 2. Create the dblink link

Step 3. Access external data

References

Sample for Using mysql_fdw

Step 1. Create the mysql_fdw extension

Step 2. Create a server

Step 3. Create a user mapping

Step 4. Access external data

References

Sample for Using cos_fdw

Note

Was this page helpful?

Was this page helpful?

Sample for Using `postgres_fdw`

Sample for Using `dblink`

Step 1. Create the `dblink` extention

Step 2. Create the `dblink` link

Sample for Using `mysql_fdw`

Step 1. Create the `mysql_fdw` extension

Sample for Using `cos_fdw`