Postgresql – To get rid of heavy archive logs in PostgreSQL 9.1

postgresql-9.1

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?

Best 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 to none.
client_min_messages: can be set to ERROR
log_min_messages: can be set to ERROR
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.

Demo:

Setup:

CREATE ROLE thegroup;
CREATE USER user1 IN ROLE thegroup;
CREATE USER user2 IN ROLE thegroup;
CREATE TABLE t1 ( x integer not null );
ALTER TABLE t1 OWNER TO user1;

Without further changes, here's what happens if user2 tries to add an index on t1.x:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
ERROR:  must be owner of relation t1

The solution is to grant the table ownership to the shared role. As user1 or a superuser:

ALTER TABLE t1 OWNER TO thegroup;

now:

regress=# SET ROLE user2;
SET
regress=> CREATE INDEX t1_x_idx ON t1(x);
CREATE INDEX
regress=>

Postgresql – Write performance of Postgresql 9.1 with read-only slave

Truncate should be a logical operation that should not be too bad disk I/O-wise. I would not expect a lot of problems there.

Best Answer

Related Solutions

Postgresql – Error creating index on PostgreSQL 9.1

Demo:

Postgresql – Write performance of Postgresql 9.1 with read-only slave

Related Question