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?

1 Answer 1

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.

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, 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/%f Jan 22, 2018 at 9:31
  • Is that you mean that before copying it will compress the file size?
    – Arun Raut
    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
    – Arun Raut
    Jan 22, 2018 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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