What does pg_resetxlog do? And how does it work?

Question

I looked at the postgres documentation and the following is a brief description:

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

But nowhere in the documentation do they explain what datadir is. Is it %postgres-path%/9.0/data or maybe %postgres-path%/9.0/data/pgxlog ?

Also, if I want to change my xlog directory, can I just move the items in the current pg_xlog directory and run the command to point to another directory? (Suppose my current pg_xlog directory is in /data1/postgres/data/pg_xlog and the directory in which I want these logs to be: /data2/pg_xlog )

Will the next team achieve what I just described?

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

Answer 1

pg_resetxlog is the tool of the last attempt to start your database again after:

• You have deleted files that should not have from pg_xlog ;

• You restored a file system level backup that did not specify the pg_xlog directory due to a backup system configuration error (this happens more than you think, people think that it has a name in the log, so it should not matter, I will leave it from backups ").

• Damage to the file system due to hardware failure or hard disk failure damaged your data directory; or potentially even

• A PostgreSQL error or an operating system error has damaged write-ahead logs (extremely rare).

As the manual says:

pg_resetxlog clears the log (WAL) [...]. This feature is sometimes necessary if these files are damaged. This should only be used as a last resort when the server does not start due to such corruption.

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

pg_resetxlog can corrupt your database , as the documentation warns. If you need to use it, you must REINDEX , reset your databases, restart initdb and restart your databases. Do not continue to use a damaged cluster. According to the documentation:

After running this command, it should be possible to start the server, but keep in mind that the database may contain inconsistent data due to partially completed transactions. You should immediately flush the data, run initdb and reboot. After rebooting, check for inconsistencies and repair if necessary.

If you just want to move your recording log directory to another location, you need to:

• Stop PostgreSQL
• Move pg_xlog
• Add symbolic link from old location to new location
• Launch PostgreSQL

Or, as the documentation says :

This is useful if the log is on a different drive with the main database files. This can be achieved by moving the pg_xlog directory to another location (while the server is off, from the course) and creating a symbolic link from the source location to the main data directory to a new location.

If PostgreSQL does not start, you did something wrong. Do not use pg_resetxlog to “fix” it. Discard your changes and find out what you did wrong.

Answer 2

Move the contents of the pg_xlog directory to the desired location, for example, '/ home / foo / pg_xlog'

 mv pg_xlog/* /home/foo/pg_xlog 

Delete the pg_xlog directory

 rm -rf pg_xlog 

Create the pg_xlog soft link

 ln -s /home/foo/pg_xlog pg_xlog 

Check link

 ls -lrt pg_xlog 

Note: pg_resetxlog is not the right tool to move pg_xlog, please read

http://www.postgresql.org/docs/9.2/static/app-pgresetxlog.html

Answer 3

The data directory corresponds to the data_directory in the postgresql.conf file or the PGDATA environment variable, and can also be requested in real time in SQL using the SHOW data_directory . It does not point to the pg_xlog directory, but one level up.

To change the location of the WAL files, the PG server must be disconnected, the pg_xlog directory and its contents moved to a new location, a symbolic link must be created from the old location to the new location, and the server rebooted. pg_resetxlog should not be used for this, since it can suppress recent transactions (this tool is usually used in disaster recovery situations when everything else does not work).

Answer 4

You should never manually touch the WAL files, which is perfectly clear.

If there are dangling files in the pg_xlog directory, that is, there is a file ending with .done* in the sub-folder archive_status`, which must be cleaned manually, which can be done using the sql command

 CHECKPOINT; 

which forces the transaction breakpoint to include WAL segment file cleanup.

See the documentation for 9.3 , but exists in all current versions of Postgresql.