postgresql_pgpool.tex

\chapter{PgPool-II}

\begin{epigraphs}
\qitem{Имеется способ сделать лучше~--- найди его.}{Томас Алва Эдисон}
\end{epigraphs}

\section{Введение}

pgpool-II~--- это прослойка, работающая между серверами PostgreSQL и клиентами СУБД PostgreSQL. Она предоставляет следующие функции:

\begin{itemize}

\item \textbf{Объединение соединений}

Pgpool-II сохраняет соединения с серверами PostgreSQL и использует их повторно в случае если новое соединение устанавливается с теми же параметрами (т.е. имя пользователя, база данных, версия протокола). Это уменьшает накладные расходы на соединения и увеличивает производительность системы в целом;

\item \textbf{Репликация}

Pgpool-II может управлять множеством серверов PostgreSQL. Использование функции репликации данных позволяет создание резервной копии данных в реальном времени на  2 или более физических дисков, так что сервис может продолжать работать без остановки серверов в случае выхода из строя диска;

\item \textbf{Балансировка нагрузки}

Если база данных реплицируется, то выполнение запроса SELECT на любом из серверов вернет одинаковый результат. pgpool-II использует преимущество функции репликации для уменьшения нагрузки на каждый из серверов PostgreSQL распределяя запросы SELECT на несколько серверов, тем самым увеличивая производительность системы вцелом. В лучшем случае производительность возрастает пропорционально числу серверов PostgreSQL. Балансировка нагрузки лучше всего работает в случае когда много пользователей выполняют много запросов в одно и то же время.

\item \textbf{Ограничение лишних соединений}

Существует ограничение максимального числа одновременных соединений с PostgreSQL, при превышении которого новые соединения отклоняются. Установка максимального числа соединений, в то же время, увеличивает потребление ресурсов и снижает производительность системы. pgpool-II также имеет ограничение на максимальное число соединений, но <<лишние>> соединения будут поставлены в очередь вместо немедленного возврата ошибки.

\item \textbf{Параллельные запросы}

Используя функцию параллельных запросов можно разнести данные на множество серверов, благодаря чему запрос может быть выполнен на всех серверах одновременно для уменьшения общего времени выполнения. Параллельные запросы работают лучше всего при поиске в больших объемах данных.

\end{itemize}

Pgpool-II общается по протоколу бэкенда и фронтенда PostgreSQL и располагается между ними. Таким образом, приложение базы данных (фронтенд) считает что pgpool-II~--- настоящий сервер PostgreSQL, а сервер (бэкенд) видит pgpool-II как одного из своих клиентов. Поскольку pgpool-II прозрачен как для сервера, так и для клиента, существующие приложения, работающие с базой данных, могут использоваться с pgpool-II практически без изменений в исходном коде.

Оригинал руководства доступен по адресу \href{http://pgpool.projects.pgfoundry.org/pgpool-II/doc/tutorial-en.html}{pgpool.projects.pgfoundry.org}.

\section{Давайте начнем!}
\label{sec:pgpool-II-begin}

Перед тем как использовать репликацию или параллельные запросы мы должны научиться устанавливать и настраивать pgpool-II и узлы базы данных.

\subsection{Установка pgpool-II}

Установка pgpool-II очень проста. В каталоге, в который вы распаковали архив с исходными текстами, выполните следующие команды.

\begin{lstlisting}[language=Bash,label=lst:pgpool1,caption=Установка pgpool-II]
$ ./configure
$ make
$ make install
\end{lstlisting}

Скрипт configure собирает информацию о вашей системе и использует ее в процедуре компиляции. Вы можете указать параметры в командной строке скрипта configure чтобы изменить его поведение по умолчанию, такие, например, как каталог установки. pgpool-II по умолчанию будет установлен в каталог /usr/local.

Команда make скомпилирует исходный код, а make install установит исполняемые файлы. У вас должно быть право на запись в каталог установки.

Обратите внимание: для работы pgpool-II необходима библиотека libpq для PostgreSQL 7.4 или более поздней версии (3 версия протокола). Если скрипт configure выдает следующее сообщение об ошибке, возможно не установлена библиотека libpq или она не 3 версии.

\begin{lstlisting}[label=lst:pgpool2,caption=Установка pgpool-II]
configure: error: libpq is not installed or libpq is old
\end{lstlisting}

Если библиотека 3 версии, но указанное выше сообщение все-таки выдается, ваша библиотека libpq, вероятно, не распознается скриптом configure.

Скрипт configure ищет библиотеку libpq начиная от каталога /usr/local/pgsql. Если вы установили PostgreSQL в каталог отличный от /usr/local/pgsql используйте параметры командной строки \lstinline!--with-pgsql! или \lstinline!--with-pgsql-includedir! вместе с параметром \lstinline!--with-pgsql-libdir! при запуске скрипта configure.

Во многих Linux системах pgpool-II может находиться в репозитории пакетов. Для Ubuntu Linux, например, достаточно будет выполнить:

\begin{lstlisting}[language=Bash,label=lst:pgpool3,caption=Установка pgpool-II]
$ sudo aptitude install pgpool2
\end{lstlisting}

\subsection{Файлы конфигурации}

Параметры конфигурации pgpool-II хранятся в файле pgpool.conf. Формат файла: одна пара <<параметр = значение>> в строке. При установке pgpool-II автоматически создается файл pgpool.conf.sample. Мы рекомендуем скопировать его в файл pgpool.conf, а затем отредактировать по вашему желанию.

\begin{lstlisting}[language=Bash,label=lst:pgpool4,caption=Файлы конфигурации]
$ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
\end{lstlisting}

pgpool-II принимает соединения только с localhost на порт 9999. Если вы хотите принимать соединения с других хостов, установите для параметра \lstinline!listen_addresses! значение <<*>>.

\begin{lstlisting}[label=lst:pgpool5,caption=Файлы конфигурации]
listen_addresses = 'localhost'
port = 9999
\end{lstlisting}

Мы будем использовать параметры по умолчанию в этом руководстве.

В Ubuntu Linux конфиг находится /etc/pgpool.conf.

\subsection{Настройка команд PCP}

У pgpool-II есть интерфейс для административных целей~--- получить информацию об узлах базы данных, остановить pgpool-II и т.д.~--- по сети. Чтобы использовать команды PCP, необходима идентификация пользователя. Эта идентификация отличается от идентификации пользователей в PostgreSQL. Имя пользователя и пароль нужно указывать в файле pcp.conf. В этом файле имя пользователя и пароль указываются как пара значений, разделенных двоеточием (:). Одна пара в строке. Пароли зашифрованы в формате хэша md5.

\begin{lstlisting}[label=lst:pgpool6,caption=Настройка команд PCP]
postgres:e8a48653851e28c69d0506508fb27fc5
\end{lstlisting}

При установке pgpool-II автоматически создается файл pcp.conf.sample. Я рекомендуем скопировать его в файл pcp.conf и отредактировать.

\begin{lstlisting}[language=Bash,label=lst:pgpool7,caption=Настройка команд PCP]
$ cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf
\end{lstlisting}

В Ubuntu Linux файл находится /etc/pcp.conf.

Для того чтобы зашифровать ваш пароль в формате хэша md5 используете команду pg\_md5, которая устанавливается как один из исполняемых файлов pgpool-II. pg\_md5 принимает текст в параметре командной строки и отображает текст его md5 хэша.

Например, укажите <<postgres>> в качестве параметра командной строки и pg\_md5 выведет текст хэша md5 в стандартный поток вывода.

\begin{lstlisting}[label=lst:pgpool8,caption=Настройка команд PCP]
$ /usr/bin/pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5
\end{lstlisting}

Команды PCP выполняются по сети, так что в файле pgpool.conf должен быть указан номер порта в параметре \lstinline!pcp_port!.

Мы будем использовать значение по умолчанию для параметра \lstinline!pcp_port! 9898 в этом руководстве.

\begin{lstlisting}[label=lst:pgpool9,caption=Настройка команд PCP]
pcp_port = 9898
\end{lstlisting}


\subsection{Подготовка узлов баз данных}

Теперь нам нужно настроить серверы бэкендов PostgreSQL для pgpool-II. Эти серверы могут быть размещены на одном хосте с pgpool-II или на отдельных машинах. Если вы решите разместить серверы на том же хосте, для всех серверов должны быть установлены разные номера портов. Если серверы размещены на отдельных машинах, они должны быть настроены так чтобы могли принимать сетевые соединения от pgpool-II.

В этом руководстве мы разместили три сервера в рамках одного хоста вместе с pgpool-II и присвоили им номера портов 5432, 5433, 5434 соответственно. Для настройки pgpool-II отредактируйте файл pgpool.conf как показано ниже.

\begin{lstlisting}[label=lst:pgpool10,caption=Подготовка узлов баз данных]
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1
backend_hostname2 = 'localhost'
backend_port2 = 5434
backend_weight2 = 1
\end{lstlisting}

В параметрах \lstinline!backend_hostname!, \lstinline!backend_port!, \lstinline!backend_weight! укажите имя хоста узла базы данных, номер порта и коэффициент для балансировки нагрузки. В конце имени каждого параметра должен быть указан идентификатор узла путем добавления положительного целого числа начиная с 0 (т.е. 0, 1, 2).

Параметры \lstinline!backend_weight! все равны 1, что означает что запросы SELECT равномерно распределены по трем серверам.

\subsection{Запуск/Остановка pgpool-II}
\label{sec:pgpool-II-start-stop}

Для старта pgpool-II выполните в терминале следующую команду.

\begin{lstlisting}[language=Bash,label=lst:pgpool11,caption=Запуск]
$ pgpool
\end{lstlisting}

Указанная выше команда, однако, не печатает протокол своей работы потому что pgpool отсоединяется от терминала. Если вы хотите показать протокол работы pgpool, укажите параметр -n в командной строке при запуске pgpool. Pgpool-II будет запущен как процесс не-демон и терминал не будет отсоединен.

\begin{lstlisting}[language=Bash,label=lst:pgpool12,caption=Запуск]
$ pgpool -n &
\end{lstlisting}

Протокол работы будет печататься на терминал, так что рекомендуемые для использования параметры командной строки, например, такие.

\begin{lstlisting}[language=Bash,label=lst:pgpool13,caption=Запуск]
$ pgpool -n -d > /tmp/pgpool.log 2>&1 &
\end{lstlisting}

Параметр \lstinline!-d! включает генерацию отладочных сообщений.

Указанная выше команда постоянно добавляет выводимый протокол работы в файл /tmp/pgpool.log. Если вам нужно ротировать файлы протоколов, передавайте протоколы внешней команде, у которой есть функция ротации протоколов. Вам поможет, например, cronolog.

\begin{lstlisting}[language=Bash,label=lst:pgpool14,caption=Запуск]
$ pgpool -n 2>&1 | /usr/sbin/cronolog \
  --hardlink=/var/log/pgsql/pgpool.log \
  '/var/log/pgsql/%Y-%m-%d-pgpool.log' &
\end{lstlisting}

Чтобы остановить процесс pgpool-II, выполните следующую команду.

\begin{lstlisting}[language=Bash,label=lst:pgpool15,caption=Остановка]
$ pgpool stop
\end{lstlisting}

Если какие-то из клиентов все еще присоединены, pgpool-II ждет пока они не отсоединятся и потом завершает свою работу. Если вы хотите завершить pgpool-II насильно, используйте вместо этой следующую команду.

\begin{lstlisting}[language=Bash,label=lst:pgpool16,caption=Остановка]
$ pgpool -m fast stop
\end{lstlisting}

\section{Ваша первая репликация}
\label{sec:pgpool-II-replica}

Репликация включает копирование одних и тех же данных на множество узлов базы данных.

В этом разделе мы будем использовать три узла базы данных, которые мы уже установили в разделе <<\ref{sec:pgpool-II-begin}~\nameref{sec:pgpool-II-begin}>>, и проведем вас шаг за шагом к созданию системы репликации базы данных. Пример данных для репликации будет сгенерирован программой для тестирования pgbench.

\subsection{Настройка репликации}

Чтобы включить функцию репликации базы данных установите значение true для параметра \lstinline!replication_mode! в файле pgpool.conf.

\begin{lstlisting}[label=lst:pgpool17,caption=Настройка репликации]
replication_mode = true
\end{lstlisting}

Если параметр \lstinline!replication_mode! равен true, pgpool-II будет отправлять копию принятого запроса на все узлы базы данных.

Если параметр \lstinline!load_balance_mode! равен true, pgpool-II будет распределять запросы SELECT между узлами базы данных.

\begin{lstlisting}[label=lst:pgpool18,caption=Настройка репликации]
load_balance_mode = true
\end{lstlisting}

В этом разделе мы включили оба параметра \lstinline!replication_mode! и \lstinline!load_balance_mode!.

\subsection{Проверка репликации}

Для отражения изменений, сделанных в файле pgpool.conf, pgpool-II должен быть перезапущен. Пожалуйста обращайтесь к разделу <<\ref{sec:pgpool-II-start-stop}~\nameref{sec:pgpool-II-start-stop}>>.

После настройки pgpool.conf и перезапуска pgpool-II, давайте проверим репликацию в действии и посмотрим все ли работает хорошо.

Сначала нам нужно создать базу данных, которую будем реплицировать. Назовем ее \lstinline!bench_replication!. Эту базу данных нужно создать на всех узлах. Используйте команду createdb через pgpool-II и база данных будет создана на всех узлах.

\begin{lstlisting}[language=Bash,label=lst:pgpool19,caption=Проверка репликации]
$ createdb -p 9999 bench_replication
\end{lstlisting}

Затем мы запустим pgbench с параметром \lstinline!-i!. Параметр \lstinline!-i! инициализирует базу данных предопределенными таблицами и данными в них.

\begin{lstlisting}[label=lst:pgpool20,caption=Проверка репликации]
$ pgbench -i -p 9999 bench_replication
\end{lstlisting}

Указанная ниже таблица содержит сводную информацию о таблицах и данных, которые будут созданы при помощи \lstinline!pgbench -i!. Если на всех узлах базы данных перечисленные таблицы и данные были созданы, репликация работает корректно.

\begin{tabular}{ | c | c | }
  \hline
  Имя таблицы & Число строк \\
  \hline
  branches & 1 \\
  \hline
  tellers & 10 \\
  \hline
  accounts & 100000 \\
  \hline
  history & 0 \\
  \hline
\end{tabular}

Для проверки указанной выше информации на всех узлах используем простой скрипт на shell. Приведенный ниже скрипт покажет число строк в таблицах branches, tellers, accounts и history на всех узлах базы данных (5432, 5433, 5434).

\begin{lstlisting}[label=lst:pgpool21,caption=Проверка репликации]
for port in 5432 5433 5434; do
>     echo $port
>     for table_name in branches tellers accounts history; do
>         echo $table_name
>         psql -c "SELECT count(*) FROM $table_name" -p \
>         $port bench_replication
>     done
> done
\end{lstlisting}


\section{Ваш первый параллельный запрос}

Данные из разных диапазонов сохраняются на двух или более узлах базы данных параллельным запросом. Это называется распределением (часто используется без перевода термин partitioning прим. переводчика). Более того, одни и те же данные на двух и более узлах базы данных могут быть воспроизведены с использованием распределения.

Чтобы включить параллельные запросы в pgpool-II вы должны установить еще одну базу данных, называемую <<Системной базой данных>> (<<System Database>>) (далее будем называть ее SystemDB).

SystemDB хранит определяемые пользователем правила, определяющие какие данные будут сохраняться на каких узлах базы данных. Также SystemDB используется чтобы объединить результаты возвращенные узлами базы данных посредством dblink.

В этом разделе мы будем использовать три узла базы данных, которые мы установили в разделе <<\ref{sec:pgpool-II-begin}~\nameref{sec:pgpool-II-begin}>>, и проведем вас шаг за шагом к созданию системы баз данных с параллельными запросами. Для создания примера данных мы снова будем использовать pgbench.

\subsection{Настройка параллельного запроса}

Чтобы включить функцию выполнения параллельных запросов установите для параметра \lstinline!parallel_mode! значение true в файле pgpool.conf.

\begin{lstlisting}[label=lst:pgpool22,caption=Настройка параллельного запроса]
parallel_mode = true
\end{lstlisting}

Установка параметра \lstinline!parallel_mode! равным true не запустит параллельные запросы автоматически. Для этого pgpool-II нужна SystemDB и правила определяющие как распределять данные по узлам базы данных.

Также SystemDB использует dblink для создания соединений с pgpool-II. Таким образом, нужно установить значение параметра \lstinline!listen_addresses! таким образом чтобы pgpool-II принимал эти соединения.

\begin{lstlisting}[label=lst:pgpool23,caption=Настройка параллельного запроса]
listen_addresses = '*'
\end{lstlisting}

Внимание: Репликация не реализована для таблиц, которые распределяются посредством параллельных запросов и, в то же время, репликация может быть успешно осуществлена. Вместе с тем, из-за того что набор хранимых данных отличается при параллельных запросах и при репликации, база данных <<bench\_replication>>, созданная в разделе <<\ref{sec:pgpool-II-replica}~\nameref{sec:pgpool-II-replica}>> не может быть повторно использована.

\begin{lstlisting}[label=lst:pgpool24,caption=Настройка параллельного запроса]
replication_mode = true
load_balance_mode = false
\end{lstlisting}

или

\begin{lstlisting}[label=lst:pgpool25,caption=Настройка параллельного запроса]
replication_mode = false
load_balance_mode = true
\end{lstlisting}

В этом разделе мы установим параметры \lstinline!parallel_mode! и \lstinline!load_balance_mode! равными true, \lstinline!listen_addresses! равным <<*>>, \lstinline!replication_mode! равным false.


\subsection{Настройка SystemDB}

В основном, нет отличий между простой и системной базами данных. Однако, в системной базе данных определяется функция dblink и присутствует таблица, в которой хранятся правила распределения данных. Таблицу dist\_def необходимо определять. Более того, один из узлов базы данных может хранить системную базу данных, а pgpool-II может использоваться для распределения нагрузки каскадным подключеним.

В этом разделе мы создадим SystemDB на узле с портом 5432. Далее приведен список параметров конфигурации для SystemDB

\begin{lstlisting}[label=lst:pgpool26,caption=Настройка SystemDB]
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''
\end{lstlisting}

На самом деле, указанные выше параметры являются параметрами по умолчанию в файле pgpool.conf. Теперь мы должны создать пользователя с именем <<pgpool>> и базу данных с именем <<pgpool>> и владельцем <<pgpool>>.

\begin{lstlisting}[language=Bash,label=lst:pgpool27,caption=Настройка SystemDB]
$ createuser -p 5432 pgpool
$ createdb -p 5432 -O pgpool pgpool
\end{lstlisting}

\subsubsection{Установка dblink}

Далее мы должны установить dblink в базу данных <<pgpool>>. dblink~--- один из инструментов включенных в каталог contrib исходного кода PostgreSQL.

Для установки dblink на вашей системе выполните следующие команды.

\begin{lstlisting}[language=Bash,label=lst:pgpool28,caption=Установка dblink]
$ USE_PGXS=1 make -C contrib/dblink
$ USE_PGXS=1 make -C contrib/dblink install
\end{lstlisting}

После того как dblink был установлен в вашей системе мы добавим функции dblink в базу данных <<pgpool>>. Если PostgreSQL установлен в каталог /usr/local/pgsql, dblink.sql (файл с определениями функций) должен быть установлен в каталог /usr/local/pgsql/share/contrib. Теперь выполним следующую команду для добавления функций dblink.

\begin{lstlisting}[language=Bash,label=lst:pgpool29,caption=Установка dblink]
$ psql -f /usr/local/pgsql/share/contrib/dblink.sql -p 5432 pgpool
\end{lstlisting}

\subsubsection{Создание таблицы dist\_def}

Следующим шагом мы создадим таблицу с именем <<dist\_def>>, в которой будут храниться правила распределения данных. Поскольку pgpool-II уже был установлен, файл с именем system\_db.sql должен быть установлен в /usr/local/share/system\_db.sql (имейте в виду, что это учебное руководство и мы использовали для установки каталог по умолчанию~--- /usr/local). Файл system\_db.sql содержит директивы для создания специальных таблиц, включая и таблицу <<dist\_def>>. Выполните следующую команду для создания таблицы <<dist\_def>>.

\begin{lstlisting}[language=Bash,label=lst:pgpool30,caption=Создание таблицы dist\_def]
$ psql -f /usr/local/share/system_db.sql -p 5432 -U pgpool pgpool
\end{lstlisting}

Все таблицы в файле system\_db.sql, включая <<dist\_def>>, создаются в схеме <<pgpool\_catalog>>. Если вы установили параметр system\_db\_schema на использование другой схемы, вам нужно, соответственно, отредактировать файл system\_db.sql.

Описание таблицы <<dist\_def>> выглядит так как показано ниже. Имя таблицы не должно измениться.

\begin{lstlisting}[language=SQL,label=lst:pgpool31,caption=Создание таблицы dist\_def]
CREATE TABLE pgpool_catalog.dist_def (
    dbname text, -- имя базы данных
    schema_name text, -- имя схемы
    table_name text, -- имя таблицы
    col_name text NOT NULL CHECK (col_name = ANY (col_list)),
    -- столбец ключ для распределения данных
    col_list text[] NOT NULL, -- список имен столбцов
    type_list text[] NOT NULL, -- список типов столбцов
    dist_def_func text NOT NULL,
    -- имя функции распределения данных
    PRIMARY KEY (dbname, schema_name, table_name)
);
\end{lstlisting}

Записи, хранимые в таблице <<dist\_def>>, могут быть двух типов:

\begin{itemize}
  \item Правило Распределения Данных (\lstinline!col_name!, \lstinline!dist_def_func!);
  \item Мета-информация о таблицах (\lstinline!dbname!, \lstinline!schema_name!, \lstinline!table_name!, \lstinline!col_list!, \lstinline!type_list!).
\end{itemize}

Правило распределения данных определяет как будут распределены данные на конкретный узел базы данных. Данные будут распределены в зависимости от значения столбца \lstinline!col_name!. \lstinline!dist_def_func!~--- это функция, которая принимает значение \lstinline!col_name! в качестве агрумента и возвращает целое число, которое соответствует идентификатору узла базы данных, на котором должны быть сохранены данные.

Мета-информация используется для того чтобы переписывать запросы. Параллельный запрос должен переписывать исходные запросы так чтобы результаты, возвращаемые узлами-бэкендами, могли быть объединены в единый результат.


\subsubsection{Создание таблицы replicate\_def}

В случае если указана таблица, для которой производится репликация в выражение SQL, использующее зарегистрированную в <<dist\_def>> таблицу путем объединения таблиц, информация о таблице, для которой необходимо производить репликацию, предварительно регистрируется в таблице с именем <<replicate\_def>>. Таблица <<replicate\_def>> уже была создана при обработке файла system\_db.sql во время создания таблицы <<dist\_def>>. Таблица <<replicate\_def>> описана так как показано ниже.

\begin{lstlisting}[language=SQL,label=lst:pgpool31:1,caption=Создание таблицы replicate\_def]
CREATE TABLE pgpool_catalog.replicate_def (
    dbname text, -- имя базы данных
    schema_name text, -- имя схемы
    table_name text, -- имя таблицы
    col_list text[] NOT NULL, -- список имен столбцов
    type_list text[] NOT NULL, -- список типов столбцов
    PRIMARY KEY (dbname, schema_name, table_name)
);
\end{lstlisting}


\subsection{Установка правил распределения данных}
\label{sec:pgpool-II-raspr-data}

В этом учебном руководстве мы определим правила распределения данных, созданных программой pgbench, на три узла базы данных. Тестовые данные будут созданы командой \lstinline!pgbench -i -s 3! (т.е. масштабный коэффициент равен 3). Для этого раздела мы создадим новую базу данных с именем <<bench\_parallel>>.

В каталоге sample исходного кода pgpool-II вы можете найти файл dist\_def\_pgbench.sql. Мы будем использовать этот файл с примером для создания правил распределения для pgbench. Выполните следующую команду в каталоге с распакованным исходным кодом pgpool-II.

\begin{lstlisting}[language=Bash,label=lst:pgpool32,caption=Установка правил распределения данных]
$ psql -f sample/dist_def_pgbench.sql -p 5432 pgpool
\end{lstlisting}

Ниже представлено описание файла dist\_def\_pgbench.sql.

В файле dist\_def\_pgbench.sql мы добавляем одну строку в таблицу <<dist\_def>>. Это функция распределения данных для таблицы accounts. В качестве столбца-ключа указан столбец aid.

\begin{lstlisting}[language=SQL,label=lst:pgpool33,caption=Установка правил распределения данных]
INSERT INTO pgpool_catalog.dist_def VALUES (
    'bench_parallel',
    'public',
    'accounts',
    'aid',
    ARRAY['aid', 'bid', 'abalance', 'filler'],
    ARRAY['integer', 'integer', 'integer',
    'character(84)'],
    'pgpool_catalog.dist_def_accounts'
);
\end{lstlisting}

Теперь мы должны создать функцию распределения данных для таблицы accounts. Заметим, что вы можете использовать одну и ту же функцию для разных таблиц. Также вы можете создавать функции с использованием языков отличных от SQL (например, PL/pgSQL, PL/Tcl, и т.д.).

Таблица accounts в момент инициализации данных хранит значение масштабного коэффициента равное 3, значения столбца aid от 1 до 300000. Функция создана таким образом что данные равномерно распределяются по трем узлам базы данных.

Следующая SQL-функция будет возвращать число узлов базы данных.

\begin{lstlisting}[language=SQL,label=lst:pgpool34,caption=Установка правил распределения данных]
CREATE OR REPLACE FUNCTION
pgpool_catalog.dist_def_branches(anyelement)
RETURNS integer AS $$
    SELECT CASE WHEN $1 > 0 AND $1 <= 1 THEN 0
        WHEN $1 > 1 AND $1 <= 2 THEN 1
        ELSE 2
    END;
$$ LANGUAGE sql;
\end{lstlisting}

\subsection{Установка правил репликации}

Правило репликации~--- это то что определяет какие таблицы должны быть использованы для выполнения репликации.

Здесь это сделано при помощи pgbench с зарегистрированными таблицами <<branches>> и <<tellers>>.

Как результат, стало возможно создание таблицы <<accounts>> и выполнение запросов, использующих таблицы <<branches>> и <<tellers>>.

\begin{lstlisting}[language=SQL,label=lst:pgpool35,caption=Установка правил репликации]
INSERT INTO pgpool_catalog.replicate_def VALUES (
    'bench_parallel',
    'public',
    'branches',
    ARRAY['bid', 'bbalance', 'filler'],
    ARRAY['integer', 'integer', 'character(88)']
);

INSERT INTO pgpool_catalog.replicate_def VALUES (
    'bench_parallel',
    'public',
    'tellers',
    ARRAY['tid', 'bid', 'tbalance', 'filler'],
    ARRAY['integer', 'integer', 'integer', 'character(84)']
);
\end{lstlisting}

Подготовленный файл Replicate\_def\_pgbench.sql находится в каталоге sample. Команда psql запускается с указанием пути к исходному коду, определяющему правила репликации, например, как показано ниже.

\begin{lstlisting}[language=Bash,label=lst:pgpool36,caption=Установка правил репликации]
$ psql -f sample/replicate_def_pgbench.sql -p 5432 pgpool
\end{lstlisting}

\subsection{Проверка параллельного запроса}

Для отражения изменений, сделанных в файле pgpool.conf, pgpool-II должен быть перезапущен. Пожалуйста, обращайтесь к разделу <<\ref{sec:pgpool-II-start-stop}~\nameref{sec:pgpool-II-start-stop}>>.

После настройки pgpool.conf и перезапуска pgpool-II давайте проверим хорошо ли работают параллельные запросы.

Сначала нам нужно создать базу данных, которая будет распределена. Мы назовем ее <<bench\_parallel>>. Эту базу данных нужно создать на всех узлах. Используйте команду createdb посредством pgpool-II и база данных будет создана на всех узлах.

\begin{lstlisting}[language=Bash,label=lst:pgpool37,caption=Проверка параллельного запроса]
$ createdb -p 9999 bench_parallel
\end{lstlisting}

Затем запустим pgbench с параметрами \lstinline!-i -s 3!. Параметр \lstinline!-i! инициализирует базу данных предопределенными таблицами и данными. Параметр \lstinline!-s! указывает масштабный коэффициент для инициализации.

\begin{lstlisting}[language=Bash,label=lst:pgpool38,caption=Проверка параллельного запроса]
$ pgbench -i -s 3 -p 9999 bench_parallel
\end{lstlisting}

Созданные таблицы и данные в них показаны в разделе <<\ref{sec:pgpool-II-raspr-data}~\nameref{sec:pgpool-II-raspr-data}>>.

Один из способов проверить корректно ли были распределены данные~--- выполнить запрос SELECT посредством pgpool-II и напрямую на бэкендах и сравнить результаты. Если все настроено правильно база данных <<bench\_parallel>> должна быть распределена как показано ниже.

\begin{tabular}{ | c | c | }
  \hline
  Имя таблицы & Число строк \\
  \hline
  branches & 3 \\
  \hline
  tellers & 30 \\
  \hline
  accounts & 300000 \\
  \hline
  history & 0 \\
  \hline
\end{tabular}

Для проверки указанной выше информации на всех узлах и посредством pgpool-II используем простой скрипт на shell. Приведенный ниже скрипт покажет минимальное и максимальное значение в таблице accounts используя для соединения порты 5432, 5433, 5434 и 9999.

\begin{lstlisting}[label=lst:pgpool39,caption=Проверка параллельного запроса]
for port in 5432 5433 5434i 9999; do
>     echo $port
>     psql -c "SELECT min(aid), max(aid) FROM accounts" \
>     -p $port bench_parallel
> done
\end{lstlisting}


\section{Master-slave режим}

Этот режим предназначен для использования pgpool-II с другой репликацией (например Slony-I, Londiste). Информация про БД указывается как для репликации. \lstinline!master_slave_mode! и \lstinline!load_balance_mode! устанавливается в true. pgpool-II будет посылать запросы INSERT/UPDATE/DELETE на Master DB (1 в списке), а SELECT~--- использовать балансировку нагрузки, если это возможно.

При этом, DDL и DML для временной таблицы может быть выполнен только на мастере. Если нужен SELECT только на мастере, то для этого нужно использовать комментарий \lstinline!/*NO LOAD BALANCE*/! перед \lstinline!SELECT!.

В Master/Slave режиме \lstinline!replication_mode! должен быть установлен false, а \lstinline!master_slave_mode!~--- true.

\subsection{Streaming Replication (Потоковая репликация)}

В master-slave режиме с потоковой репликацией, если мастер или слейв упал, возможно использовать отказоустоичивый функционал внутри pgpool-II. Автоматически отключив упавший нод PostgreSQL, pgpool-II переключится на следующий слейв как на новый мастер (при падении мастера), или останется работать на мастере (при падении слейва). В потоковой репликации, когда слейв становится мастером, требуется создать триггер файл (который указан в recovery.conf, параметр \lstinline!trigger_file!), чтобы PostgreSQL перешел из режима
восстановления в нормальный. Для этого можно создать небольшой скрипт:

\begin{lstlisting}[label=lst:pgpool40,caption=Скрипт выполняется при падении нода PostgreSQL]
#! /bin/sh
# Failover command for streming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
# If standby goes down, does nothing. If primary goes down, create a
# trigger file so that standby take over primary node.
#
# Arguments: $1: failed node id. $2: new master hostname. $3: path to
# trigger file.

failed_node=$1
new_master=$2
trigger_file=$3

# Do nothing if standby goes down.
if [ $failed_node = 1 ]; then
	exit 0;
fi

# Create trigger file.
/usr/bin/ssh -T $new_master /bin/touch $trigger_file

exit 0;
\end{lstlisting}

Работает он просто: если падает слейв~--- скрипт ничего не выполняет, при падении мастера~--- создает триггер файл на новом мастере. Сохраним этот файл под именем <<failover\_stream.sh>> и в pgpool.conf добавим:

\begin{lstlisting}[label=lst:pgpool41,caption=Что выполнять при падении нода]
failover_command = '/path_to_script/failover_stream.sh %d %H /tmp/trigger_file'
\end{lstlisting}

где \lstinline!/tmp/trigger_file!~--- триггер файл, указаный в конфиге recovery.conf.

Теперь, если мастер СУБД упадет, слейв будет переключен из режима восстановления в обычный и сможет принимать запросы на запись.

\section{Онлайн восстановление}

pgpool-II в режиме репликации может синхронизировать базы данных и добавлять их как ноды к pgpool. Называется это <<онлайн восстановление>>. Этот метод также может быть использован когда нужно вернуть в репликацию упавший нод базы данных.

Вся процедура выполняется в два задания. Несколько секунд или минут клиент может ждать подключения к pgpool, в то время как восстанавливается узел базы данных. Онлайн восстановление состоит из следующих шагов:

\begin{itemize}
  \item CHECKPOINT;
  \item Первый этап восстановления;
  \item Ждем, пока все клиенты не отключатся;
  \item CHECKPOINT;
  \item Второй этап восстановления;
  \item Запуск postmaster (выполнить \lstinline!pgpool_remote_start!);
  \item Восстанавливаем нод СУБД.
\end{itemize}

Для работы онлайн восстановления потребуется указать следующие параметры:

\begin{itemize}

  \item \lstinline!backend_data_directory! Каталог данных определенного PostgreSQL кластера;
  \item \lstinline!recovery_user! Имя пользователя PostgreSQL;
  \item \lstinline!recovery_password! Пароль пользователя PostgreSQL;
  \item \lstinline!recovery_1st_stage_command! Параметр указывает команду для первого этапа онлайн восстановления. Файл с командами должен быть помещен в каталог данных СУБД кластера из соображений безопасности. Например, если \lstinline!recovery_1st_stage_command = 'some_script'!, то pgpool-II выполнит \lstinline!$PGDATA/some_script!. Обратите внимание, что pgpool-II принимает подключения и запросы в то время как выполняется \lstinline!recovery_1st_stage!;
  \item \lstinline!recovery_2nd_stage_command! Параметр указывает команду для второго этапа онлайн восстановления. Файл с командами должен быть помещен в каталог данных СУБД кластера из-за проблем безопасности. Например, если \lstinline!recovery_2st_stage_command = 'some_script'!, то pgpool-II выполнит \lstinline!$PGDATA/some_script!. Обратите внимание, что pgpool-II НЕ принимает подключения и запросы в то время как выполняется \lstinline!recovery_2st_stage!. Таким образом, pgpool-II будет ждать пока все клиенты не закроют подключения.

\end{itemize}


\subsection{Streaming Replication (Потоковая репликация)}

В master-slave режиме с потоковой репликацией, онлайн восстановление~--- отличное средство вернуть назад упавший нод PostgreSQL. Вернуть возможно только слейв ноды, таким методом не восстановить упавший мастер. Для восстановления мастера потребуется остановить все PostgreSQL ноды и pgpool-II (для восстановления из резервной копии мастера).

Для настройки онлайн восстановления нам потребуется:

\begin{itemize}

  \item Установить \lstinline!recovery_user!. Обычно это <<postgres>>.

\begin{lstlisting}[label=lst:pgpool42,caption=recovery\_user]
recovery_user = 'postgres'
\end{lstlisting}

  \item Установить \lstinline!recovery_password! для \lstinline!recovery_user! для подключения к СУБД.

\begin{lstlisting}[label=lst:pgpool43,caption=recovery\_password]
recovery_password = 'some_password'
\end{lstlisting}

  \item Настроить \lstinline!recovery_1st_stage_command!. Для этого создадим скрипт basebackup.sh и положим его в папку с данными мастера (\lstinline!$PGDATA!), установив ему права на выполнение. Содержание скрипта:

\begin{lstlisting}[label=lst:pgpool44,caption=basebackup.sh]
#! /bin/sh
# Recovery script for streaming replication.
# This script assumes that DB node 0 is primary, and 1 is standby.
#
datadir=$1
desthost=$2
destdir=$3

psql -c "SELECT pg_start_backup('Streaming Replication', true)" postgres

rsync -C -a --delete -e ssh --exclude postgresql.conf --exclude postmaster.pid \
--exclude postmaster.opts --exclude pg_log --exclude pg_xlog \
--exclude recovery.conf $datadir/ $desthost:$destdir/

ssh -T localhost mv $destdir/recovery.done $destdir/recovery.conf

psql -c "SELECT pg_stop_backup()" postgres
\end{lstlisting}

При восстановления слейва, скрипт запускает бэкап мастера и через rsync передает данные с мастера на слейв. Для этого необходимо настроить SSH так, чтобы \lstinline!recovery_user! мог заходить с мастера на слейв без пароля.

Далее добавим скрипт на выполнение для первого этапа онлайн востановления:

\begin{lstlisting}[label=lst:pgpool45,caption=recovery\_1st\_stage\_command]
recovery_1st_stage_command = 'basebackup.sh'
\end{lstlisting}

  \item Оставляем \lstinline!recovery_2nd_stage_command! пустым. После успешного выполнения первого этапа онлайн восстановления, разницу в данных, что успели записатся во время работы скрипта basebackup.sh, слейв заберет через WAL файлы с мастера.

  \item Устанавливаем C и SQL функции для работы онлайн востановления на каждый нод СУБД.

\begin{lstlisting}[language=Bash,label=lst:pgpool46,caption=Устанавливаем C и SQL функции]
$ cd pgpool-II-x.x.x/sql/pgpool-recovery
$ make
$ make install
$ psql -f pgpool-recovery.sql template1
\end{lstlisting}

\end{itemize}

Вот и все. Теперь возможно использовать \lstinline!pcp_recovery_node! для онлайн восстановления упавших слейвов.


\section{Заключение}

PgPool-II~--- прекрасное средство, которое нужно применять при масштабировании PostgreSQL.