Re: could not determine encoding for locale "et_EE.UTF-8": codeset is"CPUTF-8" in pg_restore

On 3/29/20 11:12 AM, Andrus wrote:
> Hi!
> 
>> There seems to a difference of opinion of what Baltic Code Page to use:
>> https://en.wikipedia.org/wiki/Code_page_775
>> https://en.wikipedia.org/wiki/Windows-1257
>> The post below shows a users method of dealing with this for another CP:
>> https://www.postgresql.org/message-id/549275CC.4010607%40gmail.com
> 
> Console code page 775 message appears since psql is console application 
> running from command line.
> 
> It does not have any relation to pg_dump/pg_restore issue since console 
> code page is not used in this case.
> 
> There is Estonian locale everywhere.
> Maybe this warning is harmless since Linux code page is ignored and 
> default collation is used.
> All table definitions in restored database contain references to default 
> collation:
> 
> CREATE TABLE firma1.acquirpo
> (
>     kassanr numeric(3,0) NOT NULL,
>     policyid character(2) COLLATE pg_catalog."default" NOT NULL,
>     trantype character(6) COLLATE pg_catalog."default",
>     tacdefault character(10) COLLATE pg_catalog."default",
>     tacdenial character(10) COLLATE pg_catalog."default",
>     taconline character(10) COLLATE pg_catalog."default",
>     floorlimit numeric(12,0),
>     randselthr numeric(12,0),
>     minrandper numeric(2,0),
>     maxrandper numeric(2,0),
>     CONSTRAINT acquirpo_pkey PRIMARY KEY (kassanr, policyid)
> )
> 
> TABLESPACE pg_default;
> 
> Same warning appears two times. This command execute by pg_restore 
> probably causes this (harmless?)  warning:

What warning?

I cranked up a Windows 7 instance and tried to migrate a Postgres 11 
database from Ubuntu and it failed on the CREATE DATABASE step because 
of this line in the dump file:

CREATE DATABASE redmine WITH TEMPLATE = template0 ENCODING = 'UTF8' 
LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

When I manually changed it in the plain text version of the dump file to:

CREATE DATABASE redmine WITH TEMPLATE = template0 ENCODING = 'UTF8' 
LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United 
States.1252';

borrowing from Tom's suggestion here:

https://www.postgresql.org/message-id/22672.1585442578%40sss.pgh.pa.us

it worked.

So basically what you see below.

If you followed Tom's suggestion fully you could restore into a manually 
created database on the Windows side by dropping the --create and 
pointing -d at the previously created database.

> 
> CREATE DATABASE mydb
>     WITH
>     OWNER = mydb_owner
>     ENCODING = 'UTF8'
>     LC_COLLATE = 'et_EE.UTF-8'
>     LC_CTYPE = 'et_EE.UTF-8'
>     TABLESPACE = pg_default
>     CONNECTION LIMIT = -1;
> 
> If new database is created manually in windows cluster the following 
> command is generated:
> 
> 
> CREATE DATABASE mydbmanually
>     WITH
>     OWNER = mydbmanually_owner
>     ENCODING = 'UTF8'
>     LC_COLLATE = 'Estonian_Estonia.1257'
>     LC_CTYPE = 'Estonian_Estonia.1257'
>     TABLESPACE = pg_default
>     CONNECTION LIMIT = -1;
> 
> Andrus.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com