Postgres in alpine has collation warning if database was created in another OS.

[kviset]

Postgres shows warning WARNING: database "postgres" has no actual collation version, but a version was recorded if database was created in another OS.

Steps for reproduce:

1. Execute postgres in debian image: docker run --rm -e POSTGRES_PASSWORD=password -e POSTGRES_DB=app -v $(pwd)/data:/var/lib/postgresql/data postgres:15.8-bookworm
2. Stop this
3. Execute postgres in alpine image: docker run --rm --name postgres -v $(pwd)/data:/var/lib/postgresql/data postgres:15.8-alpine3.20
4. Run psql:

$ docker exec -ti --user postgres postgres psql
WARNING:  database "postgres" has no actual collation version, but a version was recorded
psql (15.8)
Type "help" for help.

postgres=# \c app
WARNING:  database "app" has no actual collation version, but a version was recorded
You are now connected to database "app" as user "postgres".
app=# 

Research:

I found that a warning occurs in the CheckMyDatabase function when comparing the collate version in the database and the current one in this row.

...
	if (!isnull)
	{
		char	   *actual_versionstr;
		char	   *collversionstr;

		collversionstr = TextDatumGetCString(datum);

		actual_versionstr = get_collation_actual_version(dbform->datlocprovider, dbform->datlocprovider == COLLPROVIDER_ICU ? iculocale : collate);
		if (!actual_versionstr)
			/* should not happen */
			elog(WARNING,
				 "database \"%s\" has no actual collation version, but a version was recorded",
				 name);
		else if (strcmp(actual_versionstr, collversionstr) != 0)
...

After calling get_collation_actual_version, the actual_version_str variable is NULL, because the get_collation_actual_version function after the preprocessor looks like:

char *
get_collation_actual_version(char collprovider, const char *collcollate)
{
	char	   *collversion = NULL;

		if (collprovider == COLLPROVIDER_LIBC &&
			pg_strcasecmp("C", collcollate) != 0 &&
			pg_strncasecmp("C.", collcollate, 2) != 0 &&
			pg_strcasecmp("POSIX", collcollate) != 0)
	{
	}

	return collversion;
}

And it always returns NULL

[tianon]

My best guess is that this is due to the combination of https://wiki.musl-libc.org/open-issues#Locale-limitations and Alpine not being an officially supported platform in PostgreSQL (that I've seen). 😞

[ardentperf]

postgres images with different base OS's are not 100% safely interchangeable (even between bullseye and bookworm), in some cases you can ignore the warning but be careful because there are cases where ignoring it can lead to database corruption (wrong query results, apparently missing data, duplicate primary keys, etc)

cf #276 and also #1099 (comment)

https://wiki.postgresql.org/wiki/Locale_data_changes