Trying a Postgresql Migration but can not create DB

[pnelsonsr]

Setup Stash and its working fine on Fedora 18 with integrated db.

I started trying to setup the postgresql db (already have postgresql working on this system). I'm getting a error and I can not figure out how to get it working.

When I run:

CREATE DATABASE stash WITH ENCODING='UTF8' OWNER=stashuser CONNECTION LIMIT=-1;

I get:

ERROR:  encoding "UTF8" does not match locale "en_US"
DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".

and it does not create the db. My local output is:

[ss ~]$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
...

Anyone know what steps I need to do to address this? Will it change my other dbs?

Oh and in psql a '\l' lists other dbs with encoding of LATIN1.

[pnelsonsr · Accepted solution]

Went into psql and did a \h create database and used that to add all the proper "WITH" statements and ultimately got it working with:

sec=# CREATE DATABASE stash WITH OWNER=stashuser TEMPLATE=template0 ENCODING='UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1;

Thanks to Marcus Silveira for giving me hope it was just a statement away from success.

[Marcus Silveira]

Hi Patrick,

Try setting the LC_CTYPE encoding explicitly in the create database statement

CREATE DATABASE stash WITH ENCODING='UTF8' LC_CTYPE='en_US.UTF-8' OWNER=stashuser CONNECTION LIMIT=-1;

Hope this helps,

Marcus

[pnelsonsr]

Well at least I get a new error:

ERROR:  encoding "UTF8" does not match locale "en_US"
DETAIL:  The chosen LC_COLLATE setting requires encoding "LATIN1".

[pnelsonsr]

This is a standard installation of F18 and ProstgreSQL 9.2.3 so someone must know how this is set?

[Marcus Silveira]

Hi Patrick,

Try adding the LC_COLLATE to the create command also.

CREATE DATABASE stash WITH ENCODING='UTF8' LC_CTYPE='en_US.UTF-8' LC_COLLATE='en_US.UTF-8' OWNER=stashuser CONNECTION LIMIT=-1;

[pnelsonsr]

This produced:

ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (LATIN1)
HINT:  Use the same encoding as in the template database, or use template0 as template.

so I messed around and got it working with template0.

Thanks so much for helping me go down the correct path.