pg_dump filling up root directory

From: David Rickard <David(dot)Rickard(at)GTScompanies(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: pg_dump filling up root directory
Date: 2004-04-07 23:28:32
Message-ID: 5.2.1.1.0.20040407153624.02cf3590@mail.gtscompanies.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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(dot)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]

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Kevin Lohka 2004-04-08 00:06:47 Re: Help with Select Statement
Previous Message David Kitzinger ARA/SWD 2004-04-07 22:45:05 binary bytea