In some situations database indexes can be corrupted, and you need to rebuild them with the REINDEX command.
Rebuilding indexes is very simple:
REINDEX DATABASE db_name;
In some situations indexes can become corrupted. This can happen because of bugs in PostgreSQL or because of changes in macOS.
Bugs in PostgreSQL that require a REINDEX are typically mentioned in the PostgreSQL release notes.
Changes in macOS that require a REINDEX are unfortunately not documented anywhere. The only problematic change we are aware of currently is that the default text sort order (collation) has changed in macOS 11, which means indexes created or updated on earlier versions of macOS are no longer valid and must be rebuilt.
It is very hard to tell if indexes have actually been corrupted or not. Postgres.app tries to detect some scenarios where an index could have been corrupted, and shows a warning suggesting to reindex your database.
If you are unsure, we recommend to perform the reindex operation to be safe.
For small databases it will take just a few seconds, but if you have a lot of data it could take a few minutes.
You can use the command
REINDEX (VERBOSE) DATABASE db_name; to see status messages if you have a large database.
Please note that to perform the reindex any concurrent writing transactions need to come to an end and new transactions and sessions may need to wait for the reindex to finish. If any client keeps a writing transaction open, the reindex operation will block and wait for that without any warning. You can cancel and restart the operation if needed.
ERROR: could not create unique indextake a note on the message and the details, connect to the database in question, and manually resolve the unique conflict. When querying the data, try to avoid using indexes, e.g. by issuing
SET enable_indexscan = off; SET enable_indexonlyscan = off; SET enable_bitmapscan = off;in the session you use for this. Then retry the reindex operation.
If not explicitly requested otherwise (ICU collations), PostgreSQL uses the collations (language dependent rules for sorting text) provided by the operation system. PostgresApp sets the default locale (and thus collation) to ‘en_US.UTF-8’ when initialising a new cluster since PostgresQL 9.4.1 (End of 2014). However, UTF-8 based collations are not actually implemented in macOS (like in most BSD Systems). Thus, the effective sort order was rather following byte order, see Issue #216.
With the update to macOS 11, Apple started to use the ISO8859-1 collations for about half of the available locales, including the default locale of Postgres.app, ‘en_US.UTF-8’. As Database Indexes store the order of elements at the time these are inserted, corruption can happen if the sorting rules change later.
Postgres.app records the version of macOS where
initdb was called, and also all versions
of macOS that the server was started on. Since this information is not available for old
data directories, Postgres.app guesses the macOS version used to call
existing data directories based on the install history of macOS updates. If Postgres.app
detects that the data directory was used both pre macOS 11 and post macOS 11 or the macOS
version used for initdb of a data directory is unknown, it shows the reindex warning.
So the warning is a good indicator, but may not be absolutely accurate. If you prefer to do a manual assessment, here are some guidelines:
You are not affected if:
initdbwas run manually (not with the button ‘Initialize’ within PostgresApp) with
--localeset to “C” or “POSIX” or to an unaffected locale and no other libc-based collations are used on columns or in indexes. Database default collation can be shown with
SELECT datname, datcollate FROM pg_database;, use of object level collations can be determined by joining
pg_dumpall) after updating the OS version (exception: range partion keys, see below).
You are likely affected if:
pg_upgradeon macOS 11 or later to update a cluster inited on macOS 10.15 or earlier.
pg_basebackup) from a Mac using macOS 10.15 or earlier to macOS 11 or later or vice versa.
There is no relation with the version of PostgresApp or PostgreSQL or with the architecture (Apple Silcon / Intel) in use.
reindexdbcommand line or
pg_stat_progress_create_indexview in psql:
TABLE pg_stat_progress_create_index \watch 1
wait_event‘Lock’ in the
SELECT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid) FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s JOIN pg_collation c ON coll=c.oid WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');
CHECKconstraints that depend on character order it is possible you have undetected constraint violations in the existing data. Unlike the
UNIQUEviolations mentioned before, these need to be searched for and resolved manually.
~operators), you may want to create additional indexes with the matching
xxx_pattern_opsopclass to restore support for this.