cancel
Showing results for 
Search instead for 
Did you mean: 

Postgresql Log truncation failing in 9.5

SOLVED
Roy
Adventurer

Postgresql Log truncation failing in 9.5

Hello Team,

 

I am trying to implement auto log rotation and truncation of DB logs in EDB PPAS 9.5 test environment.

 

Below are my settings currently for the same

 

log_directory = 'pg_log'                # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log'      # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on           # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 1d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 0   

 

Since it is a test machine, I start the Postgresql cluster when my day starts and stops the same accordingly. I can see that the logs are not truncating but appending daily to the respective daily log files

a sample from postgresql-Mon.log

 

    explain update pgbench_tellers set tbalance = 920 where tid = 4;
< 2018-02-12 17:03:13.132 IST > ERROR:  duplicate key value violates unique constraint "pgbench_tellers_pkey"
< 2018-02-12 17:03:13.132 IST > DETAIL:  Key (tid)=(11) already exists.
< 2018-02-12 17:03:13.132 IST > STATEMENT:  insert into pgbench_tellers values (11,1,210,'test');
< 2018-02-12 17:05:43.260 IST > ERROR:  syntax error at or near "select" at character 32
< 2018-02-12 17:05:43.260 IST > STATEMENT:  select txid_current_snapshot()
    select txid_current_snapshot();
< 2018-02-12 21:14:19.894 IST > LOG:  received smart shutdown request
< 2018-02-12 21:14:19.900 IST > LOG:  autovacuum launcher shutting down
< 2018-02-12 21:14:19.902 IST > LOG:  shutting down
< 2018-02-12 21:14:19.958 IST > LOG:  database system is shut down
< 2018-02-26 15:24:02.114 IST > LOG:  database system was shut down at 2018-02-12 21:14:19 IST
< 2018-02-26 15:24:02.126 IST > LOG:  MultiXact member wraparound protections are now enabled
< 2018-02-26 15:24:02.128 IST > LOG:  autovacuum launcher started
< 2018-02-26 15:24:02.128 IST > LOG:  database system is ready to accept connections
< 2018-02-26 15:25:01.685 IST > ERROR:  could not open extension control file "/usr/pgsql-9.6/share/extension/pg_crypto.control": No such file or directory
< 2018-02-26 15:25:01.685 IST > STATEMENT:  create extension pg_crypto;
< 2018-02-26 20:31:27.801 IST > LOG:  received fast shutdown request
< 2018-02-26 20:31:27.801 IST > LOG:  aborting any active transactions
< 2018-02-26 20:31:27.801 IST > LOG:  autovacuum launcher shutting down
< 2018-02-26 20:31:27.804 IST > LOG:  shutting down
< 2018-02-26 20:31:27.967 IST > LOG:  database system is shut down

 

Please help.

 

Thanks & Regards,

Rijo Roy

 

1 ACCEPTED SOLUTION

Accepted Solutions
EDB Team Member

Re: Postgresql Log truncation failing in 9.5

Hi Rijo,

 

Hope you are doing good.

Could you please hash the parameter log_rotation_size in postgresql.conf file and restart your cluster and let us know if it works.

If its not working could you please share the output of show all; from your database.

 

Thanks, Swagata

4 REPLIES
EDB Team Member

Re: Postgresql Log truncation failing in 9.5

Hi Rijo,

 

Hope you are doing good.

Could you please hash the parameter log_rotation_size in postgresql.conf file and restart your cluster and let us know if it works.

If its not working could you please share the output of show all; from your database.

 

Thanks, Swagata

Roy
Adventurer

Re: Postgresql Log truncation failing in 9.5

Hello Swagata,

 

Sorry for  the delayed response, really appreciate your quick response on the topic!

 

I have commented the log_rotation_size parameter yesterday, waiting for a week to complete to observe the rotation and retention. Will update you soon.

 

Many Thanks,

Rijo Roy

Highlighted
Moderator

Re: Postgresql Log truncation failing in 9.5


@Roy wrote:

 

I have commented the log_rotation_size parameter yesterday, waiting for a week to complete to observe the rotation and retention. Will update you soon.

Hi Roy,

 

Just wanted to check in with you on this--has commenting out log_rotation_size solved your problem?

Roy
Adventurer

Re: Postgresql Log truncation failing in 9.5

Hello Richy,

 

Yeah, it solved the problem. Thanks for the help.

 

Much appreciated and really sorry for not responding faster.

 

Thanks,

Roy