关于数据库: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 等