Резервное копирование PostgreSQL 12 и восстановление на определенный момент времени

Публикация № 1495441 11.08.21

Администрирование БД - Архивирование (backup)

Администрирование Postgresql Непрерывное архивирование WAL журналы

Непрерывное архивирование базы данных PostgreSQL на 12ой версии и восстановление на определенный момент времени.

Предисловие 

Линуксом увлекаюсь недавно, так что мои знания относительно скромные. На работе появилась задача сделать отказоустойчивую систему. Одного pg_dump раз в пару часов недостаточно, поэтому появляется идея архивировать WAL файлы и в случае краша восстанавливаться на самую ближайшую точку.

В интернете инфы мало, да есть записи вебинаров и статьи, НО либо они подходят на старую версию Postgres, либо написаны сложным языком (непонятны новичку).

Приступим

Для начала у вас должен быть установлен Postgres 12 версии и выше, возможно в 14 версии они опять что то переделают, но это будет не особо отличаться.

Создаем каталоги для WAL файлов и полного бэкапа 

sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive

sudo -H -u postgres mkdir /var/lib/postgresql/db_file_backup

Переходим в конфиг Postgres`a 

sudo nano /etc/postgresql/12/main/postgresql.conf

Раскомментироваем и редактируем следующие параметры 

archive_mode = on 

archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/%f && cp %p /var/lib/postgresql/pg_log_archive/%f'

archive_timeout = 1800 (указываем значение в секундах)((Принудительно переключается на новый файл сегмента WAL))

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

sudo systemctl restart postgresql@12-main

После перезапуска в каталоге /var/lib/postgresql/pg_log_archive/ появляются первые WAL журналы 000000010000000000000081
000000010000000000000082 ; 00000001 - ветка времени (немного подробнее тут ) 
Делаем бэкап или же контрольную точку от пользователя postgres

su postgres
cd ~
#Простая копия кластера в каталог
 pg_basebackup -D /var/lib/postgresql/db_file_backup
#Бэкап в архиве tar
pg_basebackup -Ft -D /var/lib/postgresql/db_file_backup
#Бэкап в сжатом архиве 
pg_basebackup -Ft -X none -D - | gzip > /var/lib/postgresql/db_file_backup.tar.gz

Восстановление 

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

su postgres 
cd ~
psql -c "select pg_switch_wal();"

Выключаем Postgres 

sudo systemctl stop postgresql@12-main
#Проверяем статус
sudo systemctl status postgresql@12-main

Удаляем данные кластера 

sudo rm -rf /var/lib/postgresql/12/main

Восстанавливаем кластер из контрольной точки ( pg_basebackup) 

#Простой бэкап
sudo cp -a /path/to/database_backup/. /var/lib/postgresql/12/main/ 
#Извлечение из архива
tar xvf /var/lib/postgresql/db_file_backup/base.tar -C /var/lib/postgresql/12/main/
#Сжатый архив 
tar xvfz /var/lib/postgresql/db_file_backup.tar.gz -C /var/lib/postgresql/12/main/

После восстановления даем права на каталог пользователю postgres 

sudo chown postgres:postgres /var/lib/postgresql/12/main

sudo chmod 700 /var/lib/postgresql/12/main

Прописываем параметры восстановления 

sudo nano /etc/postgresql/12/main/postgresql.conf

#Раскомментить и прописать путь, где лежат WAL журналы
restore_command = 'cp /var/lib/postgresql/pg_log_archive/%f %p'

#Восстановление на определенный момент времени
##Раскомментить и прописать дату 
recovery_target_time = 'yyyy-mm-dd hh:mm:ss.ss' ('2021-08-10 15:20:00')

#Восстановление на определенную ветку времени
##Раскомментить и прописать значение 
recovery_target_timeline = 'latest' - до последней ветки
recovery_target_timeline = '1' - до указанной

Остальные параметры восстановления и их описание (клик)

Чтобы запустить сервер в режиме восстановления, создадим в корнем каталоге файл recovery.signal 

sudo touch /var/lib/postgresql/12/main/recovery.signal

Как только кластер базы данных достигнет цели восстановления, он удалит recovery.signal файл

Запускаем postgres

sudo systemctl start postgresql@12-main
sudo systemctl status postgresql@12-main

#Получим что то подобное 

`79; postgresql@12-main.service - PostgreSQL Cluster 12-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled)
Active: active (running) since Tue 2021-08-10 14:07:34 MSK; 3s ago
Process: 29045 ExecStop=/usr/bin/pg_ctlcluster —skip-systemctl-redirect -m fast 12-main stop (code=exited, status=0/SUCCESS)
Process: 29052 ExecStart=/usr/bin/pg_ctlcluster —skip-systemctl-redirect 12-main start (code=exited, status=0/SUCCESS)
Main PID: 29059 (postgres)
Tasks: 6 (limit: 4915)
CGroup: /system.slice/system-postgresql.slice/postgresql@12-main.service
_00;^72;29059 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgr
_00;^72;29060 postgres: 12/main: logger
_00;^72;29061 postgres: 12/main: startup recovering 00000002000000000000008A
_00;^72;29065 postgres: 12/main: checkpointer
_00;^72;29066 postgres: 12/main: background writer
^92;^72;29068 postgres: 12/main: stats collector

авг 10 14:07:31 astra systemd[1]: Stopped PostgreSQL Cluster 12-main.
авг 10 14:07:31 astra systemd[1]: Starting PostgreSQL Cluster 12-main...
авг 10 14:07:34 astra systemd[1]: Started PostgreSQL Cluster 12-main.

В будущем хочу научиться потоковой репликации wal журналов, чтобы обеспечить полную отказоустойчивую систему
Так что ждите новых мануалов)

Специальные предложения

Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. baracuda 2 14.08.21 20:44 Сейчас в теме
Спасибо за мануал! Думаю много кому пригодится.
2. al.semenenko88 16.08.21 14:44 Сейчас в теме
свапаем на новую стенку

Можно расшифровку этой фразы?
3. Vismut 39 16.08.21 15:58 Сейчас в теме
Переключаемся на новую стенку. Некоторые транзакции возможно не успели записаться перед восстановлением, хотя после выключения Postgres`a он может их сохранить. Вы заметите это когда будете поэкспериментировать на виртуалке
4. KlSergey 28 17.08.21 13:43 Сейчас в теме
(3)
Перед восстановлением свапаем на новую стенку дабы не потерять данные которые не успели записаться

su postgres
cd ~
psql -c "select pg_switch_wal();"

Правда не совсем понятно.
Мы же вроде хотим восстановится по времени назад , на ранее созданный бекап все нужные данные в нём ?
Зачем эта операция ?
А если сервер упал или база "развалилась", без этой команды не получится восстановиться ?
5. Vismut 39 18.08.21 12:47 Сейчас в теме
Мы можем откатываться назад по определенным причинам,вдруг после отката мы захотим вернуться обратно
Ее не обязательно прописывать при восстановлении, это просто рекомендация
Оставьте свое сообщение

См. также

Скрипт удобного восстановления базы MSSQL при дифференциальном резервировании Промо

Архивирование (backup) v7.7 v8 1cv8.cf 1cv7.md Россия Бесплатно (free)

Если у Вас база на MSSQL и Вы пользуетесь разностными бекапами, то, скорее всего, столкнулись с тем, что поднять базу/копию базы из имеющихся резервных файлов не столь удобно, как того хотелось бы. В этой статье приведен скрипт по теме.

20.01.2011    31728    Ivon    12    

Восстановление базы из DT, если она не хочет восстанавливаться

Архивирование (backup) v8 Бесплатно (free)

Решение некоторых проблем при восстановлении базы из dt.

14.04.2022    2834    Pasha1st    10    

Классическое резервное копирование

Архивирование (backup) v8 1cv8.cf Бесплатно (free)

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

04.02.2021    1635    creatermc    11    

Управление конфигуратором в режиме агента с помощью python

Инструменты администратора БД Архивирование (backup) DevOps и автоматизация разработки v8 1cv8.cf 1С:Франчайзи, автоматизация бизнеса Россия Бесплатно (free)

Управление конфигуратором 1С:Предприятие в режиме агента. Опыт применения с реализацией на языке python. Результат получен с использованием интерактивной сессии оболочки через invoke_shell().

06.08.2020    2450    Alex10166    2    

Восстановление SQL базы 1С 8.2. рухнувшей во время сохранения конфигурации. Промо

Корректировка данных Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

При обновлении конфигурации вылетела 1С. После чего наотрез отказалась входить в базу. При этом в конфигураторе выдавалось сообщение "Внимание!!! При обновлении данных, после последней реструктуризации, произошла критическая ошибка. Повторить обновление?" Потом выходило сообщение "Обнаружена незавершённая операция сохранения конфигурации. Для продолжения необходимо завершить операцию". Горела кнопка Ок - на этом работоспособность конфигуратора и базы заканчивалась.

08.02.2012    136682    VanDiesel1    140    

Выгрузка в dt на сервере 1С по расписанию с завершением соединений и подключением к консоли сервера через com

Архивирование (backup) Инструменты администратора БД v8 Россия Бесплатно (free)

Была задача настроить по расписанию выгрузку серверной базы в dt, готового решения не нашел, делюсь, может, кому пригодится.

16.04.2020    11294    karamazoff    49    

Тонкая настройка ежедневного резервного копирования базы данных 1С средствами SQL ver. 2014 (SP3) - 12.0.6024.0 (X64)

Архивирование (backup) v8 Россия Бесплатно (free)

Хочу вам предложить небольшой пример, как можно реализовать резервное копирование 1С-ых баз данных средствами SQL. Данный материал не претендует на пулитцеровскую премию. Но возможно кому-то будет интересно узнать, что-то новенькое. Данный материал для резервного копирования только одной базы данных. А именно, если у вас 20-ть баз, то вам придется создавать 20-ть планов обслуживания для каждой базы индивидуально. (Слава разработчикам SQL, они разрешили копировать блоки из одного плана в другой, вам остается только произвести небольшую настройку для каждого скопированного блока - некоторые настройки блоков сбрасываются и выставляются значением по умолчанию и остаются неактивными)

07.10.2019    16131    DrZombi    53    

Быстрое копирование таблиц большого размера и/или с большим числом строк, на примере регистра сведений (для MS SQL)

Архивирование (backup) v8 Бесплатно (free)

Моментальное восстановление затертого регистра сведений из бекапа посредством SQL.

11.08.2019    9571    Zlohobbit    30    

К вопросу об архивации баз 1С (и снова, и снова...) Промо

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

Из своего опыта хочу напомнить о самом простом способе архивации баз типовыми средствами 1С и планировщика Windows.

08.01.2010    27606    grum01    14    

Настройка резервного копирования (резервирования) баз данных 1С: Предприятие на MS SQL Server

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

Настройка резервного копирования (резервирования) баз данных на "бюджетной" версии 1С Предприятие под MS SQL Server. Используется пример MS SQL Server 2008 R2 под Windows. Для малых и средних предприятий, исключая производственные и торговые, так как тестирование на них не проводилось.

30.10.2018    13573    unclevad    16    

Работа с конфигуратором по протоколу SSH (не в режиме агента)

Архивирование (backup) Инструменты администратора БД v8 Бесплатно (free)

Рабочее решение запуска пакетного скрипта конфигуратора 1С через SSH-клиента.

28.04.2018    11784    vsbronnikov    2    

Резервное копирование "онлайн" клиент-серверных баз в dt (не отключая пользователей)

Архивирование (backup) v8 Бесплатно (free)

Как реализовать резервное копирование клиент-серверных баз 1с в формат dt, не отключая пользователей. Рассматривается способ, делающий резервирование наименее заметным для пользователей и серверного оборудования.

03.10.2017    27753    konstanta_online    82    

Настройка зеркалирования базы для MS SQL

Архивирование (backup) HighLoad оптимизация v8 1cv8.cf Бесплатно (free)

Очень удобный способ, когда нам нужна не просто резервная копия, а "горячая" замена серверов.

19.05.2017    35743    MsDjuice    20    

Быстрый бэкап изменяемых данных

Архивирование (backup) v8 Бесплатно (free)

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

19.01.2017    9791    ogroup    25    

Рекомендации по организации резервного копирования для систем с Windows Server / Windows SQL Server

Архивирование (backup) v8 Бесплатно (free)

В данной статье мы рассмотрим работу с резервными копиями базы данных 1С, а также некоторые приемы, оптимизирующие работу с резервными копиями.

10.10.2016    20588    Zebar    12    

Резервное копирование и восстановление базы 1С средствами PostgreSQL

Архивирование (backup) v8 Бесплатно (free)

Алгоритм резервного копирования баз 1С: 8 средствами PostgreSQL.

01.08.2016    78050    dimisa    35    

Как я восстанавливал разрушенную базу

Архивирование (backup) Корректировка данных Обмен между базами 1C v8 1cv8.cf Бесплатно (free)

УТ10.3 на Платформе 8.2 на базе MSSQL была разрушена после попытки её восстановить после неудачного динамического обновления. Таблица Config целевой базы была заменена на содержимое таблицы Config от другой рабочей базы. Но на самом деле конфигурации у них существенно отличались, поэтому после таких действий целевая база рухнула окончательно. Что же делать?

21.08.2015    30994    METAL    25    

Просто и сердито. Архивирование (backup) типовых конфигураций 1С 8.2, 8.3

Архивирование (backup) v8 1cv8.cf Бесплатно (free)

После эксплуатации различных "бесплатных" обработок и скриптов решил написать свой cmd-файл для ежедневного архивирования баз 1С. Работает на конфигурациях, где есть процедуры "ЗавершитьРаботуПользователей" и "РазрешитьРаботуПользователей" (т.е. во всех типовых, в нетиповые данные модули можно скопировать из типовых). Сохраняет файлы как локально так и на удаленном файловом сервере. Автоматически удаляет старые архивы и копирует на удалённый сервер отсутствующие. Расписание задается установкой соответствующего задания (запуска cmd-файла по времени) в планировщике задач Windows. Для борьбы с зависшими сеансами, рекомендуется настроить в режиме конфигуратора параметры информационной базы: "Время засыпания пассивного сеанса" и "Время завершения спящего сеанса".

18.06.2015    19402    Prelude    14    

Как выгрузить базу средствами 1С, не выгоняя пользователей. Делаем невозможное.

Архивирование (backup) Инструменты администратора БД v8 1cv8.cf Бесплатно (free)

В 1С:Предприятии 8.2 есть ограничение на выгрузку базы в формате DT. Выгрузить базу можно, только если в ней нет активных пользователей. В этой статье поделюсь способом, как обойти это ограничение и сделать невозможное.

16.09.2013    54220    yurega    55    

Восстановление SQL базы 1С 8.2. после неудачного сохранения конфигурации

Архивирование (backup) Инструменты администратора БД v8 Россия Бесплатно (free)

При динамическом обновлении, в процессе сохранения конфигурации, вылетела база 1С и отказалась заходить в режим Конфигуратора, выдавая сообщение "Внимание!!! При обновлении данных, после последней реструктуризации, произошла ошибка. Повторить обновление?", если ответить утвердительно, то появлялось сообщение "Обнаружена незавершенная операция сохранения конфигурации. Для продолжения работы необходимо завершить операцию.", после чего Конфигуратор закрывался.

18.07.2013    37892    lord_soth    45    

Резервное копирование 1С средствами MS SQL.

Архивирование (backup) v8 1cv8.cf Бесплатно (free)

В этой статье описано самое обычное резервное копирование ИБ 1С при помощи инструментов MS SQL Server 2008 R2, объяснено почему следует делать именно так, а не иначе, и развеяно несколько мифов.

17.02.2013    295825    speshuric    86    

Хранение удаленных документов в отдельной базе. Часть 1.

Инструменты администратора БД Архивирование (backup) v8 1cv8.cf Бесплатно (free)

Резервное хранение данных. Пример работы с внешними источниками данных. Работа с файлами. Подписка на событие. Работа с XML файлами. Сериализатор XDTO.

12.12.2012    16619    egorovntn    10    

Автоматическое регулярное разворачивание оперативных бэкапов (совсем просто) в MS SQL 2008

Архивирование (backup) v7.7 v8 1cv8.cf 1cv7.md Россия Бесплатно (free)

В работе регулярно возникает необходимость протестировать поведение программы на копии рабочей базы с актуальными данными

10.08.2012    19069    sergiobargio1    6    

Регулярные оперативные копии рабочих баз 1С с помощью MS SQL 2008

Инструменты администратора БД Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

В течение дня необходимо производить отладку "допилов" на свежих копия рабочих баз. Также необходимы актуальные копии и для экспериментов, в том числе и пользователям. На этом замечательном ресурсе есть много полезных публикаций с различными вариантами, как это сделать. В одном из них предлагается использовать инструмент мгновенных снимков баз, которые возможно выполнить только в версии Express Edition. В этом посте очень простой вариант, не требующий дорогостоящей версии MS SQL

01.08.2012    19523    sergiobargio1    8    

Пошаговая инструкция по процедуре восстановления базы SQL. SQL Server 2008

Архивирование (backup) Инструменты администратора БД v8 1cv8.cf Россия Бесплатно (free)

Пошаговая инструкция по по процедуре восстановления бэкапа SQL 2008.

23.07.2012    140943    Vint88    21    

Автоматизация создания резервных копий в MS SQL Express Server

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

В версии Microsoft SQL Server 2005/2008/2008R2 Express Edition серверах нет стандартных средств создания резервных копий баз данных по расписанию. Восполнить этот пробел поможет простое решение

18.06.2012    31392    LexSeIch    4    

Бэкап 1С:Предприятие 8.х

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

Рекомендации по резервному копированию. Бесплатные программы для бэкапа Egida Backup, Effector saver 3, xStarter.

09.11.2011    28407    sinjevla    10    

Резервное копирование чеков во внешние файлы и их восстановление

Архивирование (backup) v8 Розница Россия Бесплатно (free)

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

21.10.2011    11839    elizarovs    3    

Архивное копирование 1С8 автоматически и ежедневно

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

Выложил ввиду "молодости и горячести" ХД и критики со стороны, дабы не спотыкаться в дальнейшем =)

19.04.2011    8057    AActor    15    

1С и Postgres: Бэкап

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

Для начала пару слов о том, зачем и когда он нужен. Ни для кого не секрет, что сервер это не просто компьютер, а надежный компьютер! Поэтому, если он не сломался в первую неделю после запуска, то не сломается еще очень долго. И поэтому у вас всегда есть возможность какое-то время оставаться вовсе без резервной копии

17.12.2010    23086    alexcid    5    

Выгрузка ИБ 1С8 на сервере 1С:Предприятие

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

Выгрузка ИБ 1С8 на сервере 1С:Предприятие стандартными средствами ОС и 1С. Без всяких хитростей.

25.10.2010    20405    daulberg    7    

Как выгрузить не всю конфигурацию в файл, а только изменения?

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

Хочу поделиться одним способом сохранения не всей конфигурации в файл, а только изменений. Способ довольно заморочный, но он позволяет сохранить любые изменения конфигурации в файл, размер которой уменьшится, скажем, к 300 KB, по сравнению с размером конфигурации в 60 MB. Этот способ эффективен, когда у клиента очень слабое соединение с интернетом или оплачивается помегабайтно.

19.02.2010    37575    modul    78    

Архивирование баз данных 1С и не только... (настройка бесплатной программы Cobian Backup 9)

Архивирование (backup) v7.7 v8 1cv8.cf 1cv7.md Россия Бесплатно (free)

В данной статье описывается создание системы архивирования на основе бесплатной программы Cobian Backup 9 (http://www.cobiansoft.com/cobianbackup.htm)

14.01.2010    64449    Mx00    146    

SQL2005. Выгружаем базу средствами 1С не выгоняя пользователей.

Архивирование (backup) v8 1cv8.cf Россия Бесплатно (free)

Очередной велосипед на тему архивации баз данных 1С средствами 1С.

21.09.2009    23140    IamAlexy    26