What does pg_resetxlog do? And how does it work?

Question

I have looked at the postgres documentation and the synopsis below is given:

pg_resetxlog [-f] [-n] [-ooid ] [-x xid ] [-e xid_epoch ] [-m mxid ] [-O mxoff ] [-l timelineid,fileid,seg ] datadir

But at no point in the documentation do they explain what the datadir is. Is it the %postgres-path%/9.0/data or could it be %postgres-path%/9.0/data/pgxlog ?

Also, if I want to change my xlog directory, can I simply move the items in my current pg_xlog directory and run the command to point to another directory? (Assume my current pg_xlog directory is in /data1/postgres/data/pg_xlog AND the directory I want it the logs to go to is: /data2/pg_xlog)

Would the following command achieve what I've just described?

mv /data1/postgres/data/pg_xlog /data2/pg_xlog
pg_resetxlog /data2

Answer 1

pg_resetxlog is a tool of last resort for getting your database running again after:

• You deleted files you shouldn't have from pg_xlog;

• You restored a file system level backup that omitted the pg_xlog directory due to a backup system configuration mistake (this happens more than you'd think, people think "it has log in the name so it must be unimportant; I'll leave it out of the backups").

• File-system corruption due to a hardware fault or hard drive failure damaged your data directory; or potentially even

• a PostgreSQL bug or operating system bug damaged the write-ahead logs (exceedingly rare).

As the manual says:

pg_resetxlog clears the write-ahead log (WAL) [...]. This function is sometimes needed if these files have become corrupted. It should be used only as a last resort, when the server will not start due to such corruption.

Do not run pg_resetxlog unless you know exactly what you are doing and why. If you are unsure, ask on the pgsql-general mailing list or on https://dba.stackexchange.com/.

pg_resetxlog may corrupt your database, as the documentation warns. If you have to use it, you should REINDEX, dump your database(s), re-initdb, and reload your databases. Do not just continue using the damaged cluster. As per the documentation:

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. You should immediately dump your data, run initdb, and reload. After reload, check for inconsistencies and repair as needed.

If you simply want to move your write-ahead log directory to another location, you should:

• Stop PostgreSQL
• Move pg_xlog
• Add a symbolic link from the old location to the new location
• Start PostgreSQL

Or, as the documentation says:

It is advantageous if the log is located on a different disk from the main database files. This can be achieved by moving the pg_xlog directory to another location (while the server is shut down, of course) and creating a symbolic link from the original location in the main data directory to the new location.

If PostgreSQL fails to start, you've done something wrong. Do not use pg_resetxlog to "fix" it. Undo your changes and work out what you did wrong.