0

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?

Improve this question
0

If you don't need to analyze again that log. You can reduce the things that database will be log. Some of them are:

  1. log_statement: Controls which SQL statements are logged. You don't need to log all statement. It can be switched to none.
  2. client_min_messages: can be set to ERROR
  3. log_min_messages: can be set to ERROR
  4. 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.

Improve this answer
8
  • 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
  • Is that you mean that before copying it will compress the file size? – Arun Raut Jan 22 '18 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. – 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.