Home PostgreSQL pg_log, pg_xlog, pg_clog: what do they do

pg_log, pg_xlog, pg_clog: what do they do

by admin

pg_log, pg_xlog, pg_clog: what do they do
– I kind of deleted a few GB of log files from the pg_xlogdirectory here to free up disk space.Now my database won’t take off.
– Oy-wey! Ahem…When did you say the last time you backed up?
This is the form in which customers and users have called for help several times on our IRC channel Given the ease of recurrence of this error, I decided to post some information about the system directories PostgreSQL
There are three directories in the directory $PGDATA when it is created, which have the form "pg_*log"

pg_log

$PGDATA/pg_log is the default location where activity logs are stored. They include error messages, query records, and messages during DBMS startup/shutdown. This is where you should look for information if PostgreSQL does not start. Many Linux distributions have the sin of moving this directory somewhere in the /var/log/postgresql
You can freely delete, rename, compress and move files from pg_log without fear, as long as the user postgres has permission to write to the directory. If pg_log is bloated by large files, you should probably trim down the list of things to be logged by changing settings in postgresql.conf

pg_xlog

$PGDATA/pg_xlog – is where PostgreSQL stores the transaction log. This set of binary files, with names like ‘00000001000000000000008E’ which contain the data images of the latest transactions. These logs are also used in binary replication.
If replication, archiving or PITR (Point-In-Time-Recovery) fails, this directory risks becoming bloated with gigabytes of logs that the server writes in case archiving resumes. This can cause disk space overflow.
In contrast to the pg_log , you cannot freely delete, move or compress files in this directory. Deleting files from pg_xlog can cause unrecoverable damage to the database.
If you find yourself in a situation where you have 100GB of files in pg_xlog and the database won’t start and you have already disabled archiving/replication and already tried to clear disk space in any other way, then please do the two steps :

  1. Move the files from pg_xlog to a backup drive or a shared network drive, but never delete them.
  2. Copy back to pg_xlog only a few of the oldest files. This is enough for PostgreSQL to start normally.

pg_clog

$PGDATA/pg_clog contains transaction metadata logs. This log tells the server which transactions are completed and which are not. This directory is small and there is no prerequisite for it to bloat. You will probably never have to touch it.
But if you ever delete files from the pg_clog , you can safely delete the entire database directory as well. There is no way to restore the database without these logs.
It is worth noting that if you prefer to back up your files in the $PGDATA , you should make sure that the directories pg_clog and pg_xlog are also archived. Otherwise you may find that the backup is useless.

You may also like