Message-ID: <495271EB.80508@blackducksoftware.com>
Date: Wed, 24 Dec 2008 12:31:23 -0500
From: Ted Allen <tallen@blackducksoftware.com>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: <pgsql-performance@postgresql.org>
Subject: Troubles dumping a very large table. 
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

(NOTE: I tried sending this email from my excite account and it appears 
to have been blocked for whatever reason.  But if the message does get 
double posted, sorry for the inconvenience.)

Hey all,

Merry Christmas Eve, Happy Holidays, and all that good stuff.  At my 
work, I'm trying to upgrade my system from a 8.1 to 8.3 and I'm dumping 
a few large static tables ahead of time to limit the amount of downtime 
during the upgrade.  The trouble is, when I dump the largest table, 
which is 1.1 Tb with indexes, I keep getting the following error at the 
same point in the dump.

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  invalid string enlargement 
request size 1
pg_dump: The command was: COPY public.large_table (id, data) TO stdout;

As you can see, the table is two columns, one column is an integer, and 
the other is bytea.   Each cell in the data column can be as large as 
600mb (we had bigger rows as well but we thought they were the source of 
the trouble and moved them elsewhere to be dealt with separately.)

We are dumping the table using this command.

/var/lib/pgsql-8.3.5/bin/pg_dump  -O -x -t large_table mydb | gzip -c  
 >  large_table.pgsql.gz

Originally we tried dumping the table with 
'/var/lib/pgsql-8.3.5/bin/pg_dump  -O -x -t -F c > large_table.dump' but 
that was to cpu intensive and slowed down other db processes too much.  
It failed using that command as well, but I believe it is because we did 
not have enough postgres temp hard drive space.  We have since symlinked 
the postgres temp space to a much bigger file system.

The stats of the db server is as follows,

Processors:  4x Opteron 2.4 Ghz cores
Memory: 16 GB 
Disks: 42x 15K SCSI 146 GB disks.

Also, the large table has been vacuumed recently.  Lastly, we are dumping the table over nfs to very large sata array.


Thanks again and Happy Holidays,
Ted