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 22:18, Tomas Pospisek wrote:
> 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!

So I used both pg_dump and pg_restore from the newer machine. Result is 
still the same.  So I'll use Tom Lane's suggestion too and fix the 
'UTF-8' spelling in the dump file:

Tom Lane wrote:

 > This is probably more about dumping from different operating systems.
 > The spelling of the locale name is under the control of the OS,
 > and Postgres doesn't know very much about the semantics of it
 > (so I think we conservatively assume that any difference in
 > spelling is significant).
 >
 > Best bet might be to edit the dump file to adjust the locale
 > spellings to match your new system.

Many thanks to both Tom & Adrian!!!
*t