Re: [Gnumed-devel] Restoring a database under Debian - invalid locale name "en_CA"

[Busser, Jim]

On 2015-01-22, at 1:45 PM, Karsten Hilbert <address@hidden>  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.
> 
> Karsten

On 2015-01-22, at 1:45 PM, Karsten Hilbert <address@hidden> 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"

-- Jim