Postgresql как удалить старые wal файлы
Получил "в наследство" систему, в которой в качестве СУБД используется PostgreSQL на CentOS. Резервное копирование реализовано с помощью PostgreSQL Backup, которая запускает ночью задание, делает бекап двух баз и отправляет на ftp.
Решил заморочиться отказоустойчивостью.
0. УСТАНОВКА PostgreSQL
инструкция по установке PostgreSQL:
Для подключения к базе по сети:
редактируем /var/lib/pgsql/9.6/data/pg_hba.conf :
Закомментировать строку
host all all 0.0.0.0/0 ident
Добавить строку
host all all <client host ip> md5
где <client host ip> заменить на адрес машины (или подсети), из которой будете подключаться (ip сервера 1С Предприятие 8). Обязательно с маской
подсети, например: 192.168.1.11/32.
Теперь сервер базы данных доступен, можно на сервере 1С предприятия создавать базы и т.д.
1. ПОТОКОВАЯ РЕПЛИКАЦИЯ
Итак, теперь у нас есть работающая (боевая) СУБД PstgreSQL. Назовем ее MASTER, а сервер, на который будем реплицировать базу SLAVE.
Для обеспечения работы потоковой репликации на MASTER-е редактируем файл /var/lib/pgsql/9.6/data/postgresql.conf, добавляем следующие строки:
wal_level = hot_standby (прочитайте про этот параметр в интернете и про WAL файлы в принципе)
max_wal_senders = 4 (количество планируемых слейвов, число 4 - на всякий случай )
max_replication_slots = 4 ( здесь 4 - это количество replication slot'ов - равен максимальному количеству реплик)
hot_standby = on (разрешить запросы на реплике)
hot_standby_feedback = on (Определяет, будет ли сервер горячего резерва сообщать ведущему или вышестоящему резервному о запросах, которые он выполняет в данный момент)
Параметры hot_standby = on и hot_standby_feedback = on на MASTER-е не играют никакой роли, но мы их определим уже сейчас, так как на одном из следующих этапов при выстраивании потоковой репликации мы скопируем по сути вес data каталог с MASTER-а на SLAVE, в том числе и этот конфигурационный файл, так что установим эти параметры уже сейчас.
На MASTER-е создаем пользователя repluser под которым SLAVE будет подключаться к MASTER-у и таскать WAL из слотов репликации.
На MASTER-е редактируем /var/lib/pgsql/9.6/data/pg_hba.conf, то есть, обеспечиваем, чтобы в этом файле были такие строчки
host all all <ip_MASTER>/32 md5
host all all <ip_SLAVE>/32 md5
host all all <ip_Сервера1С>/32 md5 (эта строчка уже есть, создана на этапе 0.)
host replication repluser <ip_MASTER>/32 md5
host replication repluser <ip_SLAVE>/32 md5 (разрешаем SLAVE-у подключаться к MASTER-у под пользователем repluser для осуществления потоковой репликации)
По сути, выше описанными разрешениями в файле pg_hba.conf мы разрешаем MASTER-у и SLAVE-у взаимно обращаться друг к другу, поэтому, как и в случае с файлом postgresql.conf, файл pg_hba.conf на обоих серверах может совпадать. Соответственно, в дальнейшем, при необходимости, MASTER может стать SLAVE-ом.
ПЕРЕХОДИМ на SLAVE
Выполняем все что в пункте 0. УСТАНОВКА PostgreSQL
выше написанная команда делает следующее - подключается к MASTER-у под пользователем repluser (команда затребует пароль, его нужно ввести, скорей всего можно в команде этот пароль и прописать, но я не заморачивался), копирует целиком базу вместе с конфигурационными файлами, а так же создает минимально необходимый файл recovery.conf, в котором собственно прописано, что база работает в режиме реплики, а значит пока существует этот файл, то в режиме только для чтения!
Привожу текст файла recovery.conf:
standby_mode = 'on'
primary_conninfo = 'user=repluser password=супер-секретный-пароль host=ip_MASTER port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
Как я уже говорил выше, если остановить базу, и переименовать файл recovery.conf, и снова запустить, то база перестает быть репликой.
все :), SLAVE в теперь реплика, и регулярно таскает печеньки данные с MASTER-а
Что можно добавить?
Эта репликация асинхронная. Есть возможность настройки синхронной потоковой репликации. А так же каскадной, а так же и т.д. и т.д. Читайте, изучайте.
Все что сделано выше решит ситуацию, когда у вас внезапно взрывается MASTER - у вас есть актуальная копия, реплика.
Примерный сценарий таков:
- останавливается SLAVE
- переименовывается recovery.conf
- запускается SLAVE
- на сервере 1С базы переподключаются к SLAVE-у, пользователи начинают работать, а вы решаете - что там случилось с MASTER-ом.
2. НЕПРЕРЫВНОЕ АРХИВИРОВАНИЕ
Предположим MASTER не взорвался, а просто кто-то умный запустил обработку, которая перепилила и перекорежила данные и все это торжественно перелетело в реплику на SLAVE (см. все что написано выше).
Решает организация непрерывного архивирования.
чтобы иметь представление о том что такое WAL, файлы-сегменты WAL и т.д.
Вкратце схема следующая:
- настраиваем непрерывное копирование WAL файлов
- делаем базовую резервную копию, после чего возникает возможность восстановить базу данных на любую временную точку после созданной базовой копии просто "накатывая" необходимое количество WAL файлов. Если базовая резервная копия сделана достаточно давно, то вам придется дольше по времени восстанавливать, накатывая большее количество файлов. Соответственно базовые резервные копии делайте чаще. Старые WAL файлы (созданные до "контрольной точки" сформированной очередной базовой резервной копии) можно "подчищать".
Опишу простейшую систему, когда архивируем на тот же дисковый массив, где и находится собственно база (что неправильно и вы конечно настройте копирование на другой диск, сервер, ftp и т.д.).
На MASTER-е редактируем /var/lib/pgsql/9.6/data/pg_hba.conf, добавляем следующую строчку (или убеждаемся что она уже есть):
local replication all trust
Редактируем файл /var/lib/pgsql/9.6/data/postgresql.conf
archive_command = 'test ! -f /var/lib/pgsql/9.6/backups/archive/%f && cp %p /var/lib/pgsql/9.6/backups/archive/%f'
(
то есть, задаем желаемую команду оболочки в параметре archive_command. В archive_command символы %p заменяются полным путём к файлу, подлежащему архивации, а %f заменяются только именем файла. Поэтому когда WAL файл готов к копированию (заполнен) вызывается примерно следующая команда:
test ! -f /var/lib/pgsql/9.6/backups/archive/00000001000000A900000065 && cp pg_xlog/00000001000000A900000065 /var/lib/pgsql/9.6/backups/archive/00000001000000A900000065
Кстати, по результатам работы pg_basebackup в папке, куда мы копируем WAL файлы, мы увидим подобный файл.
-rw------- 1 postgres postgres 308 окт 24 02:37 00000001000000150000006B.00000028.backup
по сути - это временная метка с которой можно восстанавливать базу из архива.
К сожалению процесс восстановления описать не могу, так как мне еще предстоит его протестировать, обкатать. Надеюсь найти время и дописать статью.
pg_archivecleanup [ параметр . ] расположение_архива старейший_сохраняемый_файл
Описание
Утилита pg_archivecleanup предназначена для использования в качестве archive_cleanup_command для удаления старых файлов WAL на резервном сервере (см. Раздел 25.2). pg_archivecleanup можно использовать и как отдельную программу для выполнения тех же действий.
Чтобы использовать pg_archivecleanup на резервном сервере, добавьте эту строку в файл конфигурации recovery.conf :
Здесь расположение_архива определяет каталог, из которого должны удаляться файлы сегментов WAL.
Вызываемая в качестве archive_cleanup_command, эта программа просматривает расположение_архива и удаляет все файлы WAL, логически предшествующие значению аргумента %r . Целью этой операции является сокращение числа сохраняемых файлов без потери возможности восстановления при перезапуске. Такой вариант использования уместен, когда расположение_архива указывает на область рабочих файлов конкретного резервного сервера, но не когда расположение_архива — каталог с архивом WAL для долговременного хранения, или когда несколько резервных серверов восстанавливают записи WAL из одного расположения.
При отдельном использовании этой программы из каталога расположение_архива будут удалены все файлы WAL, логически предшествующие файлу старейший_сохраняемый_файл . В этом режиме, если вы укажете имя файла с расширением .partial или .backup , старейший_сохраняемый_файл будет определяться по имени без расширения. Благодаря такой интерпретации расширения .backup будут корректно удалены все файлы WAL, заархивированные до определённой базовой копии. Например, следующая команда удалит все файлы старее файла WAL с именем 000000010000003700000010 :
pg_archivecleanup рассчитывает на то, что расположение_архива доступно для чтения и записи пользователю, владеющему серверным процессом.
Параметры
pg_archivecleanup принимает следующие аргументы командной строки:
Вывести имена файлов, которые должны быть удалены, в stdout (не выполняя удаление). -V
--version
Вывести версию pg_archivecleanup и завершиться. -x расширение
Установить расширение, которое будет убрано из имён файлов до принятия решения об удалении определённых файлов. Это обычно полезно для очистки файлов архивов, которые сжимаются для хранения и получают расширение, задаваемое программой сжатия. Например: -x .gz . -?
--help
Вывести справку об аргументах командной строки pg_archivecleanup и завершиться.
Замечания
Программа pg_archivecleanup рассчитана на работу с PostgreSQL 8.0 и новее как отдельная утилита, а также с PostgreSQL 9.0 и новее как команда очистки архива.
Программа pg_archivecleanup написана на C; её исходный код легко поддаётся модификации (он содержит секции, предназначенные для изменения при надобности)
Примеры
В системах Linux или Unix можно использовать команду:
Предполагается, что каталог архива физически располагается на резервном сервере, так что команда archive_command обращается к нему по NFS, но для резервного сервера эти файлы локальные. Эта команда будет:
Уже давно известно, что делать бэкапы в SQL-дампы (используя pg_dump или pg_dumpall) – не самая хорошая идея. Для резервного копирования СУБД PostgreSQL лучше использовать команду pg_basebackup, которая делает бинарную копию WAL-журналов. Но когда вы начнёте изучать весь процесс создания копии и восстановления, то поймёте что нужно написать как минимум пару трёхколёсных велосипедов, чтобы всё это работало и не вызывало у вас боль как сверху, так и снизу. Дабы облегчить страдания был разработан WAL-G.
WAL-G – это инструмент, написанный на Go для резервного копирования и восстановления PostgreSQL баз данных (а с недавнего времени и MySQL/MariaDB, MongoDB и FoundationDB). Он поддерживает работу с хранилищами Amazon S3 (и аналогами, например, Yandex Object Storage), а также Google Cloud Storage, Azure Storage, Swift Object Storage и просто с файловой системой. Вся настройка сводится к простым шагам, но из-за того что статьи о нём разрозненны по интернету – нет полного how-to мануала, который бы включал все шаги от и до (на Хабре есть несколько постов, но многие моменты там упущены).
Данная статья написана в первую очередь чтобы систематизировать свои знания. Я не являюсь DBA и где-то могу выражаться обывательско-разработческим языком, поэтому приветствуются любые исправления!
Отдельно отмечу что всё нижеприведенное актуально и проверено для PostgreSQL 12.3 на Ubuntu 18.04, все команды должны выполняться от привилегированного пользователя.
Установка
В момент написания данной статьи стабильная версия WAL-G – v0.2.15 (март 2020). Её мы и будем использовать (но если вы захотите самостоятельно собрать его из master-ветки, то в репозитории на github есть все инструкции для этого). Для скачивания и установки нужно выполнить:
После этого нужно сконфигурировать вначале WAL-G, а потом сам PostgreSQL.
Настройка WAL-G
Для примера хранения бэкапов будет использоваться Amazon S3 (потому что он ближе к моим серверам и его использование обходится очень дёшево). Для работы с ним нужен «s3-бакет» и ключи доступа.
Во всех предыдущих статьях о WAL-G использовалось конфигурирование с помощью переменных окружения, но с этого релиза настройки можно расположить в .walg.json файле в домашней директории пользователя postgres. Для его создания выполним следующий bash-скрипт:
Немного поясню по всем параметрам:
- WALG_S3_PREFIX – путь к вашему S3-бакету куда будут заливаться бэкапы (можно как в корень, так и в папку);
- AWS_ACCESS_KEY_ID – ключ доступа в S3 (в случае восстановления на тестовом сервере – данные ключи должны иметь ReadOnly Policy! Об этом подробнее написано в разделе про восстановление);
- AWS_SECRET_ACCESS_KEY – секретный ключ в хранилище S3;
- WALG_COMPRESSION_METHOD – метод компрессии, лучше использовать Brotli (так как это золотая середина между итоговым размером и скоростью сжатия/разжатия);
- WALG_DELTA_MAX_STEPS – количество «дельт» до создания полного бэкапа (позволяют экономить время и размер загружаемых данных, но могут чуть замедлить процесс восстановления, поэтому не желательно использовать большие значения);
- PGDATA – путь к директории с данными вашей базы (можно узнать, выполнив команду pg_lsclusters);
- PGHOST – подключение к базе, при локальном бэкапе лучше делать через unix-socket как в этом примере.
Настройка PostgreSQL
Чтобы архиватор внутри базы сам заливал WAL-журналы в облако и восстанавливался из них (в случае необходимости) – нужно задать несколько параметров в конфигурационном файле /etc/postgresql/12/main/postgresql.conf. Только для начала вам нужно убедиться, что никакие из нижеприведенных настроек не заданы в какие-то другие значения, чтобы при перезагрузке конфигурации – СУБД не упала. Добавить эти параметры можно с помощью:
Описание устанавливаемых параметров:
- wal_level – сколько информации писать в WAL журналы, «replica» – писать всё;
- archive_mode – включение загрузки WAL-журналов используя команду из параметра archive_command;
- archive_command – команда, для архивации завершённого WAL-журнала;
- archive_timeout – архивирование журналов производится только когда он завершён, но если ваш сервер мало изменяет/добавляет данных в БД, то имеет смысл выставить тут лимит в секундах, по истечению которого команда архивации будет вызвана принудительно (у меня интенсивная запись в базу каждую секунду, поэтому я отказался от установки этого параметра в продакшене);
- restore_command – команда восстановления WAL-журнала из бэкапа, будет использоваться в случае если в «полном бэкапе» (base backup) будет недоставать последних изменений в БД.
Настройка расписания резервного копирования
Как ни крути, но самым удобным способом для запуска – является cron. Именно его мы и настроим для создания резервных копий. Начнём с команды создания полного бэкапа: в wal-g это аргумент запуска backup-push. Но для начала лучше выполнить эту команду вручную от пользователя postgres, чтобы убедиться что всё хорошо (и нет каких-то ошибок доступа):
В аргументах запуска указан путь к директории с данными – напоминаю что его можно узнать, выполнив pg_lsclusters.
Если всё прошло без ошибок и данные загрузились в хранилище S3, то далее можно настроить периодический запуск в crontab:
В данном примере процесс бэкапа запускается каждый день в 4:15 утра.
Удаление старых резервных копий
Скорее всего вам не нужно хранить абсолютно все бэкапы с мезозойской эры, поэтому будет полезно периодически «подчищать» ваше хранилище (как «полные бэкапы», так и WAL-журналы). Мы сделаем это всё также через cron задачу (обратите внимание на экранирование символов $ и %):
Cron будет выполнять эту задачу каждый день в 6:30 утра, удаляя всё (полные бэкапы, дельты и WAL’ы) кроме копий за последние 10 дней, но оставит как минимум один бэкап до указанной даты, чтобы любая точка после даты попадала в PITR.
Восстановление из резервной копии
Ни для кого не секрет что залог здоровой базы – в периодическом восстановлении и проверке целостности данных внутри. Как восстановиться с помощью WAL-G – расскажу в этом разделе, а о проверках поговорим после.
Отдельно стоит отметить что для восстановления на тестовом окружении (всё то, что не production) – нужно использовать Read Only аккаунт в S3, чтобы случайно не перезаписать бэкапы. В случае с WAL-G нужно задать пользователю S3 следующие права в Group Policy (Effect: Allow): s3:GetObject, s3:ListBucket, s3:GetBucketLocation. И, конечно, предварительно не забыть выставить archive_mode=off в файле настроек postgresql.conf, чтобы ваша тестовая база не захотела сбэкапиться по-тихому.
Восстановление производится лёгким движением руки с удалением всех данных PostgreSQL (в том числе пользователей), поэтому, пожалуйста, будьте предельно аккуратны когда будете запускать следующие команды.
Для тех, кто хочет проверять процесс восстановления — ниже подготовлен небольшой кусок bash-магии, чтобы в случае проблем в восстановлении – скрипт упал с ненулевым exit code. В данном примере делается 120 проверок с таймаутом в 5 секунд (всего 10 минут на восстановление), чтобы узнать удалился ли сигнальный файл (это будет означать что восстановление прошло успешно):
После успешного восстановления не забудьте запустить обратно все процессы (pgbouncer/monit и тд).
Проверка данных после восстановления
Обязательно нужно проверить целостность базы после восстановления, чтобы не возникло ситуации с побитой/кривой резервной копией. И лучше делать это с каждым созданным архивом, но где и как – зависит только от вашей фантазии (можно поднимать отдельные сервера на почасовой оплате или запускать проверку в CI). Но как минимум – необходимо проверять данные и индексы в базе.
Для проверки данных достаточно прогнать их через дамп, но лучше чтобы при создании базы у вас были включены контрольные суммы (data checksums):
Для проверки индексов – существует модуль amcheck, sql-запрос к нему возьмём из тестов WAL-G и вокруг выстроим небольшую логику:
Резюмируя
Выражаю благодарность Андрею Бородину за помощь в подготовке публикации и отдельное спасибо за его вклад в разработку WAL-G!
На этом данная заметка подошла к концу. Надеюсь что смог донести легкость настройки и огромный потенциал для применения этого инструмента у вас в компании. Я очень много слышал о WAL-G, но никак не хватало времени сесть и разобраться. А после того как внедрил его у себя – из меня вышла эта статья.
Отдельно стоит заметить, что WAL-G также может работать со следующими СУБД:
Устройство буферного кэша
Кэш нужен чтобы читать востребованные данные ни с диска а с более быстрой оперативной памяти. Предварительно данные приходится загружать с диска в буферных кэш оперативной памяти.
Есть 3 варианта размера страниц:
Буферный кэш занимает большую часть общей памяти.
Если процессу понадобятся какие-то данные он их вначале ищет в буферном кэше. Если данных в кэше не оказалось, то мы просим операционную систему прочитать эту страничку и поместить в буферный кэш. Операционная система имеет свой дисковые кэш, и ищет эту страничку там, если не находит, то читает с диска и помещает в дисковый кэш. Затем из дискового кэша страничка помещается в буферный кэш для PostgreSQL.
Так как буферный кэш находится в общей памяти, то чтобы разные процессы не мешали друг другу, к нему применяют различные блокировки.
Алгоритм вытеснения
Чтобы буферный кэш не переполнился нужно редко используемые страницы из него вытеснять. Другими словами удалить из буфера. Если мы страничку поменяли, то она считается грязной, и перед вытеснением из буфера её нужно записать на диск. Если мы страничку не меняли то и записывать на диск её ещё раз не имеет смыла.
В PostgreSQL реализован алгоритм вытеснения страниц из буфера при котором в кэше остаются самые часто используемые страницы.
Журнал предзаписи (WAL)
После сбоя наша база становится рассогласованной. Какие-то страницы менялись и были записаны, другие не успели записаться.
Когда мы хотим поменять какую-то страницу памяти, мы предварительно пишем, что хотим сделать в журнал предзаписи. И запись в этом журнале должна попасть на диск раньше, чем сама страница будет записана на диск.
В случае сбоя мы смотрим в журнал предзаписи и видим какие действия выполнялись, проделываем эти действия заново и восстанавливаем согласованность данных.
Почему запись в WAL эффективнее чем запись самой страницы? При записи страницы памяти на диск она пишется в произвольное место, это место еще нужно выбрать, подготовить для записи и начать запись. А запись в журнал ведется одним потоком и с этим потоком нормально справляются и обычные жёсткие диски и ssd.
WAL защищает всё что попадает в буферный кэш:
- страницы таблиц, индексов;
- статусы транзакций.
WAL не защищает:
- временные таблицы;
- нежурналируемые таблицы.
Производительность WAL
По умолчанию, каждый раз, когда транзакция фиксирует изменения, результат должен быть сброшен на диск. Для этого вначале страница сбрасывается из буферной памяти на дисковый кэш. А затем выполняется операция fsync для записи страницы на диск. То есть частые COMMIT приводят к частым fsync.
В асинхронном режиме postgresql работает быстрее, но вы можете потерять некоторые данные при сбое.
Режим работы настраивается с помощью конфигурационного файла и настройки не требуют перезагрузки сервера. Это позволяет приложению устанавливать параметры на лету. Некоторые транзакции сразу запишут изменения на диск, то есть поработают в синхронном режиме. Другие транзакции будут работать в асинхронном режиме. Условно можно поделить операции на критичные и не критичные.
Следующие параметры отвечают за режим работы WAL:
Контрольная точка
Сброс данных при контрольной точке не проходит моментально, это бы сильно нагрузило наш сервер. Чтобы не было пиковых нагрузок сброс идет примерно 50% времени от времени между контрольными точками. Например, контрольные точки делаются каждую минуту, тогда сброс осуществляют плавно в течении 30 секунд. Это регулируется и вы можете установить например 90% времени.
Контрольная точка также уменьшает размер необходимого дискового пространства. Так как весь журнал WAL не нужен, его можно периодически подрезать.
Отдельный серверный процесс контрольных точек автоматически выполняет контрольные точки с заданной частотой. Эту частоту можно настроить следующими параметрами:
Значения по умолчанию: 5 минут и 1 Гбайт, соответственно. Если после предыдущей контрольной точки новые записи WAL не добавились, следующие контрольные точки будут пропущены, даже если проходит время checkpoint_timeout. Также можно выполнить контрольную точку принудительно, воспользовавшись SQL-командой CHECKPOINT.
Уменьшение значений checkpoint_timeout и max_wal_size приводит к учащению контрольных точек. Но появляется дополнительная нагрузка на сервер.
Процессы, связанные с буферным кэшем и журналом
Уровни журналов
Практика
В журнале WAL каждая запись имеет номер LSN (Log Sequence Number). С помощью функции pg_current_wal_lsn() можно посмотреть номер текущей записи:
У нас вышло 138 KB! Так много получилось из за создания таблички, создание 1000 строк почти не нагрузит WAL.
Физически журнал хранится в файлах по 16 МБ в отдельном каталоге (PGDATA/pg_wal). На журнал можно взглянуть не только из файловой системы, но и с помощью функцию pg_ls_waldir():
Посмотреть на выполняемые процессы сервера postgres можно так:
К процессам, обслуживающим буферный кэш и журнал, можно отнести:
- checkpointer;
- background writer;
- walwriter;
Теперь давайте остановим сервер корректно и посмотрим в лог файл:
Как видим сервер просто открыл соединения на сокете и tcp порту 5432 и начал работу.
Теперь завершим работу сервера некорректно, используя опцию immediate:
Как видим журнал изменился! Перед тем, как начать принимать соединения, СУБД выполнила восстановление (automatic recovery in progress).
Читайте также: