Postgresql: how to clean pg_xlog

Postgres is an amazing open source ACID compliant database, it is reliable and has a good track of stable releases. But every now and then, you come across a nigling behaviour that risks to bring your setup down. In most cases than not, it is to do with storage and logs. Recently I came across a strange situation, where a single database of 4g took in excess of 84G of disk space.  I then discovered that because my  archive_command in postgresql.conf was failing, pg_xlogs were never rotated and were kept indefinitely since the database was started. Correct the archive command would help solve the problem, but this is what you need to do to clean pg_xlog and refresh the storage

sudo /etc/init.d/postgresql stop

du -sh /var/lib/postgresql/8.4/main/pg_xlog/    ( this is your main data folder)

84G     /var/lib/postgresql/8.4/main/pg_xlog/

/usr/lib/postgresql/8.4/bin/pg_controldata /var/lib/postgresql/8.4/main/

Latest checkpoint’s NextXID:          0/385464
Latest checkpoint’s NextOID:          159836

sudo -u postgres /usr/lib/postgresql/8.4/bin/pg_resetxlog -o 159836 -x 385464 -f /var/lib/postgresql/8.4/main/

Remember to put the correct NextOID  NExtXID, don’t just copy paste this command, you should get response: Transaction log reset

du -sh /var/lib/postgresql/8.4/main/pg_xlog/
17M     /var/lib/postgresql/8.4/main/pg_xlog/

sudo /etc/init.d/postgresql start

Bingo !!