PostgreSQL: Use pg_resetxlog to recover the deleted pg_xlog data

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

If you don’t know the importance of pg_xlog, please visit a below article.

PostgreSQL: Difference between pg_log, pg_clog and pg_xlog log directories

Recently, we were facing a size related problem in one of our PostgreSQL test servers. A system admin found that the size of pg_xlog folder is large. What he did was, he deleted few log files from pg_xlog folder and restarted the PostgreSQL service without knowing the outcome.

Postgres server got down immediately and threw error like ‘PG_CTL failed’.

A pg_xlog folder contains the main transaction log files (WAL files) which are necessary to load the database and other objects. The size of pg_xlog folder is large, so if you are facing any size related problem, you can archive it.

The very first step to recovering your database using a backup file, but if you don’t have any backup of a database, you have to use pg_resetxlog utility which reset the pg_xlog.

pg_resetxlog may corrupt your database, or you may get data loss, as the documentation warns because of missing WAL files.
After running this command, it should be possible to start the server but bear in mind that the database might contain inconsistent data due to partially-committed transactions.

Step 1:

Step 2: Execute pg_resetxlog utility to reset pg_xlog