From: Ted Allen <tallen@blackducksoftware.com>
To: pgsql-performance@postgresql.org
Subject: Troubles dumping a very large table.
Date: Wed, 24 Dec 2008 12:31:23 -0500
Message-ID: <495271EB.80508@blackducksoftware.com> (raw)
(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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tallen@blackducksoftware.com
Subject: Re: Troubles dumping a very large table.
In-Reply-To: <495271EB.80508@blackducksoftware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox