Debian install postgresql with UTF-8

[Michael Pope]

Hello,

I'm trying to install PostgreSQL 9.1 with UTF-8 encoding under Debian 7
and it keeps defaulting to SQL_ASCII.

I just install it using this command:
sudo apt-get install postgresql

Here are my environment variables:
TERM=xterm
SHELL=/bin/bash
XDG_SESSION_COOKIE=41708c93d52893509979c6fa51997d4d-1369719888.998123-2075797182
SSH_TTY=/dev/pts/2
USER=map7
LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lz=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=00;36:*.au=00;36:*.flac=00;36:*.mid=00;36:*.midi=00;36:*.mka=00;36:*.mp3=00;36:*.mpc=00;36:*.ogg=00;36:*.ra=00;36:*.wav=00;36:*.axa=00;36:*.oga=00;36:*.spx=00;36:*.xspf=00;36:
RBENV_ROOT=/usr/local/rbenv
MAIL=/var/mail/map7
PATH=/usr/local/rbenv/shims:/usr/local/rbenv/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
PWD=/home/map7
LANG=en_AU.UTF-8
SHLVL=1
HOME=/home/map7
LANGUAGE=en_AU:en
LOGNAME=map7
SSH_CONNECTION=192.168.200.4 38833 192.168.200.198 22
LC_CTYPE=en_AU.UTF-8
DISPLAY=localhost:10.0
_=/usr/bin/env

I've worked out a way to set it after but it removes all databases and
isn't something I want to use in my system build scripts
sudo pg_dropcluster --stop 9.1 main
sudo pg_createcluster --start -e UTF-8 9.1 main

Is there a nicer way of doing this?
Am I missing an environment variable?

from
Michael

[Brian May]

On 28 May 2013 16:00, Michael Pope <map...@gmail.com> wrote:

I'm trying to install PostgreSQL 9.1 with UTF-8 encoding under Debian 7 and it keeps defaulting to SQL_ASCII.

I have installed postgres on several Debian 7.0 (Wheezy) boxes and they always seem to default to UTF-8.

Are you sure your is SQL_ASCII?

[Michael Pope]

Yes. If I list my tables just after installing all tables are set to SQL_ASCII.

If I try and create a database with encoding UTF-8 it fails. If I do my work around then I can create UTF-8 databases.

From
Mick

--
You received this message because you are subscribed to the Google Groups "mlug-au" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mlug-au+u...@googlegroups.com.
To post to this group, send email to mlu...@googlegroups.com.
Visit this group at http://groups.google.com/group/mlug-au?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Michael Pope]

On 28 May 2013 16:24, "Brian May" <br...@microcomaustralia.com.au> wrote:

On 28 May 2013 16:00, Michael Pope <map...@gmail.com> wrote:

I'm trying to install PostgreSQL 9.1 with UTF-8 encoding under Debian 7 and it keeps defaulting to SQL_ASCII.

I have installed postgres on several Debian 7.0 (Wheezy) boxes and they always seem to default to UTF-8.

Are you sure your is SQL_ASCII?

I tried installing PostgresSQL on another one of my Debian 7 machines last night and it defaulted to UTF-8 so it must be something in this one machine which is making it install as SQL_ASCII.

from
Mick

[Brian May]

On 29 May 2013 09:00, Michael Pope <map...@gmail.com> wrote:

I tried installing PostgresSQL on another one of my Debian 7 machines last night and it defaulted to UTF-8 so it must be something in this one machine which is making it install as SQL_ASCII.

Most likely culprit I think would be /etc/postgresql/9.1/main/postgresql.conf (?)

If you can't see it there, suggest you run:

grep -i ASCII /etc/postgresql/9.1/main/*

I get one (commented out) result:

/etc/postgresql/9.1/main/postgresql.conf:#client_encoding = sql_ascii           # actually, defaults to database

[Michael Pope]

>
> Most likely culprit I think would
> be /etc/postgresql/9.1/main/postgresql.conf (?)
>
> If you can't see it there, suggest you run:
>
> grep -i ASCII /etc/postgresql/9.1/main/*
>
> I get one (commented out) result:
>
> /etc/postgresql/9.1/main/postgresql.conf:#client_encoding = sql_ascii
> # actually, defaults to database
>

Mine looks the same.

I've narrowed the problem down to they way I install postgresql. When I
install it using Ansible it installs it as ASCII if I install it
manually it installs UTF-8. So there must be an environment variable
not being set when using Ansible to install.

What are the environment variables which postgresql installation
requires so that it installs the UTF-8 configuration?

from
Mick

[Michael Pope]

Solved:
Had to set my locale through my ansible scripts when installing
postgresql.

- name: workaround to install postgresql with locale set
shell: LANG=$locale LC_COLLATE=$locale LC_CTYPE=$locale
LC_MESSAGES=$locale LC_MONETARY=$locale LC_NUMERIC=$locale
LC_TIME=$locale LC_ALL=$locale apt-get install -y postgresql

from
Michael

[David Schoen]

Alternatively you can just use template0 (as the HINT suggests):

postgres@foo:~$ createdb -E UTF-8 somedb
createdb: database creation failed: ERROR: new encoding (UTF8) is
incompatible with the encoding of the template database (SQL_ASCII)
HINT: Use the same encoding as in the template database, or use
template0 as template.
postgres@foo:~$ createdb -E UTF-8 somedb -T template0
postgres@foo:~$ psql -l | grep somedb
somedb | postgres | UTF8 | C | C |

... probably a bit late now.

- Dave