Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a dev system, and I have been restoring to a database, say "foo", that I'm using for dev purposes. As I'm working through the kinks, I've just been running DROP DATABASE foo. However, I quickly realized I ate up all the space on my disk. Crap.

Does VACUUM FULL, from a different logical database, free up space from the database that I previously dropped (foo)? I tried this from a different logical database, and free space was reclaimed, but I don't THINK it was enough to account for all of the CREATE DATABASE/DROP DATABASE calls I made. It might have just VACUUM'ed the logical database I ran from.

There must be a way to reclaim that space without doing a total database init?

EDIT

So I reinitialized the database from a backup, roughly following these steps. After the restore, I've reclaimed a TON of space on disk! This works for now, but any help regarding how to cleanup a dropped database would still be useful.

EDIT 2

So I've managed to collect some more information about this issue... Here's what I've come up with as an example:

Initial partition size:
                       Size   Used  Avail Use% Mounted on
                        25G   8.1G    16G  35% /apps1

After creating my new database and populating it:
                        25G    18G   6.4G  73% /apps1

After Dropping the database using "DROP database mydb" from a separate logical DB:
                        25G    13G    11G  56% /apps1

So it appears to me that the new DB took up ~9.6 GB on disk. However, after dropping it, the reclaimed disk space only grew by ~4.6G. So, there's roughly 5 GB of space that makes me wonder what's going on!?

And it continues this cycle when I recreate, populate, and drop again.

Does anyone have any idea what's lingering after a "DROP DATABASE" command is issued?

share|improve this question
    
Maybe it's also worth noting that I have archiving turned ON. It appears that the location in which the WAL archive files are being written is rather large. I haven't been monitoring it closely. Maybe I'll try the same procedure listed above and run a "du" on that directory to see if all space is reclaimed. I'll report back. –  Jmoney38 Jun 20 '12 at 21:59
    
I assume vacuum doesn't help then? –  rogerdpack Jul 17 at 17:46

2 Answers 2

My understanding is that when you drop a database then it, and it's files, are gone.

Unless you are using tablespaces then each database should have it's data in it's own subdirectory under $PGDATA/base. Using one of my servers for an example (as the postgres user):

-bash-3.2$ cd $PGDATA/base

-bash-3.2$ ls | wc -l
9

-bash-3.2$ du -sh `ls`
6.9M    1
6.7M    12690
6.9M    12698
11M     16391
341M    17339
3.8G    17341
11M     17343
6.8M    19047
8.0K    pgsql_tmp

Now, if we create a new database then there should be one more subdirectory under $PGDATA/base:

-bash-3.2$ createdb foo

-bash-3.2$ ls | wc -l
10

-bash-3.2$ du -sh `ls`
6.9M    1
6.7M    12690
6.9M    12698
11M     16391
341M    17339
3.8G    17341
11M     17343
6.8M    19047
6.9M    83637
8.0K    pgsql_tmp

Which is what we see ($PGDATA/base/83637 being the subdirectory for the new database).

Dropping that database should also delete the data files:

-bash-3.2$ dropdb foo

-bash-3.2$ ls wc -l
9

-bash-3.2$ du -sh `ls`
6.9M    1
6.7M    12690
6.9M    12698
11M     16391
341M    17339
3.8G    17341
11M     17343
6.8M    19047
8.0K    pgsql_tmp

Which is what we would expect-- the $PGDATA/base/83637 directory is gone, there should be nothing to vacuum.

Are you sure there isn't something else eating up your disk space? One of your other databases? log files?

Something that you could try would be to:

-bash-3.2$ cd $PGDATA
-bash-3.2$ du -sh `ls` > ../pre_sizes

do your various database stuff, create, drop, etc. and then:

-bash-3.2$ du -sh `ls` > ../post_sizes

to get some idea where the disk space is going.

share|improve this answer
    
I do see the same results as you do - i.e. When I add the table, the new DB appears in the base directory, and when I remove it, it's gone. However, that directory doesn't appear to comprise the entire difference in size (ie. ~9.6GB) –  Jmoney38 Jun 20 '12 at 21:56
    
@Jmoney38 -- That's why I recommended doing the "du -sh ls" on the $PGDATA directory both before and after doing the database add/drop as that should flag which other directories are growing leaps and bounds. –  gsiems Jun 21 '12 at 19:53
    
@Jmoney38 -- If I had to guess I'd say that the difference is to be found in the $PGDATA/pg_log and/or $PGDATA/pg_xlog directories. Log files are for the cluster and don't get truncated when you drop a database. –  gsiems Jun 21 '12 at 20:01

Try sudo lsof| grep deleted and check if any PostgreSQL process appears. This command looks for files which have been deleted but its file descriptors are still open by any process. Another side effect is that df -h and du -sh / differs. This is because du looks at the file system and sum up the size of all files, and df looks at the physical device.

I have just had an issue with a database which didn't free any space after a DROP table and that was the cause.

The only solution I know is to restart the database. Maybe you can try to send a reload (SIGHUP).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.