pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
restore balloons db size
2+ messages / 2 participants
[nested] [flat]

* restore balloons db size
@ 2008-07-31 15:03 Marc Fromm <Marc.Fromm@wwu.edu>
  2008-07-31 15:48 ` Re: restore balloons db size Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Marc Fromm @ 2008-07-31 15:03 UTC (permalink / raw)
  To: pgsql-admin

Does anyone have some insight on why the db size is expanding with each restore?

If I restore all the postgresql databases from pg_dumpall and use the -c to drop databases before restoring them the size of the base directory dramatically increases with each restore (193MB to 355MB to 624MB). If I run vacuumdb, it only drops by a few MB.

If I do the long restore process by:
1. stopping the postmaster
2. deleting all contents in the data folder 3. run initdb -D /var/lib/pgsql/data 4. start the postmaster 5. restore of all databases from the same pg_dumpall the size of the base directory does not balloon.

I have searched online for optimized backup and restore procedures for postgresql, so far all I have found are the postgresql.org documents which are a little vague on the subject.

I must assume there is a better method than my long process that does not bloat the size of the base directory (the databases) as the -c flag seems to do. I tried to use pg_restore but I receive a message stating the pg_dumpall file is an invalid archive.

Marc





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: restore balloons db size
  2008-07-31 15:03 restore balloons db size Marc Fromm <Marc.Fromm@wwu.edu>
@ 2008-07-31 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2008-07-31 15:48 UTC (permalink / raw)
  To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin

Marc Fromm <Marc.Fromm@wwu.edu> writes:
> Does anyone have some insight on why the db size is expanding with each restore?
> If I restore all the postgresql databases from pg_dumpall and use the -c to drop databases before restoring them the size of the base directory dramatically increases with each restore (193MB to 355MB to 624MB). If I run vacuumdb, it only drops by a few MB.

Seems a little odd.  Please look closer and find out where the bloat is,
exactly.  If you're not clear on what's what, see
http://www.postgresql.org/docs/8.3/static/storage-file-layout.html

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2008-07-31 15:48 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-31 15:03 restore balloons db size Marc Fromm <Marc.Fromm@wwu.edu>
2008-07-31 15:48 ` Tom Lane <tgl@sss.pgh.pa.us>

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