PostgreSQL 跨数据库实例之间的数据访问

前言

PostgresSQL 作为单示例多数据库，通常一个数据库实例会包含多个表空间和数据库，一个表空间可以存放多个数据库，在此关系中，一个表空间可以包含多个数据库，但是一个数据库只能属于一个表空间，不能跨表空间存放。而表空间是一个逻辑概念，实质上在 PostgresSQL 中，一个表空间实际上就是一个可以供安装 PostgresSQL 用户读写的目录，所以，一个数据库集簇的最大大小取决于组成数据库的这些表空间所对应目录大小的总和大小。那么这些多个数据库组成的数据库集簇可以用来隔离不同业务的数据，同时，也方便管理数据的存储，但是，既然以数据库来区分不同业务的数据，如果在一个数据库集簇或者在属于不同实例的数据库集簇中，这些业务数据之间有可能有关联数据，那么此刻需要跨数据库或者跨数据库实例去访问，如何才能实现？实现的方法有哪些？

上面问题的解决方式有以下几种解决方案，分别是：

· 在同一个数据库集簇或者跨实例的数据库集簇中，可以通过 postgres_fdw 和 db_link 访问。

· 使用 10 版本提供的LSR可以对需要的数据同步到查询的数据库实例上，从而在本地查询。

postgres_fdw简介

简介

fdw(Foreign Data Wrapper),外部数据封装器，使用 postgres_fdw 可以访问外部 PostgreSQL 数据库服务器上的数据。postgres_fdw 专门为同构数据库实例提供的外部封装访问扩展应用。fdw使用步骤如下：

· 创建扩展

· 创建服务

· 创建用户映射

· 创建与访问表对应的外表

1. 数据库 db1 db2

使用 postgres 用户创建两个数据库

[postgres@developer ~]$ psql -d postgres
    psql (13.4)
    Type "help" for help.

    postgres=# create database db1;
    CREATE DATABASE
    postgres=# create database db2;
    CREATE DATABASE

2. 表对象存放于不同的数据库下

在两个数据库db1和db2中分别创建一张表

postgres=# \c db1 postgres
    You are now connected to database "db1" as user "postgres".
    db1=# create table tab_db1(id int,name varchar);
    CREATE TABLE
    db1=# \c db2
    You are now connected to database "db2" as user "postgres".
    db2=# create table tab_db2(id int,name varchar);
    CREATE TABLE

3. 用户访问表不能跨库访问

   如果使用以下方式访问表，那么db1 会被当作一个schema，所以不能使用该方式进行访问

db2=# select *  from db1.tab_db1;
    ERROR:  relation "db1.tab_db1" does not exist
    LINE 1: select *  from db1.tab_d

4. 使用 postgres_fdw

   1）在 db1 上面创建扩展

[postgres@developer ~]$ psql -d db1
      psql (13.4)
      Type "help" for help.

      db1=# create extension postgres_fdw ;
      CREATE EXTENSION

2）创建服务

db1=# CREATE SERVER IF NOT EXISTS foreigin_server_for_db2
      FOREIGN DATA wrapper postgres_fdw OPTIONS (host 'localhost',
                                                      port '5432',
                                                           dbname 'db2');

      CREATE SERVE

3) 创建用户映射

db1=# CREATE USER MAPPING
      FOR postgres 
      server foreigin_server_for_db2 
      options(user 'postgres',password 'postgres');
      CREATE USER MAPPING

4) 创建本地外部表映射到远端表

这里的本地外部表的字段需要和远端待访问的表的字段一致

db1=#
      CREATE
      FOREIGN TABLE foreign_tab_db2 (id int,name varchar) 
      SERVER foreigin_server_for_db2 
      OPTIONS(schema_name 'public',table_name 'tab_db2');
      CREATE FOREIGN TABLE

5) 在 db2 的表中插入数据验证

db1=# \c db2 postgres
      You are now connected to database "db2" as user "postgres".
      db2=# insert into tab_db2 values(1,'我是待访问的表,我在 db2 中');
      INSERT 0 1
      db2=#   

6) 在 db1 中去访问

db2=# \c db1 postgres
      You are now connected to database "db1" as user "postgres".
      db1=# select * from foreign_tab_db2 ;
       id |            name            
      ----+----------------------------
        1 | 我是待访问的表,我在 db2 中
      (1 row)

总结

在使用 fdw 访问外部封装数据时，上面的实例展现的仅仅是同构数据库之间的互相访问，当然，fdw 也支持异构数据库之间的互相访问，如支持 mysql_fdw,redis_fdw,oracle_fdw,sqlserver_fdw,informix_fdw等