产品动态
种类 | 插件 |
同构跨库访问插件 | dblink、postgresql_fdw |
异构跨库访问插件 | mysql_fdw、cos_fdw |
postgres=>create role user1 with LOGIN CREATEDB tencentdb_superuser PASSWORD 'password1';CREATE ROLEpostgres=>create database testdb1;CREATE DATABASE
postgres=>create role user2 with LOGIN CREATEDB PASSWORD 'password2';postgres=> create database testdb2;CREATE DATABASEpostgres=# grant all on database testdb2 to user2;GRANTpostgres=> \\c testdb2 user2You are now connected to database "testdb2" as user "user2".Testdb2=> create schema test_schema;CREATE SCHEMAtestdb2=> create table test_schema.test_table2(id integer);CREATE TABLEtestdb2=> insert into test_schema.test_table2 values (1);INSERT 0 12
#创建postgres=> \\c testdb1You are now connected to database "testdb1" as user "user1".testdb1=> create extension postgres_fdw;CREATE EXTENSION#查看testdb1=> \\dxList of installed extensionsName | Version | Schema | Description--------------+---------+------------+----------------------------------------------------plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural languagepostgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers(2 rows)
#从本实例的 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
#从本实例的 testdb1 访问本实例 testdb2 的数据create server srv_test1 foreign data wrapper postgres_fdw options (dbname 'testdb2');
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
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
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');
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
testdb1=> create user mapping for user1 server srv_test1 options (user 'user2',password 'password2');CREATE USER MAPPING
testdb1=> create foreign table foreign_table1(id integer) server srv_test1 options(schema_name ‘test_schema’,table_name'test_table2');CREATE FOREIGN TABLE
testdb1=> select * from foreign_table1;id----1(1 row)
postgres=> create extension dblink;postgres=> \\dxList of installed extensionsName | Version | Schema | Description--------------------+---------+------------+-------------------------------------------------------------------dblink | 1.2 | public | connect to other PostgreSQL databases from within a databasepg_stat_log | 1.0 | public | track runtime execution statistics of all SQL statements executedpg_stat_statements | 1.6 | public | track execution statistics of all SQL statements executedplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language(4 rows)
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_00postgres | pg_temp_1 | user_00postgres | pg_toast_temp_1 | user_00postgres | pg_catalog | user_00postgres | public | user_00postgres | information_schema | user_00(6 rows)
postgres=> create extension mysql_fdw;CREATE EXTENSIONpostgres=> \\dx;List of installed extensionsName | Version | Schema | Description--------------------+---------+------------+------------------------------------------------------------------------dblink | 1.2 | public | connect to other PostgreSQL databases from within a databasemysql_fdw | 1.1 | public | Foreign data wrapper for querying a MySQL serverpg_stat_log | 1.0 | public | track runtime execution statistics of all SQL statements executedpg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executedplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language(5 rows)
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;
文档反馈