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?

Arun Raut
  • 57
  • 3
  • 6

1 Answers1

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.

Mabu Kloesen
  • 261
  • 2
  • 3
  • 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 09: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 09:31
  • Is that you mean that before copying it will compress the file size? – Arun Raut Jan 22 '18 at 09: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
  • Okay, try it, and please note the command if you want to recovery

    gunzip < /where/you/store/archive/%f > "%p"

     – Mabu Kloesen Jan 22 '18 at 11:45
  • Is there any way to reduce the number of archive logs generation ? – Arun Raut Jan 23 '18 at 07:20
  • Sorry, You can get more document here https://www.postgresql.org/docs/9.3/static/runtime-config-wal.html#GUC-ARCHIVE-COMMAND

    I think basically you can reduce the number but the file size will be bigger, so the total size is not changed.

     – Mabu Kloesen Jan 23 '18 at 10:16