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
2. Table objects are stored in different databases
In two databases db1 and db2 Create a table in
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. User access tables cannot be accessed across databases
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
4. Use 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'm the table to be accessed, I'm 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