Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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) to en_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.
share|improve this question
    
Just create db with --encoding=UTF8. After that run an ordinary restore. Finally change your client connection parameters. Ref: Locale and Formatting – Sahap Asci Jun 27 '16 at 11:39
    
@SahapAsci But client connections won't change the database's configured locales, right? – Abdull Jun 27 '16 at 15:26
    
no, for sure. encoding is utf8. that means your saved strings will be always the same. lc_* settings are for display formats and orderings and actually its for client. if you set it from server side and a client connect without providing any lc_* settings then it will take the default, server provided ones. – Sahap Asci Jun 27 '16 at 23:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.