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?
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 toERROR
log_min_messages
: can be set toERROR
log_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 – Arun Raut Jan 22 '18 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/%f
– Mabu Kloesen Jan 22 '18 at 9:31 -
-
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. – Mabu Kloesen Jan 22 '18 at 10:53
-
Thank you buddy. I will try this approach as soon as possible and keep you posted on this – Arun Raut Jan 22 '18 at 11:13