Thread: restore crashes PG on Linux, works on Windows

On Tue, Nov 26, 2013 at 09:25:17AM -0500, Chris Curvey wrote:
>
> CREATE DATABASE "TestDatabase" WITH TEMPLATE = template0 ENCODING = 'UTF8'
> LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United
> States.1252';

Guess guessing, but I bet the collation is what hurts, just because
that collation causes problems generally.  Maybe you could get them to
ditch that in favour of something else?  LC_COLLATE = en_US.UTF-8
would perhaps be better.

(The background for my guess: on your Linux box UTF-8 is likely the
normal local encoding, but on Windows that isn't true, and 1252 is
_almost_ but not quite Unicode.  This bites people generally in
internationalization.)

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Chris Curvey wrote:
> My vendor took a dump of our "something else" database (which runs on Windows), did their conversion
> to Postgres, and then sent me back a postgres dump (custom format) of the database for me to load onto
> my servers for testing.
> 
> 
> I was interested to find that while I can load the dump onto a PG 9.3 server running on Windows, I'm
> unable to load it on either 9.2 or 9.3 running on Linux.  At some point during the restore process
> (and it's not a consistent point), PG on Linux crashes.

You mean, the database server dies?
Or that there is an error message?
If it is the latter, can we see the error message?

> I suspect that the problem is related to the encoding specified in the database dump:
> 
> CREATE DATABASE "TestDatabase" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE =
> 'English_United States.1252' LC_CTYPE = 'English_United States.1252';

Yes, that should throw an error on a Linux system.
But you should get that error consistently, different from
what you write above.

> So my questions for the brain trust are:
> 
> 
> 1) Would you expect this to work?

No, as stated above.

> 2) If I had to load this database on Linux, what would be the best way to go about it?  (see if I can
> find that charset/encoding for Linux?  Ask the vendor for a plain-text dump? )

You can create the database beforehand and ignore the one error
from pg_restore.

You can convert the custom format dump into an SQL file with
pg_restore -f dumpfile.sql dumpfile.dmp

Yours,
Laurenz Albe

Andrew Sullivan wrote:
> Guess guessing, but I bet the collation is what hurts, [...]

> (The background for my guess: on your Linux box UTF-8 is likely the
> normal local encoding, but on Windows that isn't true, and 1252 is
> _almost_ but not quite Unicode.  This bites people generally in
> internationalization.)

I beg your pardon, but Windows-1252 has nothing to do with Unicode
or UTF-8.  The only connection is that Windows-1252 and UTF-8 both
are ASCII supersets.

Yours,
Laurenz Albe

On Tue, Nov 26, 2013 at 02:48:34PM +0000, Albe Laurenz wrote:
> I beg your pardon, but Windows-1252 has nothing to do with Unicode

Sorry, you're quite right, I'm having a brain fade (I meant ISO
8859-1, of course).

The point I wanted to make, however, is that the collation often
causes trouble with UTF-8 encoding.  I liked your conversion
suggestion, however, in your other mail.

A

--
Andrew Sullivan
ajs@crankycanuck.ca