SRE文档

深浅模式

介绍 ​

在使用异构数据库构建数据平台的过程中，异构数据库之间的访问一直是比较复杂的问题。我们使用PostgeSQL的过程中，遇到需要访问MySQL实时数据的场景。可供我们选择的方案包括FDW(Foreign Data Wrapper)是我们选用的开源方案，这个方案明显的优点是使用统一的接口方式实现多种数据库的远程访问，包括但不限于PostgreSQL, MySQL, MongoDB, HDFS

1.查看已有扩展 ​

\dx 或者 select * from pg_available_extensions; 


postgres=# \dx
                          List of installed extensions
     Name     | Version |   Schema   |               Description                
--------------+---------+------------+------------------------------------------
 pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from table
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# select * from pg_available_extensions; 
        name        | default_version | installed_version |                          comment       
                   
--------------------+-----------------+-------------------+----------------------------------------
-------------------
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 plpython3u         | 1.0             |                   | PL/Python3U untrusted procedural langua
ge
 pltcl              | 1.0             |                   | PL/Tcl procedural language
 pltclu             | 1.0             |                   | PL/TclU untrusted procedural language
 pg_stat_statements | 1.7             |                   | track execution statistics of all SQL s
tatements executed
 pg_dirtyread       | 2               | 2                 | Read dead but unvacuumed rows from tabl
e
(6 rows)
\dx 或者 select * from pg_available_extensions; 


postgres=# \dx
                          List of installed extensions
     Name     | Version |   Schema   |               Description                
--------------+---------+------------+------------------------------------------
 pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from table
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# select * from pg_available_extensions; 
        name        | default_version | installed_version |                          comment       
                   
--------------------+-----------------+-------------------+----------------------------------------
-------------------
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 plpython3u         | 1.0             |                   | PL/Python3U untrusted procedural langua
ge
 pltcl              | 1.0             |                   | PL/Tcl procedural language
 pltclu             | 1.0             |                   | PL/TclU untrusted procedural language
 pg_stat_statements | 1.7             |                   | track execution statistics of all SQL s
tatements executed
 pg_dirtyread       | 2               | 2                 | Read dead but unvacuumed rows from tabl
e
(6 rows)

2.安装 ​

[root@beta postgresql-12.2]#cd /root/postgresql-12.2/contrib/postgres_fdw

[root@beta postgres_fdw]#make

[root@beta postgres_fdw]# make install PG_CONFIG=/opt/pg12/bin/pg_config
[root@beta postgresql-12.2]#cd /root/postgresql-12.2/contrib/postgres_fdw

[root@beta postgres_fdw]#make

[root@beta postgres_fdw]# make install PG_CONFIG=/opt/pg12/bin/pg_config

3.创建 ​

sql
postgres=# create user peiyb with password 'postgrespostgres';
postgres=# create database peiybdb with owner=peiyb;


postgres=# create extension postgres_fdw;
CREATE EXTENSION

postgres=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from table
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(3 rows)

#查看fdw属于那个库
postgres=# \dew
                      List of foreign-data wrappers
     Name     |  Owner   |       Handler        |       Validator        
--------------+----------+----------------------+------------------------
 postgres_fdw | postgres | postgres_fdw_handler | postgres_fdw_validator
(1 row)
postgres=# create user peiyb with password 'postgrespostgres';
postgres=# create database peiybdb with owner=peiyb;


postgres=# create extension postgres_fdw;
CREATE EXTENSION

postgres=# \dx
                               List of installed extensions
     Name     | Version |   Schema   |                    Description                     
--------------+---------+------------+----------------------------------------------------
 pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from table
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(3 rows)

#查看fdw属于那个库
postgres=# \dew
                      List of foreign-data wrappers
     Name     |  Owner   |       Handler        |       Validator        
--------------+----------+----------------------+------------------------
 postgres_fdw | postgres | postgres_fdw_handler | postgres_fdw_validator
(1 row)

验证 ​

查询postgres的所有拓展，验证fdw安装成功
select * from pg_available_extensions;
查询postgres的所有拓展，验证fdw安装成功
select * from pg_available_extensions;

创建远程server ​

192.168.1.10 源端

168.2.237.207 目标地址

server_remote是给这个远程server取得名字。

options中是连接远程server需要的信息，包括地址、端口和需要连接的数据库名

注意： 创建server 需要超级

192.168.1.10>postgres=# create server server_remote FOREIGN data wrapper postgres_fdw OPTIONS(host '168.2.237.207', port '5432', dbname 'aasccs');
CREATE SERVER

#查看所有远程连接，验证server创建成功
192.168.1.10># SELECT * from pg_foreign_server;
  oid  |    srvname    | srvowner | srvfdw | srvtype | srvversion | srvacl |                  srvop
tions                  
-------+---------------+----------+--------+---------+------------+--------+-----------------------
-----------------------
 25974 | server_remote |       10 |  25973 |         |            |        | {host=168.2.237.207,po
rt=5432,dbname=aasccs}
(1 row)
192.168.1.10>postgres=# create server server_remote FOREIGN data wrapper postgres_fdw OPTIONS(host '168.2.237.207', port '5432', dbname 'aasccs');
CREATE SERVER

#查看所有远程连接，验证server创建成功
192.168.1.10># SELECT * from pg_foreign_server;
  oid  |    srvname    | srvowner | srvfdw | srvtype | srvversion | srvacl |                  srvop
tions                  
-------+---------------+----------+--------+---------+------------+--------+-----------------------
-----------------------
 25974 | server_remote |       10 |  25973 |         |            |        | {host=168.2.237.207,po
rt=5432,dbname=aasccs}
(1 row)

创建用户匹配信息 ​

postgres 用户
表示在server_remote下为角色postgres创建一个用户匹配信息，options里是用户名和密码

#指定用户
create user mapping for postgres server server_remote options(user 'postgres',password 'yourpassword');

#指定所有用户
postgres=# create user mapping for public server_remote options(user 'postgres',password '123456');                          
CREATE USER MAPPING


create user mapping for peiyb server shard1 options (user 'peiyb', password 'postgrespostgres');

注意： 
create user mapping是将server与本地的用户连接起来。
public表示当前库的没有明确指定user mapping 的所有用户
postgres 用户
表示在server_remote下为角色postgres创建一个用户匹配信息，options里是用户名和密码

#指定用户
create user mapping for postgres server server_remote options(user 'postgres',password 'yourpassword');

#指定所有用户
postgres=# create user mapping for public server_remote options(user 'postgres',password '123456');                          
CREATE USER MAPPING


create user mapping for peiyb server shard1 options (user 'peiyb', password 'postgrespostgres');

注意： 
create user mapping是将server与本地的用户连接起来。
public表示当前库的没有明确指定user mapping 的所有用户

授权 ​

 grant usage on foreign data wrapper postgres_fdw to user_name;
 grant usage on foreign data wrapper postgres_fdw to user_name;

创建外部表 ​

#public
postgres=# create foreign table foreign_goods(id integer,name character varying)server server_remote options(schema_name 'public',table_name 'goods');
CREATE FOREIGN TABLE

#
CREATE FOREIGN TABLE t_resource 
 (   fd_resid SERIAL,
         fd_res_name TEXT,
         fd_res_desc TEXT,
         fd_tenantid INTEGER, 
         fd_service TEXT, 
         fd_display_name TEXT, 
         fd_modify_date TEXT, 
         fd_modify_person_id INTEGER
)
server server_remote 
options (table_name 't_resource_remote');
#public
postgres=# create foreign table foreign_goods(id integer,name character varying)server server_remote options(schema_name 'public',table_name 'goods');
CREATE FOREIGN TABLE

#
CREATE FOREIGN TABLE t_resource 
 (   fd_resid SERIAL,
         fd_res_name TEXT,
         fd_res_desc TEXT,
         fd_tenantid INTEGER, 
         fd_service TEXT, 
         fd_display_name TEXT, 
         fd_modify_date TEXT, 
         fd_modify_person_id INTEGER
)
server server_remote 
options (table_name 't_resource_remote');

执行计划 ​

postgres=# explain (analyze,verbose) select * from foreign_goods where id=1;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.foreign_goods  (cost=100.00..127.20 rows=7 width=36) (actual time=1.256..1.257 rows=1 loops=1)
   Output: id,name
   Remote sql: SELECT id,name FROM public.goods WHERE ((id = 1)) Total runtime: 2.614 ms (4 rows)
postgres=# explain (analyze,verbose) select * from foreign_goods where id=1;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.foreign_goods  (cost=100.00..127.20 rows=7 width=36) (actual time=1.256..1.257 rows=1 loops=1)
   Output: id,name
   Remote sql: SELECT id,name FROM public.goods WHERE ((id = 1)) Total runtime: 2.614 ms (4 rows)

案例 ​

1.目的
在node2中查询node1的t1表
select * from t1;
 id | name 
----+------
  1 | aa
  
 
2.实施
node1数据准备

[postgres@localhost ~]$ psql
Password for user postgres: 
psql (12.4)
Type "help" for help.
postgres=# create database db_1;
postgres=# create user user1 password 'Aa111';
postgres=# alter database db_1 owner to user1;
db_1=# \q
[postgres@localhost ~]$ psql -U user1 -d db_1
Password for user user1: 
psql (12.4)
Type "help" for help.
db_1=> create table t1(id int,name varchar) tablespace pg_default;
db_1=> insert into t1 values(1,'aa');
db_1=> select * from t1;
 id | name 
----+------
  1 | aa
(1 row)



node2设置
-bash-4.1$ psql
psql (12.3)
Type "help" for help.

postgres=# create user user2 password 'Aa111';
postgres=# create database db_2;
postgres=# alter database db_2 owner to user2;

postgres=# \c db_2
You are now connected to database "db_2" as user "postgres".
db_2=# create extension postgres_fdw;
db_2=# grant usage on foreign data wrapper postgres_fdw to user2;
db_2=# create server fnode1 foreign data wrapper postgres_fdw options (host 'node1', port '5432', dbname 'db_1');
db_2=# grant usage on foreign server fnode1 to user2; 

-bash-4.1$ psql -U user2 -d db_2 -h node2
Password for user user2: 
psql (12.3)
Type "help" for help.

db_2=> # create user mapping for user2 server fnode1 options (user 'user1', password 'Aa111');
db_2=> # create foreign table t1 (id int,name varchar) server fnode1 options (schema_name 'public', table_name 't1');
db_2=> select * from t1;
 id | name 
----+------
  1 | aa
(1 row)


3.测试
db_2=> insert into t1 values(2,'bb');
INSERT 0 1
db_2=> select  * from t1;
 id | name 
----+------
  1 | aa
  2 | bb
(2 rows)

db_2=> create table t2 (id int);
CREATE TABLE
db_2=> insert into t2 values(2),(3);
INSERT 0 2
db_2=> select * from t1,t2 where t1.id=t2.id;
 id | name | id 
----+------+----
db_2=> explain select * from t1,t2 where t1.id=t2.id;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Merge Join  (cost=401.81..669.70 rows=17404 width=40)
   Merge Cond: (t1.id = t2.id)
   ->  Sort  (cost=222.03..225.44 rows=1365 width=36)
         Sort Key: t1.id
         ->  Foreign Scan on t1  (cost=100.00..150.95 rows=1365 width=36)
   ->  Sort  (cost=179.78..186.16 rows=2550 width=4)
         Sort Key: t2.id
         ->  Seq Scan on t2  (cost=0.00..35.50 rows=2550 width=4)
(8 rows)
1.目的
在node2中查询node1的t1表
select * from t1;
 id | name 
----+------
  1 | aa
  
 
2.实施
node1数据准备

[postgres@localhost ~]$ psql
Password for user postgres: 
psql (12.4)
Type "help" for help.
postgres=# create database db_1;
postgres=# create user user1 password 'Aa111';
postgres=# alter database db_1 owner to user1;
db_1=# \q
[postgres@localhost ~]$ psql -U user1 -d db_1
Password for user user1: 
psql (12.4)
Type "help" for help.
db_1=> create table t1(id int,name varchar) tablespace pg_default;
db_1=> insert into t1 values(1,'aa');
db_1=> select * from t1;
 id | name 
----+------
  1 | aa
(1 row)



node2设置
-bash-4.1$ psql
psql (12.3)
Type "help" for help.

postgres=# create user user2 password 'Aa111';
postgres=# create database db_2;
postgres=# alter database db_2 owner to user2;

postgres=# \c db_2
You are now connected to database "db_2" as user "postgres".
db_2=# create extension postgres_fdw;
db_2=# grant usage on foreign data wrapper postgres_fdw to user2;
db_2=# create server fnode1 foreign data wrapper postgres_fdw options (host 'node1', port '5432', dbname 'db_1');
db_2=# grant usage on foreign server fnode1 to user2; 

-bash-4.1$ psql -U user2 -d db_2 -h node2
Password for user user2: 
psql (12.3)
Type "help" for help.

db_2=> # create user mapping for user2 server fnode1 options (user 'user1', password 'Aa111');
db_2=> # create foreign table t1 (id int,name varchar) server fnode1 options (schema_name 'public', table_name 't1');
db_2=> select * from t1;
 id | name 
----+------
  1 | aa
(1 row)


3.测试
db_2=> insert into t1 values(2,'bb');
INSERT 0 1
db_2=> select  * from t1;
 id | name 
----+------
  1 | aa
  2 | bb
(2 rows)

db_2=> create table t2 (id int);
CREATE TABLE
db_2=> insert into t2 values(2),(3);
INSERT 0 2
db_2=> select * from t1,t2 where t1.id=t2.id;
 id | name | id 
----+------+----
db_2=> explain select * from t1,t2 where t1.id=t2.id;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Merge Join  (cost=401.81..669.70 rows=17404 width=40)
   Merge Cond: (t1.id = t2.id)
   ->  Sort  (cost=222.03..225.44 rows=1365 width=36)
         Sort Key: t1.id
         ->  Foreign Scan on t1  (cost=100.00..150.95 rows=1365 width=36)
   ->  Sort  (cost=179.78..186.16 rows=2550 width=4)
         Sort Key: t2.id
         ->  Seq Scan on t2  (cost=0.00..35.50 rows=2550 width=4)
(8 rows)

参考地址：

https://helpcdn.aliyun.com/document_detail/142422.html

https://www.postgresql.org/docs/12/postgres-fdw.html

https://www.postgresql.org/docs/9.2/dblink.html