I have a PostreSQL 9.x database on an Ubuntu 14.04 LTS production machine. My development machine is Windows 7-based, providing PostreSQL 9.y.
I want to restore the Ubuntu PostreSQL database on my development machine.
I noticed that the Ubuntu database uses the following locale settings:
- character set encoding=
UTF8
- collation order, string sort order=
en_US.UTF-8
- character type, character classification=
en_US.UTF-8
When I restore the database on the Windows machine without specifying locales, it will be set up with
- character set encoding=
UTF8
- collation order, string sort order=
German_Germany.1252
- character type, character classification=
German_Germany.1252
My plan is to have the database on my development machine as similar as possible to the database on my Ubuntu machine. So my idea was to first create a database on my Windows machine with production-matching locales, then restore my Ubuntu database prod-db.backup
backup file into that created database:
createdb --host=localhost --username=postgres --encoding=Unicode --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 --owner=prod prod-db
pg_restore --host=localhost --username=postgres --format=custom prod-db.backup --dbname=prod-db
This ideas does not work as the createdb
on Windows will complain with the error invalid locale name en_US.UTF-8.
I went on a hunt to find the Windows locale names that match the Ubuntu locale names, including a solution to use the template0 template database, experimenting with different locale identifiers such as en_US.UTF-8
and en_us_utf8
I found scattered in the Internet ... but no solution works.
- Is there a locale identifier for Windows that matches Ubuntu's
en_US.UTF-8
? - Or is locale
German_Germany.1252
identical (enough) toen_US.UTF-8
so that I can stick to it and not worry about locales - I want to make sure that database queries behave identical when it comes to aspects such as query result set ordering.