Re: 2,2gb of pg_xlog ?? - Mailing list pgsql-admin

Hi Stefan,

These pg_xlog files are, as you said, 16MB files that store all the transactions made to your database – if you’re doing regular SQL dump backups, and you know you won’t need Point-In-Time (PITR) recovery, you won’t need these files. 

However, you will need to arrange for old files to be automatically archived into a separate folder outside of your pg_xlog directory, by setting an archive_command in the WAL configuration in postgresql.conf.  A dead simple command looks like this:

Archive_command = “mv %p /your/backup/directory/%f”

This tells PGSQL to move (“mv”) the file no longer in use “%p” to the directory “/your/backup/directory/” with the filename “%f”.  %f is the original filename of the file being archived.

Once you’ve put that in, killall –HUP postmaster, and you should notice your files being moved out.  Note: the postgresql Unix user (from /etc/passwd) will need write access to /your/backup/directory.

Tip: if you’re absolutely sure you will never need these transaction logs, you can use the following command to delete them when they’re finished with:

Archive_command = “rm –f %p”

Or

Archive_command = “mv %p /dev/null”

This is for PostgreSQL 8.0 above, I believe it should be the same for 7.2.

Hope this helps!

Andy