Tag Archives: pg_xlog

Postgresql: how to clean pg_xlog

sudo /etc/init.d/postgresql stop

du -sh /var/lib/postgresql/8.4/main/pg_xlog/
985M     /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:          1/2718420992
Latest checkpoint's NextOID:          496696
...
sudo -u postgres /usr/lib/postgresql/8.4/bin/pg_resetxlog -o 496696 -x 2718420992 -f /var/lib/postgresql/8.4/main/

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