Как проверить postgresql на ошибки - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

pg_amcheck — checks for corruption in one or more PostgreSQL databases

Synopsis

pg_amcheck [option…] [dbname]

Description

pg_amcheck supports running amcheck’s corruption checking functions against one or more databases, with options to select which schemas, tables and indexes to check, which kinds of checking to perform, and whether to perform the checks in parallel, and if so, the number of parallel connections to establish and use.

Only ordinary and toast table relations, materialized views, sequences, and btree indexes are currently supported. Other relation types are silently skipped.

If dbname is specified, it should be the name of a single database to check, and no other database selection options should be present. Otherwise, if any database selection options are present, all matching databases will be checked. If no such options are present, the default database will be checked. Database selection options include --all, --database and --exclude-database. They also include --relation, --exclude-relation, --table, --exclude-table, --index, and --exclude-index, but only when such options are used with a three-part pattern (e.g. mydb*.myschema*.myrel*). Finally, they include --schema and --exclude-schema when such options are used with a two-part pattern (e.g. mydb*.myschema*).

dbname can also be a connection string.

Options

The following command-line options control what is checked:

-a --all

Check all databases, except for any excluded via --exclude-database.

-d pattern --database=pattern

Check databases matching the specified pattern, except for any excluded by --exclude-database. This option can be specified more than once.

-D pattern --exclude-database=pattern

Exclude databases matching the given pattern. This option can be specified more than once.

-i pattern --index=pattern

Check indexes matching the specified pattern, unless they are otherwise excluded. This option can be specified more than once.

This is similar to the --relation option, except that it applies only to indexes, not to other relation types.

-I pattern --exclude-index=pattern

Exclude indexes matching the specified pattern. This option can be specified more than once.

This is similar to the --exclude-relation option, except that it applies only to indexes, not other relation types.

-r pattern --relation=pattern

Check relations matching the specified pattern, unless they are otherwise excluded. This option can be specified more than once.

Patterns may be unqualified, e.g. myrel*, or they may be schema-qualified, e.g. myschema*.myrel* or database-qualified and schema-qualified, e.g. mydb*.myschema*.myrel*. A database-qualified pattern will add matching databases to the list of databases to be checked.

-R pattern --exclude-relation=pattern

Exclude relations matching the specified pattern. This option can be specified more than once.

As with --relation, the pattern may be unqualified, schema-qualified, or database- and schema-qualified.

-s pattern --schema=pattern

Check tables and indexes in schemas matching the specified pattern, unless they are otherwise excluded. This option can be specified more than once.

To select only tables in schemas matching a particular pattern, consider using something like --table=SCHEMAPAT.* --no-dependent-indexes. To select only indexes, consider using something like --index=SCHEMAPAT.*.

A schema pattern may be database-qualified. For example, you may write --schema=mydb*.myschema* to select schemas matching myschema* in databases matching mydb*.

-S pattern --exclude-schema=pattern

Exclude tables and indexes in schemas matching the specified pattern. This option can be specified more than once.

As with --schema, the pattern may be database-qualified.

-t pattern --table=pattern

Check tables matching the specified pattern, unless they are otherwise excluded. This option can be specified more than once.

This is similar to the --relation option, except that it applies only to tables, materialized views, and sequences, not to indexes.

-T pattern --exclude-table=pattern

Exclude tables matching the specified pattern. This option can be specified more than once.

This is similar to the --exclude-relation option, except that it applies only to tables, materialized views, and sequences, not to indexes.

--no-dependent-indexes

By default, if a table is checked, any btree indexes of that table will also be checked, even if they are not explicitly selected by an option such as --index or --relation. This option suppresses that behavior.

--no-dependent-toast

By default, if a table is checked, its toast table, if any, will also be checked, even if it is not explicitly selected by an option such as --table or --relation. This option suppresses that behavior.

--no-strict-names

By default, if an argument to --database, --table, --index, or --relation matches no objects, it is a fatal error. This option downgrades that error to a warning.

The following command-line options control checking of tables:

--exclude-toast-pointers

By default, whenever a toast pointer is encountered in a table, a lookup is performed to ensure that it references apparently-valid entries in the toast table. These checks can be quite slow, and this option can be used to skip them.

--on-error-stop

After reporting all corruptions on the first page of a table where corruption is found, stop processing that table relation and move on to the next table or index.

Note that index checking always stops after the first corrupt page. This option only has meaning relative to table relations.

--skip=option

If all-frozen is given, table corruption checks will skip over pages in all tables that are marked as all frozen.

If all-visible is given, table corruption checks will skip over pages in all tables that are marked as all visible.

By default, no pages are skipped. This can be specified as none, but since this is the default, it need not be mentioned.

--startblock=block

Start checking at the specified block number. An error will occur if the table relation being checked has fewer than this number of blocks. This option does not apply to indexes, and is probably only useful when checking a single table relation. See --endblock for further caveats.

--endblock=block

End checking at the specified block number. An error will occur if the table relation being checked has fewer than this number of blocks. This option does not apply to indexes, and is probably only useful when checking a single table relation. If both a regular table and a toast table are checked, this option will apply to both, but higher-numbered toast blocks may still be accessed while validating toast pointers, unless that is suppressed using --exclude-toast-pointers.

The following command-line options control checking of B-tree indexes:

--heapallindexed

For each index checked, verify the presence of all heap tuples as index tuples in the index using amcheck’s heapallindexed option.

--parent-check

For each btree index checked, use amcheck’s bt_index_parent_check function, which performs additional checks of parent/child relationships during index checking.

The default is to use amcheck‘s bt_index_check function, but note that use of the --rootdescend option implicitly selects bt_index_parent_check.

--rootdescend

For each index checked, re-find tuples on the leaf level by performing a new search from the root page for each tuple using amcheck’s rootdescend option.

Use of this option implicitly also selects the --parent-check option.

This form of verification was originally written to help in the development of btree index features. It may be of limited use or even of no use in helping detect the kinds of corruption that occur in practice. It may also cause corruption checking to take considerably longer and consume considerably more resources on the server.

Warning

The extra checks performed against B-tree indexes when the --parent-check option or the --rootdescend option is specified require relatively strong relation-level locks. These checks are the only checks that will block concurrent data modification from INSERT, UPDATE, and DELETE commands.

The following command-line options control the connection to the server:

-h hostname --host=hostname

Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.

-p port --port=port

Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.

-U --username=username

User name to connect as.

-w --no-password

Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W --password

Force pg_amcheck to prompt for a password before connecting to a database.

This option is never essential, since pg_amcheck will automatically prompt for a password if the server demands password authentication. However, pg_amcheck will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

--maintenance-db=dbname

Specifies a database or connection string to be used to discover the list of databases to be checked. If neither --all nor any option including a database pattern is used, no such connection is required and this option does nothing. Otherwise, any connection string parameters other than the database name which are included in the value for this option will also be used when connecting to the databases being checked. If this option is omitted, the default is postgres or, if that fails, template1.

Other options are also available:

-e --echo

Echo to stdout all SQL sent to the server.

-j num --jobs=num

Use num concurrent connections to the server, or one per object to be checked, whichever is less.

The default is to use a single connection.

-P --progress

Show progress information. Progress information includes the number of relations for which checking has been completed, and the total size of those relations. It also includes the total number of relations that will eventually be checked, and the estimated size of those relations.

-v --verbose

Print more messages. In particular, this will print a message for each relation being checked, and will increase the level of detail shown for server errors.

-V --version

Print the pg_amcheck version and exit.

--install-missing --install-missing=schema

Install any missing extensions that are required to check the database(s). If not yet installed, each extension’s objects will be installed into the given schema, or if not specified into schema pg_catalog.

At present, the only required extension is amcheck.

-? --help

Show help about pg_amcheck command line arguments, and exit.

Notes

pg_amcheck is designed to work with PostgreSQL 14.0 and later.

Источник

Предлагаю ознакомиться с расшифровкой доклада начала 2019 года Алексея Лесовского — «Поиск и устранение проблем в Postgres с помощью pgCenter»

Время от времени при эксплуатации Postgres’а возникают проблемы, и чем быстрее найдены и устранены источники проблемы, тем благодарнее пользователи. pgCenter это набор CLI утилит которые является мощным средством для выявления и устранения проблем в режиме «здесь и сейчас». В этом докладе я расскажу как эффективно использовать pgCenter для поиска и устранения проблем, в каких направлениях осуществлять поиск и как реагировать на те или иные проблемы, в частности, как:

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

Всем привет, меня зовут Алексей Лесовский. Я работаю в компании Data Egret. Это консалтинговая компания. И я вам расскажу, как мы в нашей консалтинговой компании занимаемся поиском и устранением неисправностей в PostgreSQL.

Я расскажу о том, как с помощью консольной утилиты pgCenter можно хорошо, быстро и эффективно находить самые разные проблемы и переходить к их устранению.

Немного о себе. Я долгое время был системным администратором. Занимался Linux, виртуализацией, мониторингом. И в какой-то момент времени стал заниматься больше Postgres’ом. И работа с Postgres стала занимать большую часть времени. И так я стал PostgreSQL DBA. И сейчас уже работая в консалтинговой компании, я работаю с Postgres каждый день. И каждый день наши заказчики предоставляют нам самый разный материал для новых конференций.

Все общение с нашими заказчиками происходит в виде беседы в чатах. Это самые разные чаты: Slack, Telegram. Но наши заказчики часто обнаруживают какую-нибудь проблему у себя и пишут. Мы в свою очередь должны на это отреагировать.

На слайде всем широко известная диаграмма Брендана Грэгга, как находить различные проблемы, связанные с производительностью в Linix. Это довольно познавательная диаграмма. Она показывает, как устроен Linux и какие утилиты есть для нахождения проблем. По сути, можно обложиться всеми этими утилитами и смотреть, что происходит.

Но в любом случае мы увидим то, что у нас все замыкается на Postgres. Процессорное время потребляется Postgres. Дисковый ввод-вывод так же генерируется Postgres’ом. Всю память съел тоже Postgres. Мы будем видеть только один Postgres.

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

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

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

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

Изначально она была написана на С. Это консольная утилита, которая показывала статистику в TOP-подобном виде.

Через какое-то время я понял, что C мне не очень подходит. т.к. я не профессиональный программист. И я переписал ее на Golang, этот язык показался мне более простым, но при этом напоминал С. И мне на нем легче добавлять новые функции.

Go это компилируемый язык и чтобы использовать pgCenter его нужно скомпилировать. Но в репозитории уже есть пакеты и вам как пользователю не нужно компилировать программу самостоятельно. К репозиторию подключена система сборки, которая после каждого коммита, компилирует бинарник, собирает targz, deb- и rpm-пакеты и выкладывает их в релизы. Т.е. не нужно устанавливать какие-то пакеты, ставить make, GCC или Golang. Достаточно просто зайти в релизы, скопировать нужный пакет по ссылке, распаковать руками или установить через дефолтный пакетный менеджер и можно уже пользоваться.

Изначально весь pgCenter представлял собой именно просмотрщик статистики, который в top-режиме показывает текущие изменения статистики за последнюю секунду (интервал изменений настраивается).

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

По ходу разработки я постарался сохранить синтаксис команды PSQL. Если вы работаете с Postgres, вы знаете что запустив PSQL без аргументов и параметров можно подключиться к Postgres. С pgCenter тоже самое, достаточно запустить «pgcenter top» из под postgres пользователя и она начнет показывать вам какую-то статистику (по-умолчанию, текущая активность в БД).

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

Но можно даже не указывать хост, можно подключаться через UNIX-сокеты, т. к. go’шный драйвер, который используется под капотом pgCenter позволяет подключиться не только к сетевым сокетам, но и к UNIX.

Кроме того, тот же драйвер поддерживает переменные окружения libpq. Даже если у вас особенный случай, например, pg_stat_statements установлен в отдельной схеме и pgCenter не может его найти, то можно переопределить поведение через переменные окружения. Это тоже поддерживается.

И вот так выглядит внешний вид утилиты (pgcenter top). И в первый раз, когда ее запускаешь, это может немного напрячь. Это похоже, что это какой-то центр управления полетами. Много цифр, много букв, все это быстро меняется. Но это на самом деле не важно. Здесь важно помнить, что интерфейс pgCenter, а именно top-просмотрщика, состоит из трех частей.

Первая часть – системная информация. Эта информация находится в верхнем левом углу.

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

И третья часть – статистика из самих статистических представлений (views). Здесь информация из stat-представлений, которые есть в Postgres, в этой части отображаются изменения этой статистики.

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

Если информации слишком много, можно использовать фильтры. В качестве фильтров там применяются регулярные выражения (regexp). И вы можете отфильтровать выводимую инфоормацию по интересующим вас критериям. Например, показать запросы конкретного пользователя, базы или конкретно какие-то запросы: селекты или апдейты.

Далее в докладе я расскажу, с какими конкретно кейсами вы можете столкнуться у себя в работе и какие подходы можете применять используя pgCenter. И самый основной кейс – это проверка все ли в порядке с базой и нет ли там каких-то проблем.

И здесь мы все делаем, как по известному USE методу. Нам нужно определить использование ресурсов. Если ресурсы используются более-менее хорошо и прекрасно, то мы смотрим наличие ошибок.

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

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

И, соответственно, swap. Если в системе есть swap, а для базы данных он важен, то можно посмотреть еще и статистику по swap. Таким образом в части по системной статистике можно быстро определить – есть ли у нас проблема с утилизацией ресурсов.

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

Когда мы посмотрели нет ли у нас проблем с утилизацией ресурсов, мы можем идти к проверке – нет ли у нас каких-либо ошибок на уровне работы Postgres. Мы можем посмотреть uptime. Вообще, uptime в Postgres – не совсем честный, но тем не менее это лучше, чем совсем ничего. С аптаймом можно быстро определить, как давно у нас работает Postgres и не было ли у него незапланированных рестартов.

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

И, конечно, автовакуум. Я думаю, многие из вас знают, что такое автовакуум. С ним связано много интересных историй, поэтому про вакуум тоже можно посмотреть. Например, сколько воркеров запущено и посмотреть их длительность. И после этого можно как-то реагировать на эту информацию.

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

На этом слайде было переключение на статистику баз данных с помощью горячей клавиши ‘d’.

Говоря про ошибки, важно отметить pg_stat_database которая предоставляет некоторое количество информации об ошибках, тут нас интересует поле «rollbacks». Это поле не только про команду «ROLLBACK», но еще и про различные ошибки которые привели к отмене запроса/транзакции. Это могут быть ошибки нарушения ограничений (constraints), это могут быть ошибки синтаксиса и т. п. По этой статистике можно уже отследить, что происходит в базе.

И плюс в pg_stat_database есть еще информация по конфликатм репликации (conflicts) и взаимоблокировкам (deadlocks). По сути, это тоже виды ошибок, которые говорят о том, что в базе что-то идет не так.

ОК, мы запустили pgCenter. И за относительно короткое время мы смогли посмотреть много вещей, ради которых нам бы пришлось запустить несколько утилит. Во-первых, это:

top
vmstat
iostat
nicstat
pg_stat_activity
pg_stat_statements

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

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

Вот такой простой пример. Вам не обязательно все это рассматривать, важно отследить те места, которые используются в процессе оценки. Т. е. здесь CPU usage – 85 %. Это говорит о том, что у нас нагрузка на процессоры довольно-таки высокая. И нам нужно найти, кто же так активно использует процессора. Понятно, что это Postgres. Нам нужно заглянуть глубже в Postgres и посмотреть, какие типы запросов у нас больше всего потребляют процессорного времени.

Если мы посмотрим на вторую часть экрана, то мы увидим, что у нас 38 активных клиентов, которые что-то делают. При этом нужно посмотреть на соседние state: на waiting и на idle_xact. Waiting у нас 0, т. е. у нас клиенты не находятся в режиме ожидания и это хорошо. С другой стороны, у нас есть 20 idle транзакций. Соответственно, мы можем включить сортировку по длительности транзакций (xact_age) и посмотреть – сколько времени наши транзакции находятся в простое. И здесь видно, что простаивает всего одна транзакция. И ее время жизни – 15 секунд. Это не страшно, и в большинстве случаев это можно не считать криминалом.

(Примечание: На слайде переключились в pg_stat_statements. Чтобы переключится в pg_stat_statements необходимо нажать «x», там будут нужные столбцы. Второй вариант «shift + x» и в меню выбрать pg_stat_statements_timings. Если будет ошибка «pg_stat_statements not available on this database», то нужно установить расширение pg_stat_statements от юзера postgres в базу postgres: create extension pg_stat_statements;)

Но мы же ищем источник, кто у нас потребляет больше всего (процессорного) времени. Нам нужно оценить, какие запросы используют больше всего времени. Для этого мы используем pg_stat_statements. Это contrib. Он показывает нам статистику по запросам: сколько они выполнялись, сколько ресурсов потребляли. Этот contrib должен быть установлен в базе, чтобы брать с нее статистику. К сожалению, он выключен по-умолчанию. И одна из основных рекомендаций по настройке Postgres – включать pg_stat_statements.

Предположим, что он у нас стоит. Нам нужно посмотреть время, кто у нас тратить больше всего. Мы стрелочками переключаем сортировку. И видим те запросы, которые у нас тратили больше всего CPU с момента сброса статистики pg_stat_statements. Тут отражен примерно суточный разрез – за сутки конкретный тип запроса отработал 2 часа с лишним. Это запрос SELECT COUNT (*) FROM "game_competition_events". Т. е. уже имея на руках запрос, мы можем сходить в логи, взять параметры этого запроса и посмотреть, какой у него план, и попытаться с ним разобраться. Может быть, там нет какого-нибудь индекса, может быть, там запрос написан неоптимальный или еще что-то. Уже у нас есть конкретная информация о том, кто потребляет процессорное время.

Но здесь есть небольшая ловушка. Мы используем сортировку под total_time. А в total_time включается не только процессорное время, но еще и время, потраченное на операции блочного ввода/вывода: на чтение и на запись. Соответственно, нам желательно включить сортировку по полю «t_cpu_t». Оно нам более релевантно. Оно нам позволяет смотреть именно процессороемкие запросы.

Как я уже сказал, эта статистика показывает самые жадные до ресурсов запросы с момента сброса статистики. Если нам нужно смотреть запросы, которые отнимают процессорное время здесь и сейчас, то мы смотрим уже по полю «cpu_t», это, условно говоря, дельта. Мы берем snapshot статистики за прошлую секунду, за текущую секунду, считаем дельту и показываем. Здесь запрос уже совершенно другой. Это SELECT "courses_logs".* FROM course_logs. И здесь видно, что текущую секунду он съел уже 5 секунд процессорного времени. Это либо запрос, который использует параллельные воркеры, либо, возможно, он просто запускается слишком часто.

И если посмотреть на соседнюю колонку «calls», то там будет видно, что запрос выполняется один. Один запрос в секунду. Т. е. это запрос с параллельными воркерами.

Пока мы все это смотрели, мы могли использовать другие утилиты. Это Top и плюс нам нужно было заглянуть в pg_stat_activity и в pg_stat_statements. Но с помощью pgCenter это все в одном месте собрано и можно этим пользоваться.

Другой вариант – это нагрузка на ввод-вывод. Это другая противоположность, когда с процессорами у нас все в порядке, но диски слабые и нужно разобраться, кто утилизирует ввод-вывод.

Ситуация похожа на предыдущий случай. Мы смотрим на утилизацию процессоров и видим, что у нас утилизация процессоров на время ожидания блочного ввода-вывода довольно высокое – 27 %. Нам нужно найти те запросы, которые вызывают этот ввод-вывод.

Плюс мы можем еще обратить внимание на то, что многие клиенты с типом «background worker». Это явный показатель, что у нас параллелизм включен и запрос выполняется параллельно.

Посмотрим по соседним «wait_event». И тут видно, что эти клиенты находятся в ожидание ввода-вывода. Т. е. очень много времени тратится на чтение данных с диска.

И здесь нам уже понадобится статистика по блочному вводу-выводу. С помощью горячей клавиши ‘B’ мы включаем встроенный iostat. И он нам показывает утилизацию дисковых устройств. Здесь видно, что утилизация одного из устройств 99 %. Но здесь самое главное – это не попасть в ловушку, потому что устройство у нас NVME. И нужно уже смотреть не только на утилизацию, но и на latency.

Если посмотреть на latency, то latency для этого устройства будет составлять всего лишь 1 миллисекунду. И это вполне нормально.

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

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

Аналогичная колонка есть и для просмотра статистики за последнюю секунду. Это колонка «read_t». Мы можем менять сортировки и смотреть, какие запросы за весь интервал времени сожрали больше всего ввода-вывода, либо за последнюю секунду.

Важно отметить что статистика по ввод/выводу собирается только при включенном track_io_timing.

И уже имея текст запроса мы можем переходить к его поиску в логах, найти его параметры и узнать, что там долго там работает. Но pgCenter еще предоставляет такую штуку как queryid. Это такой идентификатор запроса. Но это не такой идентификатор, который предоставляется в pg_stat_statements. Он немного другой. Его можно использовать для построения отчетов. Т. е. pgCenter предоставляет такую функцию как построение отчетов по конкретной группе запросов. Также через горячие клавиши мы смотрим по queryid. И pgCenter предоставляет отчет.

Отчет состоит из трех частей:

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

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

И если рассматривать, что мы затронули под капотом, пока это все смотрели, то все это покрывается утилитами top, iostat и представлениями pg_stat_activity, pg_stat_statements. Плюс там есть еще несколько функций, которые приводят все это в понятный вид.

Но запросы клиентские – это не единственная вещь, которая позволяет генерировать ввод-вывод. И в Postgres есть еще всякие фоновые задачи, которые тоже могут создавать нагрузку на диск.

Это:

Checkpointer pocess.
WAL writer process.
Autovacuum workers.
Background workers.

На данный момент pgCenter показывает только прогресс по вакууму, по остальным пока информации нет, но тем не менее это уже хорошо.

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

И чаще всего это описывается ситуациями, когда клиент пишет в чат, что у него ничего не работает. Всё лежит и нужно что-то делать.

Здесь мы мельком посмотрели утилизацию ресурсов.

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

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

Нужно посмотреть на соседнее поле, которое показывает транзакции в режиме простоя (idle in transaction). И здесь мы видим, что их 6 штук. И нужно включить сортировку по времени работы транзакции.

Если посмотреть на отсортированное поле, то мы увидим, что у нас есть 10-минутная транзакция, которая ничего не делает в данный момент. Если мы посмотрим ниже, то есть еще куча транзакций, которые 7 минут находятся в ожидании. И они явно выстроились как раз в хвост за 10-минутной транзакцией.

Если посмотреть на wait_etype, wait_event этой транзакции, которая ничего не делает, то мы увидим, что она ждет как раз ожидания ввода со стороны клинского приложения (Client:Client Read). Приложение открыло транзакцию, что-то поделало, а потом ушло делать какую-то другую работу. И, возможно, где-то произошла ошибка, приложаени упало в том участке кода, но транзакция осталась незакрытой. Пришли другие транзакции и попытались обновить другие строки и прочитать данные, которые изменила эта транзакция, но попали в заблокированное состояние и теперь они все ждут завершения транзакции.

Самое просто решение – это отменить эту транзакцию. Есть две функции: pg_cancel_backend и pg_terminate_backend. Они позволяют отменить запрос, либо просто завершить работу этого backend. В pgCenter тоже есть эти функции. Можно с помощью горячих клавиш убивать как отдельный backend и запросы на основе pid, либо убивать их группами на основе маски.

Тем не менее под капотом здесь у нас:

Pg_stat_activity.
Pg_stat_statements.
Pg_cancel_backend ().
Pg_terminate_backend ().

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

Бывает долгая транзакция на таблице с очередью. Тот случай когда очередь реализована не отдельным брокером сообщений, а реализованы непосредственно в базе данных. У нас есть какая-то таблица. В нее вставляется много записей, также много строк обновляется и много строк удаляется (добавилось событие, изменился его статус, удалилось событие). Пришла какая-то долгая транзакция, которая поработала с этой таблицей, но также она перешла в состояние idle transaction и ничего не делает. И у нас также собрался длинный хвост из блокировок и все повисло — очередь перестала работать.

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

Миграции. Можно сделать ALTER TABLE, добавление колонки, например, с простановкой дефолтных значений. Это очень тяжелая операция. Ее, к счастью, исправили в 11-ой версии и начиная с нее проставление дефолтов при добавлении поля работает безболезненно. Но тем не менее у многих заказчиков стоят старые версии Postgres, которые работают по старому. И любой такой тяжелый ALTER может также собрать на себе хвост ждущих транзакций и остановить работу приложения.

И классика жанра – это CREATE INDEX без CONCURRENTLY, когда кто-то по незнанию, либо просто забыл, что запустил создание индекса. Создание индекса заблокировало таблицу и появился снова хвост из блокировок.

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

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

И pgCenter тоже поддерживает pg_stat_replication. И можно переключиться с помощью горячих клавиш, и посмотреть, что там происходит.

В данном случае у нас здесь 5 клиентов. Они все подключены и принимают журнал транзакций.

Если посмотреть на их имена, то можно будет понять, кто это такие и что они делают. У нас здесь 2 walreceiver, т. е. это конкретно 2 реплики.

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

Соответственно, pg_stat_replication предоставляет разную информацию, которая позволяет нам посчитать лаг в байтах и лаг в секундах. И здесь лаг у одной из реплик на уровне 1,5 GB. И replay_lag в секундах – 2 часа. На самом деле это нормальная реплика. Она просто настроена с отложенным восстановлением журнала транзакций. У нее выставлено восстановление на уровне 2-х часов. Она скачивает все журналы к себе и воспроизводит их с задержкой в 2 часа, т. е. это вполне нормальная ситуация.

Если мы посмотрим на других клиентов, то будет видно, что у нас есть 2 pg_basebackup и 1 pg_receivewal. Pg_basebackup – это резервное копирование которое также работает по протоколу репликации. Он также виден в pg_stat_replication. И pg_receivewal – это процесс, который принимает журналы транзакций и сохраняет для задач архивирования. Т. е. здесь, в принципе, никакой проблемы нет. Здесь нет каких-то криминальных реплик, которые нужно было бы расследовать.

Но тем не менее pg_stat_replication позволяет показывать лаг в нескольких единицах измерениях. Это байты. И самое интересное, что этих метрик здесь аж 5 штук. Это: pending, write, flush, replay, total_lag. Т. е. лаг репликации может быть разным.

Pending – это когда журнал транзакций сгенерировался, лежит на Мастере. И Мастер его еще не успел передать реплике.

Write – это когда передача журналов уже идет, но до реплики еще не дошла, т. е. она еще не успела записаться.

Flush – это когда успели записать уже на реплику, но не успели сбросить на надежное хранилище.

Replay – это когда сбросили на надежное хранилище. И осталось только проиграть.

Total_lag – это максимальная величина от момента генерации до момента проигрывания.

Соответственно, наблюдая лаг в этих местах, в этих контрольных точках, мы можем более-менее понять, где у нас проблема. Например, проблема на дисковой подсистеме Мастера; либо ошибки сети, которые снижают скорость передачи; либо это загруженная дисковая подсистема реплики, которая не успевает все это писать, синхронизировать с диском и воспроизводить.

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

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

Под капотом этой всей диагностики у нас:

pg_stat_replication.
pg_wal_lsn_diff().
pg_current_wal_lsn().
pg_last_commited_xact().

Я вам рассказал все эти кейсы, но за кадром есть еще много других вещей.

Например, в top можнос смотреть статистику по таблицам. Табличные статистики – это все Seq Scan, количество update, delete, insert, живые и мертвые строки.

Статистика по индексам. Можно посмотреть утилизацию индексов. Отыскивать неиспользуемые индексы и их заносить в черный список и потом удалять.

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

И, конечно, pg_stat_progress_vacuum появился в 9.6. Раньше, в плане статистики, вакуум был черным ящиком, было сложно понять, как долго работает, как скоро он закончится и сколько ему еще работы надо делать. И pg_stat_progress_vacuum – это способ заглянуть в этот черный ящик и понять что там происходит. Можно оценить, сколько ему там осталось доработать. Хотя, конечно, есть недостатки, есть претензии к нему. Но тем не менее лучше, чем ничего.

И есть вспомогательные, админские функции для самого администратора. Это просмотр логов, просмотр и изменение конфигурации, т. е. мы можем через горячие клавиши открыть postgresql.conf, что-то в нем поправить и потом горячей клавишей сделать reload. Это не самая правильная практика, конечно, но тем не менее возможность есть.

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

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

top-просмотрщик – это основная утилита, которая развивалась изначально в pgCenter. Но помимо top есть еще другие утилиты, которые тоже являются частью pgCenter. Это record и report.

Суть в том, что мы делаем мгновенные снимки статистики и сохраняем их в файле. Мы можем запускать сбор с нужным интервалом и эти снимки будут сохраняться. А потом с помощью report строить отчеты, аналогичные тому, что показывает Top. В некоторых ситуациях это бывает полезно — например отсутствие полноценого мониторинга.

Я использую это для своих микро-тестов. Когда мне нужно что-то потестировать, я запускаю record. Раз в секунду он там все записывает и мне не нужно ставить никаких мониторинговых агентов. Я могу это все так быстро на коленке посмотреть.

Плюс недавно я добавил сэмплер wait_event’ов, можно брать свои долгие запросы и смотреть, на каких участках запрос тратит свое время на ожидание.

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

Второй пример: это VACUUM FULL. Здесь видно, что большую часть времени VACUUM FULL работает с дисковым IO и читает данные, а работает он всего 12 %. Вот эта штука довольно полезная, когда есть любопытство попрофилировать свои долгие запросы и посмотреть, чем они занимаются и в каких местах проводят время в ожидании.

Вопросы

Спасибо за утилиту! Лично я ею пользуюсь. Она мне нравится. Я часто использую ее с ноутбука. И когда я смотрю, например, pg_stat_statements, у меня колонка query с текстом не влезает. Есть ли какая-то возможность поменять порядок столбцов или какие-то столбцы отключить на время?

К сожалению, такой функции нет. Архитектурно программа так устроена, что эта фичу тяжело запилить, но можно, наверное, что-то придумать там, переписать и такая функция появится. Как минимум, не перестановку колонок, а отключение-включение по желанию. Но отсюда вылезает второй вопрос. Наверняка кто-то захочет сохранить отображение этих колонок и при последующем запуске показать. Т. е. появится необходимость конфига, который нужно поддерживать, хранить где-то. Это такая задачка, которая собирает еще другие задачки, чтобы ее реализовать. Я думаю, что это можно сделать, но пока этого нет.

И второй комментарий из той же оперы. Когда режим базы данных, то у вас количество rollbacks. Иногда хотелось бы отличать rollbacks, которые были по команде от тех rollbacks, которые не по команде.

Это, к сожалению, невозможно, потому что сам интерфейс pg_stat_database не позволяет такого. Он показывает только rollbacks и все.

Там можно из pg_stat_statements считать rollbacks, вычитать.

Можно, если заморочиться. Можно расширить запрос. Мы делаем `SELECT FROM pg_stat_database JNOIN pg_stat_statements` под запрос к pg_stat_statements, где мы считаем rollbacks и плюс арифметика, которая это все высчитывает. Теоретически можно. Но нужно посмотреть, как быстро будет работать этот запрос и не займет ли выполнение больше 10-20 миллисекунд.

Алексей, спасибо за доклад! С какой минимальной версией Postgres ваша утилита работает?

Я ее тестировал с версии 9.0-9.1, когда еще на C писал. Если какие-то запросы, особенно связанные с репликацией, не работают, то она пишет небольшую ошибку и есть возможность переключится на другой скрин, на другую статистику. Go версию тестировал, начиная с 9.4. Потому что в 9.4 появился функционал, когда мы берем SELECT… where filter. Это такой синтаксис интересный. В старых версиях (9.3) его нет. А у меня часть запросов как раз используют этот синтаксис и в старых версиях это работать не будет. Либо это будет работать, но будет показывать нули там, где эта статистика используется. Но я стараюсь на всех версия тестировать, проверять, чтобы, как минимум, ошибок не было.

Алексей, спасибо за утилиту! И спасибо за то, что интерфейс там от TOP. За это двойное спасибо.

Более того, я старался горячие клавиши делать, похожими на другие утилиты. Например, кнопка фильтра – это слеш. Кто знаком с less (пейджер такой), то слеш – это поле ввода, чтобы набрать шаблон для поиска.

У меня вопрос по поводу сортировки. Там так же как в TOP подсветка поля идет сортируемого?

Да. У меня на скриншотах это не видно, я, видимо, упустил этот момент. На некоторых скриншотах видно, на некоторых не видно. Да, поле подсвечивается. Когда вы стрелками перемещаетесь, вы видите, что у вас сортировка меняется и имя в колонке подсвечивается.

Спасибо за утилиту! Сегодня в первый раз о ней узнал. Оказывается, столько много возможностей. Вы размышляете, что вот у меня 85% CPU usage, давайте проанализирую, что сейчас происходит. Для этого пойду и обращусь к временным снимкам pg_stat_statements. Но там же архив находится. А нас интересует, что сейчас происходит.

Не архив. В нагрузке на CPU есть два поля. Первое – t_all_t. Это сколько времени намотал запрос со времени сброса статистики pg_stat_statements. Условно, у нас суточный срез. Если мы раз в сутки сбрасываем статистику, то мы получим статистику за текущий момент от начала суток. Плюс есть разбивка t с подчеркиванием и они уже здесь заканчиваются. Т. е. у нас есть аналогичные поля без префикса «t». Они как раз нам показывают статистику за текущую секунду. Т. е. мы можем смотреть, сколько процессорного времени потрачено запросом прямо сейчас.

Это понятно. Но если сейчас происходит то, что еще нет в pg_stat_statements, то как мы это проанализируем?

Мы каждую секунду выходим к pg_stat_statements и берем снапшот статистики прямо в real time.

Вопрос был в том, что в pg_stat_statements залетит уже после, а цифра 85 CPU usage – она сейчас.

Да, расхождение статистики будет. Но вы все равно можете смотреть текущую статистику, которую вам pg_stat_statements показывает. Если вы видите, что у вас использование процессора упало, то вы уже не увидите тот срез статистики, который был 10 секунд назад, когда использование процессоров было высокое. Тут нет такой интроспекции на 10 секунд назад – запомнить и отмотать, как это сделано, например, в atop. Вы на atop намекаете?

Примерно, да. Тут некое будущее и прошлое pg_stat_statements, а CPU, которая сейчас, нужно потом проанализировать, что залетит в pg_stat_statements.

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

В связи с этим же вопрос. pgCenter умеет как atop сбрасывать статистику в текстовом виде, чтобы потом можно было запихнуть в Grafana?

Есть функции pgCenter report и pgCenter record, т. е. можно снапшоты статистики сбрасывать в текстовые файлы. Единственное, что там нет интерфейса, как по стрелочкам переключаться и смотреть. Т. е., условно говоря, с помощью pgCenter report запросить нужную статистику, например, по pg_stat_database и он прочитаем там все файлы накопленной статистики и как sar покажет. Я вдохновлялся больше им. Нет такого как у atop, когда можно стрелочками работать.

Алексей, большое спасибо! В отличие от всей известной базы Х, в Postgres, к сожалению, нет, кумулятивных статистик ожиданий. Вы сделали профайлер, который показывает разброску по ожиданиям. А с какой частой вы их опрашиваете? Какая там детализация?

Дефолтный интервал в 10 миллисекунд. А через флажок «-f», можно указывать частоту детализаций. Понятно, что высокая частота детализации, например, в 10 миллисекунд тоже создает нагрузку на систему. Но учитывая, что pg_stat_activity в памяти, то запросы там довольно легковесные. Они доли миллисекунд занимают. Но если такая частота напрягает, можно менять. Поставить, например, раз в 50 миллисекунд. Я замерял, как отражается это на конечной статистике, там погрешность есть на уровне 1-2 %. Т. е. если просуммировать все столбики, то мы увидим, что у нас куда-то 0,5-2 % потерялось.

Там в конце суммарная статистика есть. Можно видеть, что что-то потерялось.

Да, там видно будет. Да, даже на нашем примере мы видим, что 0,04 % не учли. Но, я думаю, это не критично. Это не какой-то инструмент для хардкор аналитики.

Но лучше все равно ничего нет.

Интересно просто посмотреть, что там у нас происходит.

Алексей, спасибо за доклад! Вдогонку вопрос по профайлеру. Вот этот wait_event Running – это просто отсутствующий?

Да.

Я его воспринимаю как ожидание на CPU.

Да, именно так. Т. е. когда мы заметили, что wait_events у этого PID нет, то мы считаем, что backend делает какую-то полезную работу, прямо крутится на процессоре, что-то высчитывает. И мы закидываем в Running, типа он работает, т. е. он не находится в ожидании.

Но все же это ближе к CPU?

Да, это ближе к CPU, т. е. запрос делает какую-то работу. Это не ожидание.

Привет! Спасибо за доклад! Насчет пакетирования есть какие-то планы, например, Ubuntu?

Когда она была написана на C, то все майнтейнеры были радостные. Говорили, что круто, сейчас тебе пакетов насобираем. И в официальном репозитории PDGD были пакеты собраны для Ubuntu. Я на Launchpad собирал пакеты, но у них какая-то странная система сборки. Бинарник иногда сегфолтится. И я не мог понять, почему так. А сейчас на Go у меня просто Мастер-ветка, dev-ветка. В Мастере она релизы отсчитывает. И когда я делаю коммит с релизом, то travis-ci не только делает build, он еще делает build бинарника и выкатывает его в раздел релизов. Т. е. если посмотреть в Realeses, то туда релизы будут сваливаться. Вам остается только взять wget, сходить по ссылке, забрать и распаковать tar’ом архив, и можно будет пользоваться.

Есть проект Goreleaser, который позволяет автоматизировать это. И можно собирать пакеты.

Круто, я с GO не очень знаком. Я еще раз повторяю, я не профессиональный программист. Я не знаю, что такое SOLID. И то, что вы говорите, что Goreleaser есть, это интересная штука, я посмотрю, что это такое. Раньше C’шные исходники у меня собирались, и я был счастлив. А сейчас мне приходится всем говорить, что есть ссылка на Realeses. Спасибо за совет!

Updated. Goreleaser успешно добавлен, большое спасибо за идею!

Алексей, вопрос по поводу queryid. Мы там видим queryid. У вас получается, что там поле немножко урезано и мы хвост не видим. Мы можем его полностью увидеть?

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

Ясно, это важный момент, потому что queryid – это четкий адрес.

Да, изначально ширина колонок прыгала и это раздражало. Я в dev-ветке это поправил, а в Мастер-ветке этого пока нет. В середине февраля я хочу выпустить Event Profiler. И как раз фиксированная ширина колонок будет.

Замечательно.

Да, через стрелки можно регулировать ширину.

Спасибо, Алексей!

Спасибо большое вам!

Видео:

Источник

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption?

In the Microsoft SQL Server world there’s a command that you can execute DBCC CHECKDB that will tell you if there’re issues. Here’s a link if your intrested in learning more on the command. DBCC CHECKDB (Transact-SQL)

I’m a paranoid database integrity minded person (which anyone who works with the database in a DBA type role should be) and this type of stuff makes it hard for me to sleep well at night. A utility like this is a must! Searches on google have found a few attempts at tools like this and in my opinion unless it’s an official accepted tool by the PostgreSQL project I won’t trust it for something this important.

Here are some links to people asking similar questions with what I consider no real definitive answer. And in my opinion shows that PostgreSQL needs to have some tools in place that Oracle and Microsoft SQL Server seem to have.

The first link is the most interesting I have found on this subject. I think a comment on the article that probably sums it up states: «Postgres is pretty lame when it comes to identifying database corruption and repairing it. The only way to detect it is by dumping the database or select * from every table in the database.»

How PostgreSQL protects against partial page writes and data corruption

Checking data and index file corruption — Dev Shed

Help: my table is corrupt!

PostgreSQL: Corrupt primary key, inconsistent table

I believe there is a chance 9.3 might have some corruption checking features. It appears there may be hope to having page files check summed if one chooses. So things are looking bright if you consider using ZFS and/or a future version of Postgres with page check summing.
https://commitfest.postgresql.org/action/patch_view?id=759

UPDATE: 14-JAN-2012 — Seems like using a file system based on ZFS can detect corruption by check summing each block of data. I will have to look into this further and see if this is a work around to allow one to sleep well at night knowing their database data is not silently going corrupt.

UPDATE: 17-JAN-2012 — How to find what files are corrupt with ZFS. http://docs.oracle.com/cd/E18752_01/html/819-5461/gbbwl.html#gbcuz

UPDATE:14-APR-2014 9.3 did get data checksums. https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.3

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption?

How PostgreSQL protects against partial page writes and data corruption

Checking data and index file corruption — Dev Shed

Help: my table is corrupt!

PostgreSQL: Corrupt primary key, inconsistent table

UPDATE: 17-JAN-2012 — How to find what files are corrupt with ZFS. http://docs.oracle.com/cd/E18752_01/html/819-5461/gbbwl.html#gbcuz

UPDATE:14-APR-2014 9.3 did get data checksums. https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.3

Оглавление

1 Параметры
- 1.1 Databases
- 1.2 CheckCommands
- 1.3 PhysicalOnly
- 1.4 NoIndex
- 1.5 ExtendedLogicalChecks
- 1.6 TabLock
- 1.7 FileGroups
- 1.8 Objects
- 1.9 MaxDOP
- 1.10 AvailabilityGroups
- 1.11 AvailabilityGroupReplicas
- 1.12 Updateability
- 1.13 LockTimeout
- 1.14 LogToTable
- 1.15 Execute
2 Примеры использования вложенной процедуры
- - 2.0.1 A. Проверка целостности во всех пользовательских базах
  - 2.0.2 B. Проверка физической целостности данных во всех пользовательских базах
  - 2.0.3 C. Проверка целостности данных во всех пользовательских базах, с использованием опции исключающей проверку не кластеризованных индексов
  - 2.0.4 D. Проверка целостности данных во всех пользовательских базах, с использованием опции упрощенной проверки логической структуры
  - 2.0.5 E. Проверка целостности данных в файловой группе PRIMARY базы данных AdventureWorks
  - 2.0.6 F. Проверка целостности данных во всех файловых группах, за исключением файловой группы PRIMARY базы данных AdventureWorks
  - 2.0.7 G. Проверка целостности данных в таблице Production.Product базы данных AdventureWorks
  - 2.0.8 H. Проверка целостности данных всех таблиц за исключение таблицы Production.Product базы данных AdventureWorks
  - 2.0.9 I. Проверка дискового места во всех пользовательских базах
3 Выполнение

Процедура от Ola Hallengren — DatabaseIntegrityCheck, позволяет проверить базу, файловую группу или таблицу. Если используется полноценный SQL Server, то можно использовать его в Maintains Paln. Если SQL Express, то можно использовать данную процедуру в планировщике, так как агент-SQL в этой редакции отсутствует. Проверка необходима перед архивацией базы. Нет смысла архивировать разрушенную базу!!! поэтому перед архивацией необходим проверить целостность данных. В качестве параметров можно указать список баз данных, время блокировки, объекты проверки и некоторые другие параметры. Данную процедуру удобно использовать при большом количестве баз данных или при обслуживании баз разработчиков, которые постоянно плодят новые ветки и заливают в новые базы. Полный список параметров и примеры использования ниже

Параметры

В качестве аргументов, отвечающих за выбор объектов проверки можно передать SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES и AVAILABILITY_GROUP_DATABASES, если поддерживается. Знак (-) используется для исключения базы, а знак процента (%) выполняет роль символа подстановки. Все используемые параметры могут быть разделены занятой (,)

Databases

Переменная	Описание
SYSTEM_DATABASES	Все системные базы (master, msdb, and model)
USER_DATABASES	Все пользовательские базы
ALL_DATABASES	Все базы
AVAILABILITY_GROUP_DATABASES	Все базы и группы доступности
USER_DATABASES, -AVAILABILITY_GROUP_DATABASES	Все пользовательские базы, исключая группы доступности
Db1	Ваза Db1
Db1, Db2	Базы Db1 и Db2
USER_DATABASES, -Db1	Все пользовательские базы, за исключением Db1
%Db%	Все базы, имеющие в названии Db
%Db%, -Db1	Все базы имеющие в названии Db, за исключением базы Db1
ALL_DATABASES, -%Db%	Все базы, за исключением тех, которые имеют в названии Db

CheckCommands

Использование данного параметра может ускорить скорость проверки путем исключения не нужных объектов или разделить задание на два этапа.

Переменная	Описание
CHECKDB	Проверяет логическую и физическую целостность всех объектов. По умолчанию
CHECKFILEGROUP	Проверяет распределение и структуру целостности всех таблиц и индексированных представлений в файловой группе
CHECKTABLE	Проверяет целостность всех страниц и структур, составляющих таблицу или индексированное представление
CHECKALLOC	Проверяет согласованность структур выделения места на диске
CHECKCATALOG	Проверяет согласованность каталогов в указанной базе данных
CHECKALLOC,CHECKCATALOG	Совмещает два параметра — проверку таблиц и представлений
CHECKFILEGROUP,CHECKCATALOG	Проверяет представления и таблицы для файловых групп
CHECKALLOC,CHECKTABLE,CHECKCATALOG	Проверяет параметры свободного места для таблиц и индексов, а также согласованность каталогов

При составлении команд использовалось описание для SQL Server: DBCC CHECKDB проверка баз данных, DBCC CHECKFILEGROUP проверка фаловых групп, DBCC CHECKTABLE проверка таблиц и индексных представлений, DBCC CHECKALLOC проверка свободного места в таблицах и DBCC CHECKCATALOG проверка согласованности каталогов.

PhysicalOnly

Ограничиться только физической проверкой структуры базы

Переменная	Описание
Y	Ограничиться только физической проверкой структуры базы
N	Не ограничиться только физической проверкой структуры базы. По умолчанию

Параметр PHYSICAL_ONLY использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP и DBCC CHECKTABLE

NoIndex

Не проверять не кластеризированные индексы

Переменная	Описание
Y	Не проверять
N	Проверять. По умолчанию

Параметр NoIndex использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE и DBCC CHECKALLOC

ExtendedLogicalChecks

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

Переменная	Описание
Y	Провести простую проверку
N	Не проводить простую проверку. По умолчанию

Параметр ExtendedLogicalChecks использует опции следующих команд EXTENDED_LOGICAL_CHECKS и команды SQL Server DBCC CHECKDB

Нельзя комбинировать параметры PhysicalOnly и ExtendedLogicalChecks.

TabLock

Позволяет использовать внешний снапшот

Переменная	Описание
Y	Использовать блокировку, для проверки
N	Использовать снапшот. По умолчанию

Параметр TabLock использует опции следующих команд DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE и DBCC CHECKALLOC

FileGroups

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

Переменная	Описание
ALL_FILEGROUPS	Все файловые группы
Db1.FileGroup1	Файловая группа FileGroup1 в базе Db1
Db1.FileGroup1, Db2.FileGroup2	Файловая группа FileGroup1 в базе Db1 и фаловая группа FileGroup2 в базе Db2
ALL_FILEGROUPS, -Db1.FileGroup1	Все файловые группы, за исключением FileGroup1 в базе Db1
Db1.%FileGroup%	Все файловые группы в базе Db1 имеющие в своем имени “FileGroup”

Используются только специфические опции CHECKFILEGROUPS.

Objects

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

Переменная	Описание
ALL_OBJECTS	Все объекты
Db1.Schema1.Tbl1	Объект Schema1.Tbl1 в базе Db1
Db1.Schema1.Object1, Db2.Schema2.Object2	Объект Schema1.Tbl1 в базе Db1 и объект Schema2.Tbl2 б базе данных Db2
ALL_OBJECTS, -Db1.Schema1.Object1	Все объекты, кроме Schema1.Object1 в базе Db1
Db1.Schema1.%	Все объекты в схеме Schema1 находящейся в базе Db1

Данный параметр можно использовать только совместно с параметром CHECKTABLE

MaxDOP

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

Данный параметр может быть использован совместно с параметрами DBCC CHECKDB, DBCC CHECKFILEGROUP и DBCC CHECKTABLE

AvailabilityGroups

выбор групп доступности. Параметр ALL_AVAILABILITY_GROUPS разрешает использование маски. Знак минус (-) позволяет исключить файловую группу, а знак процента (%) позволяет задать маску. Все операторы могут быть объединены запятой (,)

Переменная	Описание
ALL_AVAILABILITY_GROUPS	Все группы доступности
AG1	Группа доступности AG1
AG1, AG2	Группа доступности AG1 и AG1
ALL_AVAILABILITY_GROUPS, -AG1	Все группы доступности, за исключением группы AG1
%AG%	Все группы доступности содержащие в своем имени “AG”
%AG%, -AG1	Все группы доступности содержащие в своем имени “AG” за исключением группы AG1
ALL_AVAILABILITY_GROUPS, -%AG%	Все группы доступности не содержащие в своем имени “AG”

AvailabilityGroupReplicas

Выбор реплики для необходимости проверки.

Переменная	Описание
ALL	Проверяжтся все реплики. По умолчанию
PRIMARY	Проверяется только первичная реплика
SECONDARY	Проверяется только вторичная реплика

Updateability

Задает режим для базы данны READ_ONLY/READ_WRITE

Переменная	Описание
ALL	READ_ONLY и READ_WRITE — база данных. До умолчанию
READ_ONLY	READ_ONLY — только чтение
READ_WRITE	READ_WRITE — чтение запись

Переменная READ_ONLY в sys.databases используется если база в режиме READ_ONLY или READ_WRITE (Трудности перевода, пока не разбирался с данной переменной)

LockTimeout

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

Параметр LockTimeout в хранимой процедуре IndexOptimize использует SET LOCK_TIMEOUT в терминологии SQL Server.

LogToTable

Логирование команд в таблицу dbo.CommandLog

Переменная	Описание
Y	Логировать команды в таблицу
N	Не логировать команды. По умолчанию

Execute

Выполнение команд. По умолчанию команды отправляются на выполнение. Если установлено значение N, то команды будут выведены без выполнения.

Переменная	Описание
Y	Выполнять команды. По умолчанию
N	Только вывеси команды

Примеры использования вложенной процедуры

A. Проверка целостности во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’

B. Проверка физической целостности данных во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@PhysicalOnly = ‘Y’

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

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@NoIndex = ‘Y’

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

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@ExtendedLogicalChecks = ‘Y’

E. Проверка целостности данных в файловой группе PRIMARY базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘AdventureWorks’,
@CheckCommands = ‘CHECKFILEGROUP’,
@FileGroups = ‘AdventureWorks.PRIMARY’

F. Проверка целостности данных во всех файловых группах, за исключением файловой группы PRIMARY базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKFILEGROUP’,
@FileGroups = ‘ALL_FILEGROUPS, -AdventureWorks.PRIMARY’

G. Проверка целостности данных в таблице Production.Product базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘AdventureWorks’,
@CheckCommands = ‘CHECKTABLE’,
@Objects = ‘AdventureWorks.Production.Product’

H. Проверка целостности данных всех таблиц за исключение таблицы Production.Product базы данных AdventureWorks

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKTABLE’,
@Objects = ‘ALL_OBJECTS, -AdventureWorks.Production.Product’

I. Проверка дискового места во всех пользовательских базах

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKALLOC’

Выполнение

Для выполнения хранимой процедуры, необходимо использовать sqlcmd и опцию -b. Вызов процедуры происходит по имени

sqlcmd —E —S $(ESCAPE_SQUOTE(SRVR)) —d master —Q «EXECUTE dbo.DatabaseIntegrityCheck @Databases = ‘USER_DATABASES’» —b

0
0
голоса

Рейтинг статьи

Загрузка…

Asked
12 years, 5 months ago

Viewed
28k times

Is there a way to check PostgreSQL database(s) integrity and consistency? I know about SQL Server DBCC CHECKDB and wonder if there is something similar to PostgreSQL.

asked Aug 29, 2010 at 15:46

In 12 version of pgsql, there is pg_checksums.

answered Nov 26, 2019 at 13:16

What do you really want to achieve?

The database itself guarantees its integrity. You do not need tools to fiddle with.

answered Aug 29, 2010 at 18:08

cstamascstamas

6,64724 silver badges42 bronze badges

answered Aug 29, 2010 at 20:26

alerootaleroot

3,1705 gold badges28 silver badges37 bronze badges

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

Я работаю полу-DevOps инженером в крупной IT-компании. Наша компания занимается разработкой программного обеспечения для высоконагруженных сервисов, я же отвечаю за работоспособность, сопровождение и деплой. Передо мной поставили стандартную задачу: обновить приложение на одном сервере. Приложение написано на Django, во время обновления выполняются миграции (изменение структуры базы данных), и перед этим процессом мы снимаем полный дамп базы данных через стандартную программу pg_dump на всякий случай.

Во время снятия дампа возникла непредвиденная ошибка (версия Postgres – 9.5):
pg_dump: Oumping the contents of table “ws_log_smevlog” failed: PQgetResult() failed. pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989 pg_dump: The command was: COPY public.ws_log_smevlog [...] pg_dunp: [parallel archtver] a worker process dled unexpectedly

Ошибка «invalid page in block» говорит о проблемах на уровне файловой системы, что очень нехорошо. На различных форумах предлагали сделать FULL VACUUM с опцией zero_damaged_pages для решения данной проблемы. Что же, попрробеум…

Подготовка к восстановлению

ВНИМАНИЕ! Обязательно сделайте резервную копию Postgres перед любой попыткой восстановить базу данных. Если у вас виртуальная машина, остановите базу данных и сделайте снепшот. Если нет возможности сделать снепшот, остановите базу и скопируйте содержимое каталога Postgres (включая wal-файлы) в надёжное место. Главное в нашем деле – не сделать хуже. Прочтите это.

Поскольку в целом база у меня работала, я ограничился обычным дампом базы данных, но исключил таблицу с повреждёнными данными (опция -T, —exclude-table=TABLE в pg_dump).
Сервер был физическим, снять снепшот было невозможно. Бекап снят, двигаемся дальше.

Проверка файловой системы

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

В моём случае файловая система с базой данных была примонтирована в «/srv» и тип был ext4.
Останавливаем базу данных: systemctl stop postgresql@9.5-main.service и проверяем, что файловая система никем не используется и её можно отмонтировать с помощью команды lsof:
lsof +D /srv

Мне пришлось ещё остановить базу данных redis, так как она тоже исползовала «/srv». Далее я отмонтировал /srv (umount).

Проверка файловой системы была выполнена с помощью утилиты e2fsck с ключиком -f (Force checking even if filesystem is marked clean):

Далее с помощью утилиты dumpe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep checked) можно убедиться, что проверка действительно была произведена:

e2fsck говорит, что проблем на уровне файловой системы ext4 не найдено, а это значит, что можно продолжать попытки восстановить базу данных, а точнее вернуться к vacuum full (само собой, необходимо примонтирвоать файловую систему обратно и запустить базу данных).

Если у вас сервер физический, то обязательно проверьте состояние дисков (через smartctl -a /dev/XXX) либо RAID-контроллера, чтобы убедиться, что проблема не на аппаратном уровне. В моём случае RAID оказался «железный», поэтому я попросил местного админа проверить состояние RAID (сервер был в нескольких сотнях километров от меня). Он сказал, что ошибок нет, а это значит, что мы точно можем начать восстановление.

Попытка 1: zero_damaged_pages

Подключаемся к базе через psql аккаунтом, обладающим правами суперпользователя. Нам нужен именно суперпользователь, т.к. опцию zero_damaged_pages может менять только он. В моём случае это postgres:
psql -h 127.0.0.1 -U postgres -s [database_name]

Опция zero_damaged_pages нужна для того, чтобы проигнорировать ошибки чтения (с сайта postgrespro):

При выявлении повреждённого заголовка страницы Postgres Pro обычно сообщает об ошибке и прерывает текущую транзакцию. Если параметр zero_damaged_pages включён, вместо этого система выдаёт предупреждение, обнуляет повреждённую страницу в памяти и продолжает обработку. Это поведение разрушает данные, а именно все строки в повреждённой странице.

Включаем опцию и пробуем делать full vacuum таблицы:
VACUUM FULL VERBOSE

К сожалению, неудача.

Мы столкнулись с аналогичной ошибкой:
INFO: vacuuming "“public.ws_log_smevlog” WARNING: invalid page in block 4123007 of relation base/16400/21396989; zeroing out page ERROR: unexpected chunk number 573 (expected 565) for toast value 21648541 in pg_toast_106070

pg_toast – механизм хранения «длинных данных» в Poetgres, если они не помещаются в одну страницу (по умолчанию 8кб).

Попытка 2: reindex

Первый совет из гугла не помог. После нескольких минут поиска я нашёл второй совет – сделать reindex повреждённой таблицы. Этот совет я встречал во многих местах, но он не внушал доверия. Сделаем reindex:
reindex table ws_log_smevlog

reindex завершился без проблем.

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

Попытка 3: SELECT, LIMIT, OFFSET

В статье выше предлагали посмотреть таблицу построчно и удалить проблемные данные. Для начала необходимо было просмотреть все строки:
for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; done

В моём случае таблица содержала 1 628 991 строк! По-хорошему необходимо было позаботиться о партициирвоании данных, но это тема для отдельного обсуждения. Была суббота, я запустил вот эту команду в tmux и пошёл спать:
for ((i=0; i<1628991; i++ )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog LIMIT 1 offset $i" >/dev/null || echo $i; done

К утру я решил проверить, как обстоят дела. К моему удивлению, я обнаружил, что за 20 часов было просканировано только 2% данных! Ждать 50 дней я не хотел. Очередной полный провал.

Но я не стал сдаваться. Мне стало интересно, почему же сканирование шло так долго. Из документации (опять на postgrespro) я узнал:

OFFSET указывает пропустить указанное число строк, прежде чем начать выдавать строки.
Если указано и OFFSET, и LIMIT, сначала система пропускает OFFSET строк, а затем начинает подсчитывать строки для ограничения LIMIT.

Применяя LIMIT, важно использовать также предложение ORDER BY, чтобы строки результата выдавались в определённом порядке. Иначе будут возвращаться непредсказуемые подмножества строк.

Очевидно, что вышенаписанная команда была ошибочной: во-первых, не было order by, результат мог получиться ошибочным. Во-вторых, Postgres сначала должен был просканировать и пропустить OFFSET-строк, и с возрастанием OFFSET производительность снижалась бы ещё сильнее.

Попытка 4: снять дамп в текстовом виде

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

Но для начала, ознакомимся со структурой таблицы ws_log_smevlog:

В нашем случае у нас есть столбец «id», который содержал уникальный идентификатор (счётчик) строки. План был такой:
1) Начинаем снимать дамп в текстовом виде (в виде sql-команд)
2) В определённый момент времени снятия дампа бы прервалось из-за ошибки, но тектовый файл всё равно сохранился бы на диске
3) Смотрим конец текстового файла, тем самым мы находим идентификатор (id) последней строки, которая снялась успешно

Я начал снимать дамп в текстовом виде:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dump

Снятия дампа, как и ожидалось, прервался с той же самой ошибкой:
pg_dump: Error message from server: ERROR: invalid page in block 4123007 of relatton base/16490/21396989

Далее через tail я просмотрел конец дампа (tail -5 ./my_dump.dump) обнаружил, что дамп прервался на строке с id 186 525. «Значит, проблема в строке с id 186 526, она битая, её и надо удалить!» – подумал я. Но, сделав запрос в базу данных:
«select * from ws_log_smevlog where id=186529» обнаружилось, что с этой строкой всё нормально… Строки с индексами 186 530 — 186 540 тоже работали без проблем. Очередная «гениальная идея» провалилась. Позже я понял, почему так произошло: при удаленииизменении данных из таблицы они не удаляются физически, а помечаются как «мёртвые кортежи», далее приходит autuvacuum и помечает эти строки удалёнными и разрешает использовать эти строки повторно. Для понимая, если данные в таблице меняются и включён autovacuum, то они не хранятся последовательно.

Попытка 5: SELECT, FROM, WHERE id=

Неудачи делают нас сильнее. Не стоит никогда сдаваться, нужно идти до конца и верить в себя и свои возможности. Поэтому я решил попробовать ешё один вариант: просто просмотреть все записи в базе данных по одному. Зная структуру моей таблицы (см. выше), у нас есть поле id, которое является уникальным (первичным ключом). В таблице у нас 1 628 991 строк и id идут по порядку, а это значит, что мы можем просто перербрать их по одному:
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done

Если кто не понимает, команда работает следующим образом: просматривает построчно таблицу и отправляет stdout в /dev/null, но если команда SELECT проваливается, то выводится текст ошибки (stderr отправляется в консоль) и выводится строка, содержащая ошибку (благодаря ||, которая означает, что у select возникли проблемы (код возврата команды не 0)).

Мне повезло, у меня были созданы индексы по полю id:

А это значит, что нахождение строки с нужным id не должен занимать много времени. В теории должно сработать. Что же, запускаем команду в tmux и идём спать.

К утру я обнаружил, что просмотрено около 90 000 записей, что составляет чуть более 5%. Отличный результат, если сравнивать с предыдущим способом (2%)! Но ждать 20 дней не хотелось…

Попытка 6: SELECT, FROM, WHERE id >= and id <

У заказчика под БД был выделен отличный сервер: двухпроцессорный Intel Xeon E5-2697 v2, в нашем расположении было целых 48 потоков! Нагрузка на сервере была средняя, мы без особых проблем могли забрать около 20-ти потоков. Оперативной памяти тоже было достаточно: аж 384 гигабайт!

Поэтому команду нужно было распараллелить:
for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done

Тут можно было написать красивый и элегантный скрипт, но я выбрал наиболее быстрый способ распараллеливания: разбить диапазон 0-1628991 вручную на интервалы по 100 000 записей и запустить отдельно 16 команд вида:
for ((i=N; i<M; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done

Но это не всё. По идее, подключение к базе данных тоже отнимает какое-то время и системные ресурсы. Подключать 1 628 991 было не очень разумно, согласитесь. Поэтому давайте при одном подключении извлекать 1000 строк вместо одной. В итоге команда преобразилоась в это:
for ((i=N; i<M; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done

Открываем 16 окон в сессии tmux и запускаем команды:

1) for ((i=0; i<100000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 2) for ((i=100000; i<200000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done … 15) for ((i=1400000; i<1500000; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done 16) for ((i=1500000; i<1628991; i=$((i+1000)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id>=$i and id<$((i+1000))" >/dev/null || echo $i; done

Через день я получил первые результаты! А именно (значения XXX и ZZZ уже не сохранились):
ERROR: missing chunk number 0 for toast value 37837571 in pg_toast_106070 829000 ERROR: missing chunk number 0 for toast value XXX in pg_toast_106070 829000 ERROR: missing chunk number 0 for toast value ZZZ in pg_toast_106070 146000

Это значит, что у нас три строки содержат ошибку. id первой и второй проблемной записи находились между 829 000 и 830 000, id третьей – между 146 000 и 147 000. Далее нам предстояло просто найти точное значение id проблемных записей. Для этого просматриваем наш диапазон с проблемными записями с шагом 1 и идентифицируем id:

for ((i=829000; i<830000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number 2 (expected 0) for toast value 37837843 in pg_toast_106070 829449 for ((i=146000; i<147000; i=$((i+1)) )); do psql -U my_user -d my_database -c "SELECT * FROM ws_log_smevlog where id=$i" >/dev/null || echo $i; done 829417 ERROR: unexpected chunk number ZZZ (expected 0) for toast value XXX in pg_toast_106070 146911

Счастливый финал

Мы нашли проблемные строки. Заходим в базу через psql и пробуем их удалить:
my_database=# delete from ws_log_smevlog where id=829417; DELETE 1 my_database=# delete from ws_log_smevlog where id=829449; DELETE 1 my_database=# delete from ws_log_smevlog where id=146911; DELETE 1

К моему удивлению, записи удалились без каких-либо проблем даже без опции zero_damaged_pages.

Затем я подключился к базе, сделал VACUUM FULL (думаю делать было необязательно), и, наконец, успешно снял бекап с помощью pg_dump. Дамп снялся без каких либо ошибок! Проблему удалось решить таким вот тупейшим способом. Радости не было предела, после стольких неудач удалось найти решение!

Благодарности и заключение

Вот такой получился мой первый опыт восстановления реальной базы данных Postgres. Этот опыт я запомню надолго.
Ну и напоследок, хотел бы сказать спасибо компании PostgresPro за переведённую документацию на русский язык и за полностью бесплатные online-курсы, которые очень сильно помогли во время анализа проблемы.

Media failure is one of the crucial things that the database administrator should be aware of. Media failure is nothing but a physical problem reading or writing to files on the storage medium.

A typical example of media failure is a disk head crash, which causes the loss of all files on a disk drive. All files associated with a database are vulnerable to a disk crash, including datafiles, wal files, and control files.

This is the comprehensive post which focuses on disk failure in PostgreSQL and the ways you can retrieve the data from PostgreSQL Database after failure(other than restoring the backup).

In this post, we are going to do archaeology on the below error and will understand how to solve the error.

WARNING: page verification failed, calculated checksum 21135 but expected 3252
ERROR: invalid page in block 0 of relation base/13455/16395

During the process, you are going to learn a whole new bunch of stuff in PostgreSQL.

PostgreSQL Checksum: The definitive guide
- But what is a PostgreSQL checksum?
- How does PostgreSQL checksum work?
- How PostgreSQL Checks Page Validity?
Let’s work on the practical understanding of whatever we learned so far.
- First, check whether the checksum is enabled or not?
  - Let me enable the page checksum in PostgreSQL v12.
  - To check the PostgreSQL page errors, we use the following command.
  - Let us dig deeper into the issue and confirm that the block is corrupted
How to resolve the PostgreSQL corrupted page issue?

PostgreSQL Checksum: The definitive guide

Chapter 1: What is a checksum?
Chapter 2: PostgreSQL checksum: Practical implementation
Chapter 3: How to resolve the PostgreSQL corrupted page issue?

With v9.3, PostgreSQL introduced a feature known as data checksums and it has undergone many changes since then. Now we have a well-sophisticated view in PostgreSQL v12 to find the checksums called pg_checksums.

But what is a PostgreSQL checksum?

When the checksum is enabled, a small integer checksum is written to each “page” of data that Postgres stores on your hard drive. Upon reading that block, the checksum value is recomputed and compared to the stored one.

This detects data corruption, which (without checksums) could be silently lurking in your database for a long time.

Good, checksum, when enabled, detects data corruption.

How does PostgreSQL checksum work?

PostgreSQL maintains page validity primarily on the way in and out of its buffer cache.

Also read: A comprehensive guide – PostgreSQL Caching

From here we understood that the PostgreSQL page has to pass through OS Cache before it leaves or enters into shared buffers. So page validity happens before leaving the shared buffers and before entering the shared buffers.

when PostgreSQL tries to copy the page into its buffer cache then it will (if possible) detect that something is wrong, and it will not allow page to enter into shared buffers with this invalid 8k page, and error out any queries that require this page for processing with the ERROR message

ERROR: invalid page in block 0 of relation base/13455/16395

If you already have a block with invalid data at disk-level and its page version at buffer level, during the next checkpoint, while page out, it will update invalid checksum details but which is rarely possible in real-time environments.

confused, bear with me.

And finally,

If the invalid byte is part of the PostgreSQL database buffer cache, then PostgreSQL will quite happily assume nothing is wrong and attempt to process the data on the page. Results are unpredictable; Some times you will get an error and sometimes you may end up with wrong data.

How PostgreSQL Checks Page Validity?

In a typical page, if data checksums are enabled, information is stored in a 2-byte field containing flag bits after the page header.

Also Read: A comprehensive guide on PostgreSQL: page header

This is followed by three 2-byte integer fields (pd_lower, pd_upper, and pd_special). These contain byte offsets from the page start to the start of unallocated space, to the end of unallocated space, and to the start of the special space.

The checksum value typically begins with zero and every time reading that block, the checksum value is recomputed and compared to the stored one. This detects data corruption.

Checksums are not maintained for blocks while they are in the shared buffers – so if you look at a buffer in the PostgreSQL page cache with pageinspect and you see a checksum value, note that when you do page inspect on a page which is already in the buffer, you may not get the actual checksum. The checksum is calculated and stamped onto the page when the page is written out of the buffer cache into the operating system page cache.

Also read: A comprehensive guide – PostgreSQL Caching

Let’s work on the practical understanding of whatever we learned so far.

I have a table check_corruption wherein I am going to do all the garbage work.

my table size is 8 kB.
has 5 records.
the version I am using is PostgreSQL v12.

postgres=# select * from check_corruption;

aid | bid | abalance | filler

———+———+—————+———————————————————————————————————————————

1 | 1 | 0 | This is checksum example, checksum is for computing block corruption

2 | 1 | 0 | This is checksum example, checksum is for computing block corruption

3 | 1 | 0 | This is checksum example, checksum is for computing block corruption

4 | 1 | 0 | This is checksum example, checksum is for computing block corruption

5 | 1 | 0 | This is checksum example, checksum is for computing block corruption

(5 rows)

postgres=# SELECT * FROM page_header(get_raw_page(‘check_corruption’,0));

——————+—————+————+————+————+—————+—————+—————+——————

0/17EFCA0 | 0 | 0 | 44 | 7552 | 8192 | 8192 | 4 | 0

(1 row)

postgres=# dt+ check_corruption

List of relations

————+—————————+————+—————+——————+———————

(1 row)

postgres=# select pg_relation_filepath(‘check_corruption’);

pg_relation_filepath

———————————

base/13455/16490

(1 row)

postgres=#

First, check whether the checksum is enabled or not?

[postgres@stagdb ~]$ pg_controldata -D /u01/pgsql/data | grep checksum
Data page checksum version: 0
[postgres@stagdb ~]$

It is disabled.

Let me enable the page checksum in PostgreSQL v12.

Syntax: pg_checksums -D /u01/pgsql/data –enable –progress –verbose

[postgres@stagdb ~]$ pg_checksums —D /u01/pgsql/data —enable —progress —verbose

pg_checksums: checksums enabled in file «/u01/pgsql/data/global/2847»

pg_checksums: checksums enabled in file «/u01/pgsql/data/global/1260_fsm»

pg_checksums: checksums enabled in file «/u01/pgsql/data/global/4175»

23/23 MB (100%) computed

Checksum operation completed

Files scanned: 969

Blocks scanned: 3006

pg_checksums: syncing data directory

pg_checksums: updating control file

Data checksum version: 1

Checksums enabled in cluster

Again, check the status of checksums in PostgreSQL

[postgres@stagdb ~]$
[postgres@stagdb ~]$ pg_controldata -D /u01/pgsql/data | grep checksum
Data page checksum version: 1
[postgres@stagdb ~]$

we can disable the checksums with –disable option

[postgres@stagdb ~]$

[postgres@stagdb ~]$ pg_checksums —D /u01/pgsql/data —disable

pg_checksums: syncing data directory

pg_checksums: updating control file

Checksums disabled in cluster

[postgres@stagdb ~]$

Let’s first check the current data directory for errors, then play with data.

To check the PostgreSQL page errors, we use the following command.

pg_checksums -D /u01/pgsql/data –check

[postgres@stagdb ~]$ pg_checksums -D /u01/pgsql/data –check
Checksum operation completed
Files scanned: 969
Blocks scanned: 3006
Bad checksums: 0
Data checksum version: 1
[postgres@stagdb ~]$

Warning!! Do not perform the below case study in your production machine.

As the table check_corruption data file is 16490, I am going to corrupt the file with the Operating system’s dd command.

dd bs=8192 count=1 seek=1 of=16490 if=16490

[postgres@stagdb 13455]$ dd bs=8192 count=1 seek=1 of=16490 if=16490

Now, log in and get the result

postgres=# select * from check_corruption;

aid | bid | abalance | filler

———+———+—————+———————————————————————————————————————————

1 | 1 | 0 | This is checksum example, checksum is for computing block corruption

2 | 1 | 0 | This is checksum example, checksum is for computing block corruption

3 | 1 | 0 | This is checksum example, checksum is for computing block corruption

4 | 1 | 0 | This is checksum example, checksum is for computing block corruption

5 | 1 | 0 | This is checksum example, checksum is for computing block corruption

(5 rows)

I got the result, but why?

I got the result from shared buffers. Let me restart the cluster and fetch the same.

/usr/local/pgsql/bin/pg_ctl restart -D /u01/pgsql/data

postgres=# select * from check_corruption;

aid | bid | abalance | filler

———+———+—————+———————————————————————————————————————————

1 | 1 | 0 | This is checksum example, checksum is for computing block corruption

2 | 1 | 0 | This is checksum example, checksum is for computing block corruption

3 | 1 | 0 | This is checksum example, checksum is for computing block corruption

4 | 1 | 0 | This is checksum example, checksum is for computing block corruption

5 | 1 | 0 | This is checksum example, checksum is for computing block corruption

(5 rows)

But again why?

As we discussed earlier, during restart my PostgreSQL has replaced error checksum with the value of shared buffer.

How can we trigger a checksum warning?

We need to get that row out of shared buffers. The quickest way to do so in this test scenario is to restart the database, then make sure we do not even look at (e.g. SELECT) the table before we make our on-disk modification. Once that is done, the checksum will fail and we will, as expected, receive a checksum error:

i.e., stop the server, corrupt the disk and start it.

/usr/local/pgsql/bin/pg_ctl stop -D /u01/pgsql/data
dd bs=8192 count=1 seek=1 of=16490 if=16490
/usr/local/pgsql/bin/pg_ctl start -D /u01/pgsql/data

During the next fetch, I got below error

postgres=# select * from check_corruption;
2020-02-06 19:06:17.433 IST [25218] WARNING: page verification failed, calculated checksum 39428 but expected 39427
WARNING: page verification failed, calculated checksum 39428 but expected 39427
2020-02-06 19:06:17.434 IST [25218] ERROR: invalid page in block 1 of relation base/13455/16490
2020-02-06 19:06:17.434 IST [25218] STATEMENT: select * from check_corruption;
ERROR: invalid page in block 1 of relation base/13455/16490

Let us dig deeper into the issue and confirm that the block is corrupted

There are a couple of ways you can find the issue which includes Linux commands like

dd
od
hexdump

Usind dd command : dd if=16490 bs=8192 count=1 skip=1 | od -A d -t x1z -w16 | head -1

[postgres@stagdb 13455]$ dd if=16490 bs=8192 count=1 skip=1 | od -A d -t x1z -w16 | head -2
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.5e-05 seconds, 182 MB/s
0000000 00 00 00 00 a0 fc 7e 01 03 9a 00 00 2c 00 80 1d >……~…..,…<

here,

00 00 00 00 a0 fc 7e 01 the first 8 bytes indicate pd_lsn and the next two bytes

03 9a indicates checksums.

Using hexdump : hexdump -C 16490 | head -1

[postgres@stagdb 13455]$ hexdump -C 16490 | head -1
00000000 00 00 00 00 a0 fc 7e 01 03 9a 00 00 2c 00 80 1d |……~…..,…|
[postgres@stagdb 13455]$

Both hexdump and dd returned same result.

Let’s understand what our PostgreSQL very own pg_checksums has to say?

command: pg_checksums -D /u01/pgsql/data –check

[postgres@stagdb 13455]$ pg_checksums -D /u01/pgsql/data –check
pg_checksums: error: checksum verification failed in file “/u01/pgsql/data/base/13455/16490”, block 1: calculated checksum 9A04 but block contains 9A03
Checksum operation completed
Files scanned: 968
Blocks scanned: 3013
Bad checksums: 1
Data checksum version: 1
[postgres@stagdb 13455]$

here, according to pg_checksums checksum 9A03 is matching with that of hexdump’s checksum 9A03.

Converting Hex 9A03 to decimals, I got 39427

which is matching the error

2020-02-06 19:06:17.433 IST [25218] WARNING: page verification failed, calculated checksum 39428 but expected 39427

How to resolve the PostgreSQL corrupted page issue?

use the below function to find the exact location where the page is corrupted.

CREATE OR REPLACE FUNCTION

find_bad_row(tableName TEXT)

RETURNS tid

as $find_bad_row$

DECLARE

result tid;

curs REFCURSOR;

row1 RECORD;

row2 RECORD;

tabName TEXT;

count BIGINT := 0;

BEGIN

SELECT reverse(split_part(reverse($1), ‘.’, 1)) INTO tabName;

OPEN curs FOR EXECUTE ‘SELECT ctid FROM ‘ || tableName;

count := 1;

FETCH curs INTO row1;

WHILE row1.ctid IS NOT NULL LOOP

result = row1.ctid;

count := count + 1;

FETCH curs INTO row1;

EXECUTE ‘SELECT (each(hstore(‘ || tabName || ‘))).* FROM ‘

|| tableName || ‘ WHERE ctid = $1’ INTO row2

USING row1.ctid;

IF count % 100000 = 0 THEN

RAISE NOTICE ‘rows processed: %’, count;

END IF;

END LOOP;

CLOSE curs;

RETURN row1.ctid;

EXCEPTION

WHEN OTHERS THEN

RAISE NOTICE ‘LAST CTID: %’, result;

RAISE NOTICE ‘%: %’, SQLSTATE, SQLERRM;

RETURN result;

END

$find_bad_row$

LANGUAGE plpgsql;

Now, using the function find_bad_row(), you can find the ctid of the corrupted location.

you need hstore extension to use the function

postgres=# CREATE EXTENSION hstore;
CREATE EXTENSION
postgres=#

postgres=# select find_bad_row(‘check_corruption’);
2020-02-06 19:44:24.227 IST [25929] WARNING: page verification failed, calculated checksum 39428 but expected 39427
2020-02-06 19:44:24.227 IST [25929] CONTEXT: PL/pgSQL function find_bad_row(text) line 21 at FETCH
WARNING: page verification failed, calculated checksum 39428 but expected 39427
NOTICE: LAST CTID: (0,5)
NOTICE: XX001: invalid page in block 1 of relation base/13455/16490
find_bad_row
————–
(0,5)
(1 row)

Deleting that particular CTID will resolve the issue

postgres=# delete from check_corruption where ctid='(0,6)’;
DELETE 1
postgres=#

If deleting ctid has not worked for you, you have an alternative solution which is setting zero_damaged_pages parameter.

Example.,

postgres=# select * from master;
WARNING: page verification failed, calculated checksum 8770 but expected 8769
ERROR: invalid page in block 1 of relation base/13455/16770
postgres=#

I can’t access the data from table master as block is corrupted.

Solution:

postgres=# SET zero_damaged_pages = on;
SET

postgres=# vacuum full master;

postgres=# select * from master;

WARNING: page verification failed, calculated checksum 8770 but expected 8769

WARNING: invalid page in block 1 of relation base/13455/16770; zeroing out page

id | name | city

——+—————+——————

1 | Orson | hyderabad

2 | Colin | chennai

3 | Leonard | newyork

here, it cleared the damaged page and gave the rest of the result.

What your document has to say?

zero_damaged_pages (boolean): Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pages to on causes the system to instead report a warning, zero out the damaged page in memory, and continue processing. This behavior will destroy data, namely all the rows on the damaged page. However, it does allow you to get past the error and retrieve rows from any undamaged pages that might be present in the table. It is useful for recovering data if corruption has occurred due to a hardware or software error. You should generally not set this on until you have given up hope of recovering data from the damaged pages of a table. Zeroed-out pages are not forced to disk so it is recommended to recreate the table or the index before turning this parameter off again. The default setting is off, and it can only be changed by a superuser.

There are a couple of things to be aware when using this feature though. First, using checksums has a cost in performance as it introduces extra calculations for each data page (8kB by default), so be aware of the tradeoff between data security and performance when using it.

There are many factors that influence how much slower things are when checksums are enabled, including:

How likely things are to be read from shared_buffers, which depends on how large shared_buffers is set, and how much of your active database fits inside of it
How fast your server is in general, and how well it (and your compiler) are able to optimize the checksum calculation
How many data pages you have (which can be influenced by your data types)
How often you are writing new pages (via COPY, INSERT, or UPDATE)
How often you are reading values (via SELECT)

The more that shared buffers are used (and using them efficiently is a good general goal), the less checksumming is done, and the less the impact of checksums on database performance will be. On an average if you enable checksum the performance cost would be more than 2% and for inserts, the average difference was 6%. For selects, that jumps to 19%. Complete computation benchmark test can be found here

Bonus

You can dump the content of the file with pg_filedump before and after the test and can use diff command to analyze data corruption

pg_filedump -if 16770 > before_corrupt.txt
corrupt the disk block
pg_filedump -if 16770 > before_corrupt.txt
diff or beyond compare both the files.

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out to the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestions/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.

Источник

pg_amcheck-проверяет наличие повреждений в одной или нескольких базах данных PostgreSQL

Description

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

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

Если указано dbname , это должно быть имя отдельной базы данных для проверки, и никакие другие параметры выбора базы данных присутствовать не должны. В противном случае, если присутствуют какие-либо опции выбора базы данных, будут проверены все соответствующие базы данных. Если таких параметров нет, будет проверена база данных по умолчанию. --database выбора базы данных включают --all , —database и —exclude --exclude-database . Они также включают в себя --relation , --exclude-relation , --table , --exclude-table , --index Задает и --exclude-index , но только тогда , когда такие варианты используются с трехчастным рисунком (например , mydb*.myschema*.myrel* ). Наконец, они включают --schema и —exclude --exclude-schema когда такие параметры используются с двухчастным шаблоном (например, mydb*.myschema* ).

dbname также может быть строкой подключения .

Options

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

-a--all

Проверить все базы данных, кроме исключенных с помощью --exclude-database .

-d pattern--database=pattern

Проверить базы данных, соответствующие указанному pattern , за исключением исключенных --exclude-database . Эта опция может быть указана более одного раза.

-D pattern--exclude-database=pattern

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

-i pattern--index=pattern

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

Это похоже на параметр --relation , за исключением того, что он применяется только к индексам, а не к другим типам отношений.

-I pattern--exclude-index=pattern

Исключить индексы, соответствующие указанному pattern . Эта опция может быть указана более одного раза.

Это похоже на параметр --exclude-relation , за исключением того, что он применяется только к индексам, а не к другим типам отношений.

-r pattern--relation=pattern

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

Шаблоны могут быть неквалифицированными, например myrel* , или они могут быть квалифицированными схемой, например myschema*.myrel* или квалифицированными базой данных и квалифицированными схемами, например mydb*.myscheam*.myrel* . Шаблон с указанием базы данных добавит соответствующие базы данных в список проверяемых баз данных.

-R pattern--exclude-relation=pattern

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

Как и в случае с --relation , pattern может быть неквалифицированным, квалифицированным по схеме или с указанием базы данных и схемы.

-s pattern--schema=pattern

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

Чтобы выбрать только таблицы в схемах, соответствующих определенному шаблону, рассмотрите возможность использования чего-то вроде --table=SCHEMAPAT.* --no-dependent-indexes . Чтобы выбрать только индексы, рассмотрите возможность использования чего-то вроде --index=SCHEMAPAT.* .

Шаблон схемы может быть уточнен базой данных. Например, вы можете написать --schema=mydb*.myschema* чтобы выбрать схемы, соответствующие myschema* в базах данных, соответствующих mydb* .

-S pattern--exclude-schema=pattern

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

Как и в случае с --schema , шаблон может быть квалифицирован как база данных.

-t pattern--table=pattern

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

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

-T pattern--exclude-table=pattern

Исключить таблицы, соответствующие указанному pattern . Эта опция может быть указана более одного раза.

Это похоже на параметр --exclude-relation , за исключением того, что он применяется только к таблицам, материализованным представлениям и последовательностям, а не к индексам.

--no-dependent-indexes

По умолчанию, если таблица отмечена, любые индексы btree этой таблицы также будут проверены, даже если они не были явно выбраны с помощью такой опции, как --index или --relation . Этот параметр подавляет такое поведение.

--no-dependent-toast

По умолчанию, если таблица отмечена, ее всплывающая таблица, если таковая имеется, также будет проверена, даже если она не выбрана явно с помощью такой опции, как --table или --relation . Этот параметр подавляет такое поведение.

--no-strict-names

По умолчанию, если аргумент --database , --table , --index или --relation не соответствует ни одному объекту , это фатальная ошибка. Эта опция превращает эту ошибку в предупреждение.

Следующие параметры командной строки управляют проверкой таблиц:

--exclude-toast-pointers

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

--on-error-stop

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

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

--skip=option

Если задано all-frozen , при проверке повреждения таблиц будут пропускаться страницы во всех таблицах, которые помечены как все замороженные.

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

По умолчанию страницы не пропускаются. Это может быть указано как none , но, поскольку это значение по умолчанию, об этом нет необходимости.

--startblock=block

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

--endblock=block

Завершите проверку на указанном номере блока. Ошибка возникнет, если в проверяемой связи таблицы меньше этого количества блоков. Этот параметр не применяется к индексам и, вероятно, полезен только при проверке связи одной таблицы. Если отмечены как обычная таблица, так и таблица всплывающих окон, этот параметр будет применяться к обоим, но блоки всплывающих уведомлений с более высокими номерами все еще могут быть доступны во время проверки указателей всплывающих окон, если только это не подавлено с помощью --exclude-toast-pointers .

Следующие параметры командной строки управляют проверкой индексов B-дерева:

--heapallindexed

Для каждого индекса проверил, проверить наличие всех кучи кортежей в качестве индекса кортежей в индексе использования amcheck «S heapallindexed вариант.

--parent-check

Для каждого индекса ВТКЕЕ проверил, используйте amcheck «ы bt_index_parent_check функцию, которая выполняет дополнительные проверки отношений родитель / потомок при проверке индекса.

По умолчанию используется функция amcheck bt_index_check , но обратите внимание, что использование параметра --rootdescend неявно выбирает bt_index_parent_check .

--rootdescend

Для каждого индекса проверяется, повторно находят кортежи на уровне листьев, выполняя новый поиск с корневой страницы для каждого кортежа , используя amcheck «s rootdescend вариант.

Использование этого параметра неявно также выбирает параметр --parent-check .

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

Warning

Дополнительные проверки, выполняемые для индексов B-tree, когда указан параметр —parent --parent-check или --rootdescend , требуют относительно сильных блокировок на уровне отношений. Эти проверки являются единственными проверками, которые блокируют одновременную модификацию данных командами INSERT , UPDATE и DELETE .

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

-h hostname--host=hostname

Указание имени хоста машины,на которой запущен сервер.Если значение начинается с косой черты,то оно используется в качестве директории для доменного сокета Unix.

-p port--port=port

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

-U--username=username

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

-w--no-password

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

-W--password

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

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

--maintenance-db=dbname

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

Также доступны и другие опции:

-e--echo

Вывод на stdout всех SQL,отправленных на сервер.

-j num--jobs=num

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

По умолчанию используется одно соединение.

-P--progress

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

-v--verbose

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

-V--version

Выведите версию pg_amcheck и выйдите.

--install-missing--install-missing=schema

Установите все отсутствующие расширения, необходимые для проверки баз данных. Если еще не установлено, то все объекты расширения будут установлены в данную schema или, если они не указаны, в схему pg_catalog .

В настоящее время единственное необходимое расширение — это amcheck .

-?--help

Показать справку об аргументах командной строки pg_amcheck и выйти.

pg_amcheck предназначен для работы с PostgreSQL 14.0 и более поздними версиями.

Источник

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник