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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
