PostgreSQL: Difference between pg_log, pg_clog and pg_xlog log directories

In this post, I am sharing the basic purpose of pg_log, pg_clog and pg_xlog log directories in PostgreSQL.

If you look into your PostgreSQL data directory, you can find different types of log folders.

pg_log:

This is a default database activity logs like: error messages, executed query log, dead lock information, Postgers start/stop messages.

First, you should look into this folder for any kind of investigation. Most of the time we are getting an error because of bad queries or bad configuration so in these cases we can access this folder.

You can also delete unwanted or old log files because It stores only database action related messages which are not important for PostgreSQL Server.
There are a few parameters in Postgres.conf file related to pg_log folder, you access below article for this.

PostgreSQL: Important Parameters to enable Log for all Queries

pg_clog:

We cannot read this log file because It is for PostgreSQL internals. It contains a log for transaction metadata. Using this, PostgreSQL server can take decision the which transaction completed or which did not.

This is very important log folder, you should never miss this folder during your pg_data directory backup.

pg_xlog:

At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster’s data directory. The log records every change made to the database’s data files. This log exists primarily for crash-safety purposes.

It contains the main binary transaction log data or binary log files. If you are planning for replication or Point in time Recovery, we can use this transaction log files.

We cannot delete this file, otherwise It causes a database corruption. The size of this folder would be greater than actual data so If you dealing with huge database, 99% chance to face disk space related issues specially for the pg_xlog folder.

But don’t worry, There are multiple ways to optimize pg_xlog folder by remove old WAL files.


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz