Postgres: очистить всю базу данных перед повторным созданием / повторным заполнением из скрипта bash
Я пишу сценарий оболочки (станет cronjob), который будет:
1: сбросить мою производственную базу данных
2: импортировать дамп в мою базу данных разработки
Между шагами 1 и 2 мне нужно очистить базу данных разработки (удалить все таблицы?). Как это лучше всего сделать из сценария оболочки? Пока это выглядит так:
Я бы просто отбросил базу данных, а затем заново создал ее. В системе UNIX или Linux это должно быть сделано:
Вот как я это делаю, на самом деле.
Вышеуказанные два будут выглядеть примерно так:
Хотя следующая строка взята из пакетного скрипта Windows, команда должна быть очень похожей:
Если вы хотите очистить вашу базу данных с именем «example_db»:
1) Войдите в другую базу данных (например, ‘postgres’):
2) Удалить вашу базу данных:
3) воссоздать вашу базу данных:
Не исключены (некоторые преднамеренные; некоторые только потому, что у меня нет примера в нашей БД):
У меня также есть версия, которая удаляет «все, кроме двух таблиц и того, что им принадлежит» (последовательность, проверенная вручную, извините, я знаю, скучно), если кто-то заинтересован; разница маленькая. Свяжитесь со мной или проверьте это РЕПО если интересно.
Протестировано, за исключением более поздних дополнений ( extensions предоставленных Clément Prévost ), на PostgreSQL 9.6 ( jessie-backports ). Удаление агрегатов проверено на 9.6 и 12.2, процедура удаления также проверена на 12.2. Исправления и дальнейшие улучшения приветствуются!
В этой главе мы обсудим, как удалить базу данных в PostgreSQL. Есть два варианта удаления базы данных —
Будьте осторожны, прежде чем использовать эту операцию, потому что удаление существующей базы данных приведет к потере полной информации, хранящейся в базе данных.
Использование DROP DATABASE
Эта команда удаляет базу данных. Он удаляет записи каталога для базы данных и удаляет каталог, содержащий данные. Он может быть выполнен только владельцем базы данных. Эта команда не может быть выполнена, пока вы или кто-либо еще подключен к целевой базе данных (подключитесь к postgres или любой другой базе данных, чтобы выполнить эту команду).
Синтаксис
Синтаксис для DROP DATABASE приведен ниже —
параметры
В таблице приведены параметры с их описаниями.
С. Нет.
Параметр и описание
1
Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.
Имя базы данных для удаления.
Не выдавайте ошибку, если база данных не существует. В этом случае выдается уведомление.
Имя базы данных для удаления.
пример
Ниже приведен простой пример, который удалит testdb из вашей схемы PostgreSQL:
Использование команды dropdb
Синтаксис
параметры
В следующей таблице перечислены параметры с их описаниями
С. Нет.
Параметр и описание
1
Имя базы данных, которая будет удалена.
аргументы командной строки, которые принимает dropdb.
Имя базы данных, которая будет удалена.
аргументы командной строки, которые принимает dropdb.
Опции
В следующей таблице перечислены аргументы командной строки, которые принимает dropdb —
С. Нет.
Вариант и описание
1
Показывает команды, отправляемые на сервер.
Выдает запрос подтверждения, прежде чем делать что-либо разрушительное.
Базы данных PostgreSQL требуют периодического проведения процедуры обслуживания, которая называется очисткой. Во многих случаях очистку достаточно выполнять с помощью демона автоочистки, который описан в Подразделе 24.1.6. Возможно, в вашей ситуации для получения оптимальных результатов потребуется настроить описанные там же параметры автоочистки. Некоторые администраторы СУБД могут дополнить или заменить действие этого демона командами VACUUM (обычно они выполняются по расписанию в заданиях cron или Планировщика задач ). Чтобы правильно организовать очистку вручную, необходимо понимать темы, которые будут рассмотрены в следующих подразделах. Администраторы, которые полагаются на автоочистку, возможно, всё же захотят просмотреть этот материал, чтобы лучше понимать и настраивать эту процедуру.
24.1.1. Основные принципы очистки
Команды VACUUM в PostgreSQL должны обрабатывать каждую таблицу по следующим причинам:
Разные причины диктуют выполнение действий VACUUM с разной частотой и в разном объёме, как рассматривается в следующих подразделах.
Команда VACUUM порождает существенный объём трафика ввода/вывода, который может стать причиной низкой производительности в других активных сеансах. Это влияние фоновой очистки можно регулировать, настраивая параметры конфигурации (см. Подраздел 19.4.4).
24.1.2. Высвобождение дискового пространства
Подсказка
Подсказка
24.1.3. Обновление статистики планировщика
Демон автоочистки, если он включён, будет автоматически выполнять ANALYZE после существенных изменений содержимого таблицы. Однако администраторы могут предпочесть выполнение ANALYZE вручную, в частности, если известно, что производимые в таблице изменения не повлияют на статистику по « интересным » столбцам. Демон же планирует выполнение ANALYZE в зависимости только от количества вставленных или изменённых строк; он не знает, приведут ли они к значимым изменениям статистики.
Команду ANALYZE можно выполнять для отдельных таблиц и даже просто для отдельных столбцов таблицы, поэтому, если того требует приложение, одни статистические данные можно обновлять чаще, чем другие. Однако на практике обычно лучше просто анализировать всю базу данных, поскольку это быстрая операция, так как ANALYZE читает не каждую отдельную строку, а статистически случайную выборку строк таблицы.
Подсказка
Кроме того, по умолчанию информация об избирательности функций ограничена. Однако если вы создаёте индекс по выражению с вызовом функции, об этой функции будет собрана полезная статистическая информация, которая может значительно улучшить планы запросов, в которых используется данный индекс.
Подсказка
Демон автоочистки не выполняет команды ANALYZE для сторонних таблиц, поскольку он не знает, как часто это следует делать. Если для получения качественных планов вашим запросам необходима статистика по сторонним таблицам, будет хорошей идеей дополнительно запускать ANALYZE для них по подходящему расписанию.
24.1.4. Обновление карты видимости
Процедура очистки поддерживает карты видимости для каждой таблицы, позволяющие определить, в каких страницах есть только записи, заведомо видимые для всех активных транзакций (и всех будущих транзакций, пока страница не будет изменена). Это имеет два применения. Во-первых, сам процесс очистки может пропускать такие страницы при следующем запуске, поскольку на этих страницах вычищать нечего.
Во-вторых, с такими картами PostgreSQL может выдавать результаты некоторых запросов, используя только индекс, не обращаясь к данным таблицы. Так как индексы PostgreSQL не содержат информацию о видимости записей, при обычном сканировании по индексу необходимо извлечь соответствующую запись из таблицы и проверить её видимость для текущей транзакции. Поэтому при сканировании только индекса, наоборот, сначала проверяется карта видимости. Если известно, что все записи на странице видимы, то выборку из таблицы можно пропустить. Это наиболее полезно с большими наборах данных, когда благодаря карте видимости можно оптимизировать чтение с диска. Карта видимости значительно меньше таблицы, поэтому она легко помещается в кеш, даже когда объём самих страниц очень велик.
24.1.5. Предотвращение ошибок из-за зацикливания счётчика транзакций
Примечание
Параметр vacuum_freeze_min_age определяет, насколько старым должен стать XID, чтобы строки с таким XID были заморожены. Увеличение его значения помогает избежать ненужной работы, если строки, которые могли бы быть заморожены в ближайшее время, будут изменены ещё раз, а уменьшение приводит к увеличению количества транзакций, которые могут выполниться, прежде чем потребуется очередная очистка таблицы.
Максимальное время, в течение которого таблица может обходиться без очистки, составляет два миллиарда транзакций минус значение vacuum_freeze_min_age с момента последней агрессивной очистки. Если бы таблица не подвергалась очистке дольше, была бы возможна потеря данных. Чтобы гарантировать, что это не произойдёт, для любой таблицы, которая может содержать значения XID старше, чем возраст, указанный в конфигурационном параметре autovacuum_freeze_max_age, вызывается автоочистка. (Это случится, даже если автоочистка отключена.)
Столбец age показывает количество транзакций от граничного значения XID до XID текущей транзакции.
Если по какой-либо причине автоочистка не может вычистить старые значения XID из таблицы, система начинает выдавать предупреждающие сообщения, подобные приведённому ниже, когда самое старое значение XID в базе данных оказывается в 11 миллионах транзакций от точки зацикливания:
(Проблему можно решить, как предлагает подсказка, запустив VACUUM вручную; однако учтите, что выполнять VACUUM должен суперпользователь, в противном случае эта процедура не сможет обработать системные каталоги и, следовательно, не сможет увеличить значение datfrozenxid для базы данных.) Если эти предупреждения игнорировать, система отключится и не будет начинать никаких транзакций, как только до точки зацикливания останется менее 1 миллиона транзакций:
24.1.5.1. Мультитранзакции и зацикливание
В качестве меры защиты, агрессивное сканирование с целью очистки будет происходить для любой таблицы, возраст мультитранзакций которой больше, чем autovacuum_multixact_freeze_max_age. Агрессивное сканирование также будет выполняться постепенно со всеми таблицами, начиная с имеющих старейшие мультитранзакции, если объём занятой области членов мультитранзакций превышает 50% от объёма адресуемого пространства. Эти два варианта агрессивного сканирования осуществляются, даже если процесс автоочистки отключён.
24.1.6. Демон автоочистки
Если в течение короткого промежутка времени потребность в очистке возникает для нескольких больших таблиц, все рабочие процессы автоочистки могут продолжительное время заниматься очисткой только этих таблиц. В результате другие таблицы и базы данных будут ожидать очистки, пока не появится свободный рабочий процесс. Число рабочих процессов для одной базы не ограничивается, при этом каждый процесс старается не повторять работу, только что выполненную другими. Заметьте, что в ограничениях max_connections или superuser_reserved_connections число выполняющихся рабочих процессов не учитывается.
Для выполнения сбора статистики используется аналогичное условие: пороговое значение, определяемое как:
Автоочистка не обрабатывает временные таблицы. Поэтому очистку и сбор статистики в них нужно производить с помощью SQL-команд в обычном сеансе.
Познакомимся поближе с процессом VACUUM и теми задачами, которые он решает в PostgreSQL. Этот процесс чистит таблицы, обновляет статистику и обновляет карты видимости и свободного пространства. А также борется с переполнением счетчика транзакций.
Варианты запуска очистки
Очищать таблицы или базы данных от ненужных версий строк можно с помощью следующих команд:
Процедура VACUUM выполняется параллельно с другими транзакциями. При этом частый запуск нагружает систему, редкий запуск приводит к росту размера файлов. Подробнее почитать про очистку PostgreSQL можно тут. Мы уже встречались с понятием VACUUM в статье “Изоляция и многоверсионность в Postgresql“.
Дополнительно к этому существует процесс автоматической очистки AUTOVACUUM:
Частота работы autovacuum worker зависит от частоты изменений таблицы. Чем активнее ведётся работа с таблицей, тем чаще туда приходит autovacuum. Autovacuum настраивается конфигурационными параметрами.
Как уже рассматривалось VACUUM и AUTOVACUUM не сжимает файл, а только очищает его образовывая в нем пустые пространства. Для полного перестроения файла, другими словами чтобы файл уменьшился, нужно использовать VACUUM FULL:
VACUUM FULL очищает таблицу и перезаписывает её в новый файл, при этом файл уменьшается в размере. Для этого на таблицу навешивают блокировку, поэтому на некоторое время таблица становится недоступной. Похожим образом работает TRUNCATE.
Команда TRUNCATE блокирует таблицу и очищает её, при этом старые версии строк не сохраняются и файл уменьшается физически. Другой способ очистить таблицу это выполнить DELETE всех строк в ней, а затем запустить по этой таблице VACUUM FULL для очистки. TRUNCATE это транзакционная команда, поэтому её можно отменить (ROLLBACK).
Обновление статистики
Фоновый процесс autovacuum обновляет статистику. А статистика необходима планировщику, чтобы строить план запроса.
В ручную сбор статистику можно запустить с помощью следующих команд:
Существует много параметров конфигурации, которые управляют процессом autovacuum:
Обновление карт видимости и свободного пространства
Карта видимости – помечает те страницы, где есть только актуальные версии строк, значит очищать такие страницы не нужно.
Карта свободного пространства – нужна чтобы быстро вставлять новые версии строк.
Эти карты обновляет autovacuum:
Переполнение счётчика транзакций и заморозка
Счетчик транзакций 32-битный – это примерно 4 миллиарда значений. И он постоянно растёт! Если счетчик транзакций переполнится и начтет считать с нуля, то это приведет к большим проблемам на сервере. Для борьбы с этой проблемой существует процесс “Заморозка“.
При заморозке берутся версии строчек, в которых номер транзакции достаточно старый. То есть эта транзакция должна быть видна во всех новых снимках. Этот номер транзакции меняется на “минус бесконечность“. Система понимает, что эта строчка была создана когда-то давно и номер транзакции создавший её уже не имеет значения. Значит этот номер транзакции можно использовать повторно. Замороженные номера транзакций можно повторно использовать.
Почему не сделать счетчик 64 битным? В каждой версии строчки есть заголовок. Если счетчик будет 64 битным, то будет слишком много служебной информации на каждую версию строки.
Как сначала удалить все данные в базе данных промежуточного сервера, прежде чем восстанавливать данные из рабочего дампа?
Я хочу удалить только все данные, чтобы мне не пришлось удалять и создавать базу данных и все такое. Я просто хочу удалить данные и вставить новые данные, вот и все.
У меня нет возможности удалить и создать базу данных по нескольким причинам. Мне придется удалить все данные и просто вставить только, поэтому, чтобы найти способ сделать это, я готов пойти на это, но для начала нужна помощь, очевидно.
Мне также нужно автоматизировать этот процесс. Автоматизирует «сброс данных из производственной базы данных», затем «удаление данных в промежуточной базе данных», а затем «восстановление данных в промежуточную базу данных». Мне просто нужна помощь в части «удаление данных при постановке БД».
Я работаю на PostgreSQL 9.5.2
Вам не нужно удалять базу данных, этого должно быть достаточно, чтобы удалить все объекты в базе данных. Это можно сделать с помощью
Однако, если вы хотите удалить все, вы можете сделать это с помощью небольшого динамического SQL:
Это обрезает все таблицы в схеме public одним оператором, который также будет работать, даже если существует множество ограничений внешнего ключа, соединяющих все таблицы. Если ваши таблицы распределены по нескольким схемам, вам необходимо добавить их в where условие.
Просто чтобы уточнить, на случай, если это сбивает с толку:
Если по какой-то причине удаление и воссоздание таблиц неприемлемо, вам придется приложить больше усилий, чтобы вручную создать сценарий, который создает data only дамп из исходной базы данных, проблем TRUNCATE или DELETE в целевой базе данных, а затем загружает дамп данных. Насколько я знаю, нет быстрого / гладкого способа сделать это.
Приведенный выше запрос будет генерировать усеченные запросы для всех таблиц в базе данных.
Не исключены (некоторые преднамеренные; некоторые только потому, что у меня нет примера в нашей БД):
У меня также есть версия, которая удаляет «все, кроме двух таблиц и того, что им принадлежит» (последовательность, проверенная вручную, извините, я знаю, скучно), если кто-то заинтересован; разница маленькая. Свяжитесь со мной или проверьте этот репо, если вы заинтересованы.
Протестировано, за исключением более поздних дополнений ( extensions предоставленных Clément Prévost ), на PostgreSQL 9.6 ( jessie-backports ). Удаление агрегатов проверено на 9.6 и 12.2, процедура удаления также проверена на 12.2. Исправления и дальнейшие улучшения приветствуются!