Re: Restoring a database dump from 9.0 to 9.2

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