Thread: shrinking physical space used
hello all, i've got a database that takes up 4G of space. when i run a script that deletes all rows and then vacuum, the data directory gets down to around 3-3.5G. what i'd like is to get a blank database structure that really contains no data at all, or any unused space at all. apparently that's not what i'm getting now. is there a way to get this apart from dump, initdb, restore? i figure that if i could dump/initdb/restore i'd probably get a data directory with around 30MB only. the reason i can't do dump, initdb, restore is: I'm working with postgresql 7.1.x (no choice, the client isn't going to be ready to upgrade to 7.4.x or better for at least six months or so since they have no regression tests at all). the database i'm working on has problems with pg_dump and restoring a dump because of recursive definitions (function selects from a table, table has a default or constraint referring to the function, neither can be created when the dump is restored because the other isn't defined yet). i'm aware of check_function_bodies in 7.4.x and 8.x, but i can't take advantage of that since i can't upgrade. i tried to load the schema and data into 7.4.x and 8.x with check_function_bodies=off and then dump the data and reload it into 7.1.x but that doesn't work since there is some syntax in 7.4 and 8.x that doesn't work in 7.1 ($ instead of ' as procedure delimiters, schemas, etc). and anyway, there's still the recursive definitions (curious, does 8.x support for dumping the constraints and defaults at the end of the dump as alter table instead? how do i turn that on? i thought tom lane had a post that said something like that was possible, but i haven't worked with 8.x beta long enough to find where that is). at some point i may get desperate and i'll break down and just manually modify the (very large) schema so that all such recursive definitions are removed from the create table statement and moved to alter table statements at the bottom of the schema. i'm resisting that for now though. thanks for any pointers :) (mainly to how to shrink the space used by databases, sort of truncating the files so that no space is in there at all that isn't actually used). tiger -- Gerald Timothy Quimpo http://bopolissimus.sni.ph gquimpo@sni.ph bopolissimus@gmail.com tiger@qsr.com.ph Public Key: "gpg --keyserver pgp.mit.edu --recv-keys 672F4C78" Mene sakhet ur-seveh
> i've got a database that takes up 4G of space. when i run a script that > deletes all rows and then vacuum, the data directory gets down to > around 3-3.5G. what i'd like is to get a blank database structure that > really contains no data at all, or any unused space at all. apparently > that's not what i'm getting now. is there a way to get this apart from > dump, initdb, restore? i figure that if i could dump/initdb/restore i'd > probably get a data directory with around 30MB only. You likely need to run VACUUM one way or another and also REINDEX. Will likely need some downtime but *will* eventually alleviate your troubles. As for VACUUM be aware of the *_fsm_pages_* parameter to be set appropriately. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Sun, Nov 14, 2004 at 01:50:10PM +0800, Bopolissimus Platypus Jr wrote: > hello all, > > i've got a database that takes up 4G of space. when i run a script that > deletes all rows and then vacuum, the data directory gets down to > around 3-3.5G. what i'd like is to get a blank database structure that > really contains no data at all, or any unused space at all. apparently > that's not what i'm getting now. is there a way to get this apart from > dump, initdb, restore? i figure that if i could dump/initdb/restore i'd > probably get a data directory with around 30MB only. Perhaps you should use TRUNCATE (the SQL command) rather than DELETE. It'll probably work better... You're probably still seeing the space the indexes are using. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Bopolissimus Platypus Jr <bopolissimus.lists@gmail.com> writes:
> the reason i can't do dump, initdb, restore is: I'm working with postgresql
> 7.1.x
This is your problem right here :-(. You should be making every effort
to get updated sooner, not later. Spending time on workarounds for 7.1
limitations is silly when you could be putting that effort into
migrating sooner.
Others already mentioned that you should try REINDEX or TRUNCATE so that
you can shrink the indexes as well as the table. I don't recall if
either of those were available in 7.1 though --- you might have to
resort to dropping and recreating the indexes.
> the database i'm working on has problems with pg_dump and restoring a
> dump because of recursive definitions (function selects from a table,
> table has a default or constraint referring to the function, neither can be
> created when the dump is restored because the other isn't defined yet).
This should work with the current pg_dump. Could we see a concrete
example where it doesn't work? (Note: "works" is defined as "loads
into 8.0".)
regards, tom lane