In our production server which is having PostgreSQL 9.1 installed, too heavy archive logs are being generated, approximately 41 GB/day. Heavy updates, deletes and inserts are the cause of this. Can anyone help us to reduce the amount of archive log generation in our production server?
1 Answer
If you don't need to analyze again that log. You can reduce the things that database will be log. Some of them are:
log_statement: Controls which SQL statements are logged. You don't need to log all statement. It can be switched tonone.client_min_messages: can be set toERRORlog_min_messages: can be set toERRORlog_min_duration_statement: can be set to a-1- this means disable it.
All of them are store in file configuration postgresql.conf
You can read more document here.
Hopefully this answer will help you.
-
Thank you so much @Mabu Kloesen. But I am not talking about postgresql alert log. I am talking about transaction logs Jan 22, 2018 at 9:19
-
Oh, I'm so sorry, I'm misunderstood. So, did you try to use gzip for your archive log? The command we used in archive_command is
test ! -f /where/you/store/archive/%f && gzip < %p > /where/you/store/archive/%fJan 22, 2018 at 9:31 -
Is that you mean that before copying it will compress the file size? Jan 22, 2018 at 9:51
-
Yes, basically the xlog will store all thing you do with database for recovering, so I think, we can't reduce that. We just can reduce the archive file. One of them is compress it. Jan 22, 2018 at 10:53
-
Thank you buddy. I will try this approach as soon as possible and keep you posted on this Jan 22, 2018 at 11:13