BUG #11430: Failing to backup and restore a Windows postgres database, with Norwegian Bokmål locale.

From: asimantov(at)tableausoftware(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11430: Failing to backup and restore a Windows postgres database, with Norwegian Bokmål locale.
Date: 2014-09-15 21:56:46
Message-ID: 20140915215646.2485.73112@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 11430
Logged by: Alon Siman-Tov
Email address: asimantov(at)tableausoftware(dot)com
PostgreSQL version: 9.3.5
Operating system: Windows 7 , Windows 2008
Description:

I am failing to backup/restore a Norwegian-Bokmål database on Windows,
whether the cluster was initialized with English US or Norwegian Bokmål
locale.

The database created within this cluster, has the following locale
settings:
LC_COLLATE = ''
LC_CTYPE = ''

I have backed-up the database using the pg_dump command:
pg_dump.exe -h localhost -p 5432 -U pgadmin -F c -Z 0 -f
"C:/test/bokmaldb.pg_dump" bokmaldb

Then try restoring the pg_dump file using:
pg_restore.exe -h localhost -p 5432 -U pgadmin -C -d
postgres -j 4 "C:/test/bokmaldb.pg_dump"

Fails with this error:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1928; 1262
16384 DATABASE bokmaldb pgadmin
pg_restore: [archiver (db)] could not execute query: ERROR:
invalid byte sequence for encoding "UTF8": 0xe5 0x6c 0x29
Command was: CREATE DATABASE bokmaldb WITH TEMPLATE = template0 ENCODING
= 'UTF8' LC_COLLATE = 'Norwegian (Bokmål)_Norway.1252' LC_CTYPE...

I have tested and failed using the following Windows postgres versions:
9.3.5
9.1.14
9.0.18

Please investigate why Norwegian-Bokmål database cannot be backed-up and
restored.

Set of commands I ran:
initdb.exe -D c:/test/pgsql/data -E UTF8
--locale="Norwegian (Bokmål)_Norway.1252" -A trust -U pgadmin
pg_ctl -D "c:/test/pgsql/data" -l
c:/test/bokmalLocalenblocalePG.log start
psql.exe -h localhost -p 5432 -d postgres -U pgadmin -c
"CREATE DATABASE bokmaldb;"
pg_dump.exe -h localhost -p 5432 -U pgadmin -F c -Z 0 -f
"C:/test/bokmaldb.pg_dump" bokmaldb
psql.exe -h localhost -p 5432 -d postgres -U pgadmin -c
"DROP DATABASE bokmaldb;"
pg_restore.exe -h localhost -p 5432 -U pgadmin -C -d
postgres -j 4 "C:/test/bokmaldb.pg_dump"

Output of running restore:

C:\Program Files\PostgreSQL\9.3\bin>pg_restore.exe -h
localhost -p 5432 -U pgadmin -C -d postgres -j 4 "C:/test/bokmaldb.pg_dump
"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1928; 1262
16384 DATABASE bokmaldb pgadmin
pg_restore: [archiver (db)] could not execute query: ERROR:
invalid byte sequence for encoding "UTF8": 0xe5 0x6c 0x29
Command was: CREATE DATABASE bokmaldb WITH
TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'Norwegian
(Bokmål)_Norway.1252' LC_CTYPE...
pg_restore: [archiver (db)] could not execute query: ERROR:
database "bokmaldb" does not exist
Command was: ALTER DATABASE bokmaldb OWNER
TO pgadmin;

pg_restore: [archiver (db)] could not reconnect to database:
FATAL: database "bokmaldb" does not exist

NOTE: running the following set of commands, that restore database with
Swedish locale works fine:
initdb.exe -D c:/test/pgsql/data -E UTF8 --locale="Swedish_SWeden.1252" -A
trust -U pgadmin
pg_ctl -D "c:/test/pgsql/data" -l c:/test/swdb.log start
psql.exe -h localhost -p 5432 -d postgres -U pgadmin -c "CREATE DATABASE
swdb;"
pg_dump.exe -h localhost -p 5432 -U pgadmin -F c -Z 0 -f
"C:/test/swdb.pg_dump" swdb
psql.exe -h localhost -p 5432 -d postgres -U pgadmin -c "DROP DATABASE
swdb;"
pg_restore.exe -h localhost -p 5432 -U pgadmin -C -d postgres -j 4
"C:/test/swdb.pg_dump"

** The database is restored with following settings:

CREATE DATABASE swdb
WITH OWNER = pgadmin
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'Swedish_Sweden.1252'
LC_CTYPE = 'Swedish_Sweden.1252'
CONNECTION LIMIT = -1;

Browse pgsql-bugs by date

  From Date Subject
Next Message asimantov 2014-09-15 23:04:27 BUG #11431: Failing to backup and restore a Windows postgres database, with Norwegian Bokmål locale.
Previous Message Zhong, Van 2014-09-15 10:55:36 RE: BUG #11425: Trigger UDF doesn’t properly preserve tuple’s descriptors after "add/drop column" on target table.