preface

PostgresSQL is a single example multi database. Usually, a database instance contains multiple tablespaces and databases. One tablespace can store multiple databases. In this relationship, one tablespace can contain multiple databases, but a database can only belong to one tablespace and cannot be stored across tablespaces. Table space is a logical concept. In essence, in PostgresSQL, a table space is actually a directory that can be read and written by users installing PostgresSQL. Therefore, the maximum size of a database cluster depends on the total size of the directories corresponding to these table spaces that make up the database. Then, the database cluster composed of multiple databases can be used to isolate the data of different businesses and facilitate the storage of management data. However, since the data of different businesses are distinguished by databases, there may be associated data between these business data in a database cluster or in database clusters belonging to different instances, So how can we achieve cross database or cross database instance access? What are the implementation methods?

The solutions to the above problems include the following solutions:

·In the same database cluster or cross instance database cluster, you can use postgres_fdw and db_link access.

·Using the LSR provided in version 10, you can synchronize the required data to the queried database instance, so as to query locally.

postgres_ Introduction to FDW

brief introduction

fdw(Foreign Data Wrapper), external data wrapper, using postgres_fdw can access data on an external PostgreSQL database server. postgres_fdw is an external encapsulation access extension application specially provided for homogeneous database instances. The steps of using fdw are as follows:

·Create extension

·Create service

·Create user mapping

·Create the appearance corresponding to the access table

1. Database db1 db2

Create two databases using the postgres user

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

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

1. Table objects are stored in different databases

Create a table in the two databases db1 and db2 respectively

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

1. User access tables cannot be accessed across libraries

   If the table is accessed in the following way, db1 will be treated as a schema, so it cannot be accessed in this way

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

1. Using postgres_fdw

   1) Create an extension on db1

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

      db1=# create extension postgres_fdw ;
      CREATE EXTENSION

2) Create service

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) Create user mapping

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

4) Create local external table mapping to remote table

The fields of the local external table here need to be consistent with those of the remote table to be accessed

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) Insert data validation in db2 tables

db1=# \c db2 postgres
      You are now connected to database "db2" as user "postgres".
      db2=# insert into tab_db2 values(1, 'I am the table to be accessed, I am in db2');
      INSERT 0 1
      db2=#   

6) Access in db1

db2=# \c db1 postgres
      You are now connected to database "db1" as user "postgres".
      db1=# select * from foreign_tab_db2 ;
       id |            name            
      ----+----------------------------
        1 | I'm the watch to be visited,I am here db2 in
      (1 row)

summary

When using FDW to access external encapsulated data, the above example only shows the mutual access between homogeneous databases. Of course, FDW also supports the mutual access between heterogeneous databases, such as mysql_fdw,redis_fdw,oracle_fdw,sqlserver_fdw,informix_fdw et al