Репликация кластера

Про настройку репликации мастер-слэйв в Postgresql написано много и довольно давно. Выделю лишь этот пост на Хабре для примера. Важно различать, что репликация и "горячий бэкап" настраиваются и работают отдельно. Репликация - это синхронизация данных серверов слэйвов с мастером в режиме одновременной работы. В случае запуска слэйва после длительной остановки (достаточно 2-3 часов при активных изменениях на мастере) синхронизация с мастером прекращается, поскольку требуемые файлы изменений (транзакций) на мастере уже были удалены и возникает ощибка чтения на слэйве. В этом случае потребуется выполнить полное копирование кластера с мастера на слэйв (выполнить перебазирование) и перезапустить слэйв для старта репликации.

Для избежания этого нужна настройка "горячего резерва" - каталога, где будут храниться все файлы изменений на мастере (WAL-файлы) и откуда они будут считываться в случае прерывания репликации на слэйве.

Ниже приведен текст скрипта для Ubuntu создания зеркала (на слэйве) для базы данных на мастере в режиме только репликации без резерва.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#!/bin/bash
#
# Создаем кластер для репликации на слэйве
#
# На мастере нужно внести правки в postgresql.conf
#   wal_level = hot_standby
#   max_wal_senders = 2
#   wal_keep_segments = 32
#   checkpoint_segments = 16
# В pg_hba.conf добавить
#   host    replication     postgres        0.0.0.0/0            md5
#
# На слэйве внести в postgresql.conf
#   hot_standby = on

export PGUSER=postgres
export PGPASSWORD=postgres

service postgresql stop

pgconf=/etc/postgresql/9.3/main/postgresql.conf

cat $pgconf | sed -e 's/^\#\(hot_standby =\) off/\1 on/' > $pgconf.tmp
[ -s $pgconf.tmp ] && { 
    mv $pgconf.tmp $pgconf
    chown postgres:postgres $pgconf
    chmod 640 $pgconf
}

rm -fr /home/pgrepl
pg_basebackup --host=192.168.0.5 -R -P -D /home/pgrepl || exit 1

chown -R postgres:postgres /home/pgrepl

clusterdir="/var/lib/postgresql/9.3/main"
[ -d "$clusterdir" ] && mv $clusterdir $clusterdir.0

ln -s /home/pgrepl $clusterdir

service postgresql start

Базы данных на созданном кластере на слэйве будут доступны только на чтение и синхронно отражать изменения по запросам INSERT - UPDATE - DELETE, выполняемые на мастере.

В случае восстановления в базу данных на слэйве дампа базы, созданного pg_dump'ом, кроме дампа, созданного с параметрами "Использовать команды Insert", зеркало перестает синхронизироваться с мастером и работает только на чтение.

В случае создания в кластере слэйва триггер-файла и перевода баз в режим чтение-запись, обратно в режим зеркала (доступ на чтение и синхронизация с мастером) уже нельзя.

guru написал 1 год,5 месяцев назад

Работа с индексами в PostgreSQL

Примеры как работать с индексами таблиц для ускорения выборки данных. Для примера возьмем таблицу "grade" (данные по классам учеников школ) с 2,5 млн. записей. Тесты проводились на PostgreSQL 7.4.1.

Структура таблицы и индекса:

CREATE TABLE "grade" 
(
  "id_school" integer,
  "id_class" integer,
  "sum_count" integer,
  "year_num" integer,
  "male_ratio" double precision,
  "avg_score" double precision
)
WITH OIDS;

CREATE INDEX "grade_index" 
  ON "grade" 
  USING btree
  ("id_class", "id_school", "sum_count", "year_num", "male_ratio");

Запросы к таблице. Работает или нет индекс определяем по explain:

--индекс работает - время < 0.5 сек - ~ 300 тыс. строк
select * from "grade" where "id_class"=14 and "id_school"=1 and "sum_count"=14;
--индекс не работает - время > 2 сек - ~ 600 тыс. строк
select * from "grade" where "id_class"=14 and "id_school"=1;
select * from "grade" where "id_class"=14;

--индекс работает
select * from "grade" where 
    (id_class=14 and id_school=14 and year_num>0 and sum_count=14) or
    (id_class=1 and id_school=1 and year_num>2000 and sum_count=14) or
    (id_class=14 and id_school=2 and year_num>2000 and sum_count=14) and male_ratio between 1 and 200 limit 100;

--индекс не работает
select * from "grade" where 
    (id_class=14 and id_school=14 and year_num>0 and sum_count>0);

Индекс работает на равно (=) когда число полей в запросе больше половины числа полей в индексе. Чувствителен к операциям множеств (>,<,BETWEEN) и конструкции IN (ее нужно заменить на комбинацию с OR).

egax написал 5 лет назад

Войдите, чтобы оставлять сообщения.