Unsupported locale

[robertoschwald]

While trying to migrate a 9.6.11-alpine db to 13, I receive the following error:

connection to database failed: FATAL:  database locale is incompatible with operating system
DETAIL:  The database was initialized with LC_COLLATE "utf-8",  which is not recognized by setlocale().
HINT:  Recreate the database with another locale or install the missing locale.
Failure, exiting

This is the locale / collation of the old db: CREATE DATABASE xx WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'utf-8' LC_CTYPE = 'UTF-8';

[jonrot1906]

Just include i.e. --locale=C.UTF-8 in -e POSTGRES_INITDB_ARGS="". Example, adapted from @jooize:

POSTGRES_USER="synapse"
POSTGRES_PASSWORD="synapse-password"
PG_LOCALE="C.UTF-8"
OLD=11
NEW=12
OLD_DATA="/matrix/postgres/data-auto-upgrade-backup"
NEW_DATA="/matrix/postgres/data"

sudo docker run --rm \
-e PGUSER="$POSTGRES_USER" \
-e POSTGRES_INITDB_ARGS="-U $POSTGRES_USER --locale=$PG_LOCALE" \
-e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
-v "$OLD_DATA":"/var/lib/postgresql/$OLD/data" \
-v "$NEW_DATA":"/var/lib/postgresql/$NEW/data" \
"tianon/postgres-upgrade:$OLD-to-$NEW"

[robertoschwald]

Does not work here.

sudo docker run --rm \
-e PGUSER=mydbuser \
-e POSTGRES_INITDB_ARGS='-U mydbuser --locale=C.UTF-8' \
-e POSTGRES_PASSWORD="xxx" \
-v /DB_MIGRATION/9.6:/var/lib/postgresql/9.6/data \
-v /DB_MIGRATION/13:/var/lib/postgresql/13/data \
tianon/postgres-upgrade:9.6-to-13

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/13/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/13/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
connection to database failed: FATAL:  database locale is incompatible with operating system
DETAIL:  The database was initialized with LC_COLLATE "utf-8",  which is not recognized by setlocale().
HINT:  Recreate the database with another locale or install the missing locale.
Failure, exiting

[jonrot1906]

I just realized that I misread your logs. Basically, postgresql tells you that your database was initialized with an unknown LC_COLLATE value, which was set to "utf-8" (instead of i.e. C or C.UTF-8). To my knowledge, there's now way to get that right after creation. So before upgrading your database, you will have to drop the old database, create a new one and update your new one with your old data. Follow i.e. this tutorial.

Afterwards you should be able to update to a new version using this dockerized solution.

Sorry again for this confusion! My provided solution only works if you created a database with known locale settings.

[luckyguido]

hi, i'm facing a similar problem that should be fixed by the script you suggested, but it seems that POSTGRES_INITDB_ARGS is ignored. I get this error:

lc_collate values for database "postgres" do not match:  old "en_US.utf8", new "C.UTF-8"

setting PG_LOCALE as en_US.utf8, this is my scirpt:

POSTGRES_USER="*********"
POSTGRES_PASSWORD="*********"
PG_LOCALE="en_US.utf8"
OLD=10
NEW=13
OLD_DATA="/pgdata"
NEW_DATA="/pgdata-next"

sudo docker run --rm \
-e PGUSER="$POSTGRES_USER" \
-e POSTGRES_INITDB_ARGS="-U $POSTGRES_USER --locale=$PG_LOCALE" \
-e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
-v "$OLD_DATA":"/var/lib/postgresql/$OLD/data" \
-v "$NEW_DATA":"/var/lib/postgresql/$NEW/data" \
"tianon/postgres-upgrade:$OLD-to-$NEW"

thank you for your support.