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(dot)klaver(at)aklaver(dot)com