gnumed-devel
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Gnumed-devel] Restoring a database under Debian - invalid locale na


From: Karsten Hilbert
Subject: Re: [Gnumed-devel] Restoring a database under Debian - invalid locale name "en_CA"
Date: Sat, 24 Jan 2015 00:04:12 +0100
User-agent: Mutt/1.5.23 (2014-03-12)

On Fri, Jan 23, 2015 at 09:51:49PM +0000, Jim Busser wrote:

> > Looks all good so far. The database dump that is restored
> > contains commands to create the database gnumed_v20. We would
> > have to look at the dump to see whether that contains SQL
> > code with the problematic locale setting.
> 
> Ok … I might have finally solved this.
> 
> For future reference, the series of steps that I went through to figure this 
> out are included below, with the output appended at bottom.
> 
> The problem appears to be that the CREATE command in my PostgreSQL dump file 
> specified
> 
>       CREATE DATABASE gnumed_v20 WITH TEMPLATE = template0 ENCODING = 'UTF8' 
> LC_COLLATE = 'en_CA' LC_CTYPE = 'en_CA'; 
> 
> but, for this to work in my Debian VM with PostgreSQL 9.4, it apparently 
> needs to be
> 
>       CREATE DATABASE gnumed_v20 WITH TEMPLATE = template0 ENCODING = 'UTF8' 
> LC_COLLATE = 'en_CA.UTF-8' LC_CTYPE = 'en_CA.UTF-8';
> 
> But I'll need help to debug what is wrong with the replacement tar that I 
> tried to supply to the gnumed restore script. See next post.
> 
> -- Jim
> 
> PS despite that it seems to me that other people can expect to run into the 
> same problem, I could find this nowhere reported. Postgres documentation 
> makes no mention in either of two germaine places and only hints at it in the 
> last:
>       http://www.postgresql.org/docs/9.4/static/app-createdb.html
>       http://www.postgresql.org/docs/9.4/static/sql-createdatabase.html
>       
> http://www.postgresql.org/docs/9.4/static/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED
> 
> ============================================================
> Steps in figuring this out …
> 
> After restarting my Debian VM system which has both PostgreSQL 9.1 (port 
> 5432) and 9.4 (port 5433) installed ...
> 
> - checked that en_CA.UTF-8 is installed and available on my system (locale -a)
> - checked that my current LANG and related environmental variables seem 
> suitably set (locale)
> - checked my current postgres server encoding which, bearing in mind that I 
> did not specify a database, I am inferring to be a default (show 
> server_encoding;)
> - checked the values of any locale categories existing within postgres which 
> I understand are "fixed" at the time of database creation
>       (psql -l in both the old PostgreSQL 9.1 and newer 9.4)
>       (or I could have, from a psql session, run 'select datname, datcollate 
> from pg_database;')
> 
> All of the above seem to be as I would expect. So I then tried two commands 
> that _on surface_ ought to have worked:
> 
> 1. per http://www.postgresql.org/docs/9.4/static/locale.html, from the system 
> postgres user command line, instruct initdb exactly which locale to use:
> 
>       /usr/lib/postgresql/9.4/bin/initdb -D /var/lib/postgresql/9.4/main 
> --locale=en_CA
>       initdb: invalid locale name "en_CA" 
> 
> 
> 2. from inside psql, I tried to issue the same CREATE command as was included 
> in my dump:
> 
>       CREATE DATABASE tempdb WITH TEMPLATE = template0 ENCODING = 'UTF8' 
> LC_COLLATE = 'en_CA' LC_CTYPE = 'en_CA'; 
>       ERROR:  invalid locale name: "en_CA"
> 
> this is when I compared
> 
>       the failed CREATE command's LC_COLLATE ('en_CA') and LC_TYPE ('en_CA')
> 
> to
> 
>       what appears under the 'Collate' and 'Ctype' columns returned by psql -l
>       --> which *includes* the encoding i.e. the 'UTF-8' as in 'en_CA.UTF-8'.
> 
> !!
> 
> ==================================================
> 
> Here was my output:
> 
> address@hidden:~$ locale -a
> C
> C.UTF-8
> en_CA.utf8
> POSIX
> 
> address@hidden:~$ 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=
> 
> su root
> su postgres
> cd ~
> 
> psql -p 5432 -l
>                                   List of databases
>     Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access 
> privileges   
> ------------+----------+----------+-------------+-------------+-----------------------
>  gnumed_v17 | gm-dbo   | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
>  gnumed_v18 | gm-dbo   | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
>  postgres   | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
>  template0  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres   
>        +
>             |          |          |             |             | 
> postgres=CTc/postgres
>  template1  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres   
>        +
>             |          |          |             |             | 
> postgres=CTc/postgres
> (5 rows)
> 
> psql -p 5433 -l
>                                   List of databases
>    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access 
> privileges   
> -----------+----------+----------+-------------+-------------+-----------------------
>  postgres  | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | 
>  template0 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres    
>       +
>            |          |          |             |             | 
> postgres=CTc/postgres
>  template1 | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres    
>       +
>            |          |          |             |             | 
> postgres=CTc/postgres
> (3 rows)
> 
> /usr/lib/postgresql/9.4/bin/initdb -D /var/lib/postgresql/9.4/main 
> --locale=en_CA
>       The files belonging to this database system will be owned by user 
> "postgres".
>       This user must also own the server process.
>       initdb: invalid locale name "en_CA"

Here's the gist of the matter:

        
http://stackoverflow.com/questions/21175324/postgres-encoding-issue-on-create-restore-database

There simply is no foolproof solution to this.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346



reply via email to

[Prev in Thread] Current Thread [Next in Thread]