[Перевод] PostgreSQL кластер: партишинг с plproxy (часть 2)

оригинал в блоге Kristo Kaiv

В предыдущем посте я описал как установить plproxy и сделать базовый вариант горизонтального кластера.
Теперь взглянем на другое типичное применение в жизни: создание кластера только-чтение для вашей БД.

Разделение нагрузки на чтение.

Самое простое использование в жизни для plproxy - использование для перенаправления запросов только-чтение на реплики "только-чтение" основной БД. Реплицированые БД могут быть заполнены данными с помощью Londiste из пакета SkyTools. Описание установки может быть найдено тут. Или можно использовать Slony, который является более тяжелым решением и из моего опыта так же более труден в установке и поддержке, хотя и лучше документирован.

Типичный кластер "только-чтение" изображен на схеме

БД со знаком "p" имеют пул соединений. Мы используем PgBouncer но pgpool тоже хорошее решение.

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

В этой установке plproxy-функции определяют базу, к которой нужно перенаправить запрос. Запросы на чтение-запись идут к мастер-базе, а только-чтение к репликам "только-н-чтение".

Настройка репликации проста, как только вы пройдете сложный процесс установки утилит skytools.

Первое - давайте создадим конфигурацию для реплик и мастер-базы:

файл replica1.ini

[londiste]

job_name = londiste_master_to_r1
provider_db = dbname=write
subscriber_db = dbname=ro1
# значение будет использовано как идентификатор, так что не используйте точки и пробелы
pgq_queue_name = londiste.write
pidfile = %(job_name)s.pid
logfile = %(job_name)s.log
use_skylog = 0

Файл replica2.ini практически такой же, только название задания и имя базы нужно изменить. Теперь установим Londiste на провайдере (мастер-базе) и подписчиках (r01, r02) и запусим демонов (службы) репликации:

mbpro:~/temp kristokaiv$ londiste.py replica1.ini provider install
mbpro:~/temp kristokaiv$ londiste.py replica1.ini subscriber install
mbpro:~/temp kristokaiv$ londiste.py replica2.ini subscriber install
mbpro:~/temp kristokaiv$ londiste.py replica1.ini replay -d
mbpro:~/temp kristokaiv$ londiste.py replica2.ini replay -d

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

файл ticker_write.ini

[pgqadm]
job_name = ticker_write
db = dbname=write
# как часто запусткать задачу [минуты]
maint_delay_min = 1
# как часто проверять активность [секунды]
loop_delay = 0.1
logfile = %(job_name)s.log
pidfile = %(job_name)s.pid
use_skylog = 0

Для запуска тикера как демон (служба) выполните:

mbpro:~/temp kristokaiv$ pgqadm.py ticker_write.ini ticker -d

Давайте создадим простую таблицу, которую мы будем реплицировать с мастера на репликантов "только-чтение".

mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key, password text);" write

mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key, password text);" ro1

mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key, password text);" ro2

Добавляем её к репликации

mbpro:~/temp kristokaiv$ londiste.py replica1.ini provider add users
mbpro:~/temp kristokaiv$ londiste.py replica1.ini subscriber add users
mbpro:~/temp kristokaiv$ londiste.py replica2.ini subscriber add users

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

Функции для вставки и выборки из таблицы users:

CREATE OR REPLACE FUNCTION public.add_user(
    in i_username text,
    in i_password text,
    out status_code text
) AS $$
BEGIN
    PERFORM 1 FROM users WHERE username = i_username;
    IF NOT FOUND THEN
        INSERT INTO users (username, password) VALUES (i_username, i_password);
        status_code = 'OK';
    ELSE
        status_code = 'user exists';
    END IF;
    RETURN;
END; $$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION public.add_user(
    in i_username text,
    in i_password text,
    out status_code text
) TO plproxy;

CREATE OR REPLACE FUNCTION login(
    in i_username text,
    in i_password text,
    out status_code text
) AS $$
BEGIN
    SELECT 'OK' FROM users u WHERE username = i_username AND password = i_password INTO status_code;
    IF NOT FOUND THEN status_code = 'FAILED'; END IF;
    RETURN;
END; $$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION login(
    in i_username text,
    in i_password text,
    out status_code text
) TO plproxy;

Только для комфорта тех, кто реально попробует повторить эти шаги, привожу конфигурацию прокси-бд:

cluster config:

CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions (cluster_name text)
RETURNS SETOF text AS $$
BEGIN
   IF cluster_name = 'readonly' THEN
        RETURN NEXT 'host=127.0.0.1 dbname=ro1';
        RETURN NEXT 'host=127.0.0.1 dbname=ro2';
        RETURN;
    ELSIF cluster_name = 'write' THEN
        RETURN NEXT 'host=127.0.0.1 dbname=write';
        RETURN;
    END IF;
    RAISE EXCEPTION 'no such cluster%', cluster_name;
END; $$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
    in cluster_name text,
    out key text,
    out val text)
RETURNS SETOF record AS $$
BEGIN
    RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text) RETURNS int AS $$
    SELECT 1;
$$ LANGUAGE SQL;

Последнее что осталось сделать - создать plproxy-функцию, которая перенаправляет вызов функции логина на бд только-чтение и функцию add_user на бд для записи (мастер).

CREATE OR REPLACE FUNCTION public.login(
    in i_username text,
    in i_password text,
    out status_code text
) AS $$
CLUSTER 'readonly'; RUN ON ANY;
$$ LANGUAGE plproxy;

CREATE OR REPLACE FUNCTION public.add_user(
    in i_username text,
    in i_password text,
    out status_code text
) AS $$
CLUSTER 'write';
$$ LANGUAGE plproxy;

Вот и всё. кластер готов! Учтите, что хотя создание кластера только-чтение выглядит просто и как быстрое решение для ваших проблем с производительностью - это не решение "серебряная пуля".

Асинхронная репликация часто создает больше проблем чем решает. Так что будте осторожнее - реплицируйте только не критичную ко времени информацию. Или гарантируйте запасное решение когда данные не найдены (например - прокси функция сначала проверяет базу только-чтение и если данные не найдены - ищет их в мастер-базе).