Re: Restoring a database dump from 9.0 to 9.2 - Mailing list pgsql-general

From Ben Madin
Subject Re: Restoring a database dump from 9.0 to 9.2
Date
Msg-id -4013696261063102700@unknownmsgid
Whole thread Raw
In response to Restoring a database dump from 9.0 to 9.2  (Jay McGaffigan <hooligan495@gmail.com>)
List pgsql-general
If Tom's suggestion doesn't work, can you do your text dump by schema,
or for a subset of tables, and see if you can isolate the problem
table. (using the -n or -t options)

Have you changed the locale / languages settings between db versions?
If you find a quoting problem in a very large table you can run it
through sed to quote the offending bits

If you can dump from 9.0 and restore into 9.1, can you dump from 9.1
and try restoring it into 9.2?

Cheers

Ben



--=20

Ben Madin

t: +61 8 6102 5535
m: +61 448 887 220

Sent from my iPhone, hence the speling...

On 09/02/2013, at 4:46, Jay McGaffigan <hooligan495@gmail.com> wrote:

> Hi,
>  I've been trying to restore a fairly sizeable database dump from my prod=
uction server onto my dev box.
> Recently upgraded to 9.2.2 and wanted to try it out.
>
> So I grabbed a text dump of the database and tried the "Createdb dbname; =
psql < dmpfile" way of restoring that's always worked for me before upgradi=
ng my dev box and I'm getting errors on import.  Some of my columns have 'r=
ich text' (carriage returns, XML and other markup) in it and I suspect they=
 are causing the issues (basically the errors I'm seeing seem to imply that=
 the text formatting is getting out of wack I'm suspecting due to carriage =
returns embedded in the dump file).    This causes lots of errors as the pr=
ocessing of the file is now out of sync.
>
> I had been able to load this same file under PSql 9.1.
>
> If I get a binary dump file that I need to use something like pg_restore =
with .  it runs for over 12 hrs locks up my mac adn uses all system memory =
(i've 16G RAM on my system)
>
> This db is like 30G in size.
>
> Any one have any debugging advice?  I'm thinking if I can use the text ba=
sed dump that is created with proper escaping then things might work.  But =
so far reading documentation I haven't really figured out if this is a viab=
le path.
>
> If this is not a good way to do it I'm open for any and all suggestions.
>
> Thanks!
> Jay

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Restoring a database dump from 9.0 to 9.2
Next
From: Wells Oliver
Date:
Subject: Calling ROUND w/o a numeric cast blowing up all connections (9.1)