Restore PostgreSQL database with same locale settings as source database

Question

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.

• Sahap Asci: Just create db with --encoding=UTF8. After that run an ordinary restore. Finally change your client connection parameters. Ref: Locale and Formatting
• Abdull: @SahapAsci But client connections won't change the database's configured locales, right?
• Sahap Asci: 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.

Answer 1

These are the exact steps I take to import a copy of a PostgreSQL 9.5 database (exported on Linux using en_US.UTF-8 encoding) into PostgreSQL 9.3 on Windows 7 or PostgreSQL 9.5 on Windows 8.1. You need to create the database with the appropriate encoding prior to loading the SQL file, otherwise the encoding from Linux (en_US.UTF-8) will prompt Windows to use a default encoding.

REM WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details.
chcp 1252
C:\PostgreSQL9円.5\bin\psql.exe -U postgres -d postgres_password -c "CREATE DATABASE my_db WITH TEMPLATE=template0 ENCODING='UTF-8' LC_COLLATE='american_usa' LC_CTYPE='american_usa'"
REM Grant privileges to the appropriate database user.
C:\PostgreSQL9円.5\bin\psql.exe -U postgres -d postgres_password -c "GRANT ALL PRIVILEGES ON DATABASE my_db TO myuser"
REM Import my_db.sql. Ignore the following error (it comes from a line in the SQL file, but we have already created the database with the correct locale, so we are ok):
REM psql:my_db.sql:22: ERROR: invalid locale name: "en_US.UTF-8"
C:\PostgreSQL9円.5\bin\psql.exe -o nul --quiet -U postgres -d postgres_password -f my_db.sql

As discussed here, the locale on Windows should be american_usa (on Windows 7). On Windows 8.1, either american_usa or en-US (not en_US as with Unix) will work.

• Luiz Vaz: This really works.