pg_log, pg_xlog, pg_clog: what they eat

“I’ve deleted a few GB of log files from the pg_xlog directory to free up disk space.” Now my database is not taking off.

- Oi-wei! Khe-khe ... And when did you say the last time you made a backup?

It was in this form that customers and users called for help on our IRC channel several times. Given the ease of repeating this error, I decided to post some information about the PostgreSQL system directories.
')

There are three directories in the $ PGDATA directory at its creation that have the form "pg_ * log" .

pg_log

$ PGDATA / pg_log is the default location where activity logs are stored. They include error messages, records of requests, and messages during the start / shutdown of the DBMS. This is where you should look for information in case PostgreSQL does not start. Many Linux distributions sin because they can move this directory somewhere to / var / log / postgresql .

You can freely delete, rename, compress and move files from pg_log safely, provided that the user postgres has the right to write to the directory. If pg_log gets bloated with large files, then you should probably cut down the list of logged things by changing the settings in postgresql.conf .

pg_xlog

$ PGDATA / pg_xlog is where PostgreSQL stores transaction logs. This set of binary files, with the names of the form '00000001000000000000008E' , which contain images of the data of the latest transactions. These logs are also used in binary replication.

If replication, archiving or PITR (Point-In-Time-Recovery) fails, this directory runs the risk of becoming bloated with gigabytes of logs that the server writes in case archiving resumes. This may cause disk space to overflow.

Unlike pg_log , you cannot freely delete, move or compress files in this directory. Deleting files from pg_xlog can lead to irrecoverable database corruption.

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

1. Move files from pg_xlog to a backup disk or a shared network drive, but do not delete them in any way.
2. Copy back to pg_xlog only a few of the oldest files. This is enough for PostgreSQL to start in normal mode.

pg_clog

$ PGDATA / pg_clog contains transaction metadata logs. This log tells the server which transactions are completed and which are not. This catalog is small and there are no prerequisites for its swelling. Most likely you will never have to touch it.

But if you ever delete files from pg_clog , you can safely remove the entire database directory. There is no way to restore a database without these logs.

It is worth noting that if you prefer to back up files in the $ PGDATA directory, you should make sure that the pg_clog and pg_xlog directories are also archived. Otherwise, you may find that the backup is useless.