Комментарии 67
Две реплики - одна без отставания, с которой можно снимать бэкапы pg_dump'ом, вторая с отставанием, скажем, в три часа решают многие проблемы. Использование архивации WAL и PITR - решают практически все проблемы.
Реплики это да, но я тут читал интервью (в конце статьи) Инструменты создания бэкапов PostgreSQL. Андрей Сальников (Data Egret) / Хабр (habr.com) их надежность тоже вызывает вопросы (как контролировать отставание и сбои). Как понимаю альтернатив репликам нет, поскольку online бэкап кластера и восстановление это всеравно подъем реплики, по структуре Postgres
Так и думал, но это же городить нужно
да уж, в 2024году такие грабли. Кстати в конце статьи мозг при прочтении слова downshift автоматически убрал одну букву, из-за чего пришлось повторять транзакцию чтения предложения с этим словом ;)
Попробуйте pg_rman или pgbackrest
Ужас какой. Вот вы с MSSQL и Postgres работаете, посоветуйте. Продолжать наслаждаться райским миром MSSQL, но, так как все постепенно переходят на Postgres, закончить свою жизнь под мостом, или начать жрать Postgres прямо сейчас?
Продолжать наслаждаться райским миром MSSQL
Если лицензирование юридически прикрыто через VPN на запад , то ответ Да потому что
Бесплатный Postgres в отличии от MS SQL гораздо больше использует ресурсов ЦПУ при этом не имеет альтернатив типа Oracle RAC Postgres как предчувствие. Вычисляем процент импортозамещения в режиме Highload от 1С / Хабр (habr.com)
Бесплатнй Postgres неудобен в администировании . Например банальные wait собрать это уже задача , в платном получше дела обстоят PostgresSQL - бесплатный сыр для 1С или ступенька к Enterprise версии? | 1CUnlimited | Дзен (dzen.ru)
Архитектура Postgres это сплошной научный эксперимент . С одной стороны много интересных возможностей которых нет у других (например True версионность) , а с другой стороны убирайте мусор сами Как эффективно настроить autovacuum в Postgres для 1С / Хабр (habr.com)
Если с лицензированием проблемы (мы же понимаем что в любой момент через блекдор могут отключить) , то Postgres без вариантов
Я много работал с Oracle (сертифицированный администратор) и меня с него просто тошнит, можно часами рассказывать как можно потерять данные на ровном месте.
Про sybase и что с ним в итоге сделала Microsoft ... это так же далеко не идеальный продукт.
Как и postgres, это такой же не идеальный продукт, он другой, сравнение примерно из области вам нравится европейская кухня или восточная. Тут свои подходы к реализации бакапов, история с репликами которую предлагали выше это одно из стандартных решений, ничего страшного в этом совершенно нет. Вас вводит в заблуждение попытка переложить старый опыт по тому как проводить оптимизацию, поиск узких мест, тут же имеет смысл его забыть и принять в работу новые принципы, сначала они покажутся странными и неудобным, а через некоторое время придёт понимание почему те или иные решения сделаны так а не иначе.
Проверьте это очень интересный опыт и если удастся перестроить свой взгляд и отношение то возвращался от продуктов open source к коммерческим, каждый раз будет всё сложнее ?
Желаю разобраться и в дальнейшем увидеть множество плюсов в том же постгрес ✌
меня с него просто тошнит, можно часами рассказывать как можно потерять данные на ровном месте
Хорошая тема для статьи. Напишите .Я например в oracle с потерями не сталкивался.
тут же имеет смысл его забыть и принять в работу новые принципы, сначала они покажутся странными и неудобным, а через некоторое время придёт понимание почему те или иные решения сделаны так а не иначе
Тут надо конкретно. Когда сравниваешь например Pg_basebackup и rman как ни сравнивай понятно что в постгресе наложились только на реплику, а на обычный online backup чего то не хватило.
к сожалению было несколько раз когда Oracle с платиновой премиальной поддержкой сначала тянул и перекидывал тикет по разным временным зонам а потом говорил, упс :(
безусловно pg_basebackup и близко не rman, как верно заметили это скорее exp, даже не expdp
лично я предпочитаю наличие реплик и холодный бакап с реплики, причем снапшотом с lvm (некоторые предпочитают файловые с поддержкой снапшотов)
для не особо требовательных систем (сорри но с 1С я особо не церемонился)
я даже на оракл особо не мучился и делал lvm снапшот на ходу, да это не правильно, но как раз на этой операции никогда потерь не было.
по сути это аналог отключения питания для сервера :)
т.е ctl-ы зафиксировали какой-то номер транзакции, в журналах у нас тоже транзакции отмечены, при старте с такого снапшота происходят стандартные процедуры подката и отката и мы получаем консистентную БД на момент X.
Ну а наличие в 1С одной логической операции в разных транзакциях ..., тут ни одна СУБД не справится, только организационные меры, и резервное копирование когда нет пользователей и джобов которые могут такую ситуацию создавать.
(повторюсь, я знаю что это не правильно, но на практике именно наличие снапшотов меня несколько раз спасало, а вот rman несколько раз подводил и говорил что извините, но очередной кусок бакапа не найден, хотя по факту он был)
безусловно pg_basebackup и близко не rman, как верно заметили это скорее exp, даже не expdp
Я pg_dump сравнивал с exp , а pg_basebackup это по сути средство для организации реплик https://habr.com/ru/articles/791726/comments/#comment_26471424
А Oracle RAC это вообще сомнительная технология - несколько серваков и один массив данных.
во первых в БД и так слабое место это ввод вывод, так что RAC не особо чтото ускорит.
Так еще и кластерные ожидания появляются. ASM нужен, у меня друг начинающий ДБА, с этим ASM замучался.
А postgres он админит легко. И точка отказа в RAC одна. Винты дохнут чаще другого. Я для себя не вижу смысла в RAC.
Это как в гужевою повозку запрячь автомобили и пытатся ими управлять при помощи 3го. Легче просто машину с двигателем помощнее.
А postgres он админит легко
Чем waits собирает накопительно? Если не использовать чтото готовое как PostgresPro enterprise там эта задача не для слабонервных . Когда в Oracle и MS SQL это в коробке
А Автоваккум это отдельная тема - хотя конечно следствие true версионника
У меня впечатление что накладных расходов на администрирование у Postgres сильно больше
если вы не планируете увольняться из-за того что бэкап pg не поднимется, то сидеть до упора на MS SQL Server из-за нормальных бэкапов, и лучше переходить последним если в будущем когда то совсем без вариантов будет, только dt периодически тоже выгружайте
видел не раз как люди метнулись на pg, и потом вернулись на ms sql server
тем кто утверждает что там всё замечательно просто махайте рукой, мол всего вам доброго, хорошего настроения и здоровья
И какой у вас в опыте с Postgres был максимальный размер баз/инстансов? Наш отдел Postgres как то на наши сервера MSSQL с 95 терабайтами данных как то странно смотрят и не хотят, чтобы эти базы переводили к ним
Если у вас в базе MSSQL есть более 4 Гб данных - значит, э-э-э, потенциальный барьер платных версий давно пройден и нет никаких причин переходить с них куда-то ещё.
У меня базы не более 5 терабайт на 1С. Там есть свои неэффективности связанные с платформой 1С. 2.5 Терабайт на бестплатный Postgres без проблем, 5 Терабайт уже требуют инструментов платного Postgres для комфортной работы, иначе замучаетесь скрипты писать.
Я так понимаю 95 терабайт это некое inhouse приложение (одна или несколько баз)? Это серьезный объем, для онлайна . Я не очень представляю как с имеющимися средствами резервного копирования в Postgres ворочать такими объемами. Тут даже не вопрос оптимизации запросов в первую очередь, не сборка мусора а управление хранением и восстановлением.Одну базу Postgres при таких объемах нормально не восстановишь быстро, а делить на кластеры это уже другая история
95тб на одном сервере на разных базах, максимальный размер одной базы 18тб. Фулл бэкап всех баз идёт полтора дня
Соответственно это где то 2тб час. Рестор идёт примерно с такой же скоростью
Поскольку у Postgres нормальный бэкап может быть только на уровне кластера, то эту 18тб базу данных придется сажать на отдельный Instance. Как понимаю такие объемы работают с партицированием. Судя по гуглам и ChatGPT бэкапить отдельные партиции опять нужно через pg_dump . В MS SQL это делается на уровне файловых групп.
А pg_dump не будет это делать быстро, поскольку это аналог BCP по идеологии
Единственный вариант жить с такими объемами это держать реплику и периодически делать бэкапы с нее (холодные для надежности) . Ну и научится механизмом checksum проверять ее целостность .
С такими объемами нужно пересматривать иделогию приложения в сторону шардинга
В 1С даже на меньших объемах это очевидно
1С БодиПозитив / Хабр (habr.com)
Сразу упростится администрирование и требования к СУБД
Там все хуже, между этими базами делается join по таблицам из разных баз, то есть логически это одна база размером 95Тб
Если бэкап делается полтора дня, и если на Постгре он делается холодным, и репликация стоит, то сможет ли основной сервер так долго накапливать изменения?
Я чтото не уверен что можно сделать cross join между базами Postgres в рамках одного instance просто запросом. Там даже в синтасисе не предусмотрено указание имени таблицы с базой данных PostgreSQL : Документация: 16: SELECT : Компания Postgres Professional
Не проверял грабли, но по сути даже Pg_basebackup это копии wal
Уровень рассинхрона может в теории быть любой,
Не понимаю, почему все так стараются убедить, что pg_dump это не бэкап и вообще опасная штука. Полтора года есть 5 кластеров с базами 1с, самая большая 1.2Тб, каждый день бэкап pg_dump и каждый день восстановление этого бэкапа в контур разработки именно в несколько других баз , за полтора года ни одного сбоя или кривого бэкапа, что я делаю не так? Второй вопрос, чем ещё заменить его если нужен бэкап конкретной базы и восстановление его в несколько других баз на другом инстансе?
что я делаю не так?
Важен не только размер - а насколько это долго или быстро это происходит . Время не озвучено.
Второй вопрос, чем ещё заменить его если нужен бэкап конкретной базы и восстановление его в несколько других баз на другом инстансе?
По формуле одна база = один инстанс. У Postgres на уровне базы (не путать с instance) нет возможности восстановить к нужному моменту времени. У pg_dump нет возможности управлять tablespace при восстановлении, вы что будете использовать --no-tablespaces чтобы свалить все в кучу? А если места мало и надо по дискам раскидать?
Термин база в Postgres лукавый изначально
А потом прилетает в одну из баз конфига и получаешь проблему.... :)
1. У PG давным-давно сложилось, что размер буфера памяти для работы со строками не может превышать 1Гб.
2. pg_dump развёртывает бинарные строки в текст (под 1 байт бинарных данных отводится 16 бит (2 байта) текстовых данных). Таким образом размер данных удваивается.
3. Конфигурация в базе хранится одной бинарной строкой. Как только ее размер перевалит за ~0.5Гб. pg_dump не сможет ее сохранить, это и приводит к ошибке описанной в первом посте.
А это уже существенная проблема. Получается pg_dump не соответствует лимитам postgres?
Не сказать, что вот я прям любитель русского языка, но в самом начале статьи их количество зашкаливает. На сколько хороша эта тема - я не в курсе, ибо юзаю pg_dump, который ни разу не бэкап (но вполне может им быть - ошибки не на столько часты).
С точки зрения целостности все хорошо – используется уровень изоляции SET TRANSACTION ISOLATION LEVEL REPEATABLE READ. Он достаточно жесткий, т.е. работа параллельных процессов на запись во время длительного бэкапа будет парализована. Каждая таблица блокируется полностью до окончания команд COPY.
Нет, REPEATABLE READ в PG не блокирует запись, это же версионник
В https://www.postgresql.org/docs/current/sql-set-transaction.html для REPEATABLE READ
All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction.
Речь о воспроизводимом чтении - т.е. backup будет видеть только данные, существовавшие на момент его старта, параллельная запись в БД может выполняться
UPD. Запись в таблицу все же блокируется в ходе backup, здесь я неправ
Но блокировка выполняется отдельным оператором LOCK TABLE
Но repeatable read же не предохраняет от вставки новых записей?
Сама по себе - никак не предохраняет, ни от вставки, ни от изменения, ни от удаления. Просто в этой транзакции будет видно состояние БД на момент старта транзакции
Поэтому и называется Repeatable - повторные чтения в рамках транзакции будут возвращать одинаковый результат
REPEATABLE READ гарантирует что повторное чтение тех записей, которые вы уже прочитали, даст тот же результат. Но НЕ гарантирует, что под условие WHERE не попадут новые. Поэтому в общем случает RR не гарантирует, что SELECT вернет тотже резуьтат
Это гарантирует более сильный уровень изоляции - SERIALIZABLE. Поэтому и удивился что используется RR
Поэтому в общем случает RR не гарантирует, что SELECT вернет тотже резуьтат
В общем случае нет, но в постгресе, всё же, да: PostgreSQL's Repeatable Read implementation does not allow phantom reads. This is acceptable under the SQL standard because the standard specifies which anomalies must not occur at certain isolation levels; higher guarantees are acceptable. https://www.postgresql.org/docs/current/transaction-iso.html
Блокировку pg_dump конечно делает, но там же простой AccessShareLock, как у обычного Select-a. Заблокировать он может только вещи по типу Drop/Truncate Table или Vacuum Full, но никак не insert/update. Непонятно почему автор решил, что запись в таблицы остановится
Да, вы правы, почитал https://www.postgresql.org/docs/current/explicit-locking.html. Собственно, да, в Firebird так же было, соответствует ожиданиям
Просто у LOCK TABLE есть отдельная страница в документации https://www.postgresql.org/docs/current/sql-lock.html. И вот там есть такое
When acquiring locks automatically for commands that reference tables, PostgreSQL always uses the least restrictive lock mode possible.
LOCK TABLEprovides for cases when you might need more restrictive locking. For example, suppose an application runs a transaction at theREAD COMMITTEDisolation level and needs to ensure that data in a table remains stable for the duration of the transaction. To achieve this you could obtainSHARElock mode over the table before querying. This will prevent concurrent data changes and ensure subsequent reads of the table see a stable view of committed data, becauseSHARElock mode conflicts with theROW EXCLUSIVElock acquired by writers, and yourLOCK TABLE name IN SHARE MODEstatement will wait until any concurrent holders ofROW EXCLUSIVEmode locks commit or roll back. Thus, once you obtain the lock, there are no uncommitted writes outstanding; furthermore none can begin until you release the lock.To achieve a similar effect when running a transaction at the
REPEATABLE READorSERIALIZABLEisolation level, you have to execute theLOCK TABLEstatement before executing anySELECTor data modification statement. AREPEATABLE READorSERIALIZABLEtransaction's view of data will be frozen when its firstSELECTor data modification statement begins. ALOCK TABLElater in the transaction will still prevent concurrent writes — but it won't ensure that what the transaction reads corresponds to the latest committed values.
При беглом прочтении может сложиться впечатление (у меня сложилось), что LOCK TABLE блокирует таблицу от изменений (что в общем случае неверно). То, что для блокировки нужно указать IN SHARE MODE, а не IN ACCESS SHARE замыливается.
Там не простой Access share lock а NoWait
LOCK TABLE public._document21655 IN ACCESS SHARE MODE NOWAIT;
Получается три ситуации
1) Если pg_dump удалось захватить таблицу и он начал копировать, понятно что это может быть долго. Формально update все с других сессий будут делаться и записываться в таблицу (у нас же версионник) , мусор чистится не будет при таком раскладе, статистика соотвественно тоже не обновится. Что происходит когда вовремя не почистить мусор ? Как эффективно настроить autovacuum в Postgres для 1С / Хабр (habr.com) все планы запросов идут лесом, кластер вроде работает но никак
2) Если pg_dump удалось захватить таблицу и он начал копировать, а кому то на эту таблицу нужно ACCESS EXCLUSIVE (ну разные таблицы бывают по бизне логике) - получим отвал
3) Если pg_dump не удалось сразу захватить таблицу по nowait то как я понимаю она не скопируется . Можно проверить, в доке написано как
Например, предположим, что приложение выполняет транзакцию на уровне изоляции
READ COMMITTEDи оно должно получать неизменные данные на протяжении всей транзакции. Для достижения этой цели можно получить для таблицы блокировку в режимеSHARE, прежде чем обращаться к ней. В результате параллельные изменения данных будут исключены и при последующих чтениях будет получено стабильное представление зафиксированных данных, так как режим блокировкиSHAREконфликтует с блокировкойROW EXCLUSIVE, запрашиваемой при записи, аLOCK TABLE имя IN SHARE MODEбудет ждать, пока параллельные транзакции с блокировкойROW EXCLUSIVEне будут зафиксированы или отменены.
Бесплатного REPEATABLE READ не бывает даже в версионнике
1) Да, длинная REPEATABLE READ будет препятствовать сборке мусора. И для больших БД в версионниках классический backup - вообще долгая вещь, поэтому, насколько я в курсе, там используются инкрементальный backup и/или репликация
2) Из документации наACCESS EXCLUSIVE
Acquired by the
DROP TABLE,TRUNCATE,REINDEX,CLUSTER,VACUUM FULL, andREFRESH MATERIALIZED VIEW(withoutCONCURRENTLY) commands. Many forms ofALTER INDEXandALTER TABLEalso acquire a lock at this level. This is also the default lock mode forLOCK TABLEstatements that do not specify a mode explicitly.
Ну практически все операции - это изменения метаданных... Чтобы backup таблицы мог нормально выполняться во время ее удаления - это немного чересчур...
3) Ваша цитата - это перевод с английского той, которую я приводил. На мой взгляд, эта часть документации не очень удачна и может привести к путанице, о чем я написал. В этой цитате говорится о блокировке в режиме SHARE, а не ACCESS SHARE.
Блокировка в режиме ACCESS SHARE, которая используется в pg_dump, конфликтует только с ACCESS EXCLUSIVE. Блокировка в режиме SHARE конфликтует много с чем и используется только при создании индекса.
То, что бесплатного REPEATABLE READ не бывает, соглашусь
Видимо нужно пример запостить в отдельной статье
В результате параллельные изменения данных будут исключены и при последующих чтениях будет получено стабильное представление зафиксированных данных, так как режим блокировки
SHAREконфликтует с блокировкойROW EXCLUSIVE, запрашиваемой при записи
Там достаточно показать два случая - есть row exclusive набор строк, а pg_dump пытается ACCESS SHARE MODE NOWAIT вангую что он тупо не сможет таблицу заблочить и бэкап будет неполным
Второй вариант дедлок
Третий вариант он не совсем очевиден но есть у всех СУБД есть понятие устаревание shapshot
Например у Postgres оно регулируетя через PostgreSQL : Документация: 15: 20.4. Потребление ресурсов : Компания Postgres Professional
Там достаточно показать два случая - есть row exclusive набор строк, а pg_dump пытается ACCESS SHARE MODE NOWAIT вангую что он тупо не сможет таблицу заблочить и бэкап будет неполным
Зачем ванговать, если можно просто попробовать? Всё нормально дампится. Вам уже сказали, что вы путаете SHARE и ACCESS SHARE, а у них разная совместимость с другими блокировками.
Третий вариант он не совсем очевиден но есть у всех СУБД есть понятие устаревание shapshot
Если пройти по ваше ссылке, то можно прочитать, что в постгресе оно отключено по умолчанию.
Порекомендуйте пожалуйста платный backup \ restore кластера для Postgres и чтобы не больше двух строчек в командном файле, а то после MS SQL и Oracle есть ощущение какого то downshift. Хочется как раньше «заплати и живи спокойно».
Не уверен насчет "двух строчек в командном файле", но в последней версии отечественного КиберПротект 16.5 вроде как завезли PITR и гранулярный рестор отдельных БД. Непонятно только есть ли PITR для отдельной БД. И да, имхо, после комерческих СУБД бекап/рестор в PostgreSQL (при всем уважении к его разработчикам) это прям боль. Хотя, наверное, в целом дело привычки.
Кстати, а если делать мгновенный бэкап виртуалки?
Как это может относится к СУБД? там секунда это уже вечность
Напрямую. Многие делают так бэкапы баз. VMware (или storage) гарантирует, что снэпшот является crash-consistent, то есть нет никакой разницы во времени
Для меня это магия как это можно сделать консистентно особенно если включен асинхронный коммит для большой базы. Замороженный снимок будет хрупким ;)
.
Кстати узнал, у нас именно так делают бэкапы Postgres
Postgres pro не одобряет
Ещё один подход к резервному копированию файловой системы заключается в создании «целостного снимка» каталога с данными, если это поддерживает файловая система (и вы склонны считать, что эта функциональность реализована корректно). Типичная процедура включает создание «замороженного снимка» тома, содержащего базу данных, затем копирование всего каталога с данными (а не его избранных частей, см. выше) из этого снимка на устройство резервного копирования, и наконец освобождение замороженного снимка. При этом сервер базы данных может не прекращать свою работу. Однако резервная копия, созданная таким способом, содержит файлы базы данных в таком состоянии, как если бы сервер баз данных не был остановлен штатным образом; таким образом, когда вы запустите сервер баз данных с сохранёнными данными, он будет считать, что до этого процесс сервера был прерван аварийно, и будет накатывать журнал WAL. Это не проблема, просто имейте это в виду (и обязательно включите файлы WAL в резервную копию). Чтобы сократить время восстановления, можно выполнить команду
CHECKPOINTперед созданием снимка.
Да, точно также MSSQL в таких случаях проигрывает redo-undo по WAL.
Но это еще не все там по ссылке много интересного написано, и про rsync в том числе
Да, например про то, что если Тома разные, то несмотря на то, что каждый из бэкапов консистентный, они могут взаимно быть неконсистентными
Для MSSQL в данном случае действует "сотрудничество" с системой внешнего бэкапа через сервис shadow copy. Вы видели сообщения вида:
I/O is frozen on database DBName. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
Так что и эта проблема не проблема. Удивительно сколько всего я привык воспринимать как должное и естественное, работая с MSSQL
В случае Hyper-V + MS SQL, то там просто дёргаются VSS writers внутри виртуалки. И т.к. у MS SQL есть свои собственные VSS writers, бэкап становится консистентным.
Но, опять же, только для простых серверов баз данных подходит.
А потом вы понимаете, что заменить mssql на pg вы не можете, потому что сервер в разы мощней вы не найдете. И начинаете переходить на greenplum. И проблемы бекапов новеньких 16+ PG начинают казаться смешными и надуманными. И дауншифтинг там может лет на 10, а не на 40 как уже у вашем случае происходит.
Судя по обзору About the Greenplum Architecture (vmware.com) это еще один диспетчер независимых Postgres баз под который нужно специально писать ПО. Как только в PostgresPro сделают прозрачный горизонтальный шардинг План разработок (postgrespro.ru) это будет уже конкурент не MS SQL а Oracle RAC , поскольку MS может только Always on предложить
Статья больше похожа на наезд на Postgresql.
Тут скорее объезд препятствий которые создали архитекторы postres. Причем у них всегда перед глазами были адекватные решения в коммерческих СУБД. Т.е. это нельзя объяснить эффектом первопроходцев
Вот в статье в самом конце написано: "Порекомендуйте пожалуйста платный backup \ restore кластера для Postgres и чтобы не больше двух строчек в командном файле, а то после MS SQL и Oracle есть ощущение какого то downshift. Хочется как раньше «заплати и живи спокойно»."
Но я писал скрипты для RMAN. рабочий скрипт для RMAN это много строк. Если вы просто напишите BACKUP DATABASE - то на выходе получите не консистентный бекап. надо туда добавить обязательно plus archivelog. и не забыть про controlfile и про spfile. pg_basebackup по умолчанию кладет необходимые WAL в бекап, и на выходе плучается консистентный бекап. И делается это одной строкой.
pg_basebackup по умолчанию кладет необходимые WAL в бекап, и на выходе плучается консистентный бекап. И делается это одной строкой.
А восстанавливать как? В oracle вы RMAN используете для восстановления, а в pg_basebackup чем будете?
Если Wal postgres будет в каталоге не по умолчанию , начинаются приключения при восстановлении
Если аналогию с Rman проводить, тогда нужно брать чтото нестандартное для postgres тут много посоветовали .
Публикации
Инструкция по бэкапу одной базы в Postgres – миф или реальность