2

Postgres 17 cluster is in Windows server in Estonian locale. Databases are defined like

CREATE DATABASE mydb
    WITH
    OWNER = mydb_owner
    ENCODING = 'UTF8'
    LC_COLLATE = 'et-EE'
    LC_CTYPE = 'et-EE'
    LOCALE_PROVIDER = 'libc'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

Trying to move this to Postgres 18 running in Debian

pg_dumpall --clean -h windowsserver.com -U postgres | psql --echo-errors -h localhost -U postgres

Throws

 ERROR:  invalid LC_COLLATE locale name: "et-EE" 
 STATEMENT:  CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8'
 LOCALE_PROVIDER = libc LOCALE = 'et-EE';

locale -a shows that Estonian locale is present:

C
C.utf8
en_US.utf8
et_EE.utf8
POSIX

How to move cluster to Postgres 18 ?

Improve this question
3
  • 3
    See this thread postgresql.org/message-id/…, in particular postgresql.org/message-id/…. Commented 18 hours ago
  • I'm new to Linux. What would be sed command to add to pipe to perform locale substitution? There is only single database which needs to be moved. pg_database_size shows its size 64GB. Commented 18 hours ago
  • 1
    From pg_dumpall: --schema-only Dump only the object definitions (schema), not data.. That should be fairly small and you can directly edit the CREATE DATABASE section. Then --data-only Dump only the data, not the schema (data definitions) or statistics. to transfer the data. Commented 18 hours ago

1 Answer 1

Reset to default
2

The easiest solution might be to first move the global objects:

pg_dumpall -g -h windowsserver.com -U postgres | psql

Then manually create the database with:

CREATE DATABASE mydb
   OWNER mydb_owner
   TEMPLATE template0
   ENCODING UTF8
   LOCALE_PROVIDER icu
   ICU_LOCALE "et-EE"
   LOCALE "et_EE.utf8";

This requires that you have created the Estonian locale on your Debian system.

Now you can dump and restore the database:

pg_dump -F c -Z 0 -h windowsserver.com -U postgres mydb | pg_restore -d mydb
Improve this answer
2
  • Why LOCALE "et_EE.utf8" is used ? According to doc ICU_LOCALE "et-EE" sets it. Commented 4 hours ago
  • No, it doesn't. If you don't specify LOCALE, you'll get the default C library locale of your PostgreSQL cluster. Commented 3 hours ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.