What has caused PostgreSQL to warn: The database was created using collation version 2.37, but the operating system provides version 2.34.

The warning you're experiencing is likely due to a mismatch between the collation version used when creating your database and the current collation version provided by the operating system after the update. This situation can occur when updating the operating system, which sometimes includes updates to the glibc library.

In your case, it appears that your database was created using a newer collation version (2.37) than what is currently provided by the operating system (2.34). This is unusual, as typically updates would move to newer versions. It's possible that during the update process, an older version of glibc was installed, causing this discrepancy.

The warning is generated because PostgreSQL relies on the operating system's glibc library for collation support. When there's a mismatch between the collation version used to create the database and the one currently provided by the OS, PostgreSQL warns you to ensure data integrity and correct sorting behavior.

To address this issue safely:

1. First, verify if there are any collations that need refreshing. You can do this by running a query to check for version mismatches between the database collations and the actual collation versions.

2. If mismatches are found, you should consider following the recommended steps to REINDEX and then run ALTER DATABASE to refresh the collation version. This will ensure that your database is using the correct collation version provided by the current operating system.

3. Before performing these operations, it's crucial to:

   • Take a full backup of your database
   • Perform the operations during a maintenance window
   • Test the process on a non-production copy of your database first

4. After completing these steps, monitor your application to ensure that sorting and comparison operations behave as expected.

It's worth noting that PostgreSQL has introduced measures to reduce the impact of glibc updates in recent versions. For instance, some versions of PostgreSQL now include an independent default collation library to provide sort order stability and prevent incorrect query results.

If you're concerned about the potential impact or if your database is critical, you may want to consult with a PostgreSQL expert or AWS support to ensure you're taking the most appropriate action for your specific situation.
Sources
Collations supported in RDS for PostgreSQL - Amazon Relational Database Service
Collations supported in Aurora PostgreSQL - Amazon Aurora
Managing collations - Amazon Aurora