2

Context

I am deploying a Vagrant box for my development team, and have encountered a problem restoring UTF-8 collated dumps. I don't believe it is related to the now-infamous "UTF8" has no equivalent in "LATIN1" problem, as my issue seems to lie in the client. Additionally, I have successfully restored exactly the same dump files on other Precise boxes (though precise64) with the same client and server packages (9.1). However, I am uncertain if other supporting libraries may not be present in my Vagrant box.

I can not alter the way in which the dump files are created, but I don't believe the problem lies there, anyway.

Problem

I successfully programatically create and restore a UTF-8 (specifically, en_CA.UTF-8) dump by creating a table with:

createdb --lc-collate=en_CA.UTF-8 --lc-ctype=en_CA.UTF-8 -E UTF8 -T template0 ${SCHEMA}

(If relevant, I'll add that my other successful restores (on the precise64 system) did not require any additional locale parameters to createdb.)

Then restore,

pg_restore -d ${SCHEMA} --single-transaction /tmp/${SCHEMA}_-_latest.backup

I have attempted with both postgresql.conf's client_encoding commented out and manually set to client_encoding = 'UTF8'; both behave the same.

My /etc/default/locale contains

LC_ALL=en_CA.UTF-8
LANG=en_CA.UTF-8

And yet, at the psql prompt:

schema=# SELECT * FROM table;
ERROR:  character 0xe28099 of encoding "UTF8" has no equivalent in "LATIN1"

schema=# show server_encoding; show client_encoding;
 server_encoding 
-----------------
 UTF8
(1 row)

 client_encoding 
-----------------
 LATIN1
(1 row)

The schema is UTF-8, right (so the commented-out client_encoding should use this value)?

schema=# SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = ...
pg_encoding_to_char 
---------------------
 UTF8
(1 row)

Setting it manually at the command prompt works:

schema=# set client_encoding to UTF8
SET
schema=# show client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)

schema=# SELECT * FROM table;
<EXPECTED DATA>

What have I done wrong?

EDIT for Craig:

vagrant@precise32:~$ locale
LANG=en_CA.UTF-8
LANGUAGE=
LC_CTYPE="en_CA.UTF-8"
LC_NUMERIC="en_CA.UTF-8"
LC_TIME="en_CA.UTF-8"
LC_COLLATE="en_CA.UTF-8"
LC_MONETARY="en_CA.UTF-8"
LC_MESSAGES="en_CA.UTF-8"
LC_PAPER="en_CA.UTF-8"
LC_NAME="en_CA.UTF-8"
LC_ADDRESS="en_CA.UTF-8"
LC_TELEPHONE="en_CA.UTF-8"
LC_MEASUREMENT="en_CA.UTF-8"
LC_IDENTIFICATION="en_CA.UTF-8"
LC_ALL=
4

psql detects the client_encoding from the LC_CTYPE variable in the environment; this falls back to LC_ALL and then LANG if unset.

In the terminal you're launching psql from, run locale. e.g.

$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

Paste the result as an edit to your question. I strongly suspect your terminal locale will be en_CA, not en_CA.UTF-8.

  • Great thinking, Craig. Unfortunately…it is indeed en_CA.UTF-8 (full locale edit added). – msanford Feb 10 '14 at 21:19
  • Thought I am ashamed to admit it, I wonder if I neglected to re-source the new locale in my virtual machine, as it works now. – msanford Feb 10 '14 at 21:24
0

Edited for formatting, locale env and clarifying NOT TO USE SQL_ASCII as pointed out by @Craig Ringer (upvote 1, don't have enough reputation :) )

This worked for me (excuse the actual encoding used :) )

1. comment it in postgresql.conf file
#client_encoding = 'UTF-8' # actually, defaults to database 
                           # encoding
2. export LANG=''
**************** this is what was tested ****************

postgres@host> locale 
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
....
LC_ALL=
postgres@host> psql
psql (9.2.4)
Type "help" for help.
postgres=# \c db
You are now connected to database "db" as user "postgres".
db=# show server_encoding; show client_encoding;
server_encoding
-----------------
SQL_ASCII
(1 row)
client_encoding
-----------------
UTF8
(1 row)

db=# \q
postgres@host> export LANG=''

postgres@host> psql 
psql (9.2.4)
Type "help" for help.
postgres=# \c db
You are now connected to database "db" as user "postgres".
db=# show server_encoding; show client_encoding;
server_encoding
-----------------
SQL_ASCII
(1 row)
client_encoding
-----------------
SQL_ASCII
(1 row)

> locale 
LANG=
LC_CTYPE="POSIX"
....
LC_ALL=
  • Thanks for your reply, but I don't think that will work for me: "In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting, because PostgreSQL will be unable to help you by converting or validating non-ASCII characters." – msanford Feb 3 '14 at 16:55
  • Additionally, I want my LANG environment variable to be en_CA.UTF-8. Clever, though. – msanford Feb 3 '14 at 16:57
  • 1
    SQL_ASCII is a bad idea. It wouldn't be supported if the SQL spec didn't require it. Please don't advise its use without explaining the risks and problems carefully. (Also, please read the markdown formatting guide for stack overlfow). – Craig Ringer Feb 4 '14 at 0:56
  • yes SQL_ASCII is a bad idea, hence my comment earlier about "excuse the encoding" :) this is an old project i've inherited - we are moving to uft8. I'll post the environment setting as a separate comment. Apologies for the formatting, will spend time on that too. – amacvar Feb 6 '14 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.