Репликация в Postgresql 9.0

    Доброго времени суток. Учитывая, что с момента релиза PostgreSQL 9 прошло уже некоторое количество времени — я решил пощупать одну из его новых функций — нативную репликацию. Как известно, новый механизм основан на пересылке XLOG`a от мастера к слейву. Одним из жирных плюсов можно назвать нормальную обработку ALTER`ов. Иными словами — администратор 9й версии может обойтись без Slony.

    Будем считать, что пакеты у вас уже установлены(а если нет — для Debian/Ubuntu их можно взять здесь), создана база testdb. Я описываю процесс считая, что базы postgres`a у вас лежат в /var/lib/postgresql/9.0/, если это не так — используйте корректный для вас путь.

    Итак, поехали:
    1. Настраиваем listen && pg_hba.conf
    Предположим, что у ip мастера у нас 192.168.0.1, а слейва — 192.168.0.2. Тогда строка listen в postgresql.conf у нас будет выглядеть так:
    listen_addresses = '192.168.0.1'

    А, в pg_hba.conf на мастере будет такая запись:
    host replication postgres 192.168.0.2/32 trust

    2. Включаем на мастере всё, что нужно для репликации
    #Выставляем ведение журнала таким образом, чтобы слейв мог использоваться для чтения. Можно вместо hot_standby поставить archive и тогда он будет просто хранилищем журанала(нечитаемым).
    wal_level = hot_standby

    #Максимальное количество слейвов
    max_wal_senders = 2

    #Сколько кусков лога будем хранить?Если вдруг у вас большая нагрузка на запись в базу - возможно это значение нужно будет увеличить, чтобы всё успевало доезжать до реплики.
    wal_keep_segments = 32

    #На случай ядерной войны дублируем журнал в отдельное место(лучше чистить по крону эту локацию, удаляя всё, чему больше суток). Хотя, офф. ман говорит, что оно вообще не обязательно.
    archive_mode = on
    archive_command = 'cp %p /var/lib/postgresql/9.0/main/archive/%f'

    Теперь мастер нужно перезапустить.

    3. Отправляем базу с мастера на слейв.
    Нам потребуется что-нибудь, что умеет пересылать данные по сети. Я использовал rsync, хотя, разумеется, можно применять любое другое средство. Тушим на слейве postgresql, после чего на мастере выполняем следующее:
    $ psql -c "SELECT pg_start_backup('label', true)"
    $ rsync -a /var/lib/postgresql/9.0/main/ slave:/var/lib/postgresql/9.0/main/ --exclude postmaster.pid
    $ psql -c "SELECT pg_stop_backup()"


    4. Включаем hot_standby на слейве
    Добавляем в postgresql.conf:
    hot_standby = on

    5. Создаем конфиг репликации на слейве.
    Для этого в файлике recovery.conf(его нужно создать), лежащем в /var/lib/postgresql/9.0/main пишем следующее:
    standby_mode = 'on'
    primary_conninfo = 'host=192.168.0.1 port=5432 user=postgres'
    trigger_file = '/var/lib/postgresql/9.0/main/trigger'
    restore_command = 'cp /var/lib/postgresql/9.0/main/archive/%f "%p"'

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

    6. Готово!
    Запускаем слейв. Если на слейве команда
    ps aux | grep receiver
    Показывает что-то вида
    postgres 1953 0.0 0.0 101980 4156 ? Ss 19:19 0:00 postgres: wal receiver process streaming 2/B40001D0
    (читай — есть процесс postgres с описанием wal receiver) — можно считать, что всё работает.

    7. Мониторинг
    Теперь немного о грустном — в отличие от слонов, нативная репликация не умеет показывать лаг в человеко-читаемом формате.
    Один из возможных способов мониторинга репликации — считать разницу в позициях журнала на мастере/слейве:
    psql -c "SELECT pg_current_xlog_location()" -h192.168.0.1
    --------------------------
    0/2000000
    (1 row)

    psql -c "select pg_last_xlog_replay_location()" -h192.168.0.2
    pg_last_xlog_replay_location
    ------------------------------
    0/2000000
    (1 row)

    От полученных результатов нужно отрезать слэш и то, что до него, далее конвертировать из HEX в нормальное число — результатом будет некое абстрактное значение, критический порог которого будет индивидуален для каждого случая.
    Более правильным способом мне видится создание таблички с одним полем типа timestamp, держать там одну единственную запись и раз в n-секунд(например 30) обновлять её значение на текущее время. Тогда, вычитая из записи в мастере, содержимое этой же таблички на слейве, мы получим время отставания.

    8. Если случилось ужасное
    Как правило, репликация нужна для двух случаев: распределение нагрузки и на случай, если с мастером что-то случится. Так вот, если у вас отказал мастер, то ваши действия будут примерно такими:
    а) Создаём на слейве триггер-файл(описано в 5м пункте). Слейв откроется на запись, прекратив репликацию — можно переводить на него клиентов.
    б) Далее, когда машина, обслуживающая мастер, вернётся в строй — мы оборачиваем процесс вспять — делаем из изначального мастера реплику — выполняем на мастере 5й пункт. Когда оно восстановится — удаляем на слейве триггер-файл — всё должно вернуться в норму.

    P.S. Данный текст является вольным переводом этой вики-страницы.

    Комментарии 42

      0
      >Теперь немного о грустном — в отличие от слонов, нативная репликация не умеет показывать лаг в человеко-читаемом формате.

      И вся грусть? Или есть еще что-нибудь?
        0
        Пока никаких катаклизмов не обнаружил. Переносил несколько серверов с базами разного размера(от 3 до 250 Гб) — все переехали без проблем, путём простого импорта дампа. Работает ровно.
        +1
        а двустороннюю репликацию кто-то поднимал?
          0
          Вам нужен мастер-мастер? Зачем?
            +3
            Т.е. Вы реально считаете, что мастер-мастер не нужен?
              0
              в 90% случаев — нет
                0
                'эт Вы просто привыкли без него обходиться. хороший мастер-мастер это огогого как хорошо.
                  0
                  Да, догадываюсь, но где бы его взять в случае с постгресом)
                    0
                    Bucardo, RubyRep?
                      0
                      Ни одного из этих не использовал. Как они в работе?
                        0
                        Зависит от потребностей. Можете тут почитать — postgresql.leopard.in.ua/
                          0
                          Беглый обзор дал понять что оба они основаны на триггерах, а этого бы не хотелось т.к. по опыту со slony, вызывает дополнительный overhead.
            +1
            Это 2 отдельных случая:
            — мастер-слейв используется для создания бакапа, разгрузки мастера от операций чтения и тем самым повышения скорости работы. И то и другое полезно в веб-проектах. На мастере и слейве данные немного расходятся по времени на так называемый лаг репликации.
            — мастер-мастер — «горячий» failover для минимизации простоя и не потери данных при строгой синхронности данных. Такая система работает медленее чем один мастер-сервер, потому что он при каждой транзакции он должен дождаться подтверждения коммита от слейва. Нужна только для работы с критически важными данными — например, в банке, для веб-проекта она скорее вредна.
            +1
            Тоже присматриваюсь к новой репликации, но хочется обратить внимание на три момента, которые сразу же бросаются в глаза:

            1. Можно ли исключить из репликации часть таблиц (или хотя бы ту или иную схему) в той или иной БД? Т.е. — часть таблиц реплицируется, часть — нет.

            2. Когда мастер «дохнет», а слейвов — много, очень часто бывает, что одни слейвы успели накатить какие-то изменения, а другие — еще не успели. Соответственно, нельзя просто так назначить произвольный слейв мастером — это все поломает. Слони в этом случае (он называется failover) делает хитро: назначает слейв мастером, затем идет на все остальные слейвы и «добирает» с них то, чего не хватает, потом — рассылает всем изменения, так что в итоге весь кластер оказывается синхронизированным.

            3. Похоже, нет инструментария, который позволил бы сделать failover (посмертное переключение) / switchover (прижизненное переключение) мастера одной командой?

            Пункт (1) очень нужен для смешанной схемы «репликация+шардинг», когда в БД есть общая часть данных (всякие справочники и т.д. — то, чего мало и что редко меняется) и шарденная (то, чего много и часто меняется), причем из шарденной части есть ссылки по внешним ключам в общую, общая — реплицируется, а шарденная — нет (содержит разные данные на разных машинах). В Слони с этим проблем нет.

            Пункт (2) делает крайне опасным использование встроенной репликации для кластеров из 5-6 машин — на моей памяти было 6 или 7 случаев, когда в Slony он очень спасал (не считая учений).
              0
              1. Нет. Создается точная копия инстанса PostgreSQL.
              2. Слейвы накатывают с WAL логов данные, так что то что успел сбросить мастер перед падением будет на все слейвах (рано или поздно). Так что они всегда при падении мастера синхронны в данных (Не сразу, может зависить от настроек кластера).
              3. Одной команды нет, но возможность есть.

              репликация+шардинг = кластеризация :)
                0
                2. Не соглашусь. Представьте, что мастер умер на долгое время (дни), и за секунду перед его смертью один из слейвов не успел-таки забрать WAL-логи с него (например, сеть лагнула или просто была очень большая нагрузка на слейв в этот момент — не важно).
                  0
                  Что мешает слейву забрать логи после того как сеть не перестала «лагать»?
                  P.S. WAL логи — это файлы с изменениями в бд сброшеные в отдельную папку (а то вдруг вы про другое думаете)
                    0
                    Мешает то, что машина, с которой он должен забирать логи (т.е. мастер), умерла. Ощущение, что мы о каких-то разных вещах говорим; уточните, пожалуйста, что Вы имеете в виду?

                    Лаги сети — это только самый очевидный пример, не более того. В нагруженных системах слейвы могут на секунду-две отстать в заборе логов по самым разным причинам.
                      +2
                      Если уж мастер умер с концами(сгорел хостинг или посыпался винт), тогда достаточно проверить через на слейвах
                      select pg_last_xlog_replay_location()
                      Где наибольшее число — тот ставим мастером. Переносим данные через онлайн бекап на те слейвы, что отличаются (на те, которые успели — не надо) и работаем дальше. При этом мастер во время переноса разницы через онлайн бекап может работать без перегрузок.
              +1
              mixermsk — поставьте, пожалуйста, в статье комментарии ПЕРЕД директивами, а директивы — разделите пустой строкой, как было в исходной вики-странице.
                0
                Используем для масштабирования pl proxy.
                данные распределяются по нескольким серверам баз данных равномерно по id пользователя
                Но уж больно много телодвижений требует такая схема и с трудом себе представляю момент когда нам потребуется увеличить число баз в 2-3 раза.

                Кто какие решения использует для масштабирования postgres?
                  0
                  Londiste + PgPool-II = replication (Londiste) + load balancing (pgpool) + failover (pgpool) + connection pool (pgpool)
                  В будущем заменим Londiste на стриминг репликацию.
                    0
                    Могли бы и статейку на эту тему написать, а то ищешь ищешь, что нибудь по репликации posgtreSQL, а ничего толкового не написано.
                  0
                  PgPool2 отличная вещь, использую только ее. Единственное что могу сказать про него — нельзя использовать конструкции NOW() и вызывать UPDATE, DELETE, INSERT внутри функций который будут дергаться SELECT, это надо учитывать при разработке, а так вполне отличная штука.

                  PgPool2 позволяет реализовывать репликации любых видов, как MASTERMASTER, так и MASTERSLAVE и еще несколько модификаций.

                  Слышал что Postresql пул хотела брать под свое крыло, очень надеюсь что они это сделают.
                    0
                    Вещь то хорошая, но как утилита для репликации её стоить брать в последнюю очередь (при INSERT нужно лочить таблицы, синхронная, при нагрузке упадут все, а не только мастер). Вообще лучше делать так, что бы приложение и не знало есть там кластер или нет, и работало с минимальным изменением кода.
                      0
                      При нагрузках которые связанные с INSERT может и не стоит, однако если соотношение запросов больше в пользу SELECT запросов то очень даже хорошо использовать.

                      Вообще всегда надо смотреть в первую очередь на задачу и уже потом выбирать подход. Вот pgpool2 я предпочитаю использовать именно на когда есть большое количество INSERT и огромное количество SELECT тогда проблем нет.

                      Так что все методы хороши, но в первую очередь надо смотреть на задачу которую необходимо реализовать. Любой очень большой проект не может быть нормализированным, он всегда будет использовать не стандартные подходы реализации и поэтому говорить о том что приложение не должно волновать что там стоит кластер или нет, так говорить нельзя. Большая система она должна собираться с учетом всего и каждый элемент должен взаимодействовать с другим и знать о нем все.
                        0
                        Согласен. Многое из сказаного верно, но насчет
                        >>Большая система она должна собираться с учетом всего и каждый элемент должен взаимодействовать с другим и знать о нем все.
                        взаимодействовать — да, знать о нем все — нет.
                    0
                    Многие используют репликацию для распределения нагрузки как я вижу.

                    Но мы уперлись в размер таблицы — при достижении определенного размера (более 20 мл записей)
                    запросы проходят плохо, даже по основному ключу.
                    Потому было решено кластеризовать базу в целях уменьшения именно размера таблиц.
                    И как я понимаю репликация тут не поможет?
                      +1
                      Для начала — можно глянуть партрицирование больших таблиц(например, коментарии, логи пользователей). Если же данные все актуально выбираются (например, таблица юзеров) — шардинг.
                        0
                        партиционирование — так правильно. Ошибся при написании.
                          0
                          я правильно понял, что вы предлагаете вместо одной таблицы 'users' сделать 'users_1', 'users_2', ..., 'users_n'?
                            0
                            Вы про партиционирование?

                            Партиционирование (partitioning) — это разбиение больших таблиц на логические части по выбранным критериям. Звучит сложно, но на практике все просто.

                            Скорее всего у Вас есть несколько огромных таблиц (обычно всю нагрузку обеспечивают всего несколько таблиц СУБД из всех имеющихся). Причем чтение в большинстве случаев приходится только на самую последнюю их часть (т.е. активно читаются те данные, которые недавно появились). Примером тому может служить блог — на первую страницу (это последние 5…10 постов) приходится 40…50% всей нагрузки, или новостной портал (суть одна и та же), или системы личных сообщений… впрочем понятно. Партиционирование таблицы позволяет базе данных делать интеллектуальную выборку — сначала СУБД уточнит, какой партиции соответствует Ваш запрос (если это реально) и только потом сделает этот запрос, применительно к нужной партиции (или нескольким партициям). Таким образом, в рассмотренном случае, Вы распределите нагрузку на таблицу по ее партициям. Следовательно выборка типа “SELECT * FROM articles ORDER BY id DESC LIMIT 10” будет выполняться только над последней партицией, которая значительно меньше всей таблицы.

                            Многие СУБД поддерживают партиционирование на том или ином уровне, например:
                            dev.mysql.com/doc/refman/5.1/en/partitioning.html — Партиционирование в Mysql, отлично реализовано на уровне СУБД (убедитесь, что Ваша версия >= 5.1).
                            www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html — Партиционирование в Postgres, не так хорошо, но все же возможность есть.
                              0
                              И первый же select count(*) from articles для построения постранички приведет к тому, что БД полезет шуршать по всем партициям, что в случае с постгресом, будет означать N запросов.

                              Запросы select * from article where id = X будут выполняться так же быстро, как без партиционирования. А в умную БД которая поймет, что при ORDER BY id DESC LIMIT 10 надо смотреть только в последнюю партицию мне не верится.

                              Я затрудняюсь привести пример, когда партишнинг таблицы в рамках одного сервера приведет к заметному выигрышу в производительности. Обратных примеров видел достаточно.
                                0
                                >>И первый же select count(*) from articles для построения постранички приведет к тому, что БД полезет шуршать по всем партициям, что в случае с постгресом, будет означать N запросов.

                                Что вам мешает хранить количество в отдельном поле другой таблици и обновлять через тригеры? Тем более партиции могут делится по разным атрибутам.

                                >>Запросы select * from article where id = X будут выполняться так же быстро, как без партиционирования. А в умную БД которая поймет, что при ORDER BY id DESC LIMIT 10 надо смотреть только в последнюю партицию мне не верится.

                                Проблемы СУБД в том, что сначала будет делаться выборка по запросу, а потом лимитируется выборка. Если таблица большая — СУБД будет не сладко сделать LIMIT 10.

                                >>Я затрудняюсь привести пример, когда партишнинг таблицы в рамках одного сервера приведет к заметному выигрышу в производительности. Обратных примеров видел достаточно.

                                Обратный пример пожалуйста :)
                          0
                          А вы пробовали перестроить индексы? www.postgresql.org/docs/8.3/static/sql-reindex.html

                          Просто 20млн Primary Key должны влезать в память, и если у вас постгре хоть немного настроен, то проблем с выборкой по индексу быть не должно.

                          p.s.
                          У нас 40+ млн записей и индекс по PK занимает 900мег (опять отожрался), и выборка по индексу отрабатывает за 0.060 ms. Правда памяти 16гиг и база должна целиком влезать…
                            0
                            Да, иногда админ это делал насколько помню, но памяти на тот момент было вроде меньше 10г.
                            возможно сейчас (когда машины посильнее) мы и не заметили бы проблему.
                          0
                          Важное дополнение: репликация возможна только на одинаковых архитектурах — 64bit-64bit или 32bit-32bit.
                            0
                            Как так? не пойму
                            0
                            Спасибо, было интересно почитать.
                            После почти года использования ничего нового не появилось рассказать?
                            Странно, что по PostgreSQL так мало статей на хабре. Используется вроде бы широко.
                              0
                              Рад, что оказалось полезно. В принципе — кроме пары костылей — наверно ничего. :)
                              0
                              Есть ли какие-нибудь более вменяемые способы синхронизировать master-slave?
                              Что-то встроенное, которое будет работать предсказуемо без лишних утилит синхронизация FS? Т.е указал адрес сервера, указал базу и тип (merge, transaction) и нажал кнопку Ok, затем у тебя в интерфейсе появилась строчка с красным/зеленым значком и статусом?
                              Утрирую конечно, но слышал много хвалебных отзывов, что Postgres имеет репликацию из коробки, в отличие от MSSQL. А на деле вижу какую-то непонятную субстанцию половина которой к Postgres никак не относится.
                                0
                                rsync работает более, чем предсказуемо — проверено годами :)

                                В 9.1 появилась утилита pg_basebackup, которая делает наливку реплики из мастера одной командой.

                                В 9.3 или 9.4 (лень смотреть changelog`и) появилась поддержка replication-slot`ов. В частности, мастер не будет удалять бинлог пока все его не скачают. Но у этого есть и очевидные обратные стороны.

                                Узнать о подключенных репликах можно начиная с 9.0 из таблицы pg_stat_replication

                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                              Самое читаемое