[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
- Re: [Gnumed-devel] Restoring a database under Debian, (continued)
- Re: [Gnumed-devel] Restoring a database under Debian, Busser, Jim, 2015/01/15
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/15
- Re: [Gnumed-devel] Restoring a database under Debian, Busser, Jim, 2015/01/22
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/22
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/22
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/22
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/22
- Re: [Gnumed-devel] Restoring a database under Debian, Busser, Jim, 2015/01/22
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/22
- Re: [Gnumed-devel] Restoring a database under Debian - invalid locale name "en_CA", Busser, Jim, 2015/01/23
- Re: [Gnumed-devel] Restoring a database under Debian - invalid locale name "en_CA",
Karsten Hilbert <=
- Re: [Gnumed-devel] Restoring a database under Debian - invalid locale name "en_CA", Karsten Hilbert, 2015/01/23
- Re: [Gnumed-devel] Restoring a database under Debian, Busser, Jim, 2015/01/23
- Re: [Gnumed-devel] Restoring a database under Debian, Busser, Jim, 2015/01/24
- Re: [Gnumed-devel] Restoring a database under Debian, Busser, Jim, 2015/01/24
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/24
- Re: [Gnumed-devel] Restoring a database under Debian, Busser, Jim, 2015/01/24
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/25
- Re: [Gnumed-devel] Restoring a database under Debian, Busser, Jim, 2015/01/25
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/14
- Re: [Gnumed-devel] Restoring a database under Debian, Karsten Hilbert, 2015/01/15