Error can only reindex the currently open database

Thanks for the prompt response. But that’s what I tried and it didn’t work. I tried following command in both pgAdmin III and psql:

REINDEX DATABASE PremierIEX

Then I got error

ERROR: can only reindex the currently open database

PremierIEX is my current database. In pgAdmin III it threw extra info:

SQL state: 0A000

Thanks,

Yong

——Original Message——
From: Thomas Kellerer [mailto:spam_eater(at)gmx(dot)net]
Sent: Friday, August 23, 2013 3:13 PM
To: Yong Zhang
Cc: Yong Zhang
Subject: Re: BUG #8394: SQL command REINDEX doesn’t work

yzhang(at)trinityusa(dot)com <mailto:yzhang(at)trinityusa(dot)com> wrote on 23.08.2013 18:00:

> The following bug has been logged on the website:

>

> Bug reference: 8394

> Logged by: Yz Primus

> Email address: yzhang(at)trinityusa(dot)com <mailto:yzhang(at)trinityusa(dot)com>

> PostgreSQL version: 9.1.3

> Operating system: Windows 7 64-bit

> Description:

>

> I always received error when trying to run SQL command REINDEX either

> from pgAdmin III or psql. If I ran REINDEX DATABASE it threw error:

> ERROR: syntax error at end of input

> LINE 1: REINDEX DATABASE

>

>

> If I ran REINDEX DATABASE mydb it threw error:

> ERROR: can only reindex the currently open database

>

Quote from the manual:

Presently, REINDEX DATABASE and REINDEX SYSTEM can only reindex the current database,

so their parameter must match the current database’s name.

So you need to specify the name of the current database even though it’s not really needed.

Learned that, the psql reindex requires double quotes.

I would usually performs the reindex within psql.

VCDB=#
VCDB=# REINDEX DATABASE VCDB;
ERROR:  can only reindex the currently open database
VCDB=# REINDEX DATABASE «VCDB»;

NOTICE:  table «pg_catalog.pg_class» was reindexed
NOTICE:  table «pg_catalog.pg_statistic» was reindexed
NOTICE:  table «pg_catalog.pg_type» was reindexed
NOTICE:  table «vc.cis_kv_providers» was reindexed
NOTICE:  table «pg_catalog.pg_authid» was reindexed
NOTICE:  table «pg_catalog.pg_attribute» was reindexed
NOTICE:  table «pg_catalog.pg_proc» was reindexed
NOTICE:  table «vc.cis_kv_keyvalue» was reindexed
NOTICE:  table «pg_catalog.pg_user_mapping» was reindexed
NOTICE:  table «pg_catalog.pg_constraint» was reindexed
NOTICE:  table «pg_catalog.pg_inherits» was reindexed
NOTICE:  table «pg_catalog.pg_index» was reindexed
NOTICE:  table «pg_catalog.pg_operator» was reindexed
NOTICE:  table «pg_catalog.pg_opfamily» was reindexed

Today, for the fun of it, i performed Reindex with command line and it errors out. I do not see a logical reason to have the double quotes.

localhost:/opt/vmware/vpostgres/current/bin # ./reindexdb -U postgres -d VCDB
NOTICE:  table «pg_catalog.pg_class» was reindexed
NOTICE:  table «pg_catalog.pg_statistic» was reindexed
NOTICE:  table «pg_catalog.pg_type» was reindexed
NOTICE:  table «vc.cis_kv_providers» was reindexed
NOTICE:  table «pg_catalog.pg_authid» was reindexed
NOTICE:  table «pg_catalog.pg_attribute» was reindexed
NOTICE:  table «pg_catalog.pg_proc» was reindexed
NOTICE:  table «vc.cis_kv_keyvalue» was reindexed
NOTICE:  table «pg_catalog.pg_user_mapping» was reindexed
NOTICE:  table «pg_catalog.pg_constraint» was reindexed

An SQL command for rebuilding indexes

REINDEX is a utility command for rebuilding indexes.

REINDEX was added in PostgreSQL 7.0.

Examples

appdb=> REINDEX (VERBOSE) TABLE object;
INFO:  index "object_pkey" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
INFO:  index "object_class_id_ix" was reindexed
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
REINDEX

postgres=# REINDEX DATABASE;
ERROR:  syntax error at or near ";"
LINE 1: REINDEX DATABASE;
                        ^
postgres=# REINDEX DATABASE postgres;
REINDEX

postgres=# REINDEX DATABASE foo;
ERROR:  can only reindex the currently open database

Как и многие команды базы данных PostgreSQL, команда REINDEX очень удобна, когда нужно обновить систему базы данных. Он создан для воссоздания уже созданного ИНДЕКС вашей базы данных. Могут возникнуть ситуации, когда возникают проблемы с данными вашей базы данных из-за программных и аппаратных проблем. Эти проблемы могут привести к тому, что в вашей базе данных появятся неверные данные, а представления не смогут получить необходимые записи. Настало время восстановить наши уже созданные представления, чтобы использовать их снова с помощью инструкции REINDEX. В этой статье будет рассмотрен метод обновления уже сделанных индексов в базе данных PostgreSQL.

Использование графического интерфейса PostgreSQL PgAdmin

Давайте начнем с нового экрана рабочего стола Windows 10. Убедитесь, что в вашей Windows 10 уже есть база данных PostgreSQL, полностью настроенная для ее использования. Чтобы открыть графический интерфейс базы данных PostgreSQL, коснитесь панели поиска Windows 10 в левом нижнем углу экрана рабочего стола. Напишите «pgAdmin», и приложение появится. Выберите pgAdmin 4, чтобы быстро открыть его. После выбора для нормального открытия потребуется до 20 секунд.

Для запуска потребуется ваш пароль Сервера базы данных, который вы добавили во время установки. Добавьте этот пароль и нажмите кнопку «ОК», чтобы продолжить его использование. С левой стороны вы увидите опцию «Серверы». Разверните его, чтобы увидеть базы данных. Разверните параметр базы данных и нажмите на базу данных, которую вы хотите использовать для этой реализации. Здесь мы использовали слово «аксаясин». Щелкните значок инструмента запросов, чтобы открыть область запросов для базы данных, т. е. «aqsayasin».

Прежде чем что-либо делать, нам нужно перечислить все уже сделанные индексы нашей базы данных. Для этого вам нужно использовать инструкцию SELECT с использованием «pg_indexes» общедоступной схемы, извлекая из нее столбец «имя схемы», «имя таблицы», «имя индекса» и «определение индекса». У нас есть в общей сложности 6 индексов, которые уже есть в нашей базе данных «aqsayasin» с этим запросом.

Вы можете увидеть имена индексов уже здесь, в 3-м столбце слева от приведенного выше вывода. Давайте переиндексируем индекс «in_new» таблицы «Новая» и схемы «общедоступной», используя здесь команду REINDEX. Используйте ключевое слово «REINDEX» с именем индекса, которому предшествует ключевое слово «INDEX», как показано в запросе ниже. Выполнение этого запроса было успешным, поскольку на выходе отображается сообщение об успешном выполнении запроса. Теперь этот индекс обновляется, и если есть проблема, то ее больше не будет.

Не только индексы, но и команда REINDEX также может быть отключена для обновления всей таблицы вашей базы данных. Для этого вам нужно добавить имя таблицы вместо Index. Давайте переиндексируем таблицу «Atest» с помощью команды REINDEX, за которой следует ключевое слово «TABLE» перед именем таблицы. При выполнении этого запроса мы снова столкнулись с сообщением об успешном выполнении запроса в области вывода запроса. Таблица «Атест» также переиндексируется или создается заново для использования.

Не только таблицы и индексы, вы также будете использовать команду REINDEX для обновления всей схемы вашей системы. Это означает, что при переиндексации схемы все ваши базы данных или таблицы будут обновлены одним запросом. Таким образом, мы снова используем запрос REINDEX в нашем инструменте запросов для переиндексации «общедоступной» схемы нашей базы данных. Используйте ключевое слово «SCHEMA» после ключевого слова REINDEX и перед именем схемы в этом запросе. При выполнении вся ваша схема будет переиндексирована, и через некоторое время она покажет вам сообщение об успехе, т.е. это займет немного больше времени, чем обычный запрос. Этот запрос переиндексирует все базы данных в вашей системе, т.е. Postgres и другие.

Чтобы обновить одну базу данных с множеством таблиц, мы также можем использовать команду REINDEX. Вам просто нужно использовать ключевое слово «DATABASE» с именем базы данных для переиндексации, которому предшествует ключевое слово REINDEX. Давайте переиндексируем базу данных по умолчанию «Postgres», используя инструмент запроса другой базы данных, т. е. «aqsayasin. При выполнении этого запроса вы столкнетесь с ошибкой, показанной на изображении ниже. В нем говорится, что в настоящее время мы используем другую базу данных и пытаемся переиндексировать базу данных «Postgres». Таким образом, мы не можем делать то, что хотим. Таким образом, мы можем переиндексировать только базу данных, над которой работаем. Обновим нашу команду.

В том же запросе мы заменили имя базы данных, т.е. «aqsayasin». Запрос REINDEX теперь успешно после выполнения показывает, что база данных переиндексирована правильно.

Давайте используем ключевое слово SCHEMA в той же команде REINDEX, чтобы обновить или воссоздать индексы, найденные в нашем открытом каталоге базы данных, т. е. «aqsayasin». Это также приведет к воссозданию общих индексов. При выполнении этой инструкции в инструменте запросов мы до сих пор были успешными.

Использование оболочки PostgreSQL (psql)

Та же функциональность может быть достигнута с помощью оболочки PostgreSQL. Откройте оболочку PostgreSQL, выполнив поиск по ключевому слову «psql» в строке поиска. Нажмите на приложение оболочки MySQL и напишите имя локального хоста, который вы использовали. Теперь обязательно добавьте правильное имя вашей базы данных, в которой вы хотите выполнить переиндексацию, вместе с правильным номером порта. После этого вам будет предложено добавить имя пользователя и пароль для пользователя вашей базы данных. Вы также можете использовать базу данных «Postgres» и имя пользователя, то есть по умолчанию. Ваша оболочка будет готова к использованию.

Посмотрим, сколько индексов у нас в таблице «Ftest». Чтобы просмотреть их, используйте команду «d+» с именем таблицы «Ftest». Вывод ниже показывает общее количество 2 индексов, найденных в этой таблице, т. е. iftest и inftest. Мы будем переиндексировать эти индексы в следующих запросах.

Используя ту же команду «d+», мы получили индексы, отображаемые для таблицы «Значения». Он показывает единый индекс для этой таблицы, как показано ниже. Мы также будем переиндексировать его.

Начнем сначала переиндексировать таблицу «Ftest». Отбросьте инструкцию «REINDEX» в оболочке PostgreSQL с именем таблицы, т. е. Ftest. Это успешно, и теперь таблица переиндексирована. Полученное в результате слово «REINDEX» является доказательством нашего успеха здесь.

Чтобы переиндексировать схему PostgreSQL с именем «public», мы должны использовать ту же команду REINDEX с заголовком «SCHEMA». Это будет успешным снова в соответствии с результатом. Слово «REINDEX» — это сообщение об успехе на нашем экране.

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

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

Заключение

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

Postgresql реиндексация всей базы

reindexdb — переиндексировать базу данных PostgreSQL

Синтаксис

reindexdb [ параметр-подключения . ] [ параметр . ] [ -S | —schema схема ] . [ -t | —table таблица ] . [ -i | —index индекс ] . [ имя_бд ]

reindexdb [ параметр-подключения . ] [ параметр . ] -a | —all

reindexdb [ параметр-подключения . ] [ параметр . ] -s | —system [ имя_бд ]

Описание

Утилита reindexdb предназначена для перестроения индексов в базе данных PostgreSQL .

Утилита reindexdb представляет собой обёртку SQL-команды REINDEX . Переиндексация базы данных с её помощью по сути не отличается от переиндексации при обращении к серверу другими способами.

Параметры

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

Переиндексировать все базы данных. [ -d ] имя_бд
[ —dbname= ] имя_бд

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

Выводить команды, которые reindexdb генерирует и передаёт серверу. -i индекс
—index= индекс

Пересоздать только указанный индекс . Добавив дополнительные ключи -i , можно пересоздать несколько индексов. -q
—quiet

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

Переиндексировать только системные каталоги базы данных. -S схема
—schema= схема

Переиндексировать только указанную схему . Переиндексировать несколько схем можно, добавив несколько ключей -S . -t таблица
—table= таблица

Переиндексировать только указанную таблицу . Переиндексировать несколько таблиц можно, добавив несколько ключей -t . -v
—verbose

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

Сообщить версию reindexdb и завершиться. -?
—help

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

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

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

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

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

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

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

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

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

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

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

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

Диагностика

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

Замечания

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

/.pgpass . За дополнительными сведениями обратитесь к Разделу 34.15.

Примеры

Переиндексирование базы данных test :

Переиндексирование таблицы foo и индекса bar в базе данных abcd :

Источник

Postgresql реиндексация всей базы

reindexdb — переиндексировать базу данных PostgreSQL

Синтаксис

reindexdb [ параметр-подключения . ] [ параметр . ] [ -S | —schema схема ] . [ -t | —table таблица ] . [ -i | —index индекс ] . [ имя_бд ]

reindexdb [ параметр-подключения . ] [ параметр . ] -a | —all

reindexdb [ параметр-подключения . ] [ параметр . ] -s | —system [ имя_бд ]

Описание

Утилита reindexdb предназначена для перестроения индексов в базе данных PostgreSQL .

Утилита reindexdb представляет собой обёртку SQL-команды REINDEX . Переиндексация базы данных с её помощью по сути не отличается от переиндексации при обращении к серверу другими способами.

Параметры

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

Переиндексировать все базы данных. [ -d ] имя_бд
[ —dbname= ] имя_бд

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

Выводить команды, которые reindexdb генерирует и передаёт серверу. -i индекс
—index= индекс

Пересоздать только указанный индекс . Добавив дополнительные ключи -i , можно пересоздать несколько индексов. -q
—quiet

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

Переиндексировать только системные каталоги базы данных. -S схема
—schema= схема

Переиндексировать только указанную схему . Переиндексировать несколько схем можно, добавив несколько ключей -S . -t таблица
—table= таблица

Переиндексировать только указанную таблицу . Переиндексировать несколько таблиц можно, добавив несколько ключей -t . -v
—verbose

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

Сообщить версию reindexdb и завершиться. -?
—help

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

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

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

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

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

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

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

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

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

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

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

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

Диагностика

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

Замечания

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

/.pgpass . За дополнительными сведениями обратитесь к Разделу 32.15.

Примеры

Переиндексирование базы данных test :

Переиндексирование таблицы foo и индекса bar в базе данных abcd :

Источник

Postgresql реиндексация всей базы

REINDEX rebuilds an index using the data stored in the index’s table, replacing the old copy of the index. There are several scenarios in which to use REINDEX :

An index has become corrupted, and no longer contains valid data. Although in theory this should never happen, in practice indexes can become corrupted due to software bugs or hardware failures. REINDEX provides a recovery method.

An index has become “ bloated ” , that is it contains many empty or nearly-empty pages. This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. See Section 25.2 for more information.

You have altered a storage parameter (such as fillfactor) for an index, and wish to ensure that the change has taken full effect.

If an index build fails with the CONCURRENTLY option, this index is left as “ invalid ” . Such indexes are useless but it can be convenient to use REINDEX to rebuild them. Note that only REINDEX INDEX is able to perform a concurrent build on an invalid index.

Parameters

Recreate the specified index. This form of REINDEX cannot be executed inside a transaction block when used with a partitioned index.

Recreate all indexes of the specified table. If the table has a secondary “ TOAST ” table, that is reindexed as well. This form of REINDEX cannot be executed inside a transaction block when used with a partitioned table.

Recreate all indexes of the specified schema. If a table of this schema has a secondary “ TOAST ” table, that is reindexed as well. Indexes on shared system catalogs are also processed. This form of REINDEX cannot be executed inside a transaction block.

Recreate all indexes within the current database. Indexes on shared system catalogs are also processed. This form of REINDEX cannot be executed inside a transaction block.

Recreate all indexes on system catalogs within the current database. Indexes on shared system catalogs are included. Indexes on user tables are not processed. This form of REINDEX cannot be executed inside a transaction block.

The name of the specific index, table, or database to be reindexed. Index and table names can be schema-qualified. Presently, REINDEX DATABASE and REINDEX SYSTEM can only reindex the current database, so their parameter must match the current database’s name.

When this option is used, PostgreSQL will rebuild the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index rebuild locks out writes (but not reads) on the table until it’s done. There are several caveats to be aware of when using this option — see Rebuilding Indexes Concurrently below.

For temporary tables, REINDEX is always non-concurrent, as no other session can access them, and non-concurrent reindex is cheaper.

Specifies that indexes will be rebuilt on a new tablespace.

Prints a progress report as each index is reindexed.

Specifies whether the selected option should be turned on or off. You can write TRUE , ON , or 1 to enable the option, and FALSE , OFF , or 0 to disable it. The boolean value can also be omitted, in which case TRUE is assumed.

The tablespace where indexes will be rebuilt.

Notes

If you suspect corruption of an index on a user table, you can simply rebuild that index, or all indexes on the table, using REINDEX INDEX or REINDEX TABLE .

Things are more difficult if you need to recover from corruption of an index on a system table. In this case it’s important for the system to not have used any of the suspect indexes itself. (Indeed, in this sort of scenario you might find that server processes are crashing immediately at start-up, due to reliance on the corrupted indexes.) To recover safely, the server must be started with the -P option, which prevents it from using indexes for system catalog lookups.

One way to do this is to shut down the server and start a single-user PostgreSQL server with the -P option included on its command line. Then, REINDEX DATABASE , REINDEX SYSTEM , REINDEX TABLE , or REINDEX INDEX can be issued, depending on how much you want to reconstruct. If in doubt, use REINDEX SYSTEM to select reconstruction of all system indexes in the database. Then quit the single-user server session and restart the regular server. See the postgres reference page for more information about how to interact with the single-user server interface.

Alternatively, a regular server session can be started with -P included in its command line options. The method for doing this varies across clients, but in all libpq -based clients, it is possible to set the PGOPTIONS environment variable to -P before starting the client. Note that while this method does not require locking out other clients, it might still be wise to prevent other users from connecting to the damaged database until repairs have been completed.

REINDEX is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, the locking considerations are rather different. REINDEX locks out writes but not reads of the index’s parent table. It also takes an ACCESS EXCLUSIVE lock on the specific index being processed, which will block reads that attempt to use that index. In particular, the query planner tries to take an ACCESS SHARE lock on every index of the table, regardless of the query, and so REINDEX blocks virtually any queries except for some prepared queries whose plan has been cached and which don’t use this very index. In contrast, DROP INDEX momentarily takes an ACCESS EXCLUSIVE lock on the parent table, blocking both writes and reads. The subsequent CREATE INDEX locks out writes but not reads; since the index is not there, no read will attempt to use it, meaning that there will be no blocking but reads might be forced into expensive sequential scans.

Reindexing a single index or table requires being the owner of that index or table. Reindexing a schema or database requires being the owner of that schema or database. Note specifically that it’s thus possible for non-superusers to rebuild indexes of tables owned by other users. However, as a special exception, when REINDEX DATABASE , REINDEX SCHEMA or REINDEX SYSTEM is issued by a non-superuser, indexes on shared catalogs will be skipped unless the user owns the catalog (which typically won’t be the case). Of course, superusers can always reindex anything.

Reindexing partitioned indexes or partitioned tables is supported with REINDEX INDEX or REINDEX TABLE , respectively. Each partition of the specified partitioned relation is reindexed in a separate transaction. Those commands cannot be used inside a transaction block when working on a partitioned table or index.

When using the TABLESPACE clause with REINDEX on a partitioned index or table, only the tablespace references of the leaf partitions are updated. As partitioned indexes are not updated, it is recommended to separately use ALTER TABLE ONLY on them so as any new partitions attached inherit the new tablespace. On failure, it may not have moved all the indexes to the new tablespace. Re-running the command will rebuild all the leaf partitions and move previously-unprocessed indexes to the new tablespace.

If SCHEMA , DATABASE or SYSTEM is used with TABLESPACE , system relations are skipped and a single WARNING will be generated. Indexes on TOAST tables are rebuilt, but not moved to the new tablespace.

Rebuilding Indexes Concurrently

Rebuilding an index can interfere with regular operation of a database. Normally PostgreSQL locks the table whose index is rebuilt against writes and performs the entire index build with a single scan of the table. Other transactions can still read the table, but if they try to insert, update, or delete rows in the table they will block until the index rebuild is finished. This could have a severe effect if the system is a live production database. Very large tables can take many hours to be indexed, and even for smaller tables, an index rebuild can lock out writers for periods that are unacceptably long for a production system.

PostgreSQL supports rebuilding indexes with minimum locking of writes. This method is invoked by specifying the CONCURRENTLY option of REINDEX . When this option is used, PostgreSQL must perform two scans of the table for each index that needs to be rebuilt and wait for termination of all existing transactions that could potentially use the index. This method requires more total work than a standard index rebuild and takes significantly longer to complete as it needs to wait for unfinished transactions that might modify the index. However, since it allows normal operations to continue while the index is being rebuilt, this method is useful for rebuilding indexes in a production environment. Of course, the extra CPU, memory and I/O load imposed by the index rebuild may slow down other operations.

The following steps occur in a concurrent reindex. Each step is run in a separate transaction. If there are multiple indexes to be rebuilt, then each step loops through all the indexes before moving to the next step.

A new transient index definition is added to the catalog pg_index . This definition will be used to replace the old index. A SHARE UPDATE EXCLUSIVE lock at session level is taken on the indexes being reindexed as well as their associated tables to prevent any schema modification while processing.

A first pass to build the index is done for each new index. Once the index is built, its flag pg_index.indisready is switched to “ true ” to make it ready for inserts, making it visible to other sessions once the transaction that performed the build is finished. This step is done in a separate transaction for each index.

Then a second pass is performed to add tuples that were added while the first pass was running. This step is also done in a separate transaction for each index.

All the constraints that refer to the index are changed to refer to the new index definition, and the names of the indexes are changed. At this point, pg_index.indisvalid is switched to “ true ” for the new index and to “ false ” for the old, and a cache invalidation is done causing all sessions that referenced the old index to be invalidated.

The old indexes have pg_index.indisready switched to “ false ” to prevent any new tuple insertions, after waiting for running queries that might reference the old index to complete.

The old indexes are dropped. The SHARE UPDATE EXCLUSIVE session locks for the indexes and the table are released.

If a problem arises while rebuilding the indexes, such as a uniqueness violation in a unique index, the REINDEX command will fail but leave behind an “ invalid ” new index in addition to the pre-existing one. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql d command will report such an index as INVALID :

If the index marked INVALID is suffixed ccnew , then it corresponds to the transient index created during the concurrent operation, and the recommended recovery method is to drop it using DROP INDEX , then attempt REINDEX CONCURRENTLY again. If the invalid index is instead suffixed ccold , it corresponds to the original index which could not be dropped; the recommended recovery method is to just drop said index, since the rebuild proper has been successful.

Regular index builds permit other regular index builds on the same table to occur simultaneously, but only one concurrent index build can occur on a table at a time. In both cases, no other types of schema modification on the table are allowed meanwhile. Another difference is that a regular REINDEX TABLE or REINDEX INDEX command can be performed within a transaction block, but REINDEX CONCURRENTLY cannot.

Like any long-running transaction, REINDEX on a table can affect which tuples can be removed by concurrent VACUUM on any other table.

REINDEX SYSTEM does not support CONCURRENTLY since system catalogs cannot be reindexed concurrently.

Furthermore, indexes for exclusion constraints cannot be reindexed concurrently. If such an index is named directly in this command, an error is raised. If a table or database with exclusion constraint indexes is reindexed concurrently, those indexes will be skipped. (It is possible to reindex such indexes without the CONCURRENTLY option.)

Each backend running REINDEX will report its progress in the pg_stat_progress_create_index view. See Section 28.4.2 for details.

Examples

Rebuild a single index:

Rebuild all the indexes on the table my_table :

Rebuild all indexes in a particular database, without trusting the system indexes to be valid already:

Rebuild indexes for a table, without blocking read and write operations on involved relations while reindexing is in progress:

Compatibility

There is no REINDEX command in the SQL standard.

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Copyright © 1996-2022 The PostgreSQL Global Development Group

Источник