Clean a PostgreSQL database directory
Sep 11th, 2008 by James Murty
After building a database using an ill-judged algorithm, I ended up with junk in the database that consumed 600MB of space in the Postgres data directory. Deleting the bad data did not free this space immediately, and I was too impatient to wait for the auto vacuuming to kick in.
Here are the steps I took to clean up the database and data directory — with extreme prejudice.
In the psql console:
VACUUM FULL VERBOSE;
REINDEX DATABASE mydatabase;
REINDEX DATABASE mydatabase;
On the command line:
pg_resetxlog /path/to/my/datadir
Running all of these steps is a brutal process. Do not run the
pg_resetxlog
command if you have important data, because you will probably want to keep your write ahead log.
Hi! I was surfing and found your blog post… nice! I love your blog.
Cheers! Sandra. R.