@tbussmann has discovered an issue where indexes are corrupted when upgrading from a system running macOS 10.15 or earlier to macOS 11 or later.

The underlying issue is that Apple has changed some system collations in macOS 11, including the en_US.utf-8 collation that Postgres.app uses by default.

This problem can lead to the following problems:

• SELECT or UPDATE queries could miss some rows
• UNIQUE constraints might not be correctly enforced, leading to duplicate keys

To fix the issue, you have to reindex every database of the server. To do so, connect to each database and execute the following SQL query:

REINDEX DATABASE <database_name>;

The query can take anywhere from a few seconds to minutes or hours, depending on the size of your database.

The query will fail if you already have duplicate keys in a table. If that is the case, PostgreSQL will show a warning telling you about the problem. Identify the problematic table, fix the duplicate keys (be careful because queries might fail to show all rows because of the corrupted index), then try again.