tencent cloud

#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)

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(table_name 'test_table2');
CREATE FOREIGN TABLE

testdb1=> select * from foreign_table1;
  id
----
   1
(1 row)

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)

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)

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)

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;