Re: ERROR: new collation (en_US.UTF-8) is incompatible with the collation of the template database (en_US.utf-8)

On 22.06.22 21:25, Adrian Klaver wrote:
> On 6/22/22 12:17, Tomas Pospisek wrote:
>> Hi all,
>>
>> while doing `cat pg_dump.dump | psql` I get the above message. Note
>> that `pg_dump.dump` contains:
>>
>>      CREATE DATABASE some_db WITH TEMPLATE = my_own_template ENCODING
>> = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
>>
>> What is exactly the problem? I understand that en_US.UTF-8 and
>> en_US.utf-8 is not *exactly* the same string.
>>
>> However I do not understand how the difference came to be. And I do
>> not know what the "right" way is and how to proceed from here.
>>
>> If I `pg_dump --create` some DB on the new server (13.7-1.pgdg18.04+1)
>> I get:
>>
>>      CREATE DATABASE ... ENCODING = 'UTF8' LOCALE = 'en_US.utf-8';
>>
>> When I do the same on the old server (12.8-1.pgdg20.04+1) I get:
>>
>>      CREATE DATABASE ... ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8'
>> LC_CTYPE = 'en_US.UTF-8';
>>
>> Any hints or help?
>
> Are dumping/restoring from one version of Postgres to another?

Yes, indeed!

> If from older to newer then use the new version of pg_dump(13) to dump
> the older(12) database. Then the 13 version of restore to load the
> version 13 database.

I will. Thanks a lot Adrian!
*t