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@GTSCompanies.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]