Thread: Restoring a database dump from 9.0 to 9.2

Hi,
  I've been trying to restore a fairly sizeable database dump from my
production 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
upgrading my dev box and I'm getting errors on import.  Some of my columns
have 'rich 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 processing 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
based 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
viable path.

If this is not a good way to do it I'm open for any and all suggestions.

Thanks!
Jay

On 02/08/2013 12:43 PM, Jay McGaffigan wrote:
> Hi,
>   I've been trying to restore a fairly sizeable database dump from my
> production 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 upgrading my dev box and I'm getting errors on import.  Some of
> my columns have 'rich 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 processing of the file is
> now out of sync.
Posting the actual errors would be a good start. Also, are you using the
9.2 version of pg_dump or trying to restore a dump you took with 9.0
tools into 9.2? If you are upgrading, you should always use the dump
tools from the *new* version.
>
> 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)
Pg_restore from a custom dump *should* be faster - especially if you are
able to use the parallel restore features. What do you mean "locks up my
mac"? Are you sure you don't have a hardware issue? (Note: I've had more
than one server with bad RAM that was not found after a week-long
burn-in with memtest but caused repeatable crashes running pgbench and
which was completely fixed by a RAM replacement.)

Cheers,
Steve

Jay McGaffigan <hooligan495@gmail.com> writes:
>   I've been trying to restore a fairly sizeable database dump from my
> production 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
> upgrading my dev box and I'm getting errors on import.  Some of my columns
> have 'rich 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).

If you showed us the exact error messages rather than hand-waving, we
might be able to help, but it's hard to say much with so little detail.

Note that you generally want to look at the first few errors not the
last few, as pg_dump scripts tend to be very prone to cascading-error
syndrome.

Another thing that's often helpful is to see if you can restore a
schema-only dump (pg_dump -s), as that lets you separate schema problems
from data problems, and get any of the former resolved before you deal
with the latter.

            regards, tom lane

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

Jay McGaffigan <hooligan495@gmail.com> writes:
> So my first question.
>   If I can't use a psql 9.2.2 instance to create the backup.  Are there
> 'best' practices I should follow in creating the backup.  All my googling
> hasn't really been able to point to the best approach.

We do recommend using the newer pg_dump in an upgrade scenario when you
conveniently can, but 99% of the time the older pg_dump should work
fine.  That recommendation is mainly to forestall issues like
that-particular-older-version-has-a-bug, and at the moment there's no
evidence that that's your problem.

            regards, tom lane