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