Don't use pg_resetxlog
except as a last resort. (I think I'll submit a patch to add an --i-have-read-the-manual
option to pg_resetxlog
, I see it abused too often.)
You should dump the server and restore the dump.
The transactions logs are crucial for data integrity. Without them, you might have incompletely written transactions, transactions that were committed but never actually made it to the main database, transactions out of order, and more.
So it's not as simple as losing a few recent transactions. You lose the write-ordering that preserves data integrity.
Index blocks might not match the data in the heap. Visibility maps etc may be invalid. Queries could produce incorrect results, disk blocks could be split (resulting in errors on read), and more.
It's really a very good idea to dump, re-initdb, and restore. Keep a copy of the old damaged database around just in case. Even then, your database is damaged - the dump won't magically fix it if data is inconsistent and wrong, and it's possible you may have to repair the dump to get foreign keys to restore correctly, etc.
I would look into partitioning. If partitioned by day, you could just drop the entire partition once it gets too old. You may even no longer have to vacuum.
Also, overall performance might increase, since you're not inserting where you're deleting. You would just need to write the code to create new partitions and delete old ones.
This is exactly what partitioning is for.
Best Answer
From the manual:
So yes, you can trust the backup. Of course, it's PostgreSQL, you can trust your data in PostgreSQL.