26.1. SQL-дамп

Примеры

Усекаем таблицы bigtable и fattable:

TRUNCATE bigtable, fattable;

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

TRUNCATE bigtable, fattable RESTART IDENTITY;

Усечь таблицуothertable и выполнить каскадирование ко всем таблицам, которые ссылаются наothertable через ограничения внешнего ключа:

TRUNCATE другая таблица КАСКАД;

Заметки

Чтобы усечь таблицу, у вас должна быть привилегия TRUNCATE.

TRUNCATE нельзя использовать для таблицы, которая имеет ссылки на внешние ключи из других таблиц, если только все такие таблицы не будут усечены одной и той же командой. Проверка достоверности в таких случаях потребует сканирования таблицы, и весь смысл в том, чтобы этого не делать. Опцию CASCADE можно использовать для автоматического включения всех зависимых таблиц, но будьте очень осторожны при использовании этой опции, иначе вы можете потерять данные, которые не собирались терять! Обратите внимание, в частности, что когда таблица, подлежащая усечению, является секцией, дочерние секции остаются нетронутыми, но каскадирование происходит для всех ссылающихся таблиц и всех их секций без каких-либо различий.

TRUNCATE не будет запускать триггеры ON DELETE, которые могут существовать для таблиц. Но он сработает триггеры ON TRUNCATE. Если для любой из таблиц определены триггеры ON TRUNCATE, то все триггеры BEFORE TRUNCATE срабатывают до того, как произойдет какое-либо усечение, а все триггеры AFTER TRUNCATE срабатывают после выполнения последнего усечения и сброса всех последовательностей. Триггеры срабатывают в том порядке, в котором таблицы должны быть обработаны (сначала те, которые перечислены в команде, а затем те, которые были добавлены в результате каскадирования).

TRUNCATE безопасен для транзакций по отношению к данным в таблицах: усечение будет безопасно отменено, если окружающая транзакция не зафиксируется.

Когда указан RESTART IDENTITY, подразумеваемые операции ALTER SEQUENCE RESTART также выполняются транзакционно; то есть они будут отменены, если окружающая транзакция не будет зафиксирована. Имейте в виду, что если какие-либо дополнительные операции с последовательностями выполняются над перезапущенными последовательностями до отката транзакции, последствия этих операций для последовательностей будут отменены, но не их влияние на currval(); то есть после транзакции currval() будет продолжать отражать последнее значение последовательности, полученное внутри неудачной транзакции, даже если сама последовательность может больше не соответствовать этому. Это похоже на обычное поведение currval() после неудачной транзакции.

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

Что происходит внутри

Очистка должна обрабатывать и таблицу, и индексы одновременно, и делать это так, чтобы не блокировать работу всех процессов. Как ей это обеспечить?

Все начинается со маленькой таблицы (с учетом карты, как уже отмечалось). На прочитанных страницах ненужные строки версий и их идентификаторы (tid) для поиска в специальном массиве. Массив располагается в локальной памяти процесса очистки; для него выделен фрагмент размера Maintenance_work_mem. Значение этого параметра по умолчанию — 64 МБ. Видно, что эта память находится сразу на всей глубине, а не по мере необходимости. Правда, если таблица небольшая, то и фрагмент будет меньше.

Дальше одно из двух: либо мы дойдем до конца таблицы, либо выделенная под массивом память заканчится. В любом из двух случаев начинается фаза очистки индексов. Для этого каждый из индексов, созданных в таблице, полностью сканируется в поисках записей, которые ссылаются на запомненные версии строк. Найденные записи вычищаются из индексных страниц.

В этом месте мы получаем такую ​​картину: в индексах уже нет ссылок на ненужные строки версии, а в таблице они еще есть. Это ничему не противоречит: при выполнении запроса мы либо вообще не попадем на мертвые версии строк (при индексном доступе), либо отменим их при боковом сканировании (при сканировании таблицы).

После этого начинается фаза таблицы очистки. Таблица снова сканируется, чтобы прочитать нужные страницы, вычистить из них запомненные строки версии и уменьшить указатели. Мы можем это сделать, поскольку ссылок из индексов уже нет.

Если на первой таблице не была прочитана полностью, то массив очищается и все повторяется с того места, на котором наш проход остановился.

На больших таблицах это может занимать достойное время и создавать постоянную работу в системе. Конечно, просьба не будет блокироваться, но «лишний» ввод-вывод тоже неприятен.

Чтобы ускорить процесс, имеет смысл либо вызывать очистку чаще (чтобы за каждый раз очищалось не очень большое количество версий строк), либо выделить больше памяти.

Замечу в скобках, что, начиная с версии 11, PostgreSQL может пропускать сканирование индексов, если в этом нет насущной необходимости. Это должно облегчить жизнь владельцев больших таблиц, в которые строки только добавляются (но не изменяются).

Description

DROP DATABASE drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. It cannot be executed while you are connected to the target database. ( Connect to postgres or any other database to issue this command.) Also, if anyone else is connected to the target database, this command will fail unless you use the FORCE option described below.

DROP DATABASE cannot be undone. Use it with care!

Outputs

When VERBOSE is specified, VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.

For tables with GIN indexes, VACUUM (in any form) also completes any pending index insertions, by moving pending index entries to the appropriate places in the main GIN index structure. See Section 70.4.1 for details.

The FULL option is not recommended for routine use, but might be useful in special cases. An example is when you have deleted or updated most of the rows in a table and would like the table to physically shrink to occupy less disk space and allow faster table scans. V ACUUM FULL will usually shrink the table more than a plain VACUUM would.

The PARALLEL option is used only for vacuum purposes. If this option is specified with the ANALYZE option, it does not affect ANALYZE.

VACUUM causes a substantial increase in I/O traffic, which might cause poor performance for other active sessions. Therefore, it is sometimes advisable to use the cost-based vacuum delay feature. For parallel vacuum, each worker sleeps in proportion to the work done by that worker. See Section 20.4.4 for details.

Each backend running VACUUM without the FULL option will report its progress in the pg_stat_progress_vacuum view. Backends running VACUUM FULL will instead report their progress in the pg_stat_progress_cluster view. See Section 28.4.5 and Section 28.4.2 for details.

Переменные окружения

Параметры подключения по умолчанию

Выбирает вариант использования цвета в диагностических сообщениях. Возможные значения: always (всегда), auto (автоматически) и never (никогда).

Эта утилита, как и большинство других утилит , также использует переменные среды, поддерживаемые (см. Раздел 32.15).

Полная очистка (vacuum full)

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

Если таблица или индекс по каким-то причинам сильно выросли в размерах, то обычная очистка освободит место внутри существующих страниц: в них появятся «дыры», которые затем будут использованы для вставки новых версий строк. Но число страниц не изменится, и, следовательно, с точки зрения операционной системы файлы будут занимать ровно столько же места, сколько занимали и до очистки. А это плохо, потому что:

(Единственно исключение составляют полностью очищенные страницы, находящиеся в конце файла — такие страницы «откусываются» от файла и возвращаются операционной системе.)

Если доля полезной информации в файлах опустилась ниже некоторого разумного предела, администратор может выполнить полную очистку таблицы. При этом таблица и все ее индексы перестраиваются полностью с нуля, а данные упаковываются максимально компактно (разумеется, с учетом параметра fillfactor). При перестройке PostgreSQL последовательно перестраивает сначала таблицу, а затем и каждый из ее индексов. Для каждого объекта создаются новые файлы, а в конце перестройки старые файлы удаляются. Следует учитывать, что в процессе работы на диске потребуется дополнительное место.

Для иллюстрации снова вставим в таблицу некоторое количество строк:

Как оценить плотность информации? Для этого удобно воспользоваться специальным расширением:

Функция читает полность всю таблицу и показывает статистику по тому, сколько места какими данными занято в файлах. Основная информация, которая нам сейчас интересна — поле tuple_percent: процент, занятый полезными данными. Он меньше 100 из-за неизбежных накладных расходов на служебную информацию внутри страницы, но тем не менее довольно высок.

Для индекса выводится другая информация, но поле avg_leaf_density имеет тот же смысл: процент полезной информации (в листовых страницах).

А вот какой размер занимают таблица и индекс:

Теперь удалим 90% всех строк. Строки для удаления выбираем случайно, чтобы в каждой странице с большой вероятностью хоть одна строка, да осталась:

Какой размер будут иметь объекты после обычной очистки?

Мы видим, что размер не изменился: обычная очистка никак не может уменьшить размер файлов. Хотя плотность информации, очевидно, уменьшилась примерно в 10 раз:

Теперь проверим, что получится после полной очистки. Вот какие файлы используются сейчас таблицей и индексами:

Теперь файлы заменены на новые. Размер таблицы и индекса существенно уменьшился, а плотность информации, соответственно, увеличилась:

Обратите внимание, что плотность информации в индексе даже увеличилась по сравнению с первоначальной. Заново создать индекс (B-дерево) по имеющимся данным выгоднее, чем вставлять данные в уже имеющийся индекс строка за строкой.

Функции расширения pgstattuple, которые мы использовали, читают полностью всю таблицу. Если таблица большая, то это неудобно, и поэтому там же есть функция pgstattuple_approx, которая пропускает страницы, отмеченные в карте видимости, и показывает примерные цифры.

Еще более быстрый, но и еще менее точный способ — прикинуть отношение объема данных к размеру файла по системному каталогу. Варианты таких запросов можно найти в вики.

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

Compatibility

There is no DROP DATABASE statement in the SQL standard.

VACUUM reclaims storage occupied by dead tuples. In normal operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables.

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it’s just kept available for re-use within the same table. It also allows us to leverage multiple CPUs in order to process indexes. This feature is known as parallel vacuum. To disable this feature, one can use PARALLEL option and specify parallel workers as zero. V ACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an ACCESS EXCLUSIVE lock on each table while it is being processed.

When the option list is surrounded by parentheses, the options can be written in any order. Without parentheses, options must be specified in exactly the order shown above. The parenthesized syntax was added in 9.0; the unparenthesized syntax is deprecated.

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

Может понадобиться создать базу данных. Это можно сделать SQL-запросом:

Если мы получим ошибку:

ERROR:  encoding «UTF8» does not match locale «en_US»
DETAIL:  The chosen LC_CTYPE setting requires encoding «LATIN1».

Указываем больше параметров при создании базы:

Базовая команда

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

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Из файла gz

Сначала распаковываем файл, затем запускаем восстановление:

Или одной командой:

Определенную базу

Если резервная копия делалась для определенной базы, запускаем восстановление:

Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d:

Определенную таблицу

Если резервная копия делалась для определенной таблицы, можно просто запустить восстановление:

Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t:

С помощью pgAdmin

Выбираем наш файл с дампом:

И кликаем по Восстановить:

Использование pg_restore

Данная утилита предназначена для восстановления данных не текстового формата (в одном из примеров создания копий мы тоже делали резервную копию не текстового формата).

С созданием новой базы:

Мы можем использовать опцию d для указания подключения к конкретному серверу и базе, например:

Примеры

Очистка базы данных test:

$ vacuumdb test

Очистка и анализ для оптимизатора базы данных bigdb:

$ vacuumdb —analyze bigdb

Очистка одной таблицы foo в базе данных xyzzy и анализ только столбца bar таблицы для оптимизатора:

$ vacuumdb —analyze —verbose —table=’foo(bar)’ xyzzy

Synopsis

Do not throw an error if the database does not exist. A notice is issued in this case.

The name of the database to remove.

Attempt to terminate all existing connections to the target database. It doesn’t terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.

Selects vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

Selects aggressive of tuples. Specifying FREEZE is equivalent to performing VACUUM with the vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to zero. Aggressive freezing is always performed when the table is rewritten, so this option is redundant when FULL is specified.

Prints a detailed vacuum activity report for each table.

Updates statistics used by the planner to determine the most efficient way to execute a query.

Normally, VACUUM will skip pages based on the visibility map. Pages where all tuples are known to be frozen can always be skipped, and those where all tuples are known to be visible to all transactions may be skipped except when performing an aggressive vacuum. Furthermore, except when performing an aggressive vacuum, some pages may be skipped in order to avoid waiting for other sessions to finish using them. This option disables all page-skipping behavior, and is intended to be used only when the contents of the visibility map are suspect, which should happen only if there is a hardware or software issue causing database corruption.

Normally, VACUUM will skip index vacuuming when there are very few dead tuples in the table. The cost of processing all of the table’s indexes is expected to greatly exceed the benefit of removing dead index tuples when this happens. This option can be used to force VACUUM to process indexes when there are more than zero dead tuples. The default is AUTO, which allows VACUUM to skip index vacuuming when appropriate. If INDEX_CLEANUP is set to ON, VACUUM will conservatively remove all dead tuples from indexes. This may be useful for backwards compatibility with earlier releases of where this was the standard behavior.

INDEX_CLEANUP can also be set to OFF to force VACUUM to skip index vacuuming, even when there are many dead tuples in the table. This may be useful when it is necessary to make VACUUM run as quickly as possible to avoid imminent transaction ID wraparound (see Section 25.1.5). However, the wraparound failsafe mechanism controlled by vacuum_failsafe_age will generally trigger automatically to avoid transaction ID wraparound failure, and should be preferred. If index cleanup is not performed regularly, performance may suffer, because as the table is modified indexes will accumulate dead tuples and the table itself will accumulate dead line pointers that cannot be removed until index cleanup is completed.

Эта опция не действует для таблиц, не имеющих индекса, и игнорируется, если используется опция FULL. Это также не влияет на отказоустойчивый механизм переноса идентификатора транзакции. При его срабатывании очистка индекса будет пропущена, даже если для параметра INDEX_CLEANUP установлено значение ON.

Указывает, что VACUUM должен попытаться обработать основное отношение. Обычно это желаемое поведение и значение по умолчанию. Установка для этого параметра значения false может быть полезна, когда необходимо очистить только соответствующую таблицу TOAST отношения.

Указывает, что VACUUM должен попытаться обработать соответствующую таблицу TOAST для каждого отношения, если оно существует. Обычно это желаемое поведение и значение по умолчанию. Установка для этой опции значения false может быть полезна, когда необходимо очистить только основное отношение. Эта опция обязательна, если используется опция FULL.

Указывает, что VACUUM должен попытаться обрезать все пустые страницы в конце таблицы и разрешить возврат дискового пространства для усеченных страниц в операционную систему. Обычно это желаемое поведение и значение по умолчанию, если только для параметра Vacuum_truncate не установлено значение false для очищаемой таблицы. Установка для этой опции значения false может быть полезна, чтобы избежать блокировки ACCESS EXCLUSIVE для таблицы, которая требуется для усечения. Эта опция игнорируется, если используется опция FULL.

Выполняйте фазы вакуумирования индекса и очистки индекса VACUUM параллельно, используя целочисленные фоновые рабочие процессы (подробную информацию о каждой фазе вакуумирования см. в Таблице 28.45). Количество рабочих процессов, используемых для выполнения операции, равно количеству индексов в отношении, поддерживающих параллельную очистку, которое ограничено количеством рабочих процессов, указанным с опцией PARALLEL, если таковая имеется, которая дополнительно ограничена max_parallel_maintenance_workers. Индекс может участвовать в параллельной очистке тогда и только тогда, когда размер индекса превышает min_parallel_index_scan_size. Обратите внимание, что не гарантируется, что количество параллельных рабочих процессов, указанное в целом числе, будет использоваться во время выполнения. Возможно, что пылесос будет работать с меньшим количеством рабочих, чем указано, или даже без рабочих вообще. Для каждого индекса можно использовать только одного работника. Таким образом, параллельные воркеры запускаются только тогда, когда в таблице есть хотя бы 2 индекса. Рабочие для вакуума запускаются перед началом каждой фазы и выходят в конце фазы. Это поведение может измениться в будущем выпуске. Эту опцию нельзя использовать с опцией FULL.

Указывает, что VACUUM должен пропускать обновление статистики всей базы данных о самых старых незамороженных XID. Обычно VACUUM обновляет эту статистику один раз в конце команды. Однако в базе данных с очень большим количеством таблиц это может занять некоторое время и ничего не даст, если только таблица, содержащая самый старый незамороженный XID, не окажется среди очищенных. Более того, если параллельно выполняются несколько команд VACUUM, только одна из них может одновременно обновлять статистику всей базы данных. Поэтому, если приложение намеревается выполнить серию из множества команд VACUUM, может быть полезно установить этот параметр во всех таких командах, кроме последней; или задайте его во всех командах и потом отдельно выдайте VACUUM (ONLY_DATABASE_STATS).

Указывает, что VACUUM не должен ничего делать, кроме обновления статистики всей базы данных о самых старых незамороженных XID. Если указана эта опция, список table_and_columns должен быть пустым, и никакая другая опция не может быть включена, кроме VERBOSE.

Указывает, следует ли включить или выключить выбранную опцию. Вы можете написать TRUE, ON или 1, чтобы включить эту опцию, и FALSE, OFF или 0, чтобы отключить ее. Логическое значение также можно опустить, и в этом случае предполагается TRUE.

Указывает неотрицательное целое значение, передаваемое выбранной опции.

Имя (возможно, дополненное схемой) конкретной таблицы или материализованного представления для очистки. Если указанная таблица является секционированной, все ее конечные разделы очищаются.

Имя конкретного столбца для анализа. По умолчанию для всех столбцов. Если указан список столбцов, необходимо также указать ANALYZE.

Замечания

Утилита может включаться к серверу несколько раз, и при этом она будет каждый раз запрашивать пароль. В таких случаях удобно иметь файл ~/.pgpass. За независимыми сведениями обратиться к Разделу 32.16.

Работа с CSV

Мы можем переносить данные, используя файлы csv. Это нельзя назвать напрямую резервным копированием, но в данной инструкции материал будет интересен.

Создание файла CSV (экспорт)

Пример запроса (выполняется в командной оболочке SQL):

Также мы можем сделать выгрузку, но сделать вывод в оболочку и перенаправить его в файл:

Импорт данных из файла CSV

Также можно выполнить запрос в оболочке SQL:

Или перенаправить запрос через STDOUT из файла:

This command cannot be executed while connected to the target database. Thus, it might be more convenient to use the program instead, which is a wrapper around this command.

The SQL:2008 standard includes a TRUNCATE command with the syntax TRUNCATE TABLE tablename. The clauses CONTINUE IDENTITY/RESTART IDENTITY also appear in that standard, but have slightly different though related meanings. Some of the concurrency behavior of this command is left implementation-defined by the standard, so the above notes should be considered and compared with other implementations if necessary.

Параметры

Утилита принимает следующие аргументы командной строки:

Очистить все базы данных.

Указывает имя базы данных для очистки или анализа, когда не используется параметр -a/—all. Если это указание отсутствует, имя базы определяется переменной окружения PGDATABASE. Если эта переменная не задана, именем базы будет имя пользователя, указанное для подключения. В аргументе имя_бд может задаваться строка подключения. В этом случае параметры в строке подключения переопределяют одноимённые параметры, заданные в командной строке.

Запретить пропуск страниц в зависимости от содержимого карты видимости.

Примечание

Этот параметр доступен только для серверов версии 9.6 и новее.

Выводить команды, которые генерирует и передаёт серверу.

Произвести очистку.

Агрессивно версии строк.

Всегда удалять элементы индекса, указывающие на мёртвые кортежи.

Этот параметр доступен только для серверов версии 12 и новее.

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

будет устанавливать несколько подключений к базе данных (в количестве число_заданий), так что убедитесь в том, что значение max_connections достаточно велико, чтобы все эти подключения были приняты.

Заметьте, что использование этого режима с параметром -f (FULL) может привести к отказам из-за взаимоблокировок, если параллельно начнут обрабатываться определённые системные каталоги.

Выполнять команды очистки и анализа только для таблиц, имеющих не менее чем заданный возраст_мультитранзакции. Этот параметр полезен для выбора таблиц, первоочередная обработка которых поможет предотвратить зацикливание идентификаторов мультитранзакций (см. Подраздел 23.1.5.1).

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

Выполнять команды очистки и анализа только для таблиц, имеющих не менее чем заданный возраст_транзакции. Этот параметр полезен для выбора таблиц, обработка которых в первую очередь поможет предотвратить зацикливание идентификаторов транзакций (см. Подраздел 23.1.5).

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

Не удалять элементы индекса, указывающие на мёртвые кортежи.

Пропускать TOAST-таблицу, связанную с очищаемой таблицей (при наличии).

Этот параметр доступен только для серверов версии 14 и новее.

Не отсекать пустые страницы в конце таблицы.

Задаёт количество параллельных исполнителей для параллельной очистки. Это позволяет в ходе очистки задействовать мощности нескольких процессоров для обработки индексов. См. .

Этот параметр доступен только для серверов версии 13 и новее.

Подавлять вывод сообщений о прогрессе выполнения.

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

Производить очистку или анализ только указанной таблицы. Имена столбцов можно указать только в сочетании с параметрами —analyze и —analyze-only. Добавив дополнительные ключи -t, можно обработать несколько таблиц.

Подсказка

Если вы указываете столбцы, вам, вероятно, придётся экранировать скобки в оболочке. ( См. примеры ниже.)

Вывести подробную информацию во время процесса.

Сообщить версию и завершиться.

Также вычислить статистику для оптимизатора.

Только вычислить статистику для оптимизатора (не производить очистку).

Только вычислить статистику для оптимизатора (без очистки), подобно —analyze-only. При этом анализ выполняется в три этапа: на первом этапе для скорейшего получения полезной статистики используется минимально возможный ориентир статистики (см. default_statistics_target), а на последующих этапах вычисляется полная статистика.

Этот параметр полезен, только когда нужно провести анализ базы данных, статистика в которой отсутствует или абсолютно неактуальна, например когда база была только что наполнена данными из копии или в результате процедуры pg_upgrade. Имейте в виду, что использование этого параметра для базы, в которой уже есть статистика, может привести к тому, что качество решений оптимизатора может временно ухудшиться из-за небольших ориентиров статистики на ранних этапах.

Показать справку по аргументам командной строки и завершиться.

Утилита также принимает следующие аргументы командной строки в качестве параметров подключения:

Указывает имя компьютера, на котором работает сервер. Если значение начинается с косой черты, оно определяет каталог Unix-сокета.

Указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения.

Имя пользователя, под которым производится подключение.

Не выдавать запрос на ввод пароля. Если сервер требует аутентификацию по паролю и пароль не доступен с помощью других средств, таких как файл .pgpass, попытка соединения не удастся. Этот параметр может быть полезен в пакетных заданиях и скриптах, где нет пользователя, который вводит пароль.

Принудительно запрашивать пароль перед подключением к базе данных.

Это несущественный параметр, так как запрашивает пароль автоматически, если сервер проверяет подлинность по паролю. Однако чтобы понять это, лишний раз подключается к серверу. Поэтому иногда имеет смысл ввести -W, чтобы исключить эту ненужную попытку подключения.

Указывает имя базы данных, к которой будет выполняться подключение для определения подлежащих очистке баз данных, когда используется ключ -a/—all. Если это имя не указано, будет выбрана база postgres, а если она не существует — template1. В данном аргументе может задаваться строка подключения. В этом случае параметры в строке подключения переопределяют одноимённые параметры, заданные в командной строке. Кроме того, все параметры в строке подключения, за исключением имени базы, будут использоваться и при подключении к другим базам данных.

There is no VACUUM statement in the SQL standard.

Что делает очистка

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

Основная, «обычная» очистка выполняется командой VACUUM и ее мы будем называть просто очисткой (а про автоочистку мы будем говорить отдельно).

Итак, очистка обрабатывает таблицу полностью. Она вычищает не только ненужные версии строк, но и ссылки на них из всех индексов.

Обработка происходит параллельно с другой активностью в системе. Таблица и индексы при этом могут использоваться обычным образом и для чтения, и для изменения (однако одновременное выполнение таких команд, как CREATE INDEX, ALTER TABLE и некоторых других будет невозможно).

В таблице просматриваются только те страницы, в которых происходила какая-то активность. Для этого используется карта видимости (напомню, что в ней отмечены страницы, содержащие только достаточно старые версии строк, которые гарантированно видимы во всех снимках данных). Обрабатываются только страницы, не отмеченные в карте, а сама карта при этом обновляется.

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

Как водится, создадим таблицу:

С помощью параметра autovacuum_enabled мы отключаем автоматическую очистку. Про нее мы будем говорить в следующий раз, а пока — для экспериментов — нам важно управлять очисткой вручную.

Сейчас в таблице три версии строки, и на каждую ведет ссылка из индекса:

После очистки «мертвые» версии пропадают и остается только одна, актуальная. И в индексе тоже остается одна ссылка:

Обратите внимание, что два первых указателя получили статус unused, а не dead, как было бы при внутристраничной очистке.

TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.

Обычная очистка (vacuum)

Рассмотрим некоторые проблемы, с которыми можно столкнуться при работе с дампами PostgreSQL.

Input file appears to be a text format dump. please use psql.

Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore.

No matching tables were found

Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.

Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.

Too many command-line arguments

Причина: Утилита pg_dump чувствительна к лишним пробелам.

Решение: проверьте, что нет лишних пробелов.

Aborting because of server version mismatch

Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.

No password supplied

Причина: нет системной переменной PGPASSWORD или она пустая.

Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD).

Неверная команда

Причина: при выполнении восстановления возникла ошибка, которую СУБД не показывает при стандартных параметрах восстановления.

Решение: запускаем восстановление с опцией -v ON_ERROR_STOP=1, например:

Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.

Создание резервных копий

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

Скрипт для автоматического резервного копирования

Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.

Для начала, создадим каталог, в котором разместим скрипт, например:

И сам скрипт:

Вариант 1. Запуск от пользователя root; одна база.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Вариант 2. Запуск от пользователя postgres; все базы.

* где /backup — каталог, в котором будут храниться резервные копии; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.

Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, /backup/postgres.

Зададим в качестве владельца файла, пользователя postgres:

crontab -e -u postgres

* мы откроем на редактирование cron для пользователя postgres.

Права и запуск

Разрешаем запуск скрипта, как исполняемого файла:

chmod +x /scripts/postgresql_dump.sh

Единоразово можно запустить задание на выполнение резервной копии:

su — postgres -c «/scripts/postgresql_dump.sh»

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

Дамп определенной таблицы

Если наша таблица находится в определенной схеме, то она указывается вместе с ней, например:

Размещение каждой таблицы в отдельный файл

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Для определенной схемы

В нашей базе может быть несколько схем. Если мы хотим сделать дамп только для определенной схемы, то используем опцию -n, например:

* в данном примере мы заархивируем схему public базы данных peoples.

Только схемы (структуры)

Для резервного копирования без данных (только таблицы и их структуры):

Также, внутри каждой базы могут быть свои схемы с данными. Если нам нужно сделать дамп именно той схемы, которая внутри базы, используем ключ -n:

Или полный дамп с данными для схемы внутри базы данных:

Только данные

Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.

В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:

При желании, можно изучить дополнительные параметры для резервного копирования:

После нажимаем Резервная копия — ждем окончания процесса и кликаем по Завершено.

Не текстовые форматы дампа

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

Бинарный с компрессией:

Использование pg_basebackup

pg_basebackup позволяет создать резервную копию для кластера PostgreSQL.

pg_basebackup -h node1 -D /backup

* в данном примере создается резервная копия для сервера node1 с сохранением в каталог /backup.

Pg_dumpall

Данная утилита делает выгрузку всех баз данных, в том числе системных. На выходе получаем файл для восстановления в формате скрипта.

Утилиту удобно использовать с ключом -g (—globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).

Для создание резервного копирования со сжатием:

Диагностика

В случае возникновения трудностей, обратитесь к описаниям и , где обсуждаются потенциальные проблемы и сообщения об ошибках. Учтите, что на целевом компьютере должен работать сервер баз данных. При этом применяются все свойства подключения по умолчанию и переменные окружения, которые использует клиентская библиотека .

Похожие команды

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

Команда CLUSTER во всем аналогична VACUUM FULL, но дополнительно физически упорядочивает версии строк в соответствии с одним из имеющихся индексов. Это дает планировщику возможность более эффективно использовать индексный доступ в некоторых случаях. Однако надо понимать, что кластеризация не поддерживается: при последующих изменениях таблицы физический порядок версий строк будет нарушаться.

Команда REINDEX перестраивает отдельный индекс на таблице. Фактически, VACUUM FULL и CLUSTER используют эту команду для того, чтобы перестроить индексы.

Команда TRUNCATE логически работает так же, как и DELETE — удаляет все табличные строки. Но DELETE, как уже было рассмотрено, только помечает версии строк как удаленные, что требует дальнейшей очистки. T RUNCATE же просто создает новый, чистый файл. Как правило, это работает быстрее, но надо учитывать, что TRUNCATE полностью заблокирует работу с таблицей на все время до конца транзакции.

Анализ

Анализ, или, иными словами, сбор статистической информации для планировщика запросов, формально никак с очисткой не связан. Тем не менее мы можем выполнять анализ не только командой ANALYZE, но и совмещать очистку с анализом: VACUUM ANALYZE. При этом сначала выполняется очистка, а затем анализ — никакой экономии не происходит.

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

The name (optionally schema-qualified) of a table to truncate. If ONLY is specified before the table name, only that table is truncated. If ONLY is not specified, the table and all its descendant tables (if any) are truncated. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

Automatically restart sequences owned by columns of the truncated table(s).

Do not change the values of sequences. This is the default.

Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE.

Refuse to truncate if any of the tables have foreign-key references from tables that are not listed in the command. This is the default.