Re: [SQL] Slow Inserts Leads To Unable To Dump

"Frank Morton" <fmorton(at)base2inc(dot)com> writes:
> Now that that is done, I thought I should dump the database
> before doing any more to save those 7 days if I mess up.
> After processing 18 HOURS doing the "pg_dump -d", it ran
> out of memory and quit. Are there tools to do this differently,

Yes: pg_dump without -d. COPY should work fine.

This does suggest an item for the to-do list: making pg_dump use
DECLARE CURSOR and FETCH rather than a plain SELECT to pull the
contents of tables in -d mode. However, if your table is so large
that it doesn't fit in memory inside pg_dump, then I'd have to say
that you wouldn't want to reload it from a series of INSERT statements
anyway. So I'm not very excited about this...

It does seem that your installation is remarkably slow; other people
are working with tables exceeding 2Gb and don't seem unhappy about
the speed. Perhaps you need to check to see if the thing is thrashing;
how much real memory do you have, and what -B setting are you using?

regards, tom lane