Секционирование и внешние таблицы

В 9.5 внешние таблицы можно включать в наследование. Это позволяет распределить секции по разным источникам, в частности по разным базам данных.

Пример.

postgres@student:~$ psql -a -f fdw.sql

/*

Добавим расширение postgres_fdw в template1,

чтобы оно автоматически устанавливалось в новые БД

*/

\c template1

You are now connected to database "template1" as user "postgres".

create extension if not exists postgres_fdw;

psql:fdw.sql:6: NOTICE:  extension "postgres_fdw" already exists, skipping

CREATE EXTENSION

/*

Создадим две базы: локальную и удаленную

Для целей примера, это просто две БД в одном кластере

*/

drop database if exists db_remote;

DROP DATABASE

drop database if exists db_local;

DROP DATABASE

create database db_remote;

CREATE DATABASE

create database db_local;

CREATE DATABASE

/*

В каждой из баз создадим таблицу t с одинаковой структурой

В локальной БД дополнительно создадим родительскую таблицу t_parent

Это будет наша распределенная таблица

*/

\c db_remote

You are now connected to database "db_remote" as user "postgres".

create table t (id int, dbname text, check (dbname = 'db_remote'));

CREATE TABLE

insert into t values (1, 'db_remote');

INSERT 0 1

\c db_local

You are now connected to database "db_local" as user "postgres".

create table t (id int, dbname text, check (dbname = 'db_local'));

CREATE TABLE

insert into t values (2, 'db_local');

INSERT 0 1

create table t_parent (id int, dbname text);

CREATE TABLE

/*

В локальной БД создадим внешнюю таблицу для таблицы t

в БД db_remote

*/

create server remote_server

   foreign data wrapper postgres_fdw

   options (dbname 'db_remote');

CREATE SERVER

create user mapping for postgres

   server remote_server

   options (user 'postgres');

CREATE USER MAPPING

create foreign table t_remote

   (id int, dbname text)

   server remote_server

   options (schema_name 'public', table_name 't');

CREATE FOREIGN TABLE

/*

Для внешней таблицы также требуется ограничение check для работы секционирования

*/

alter table t_remote add constraint check_db check (dbname = 'db_remote');

ALTER TABLE

/*

Включим наследование и проверим работу секционирования

*/

alter table t_remote inherit t_parent;

ALTER TABLE

alter table t inherit t_parent;

ALTER TABLE

/*

Проверим что видны данные из обеих таблиц:

*/

select tableoid::regclass, * from t_parent;

 tableoid | id |  dbname  

----------+----+-----------

 t        |  2 | db_local

 t_remote |  1 | db_remote

(2 rows)

/*

Проверим работу секционирования

*/

explain select * from t_parent where dbname = 'db_local';

                          QUERY PLAN                          

---------------------------------------------------------------

 Append  (cost=0.00..25.88 rows=7 width=36)

   ->  Seq Scan on t_parent  (cost=0.00..0.00 rows=1 width=36)

         Filter: (dbname = 'db_local'::text)

   ->  Seq Scan on t  (cost=0.00..25.88 rows=6 width=36)

         Filter: (dbname = 'db_local'::text)

(5 rows)

explain select * from t_parent where dbname = 'db_remote';

                              QUERY PLAN                              

-----------------------------------------------------------------------

 Append  (cost=0.00..127.20 rows=8 width=36)

   ->  Seq Scan on t_parent  (cost=0.00..0.00 rows=1 width=36)

         Filter: (dbname = 'db_remote'::text)

   ->  Foreign Scan on t_remote  (cost=100.00..127.20 rows=7 width=36)

(4 rows)