PostgreSQL Version: 7.3.4
Environment: Solaris (SunOS 5.9) Ultra-250

We do a nightly export-and-analyze of our databases, using these commands:

outfile=/data/export/${instance}~EXPORT~${ymd}.sql.tar
su - ${pg_user} -c "${pg_home}/bin/pg_dump -c -C -D -F t -f
${outfile} ${instance}"
gzip ${outfile}

su - ${pg_user} -c "${pg_home}/bin/vacuumdb -a -f -z -v " >
/usr/local/app/pgsql/log/vaclog.log 2>&1

where ${instance} is a database name;

During the dump, pg_dump writes something to the root directory (the
/data/export/ directory is on an external drive), and on occasion, whatever
is written to the root directory bloats, overfilling the root storage space:
from /var/adm/messages:
1 Apr 3 03:16:30 rutabaga -- MARK --
2 Apr 3 03:36:30 rutabaga -- MARK --
3 Apr 3 03:56:30 rutabaga -- MARK --
4 Apr 3 04:00:00 rutabaga MAINT: [ID 702911 local5.info]
/usr/local/gts/pgsql/maint/pgsql_daily.s
5 Apr 3 04:16:30 rutabaga -- MARK --
6 Apr 3 04:22:52 rutabaga ufs: [ID 845546 kern.notice] NOTICE:
alloc: /: file system full
7 Apr 3 04:36:30 rutabaga -- MARK --
8 Apr 3 04:56:30 rutabaga -- MARK --


We've done some research and seen some possible causes/solutions--we would
appreciate advice on the best approach (or better alternatives):

1) We suspect that pg_dump is using tmpfile while writting the export file:
is this true? and if so, how do we (can we?) control where tmpfile writes
during the export process?
2) We have seen references to piping the export to split to produce smaller
output files: how practical is this? will it counter the tmpfile bloating
(if that is what is happening)? if practical and effective, does anyone
know of a "best-practice" line count value to pass to split?



--

David Rickard
Software Engineer
The GTS Companies
A TechBooks Company

----------------------------------------------------------------------------------

The GTS Companies:
GTS Publishing Services, GTS Graphics, GTS Innova:
Your Single-Source Solution!
Los Angeles CA * York, PA * Boston MA * New Delhi, India
----------------------------------------------------------------------------------



David.Rickard (AT) GTSCompanies (DOT) com
Visit us on the World Wide Web
http://www.gtscompanies.com
5650 Jillson St., Los Angeles, CA 90040
(323) 888-8889 x331
(323) 888-1849 [fax]

David Rickard <David.Rickard (AT) GTScompanies (DOT) com> writes:
I believe this is true when you use the tar output format. The simplest
solution is to use custom format instead (-Fc instead of -Ft).

AFAIR you can't change it without hacking the source code.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend