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 (AT) sni (DOT) ph bopolissimus (AT) gmail (DOT) com tiger (AT) qsr (DOT) 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 (AT) postgresql (DOT) org)

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Sun, Nov 14, 2004 at 01:50:10PM +0800, Bopolissimus Platypus Jr wrote:
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 (AT) svana (DOT) org> http://svana.org/kleptog/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBl21HY5Twig3Ge+YRAvU+AJoDD45igTiDyF/qyPVumDeKYMgKkQCfdNMG
r8tD4QYN8BFgxFKhD1YIfTo=
=ZS9j
-----END PGP SIGNATURE-----

Bopolissimus Platypus Jr <bopolissimus.lists (AT) gmail (DOT) com> writes:
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.

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly