Thread: restore balloons db size

restore balloons db size

From
Marc Fromm
Date:
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
sizeof the base directory dramatically increases with each restore (193MB to 355MB to 624MB). If I run vacuumdb, it
onlydrops 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
alldatabases 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.orgdocuments 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
aninvalid archive. 

Marc



Re: restore balloons db size

From
Tom Lane
Date:
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
sizeof the base directory dramatically increases with each restore (193MB to 355MB to 624MB). If I run vacuumdb, it
onlydrops 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