pg_log, pg_xlog, pg_clog: what they eat

— I then deleted several GB of log files from the pg_xlog directory to free up disk space. Now my database does not take off.

— Oy vey! Cough, cough... And when you say the last time you backed up?

In this form several times to call customers and users for help on our IRC channel. Given the ease of repetition of this error, I decided to put some information about system catalogs PostgreSQL.


There are three directories in the directory $PGDATA when it is created, which have the form "pg_ that describe these too*log".

pg_log

$PGDATA/pg_log is a default place where it stores logs of activity. They include error messages, record of requests and messages during start-up\shutdown of the DBMS. It is necessary to search information in the case, if PostgreSQL does not start. Many Linux distributions sin that can move this directory somewhere in the /var/log/postgresql.

You can freely delete, rename, compress and move files from pg_log without fear, provided that the user postgres has write access to the directory. If pg_log is inflated due to the large files, then you should probably cut the list of logged things, changing settings in postgresql.conf.

pg_xlog

$PGDATA/pg_xlog is the location where PostgreSQL stores the transaction log. This set of binaries, with names of the form '00000001000000000000008E', which contain images of the data of the last transaction. These logs are also used when binary replication.

If replication, archiving or PITR (Point-In-Time-Recovery) fail, this directory is in danger of becoming bloated with gigabytes of logs that the server writes, in case the backup will resume. This can cause overflow of disk space.

Unlike pg_log you can't delete, move or compress files in this directory. Removing files from pg_xlog can lead to unrecoverable database corruption.

If you find yourself in a situation when you have 100 GB of files in pg_xlog and the database does not start and you have already disabled archiving/replication, and already tried to clean up disk space in any other way, then please take two steps:

the
1. Move the files from pg_xlog on the backup disk or shared network drive, but in any case do not remove them.
the
2. Copy back to pg_xlog only a few most of the old files. This is sufficient to PostgreSQL started in normal mode.

pg_clog

$PGDATA/pg_clog contains a log of metadata transactions. This magazine tells the server which transactions completed and which are not. This directory is small and is not present any preconditions for its swelling. Most likely you will never have to touch it.

But if you ever delete the files from pg_clog, you can safely delete the entire directory and database. There is no way to recover the database without the logs.

It is worth noting that if you prefer to create backup copies of files in the directory $PGDATA, you should make sure that the directories pg_clog and pg_xlog also archived. Otherwise, you may discover that the backup is useless.

Article based on information from habrahabr.ru