Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #5935: Log lotation not working for default log format

BUG #5935: Log lotation not working for default log format

From
"Brad Nicholson"
Date:
The following bug has been logged online:

Bug reference:      5935
Logged by:          Brad Nicholson
Email address:      bnicholson@hp.com
PostgreSQL version: 9.0.3
Operating system:   CentOS Release 5.5
Description:        Log lotation not working for default log format
Details:

PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
CentOS release 5.5 (Final)

(using the standard PG package)

The logfile will not rotate if the log_filename is set to postgresql-%a.log,
which is the default setting after an initdb on my platform.


I have tested this by setting log_rotation_size and exceeding that threshold
and manually trying to rotate the logs with SELECT pg_rotate_logfile().
Both fail to rotate the log file.


Manual Rotate:

[postgres@pg1 pgdata]$ ls -al logs/
total 24
drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 .
drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 ..
-rw------- 1 postgres postgres  150 Mar 17 13:11 postgresql-Thu.log
[postgres@pg1 pgdata]$ psql -p 6432  -c "show log_filename;" postgres
   log_filename
-------------------
 postgresql-%a.log
(1 row)

[postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()"
postgres
 pg_rotate_logfile
-------------------
 t
(1 row)

[postgres@pg1 pgdata]$ ls -al logs/
total 24
drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 .
drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 ..
-rw------- 1 postgres postgres  150 Mar 17 13:11 postgresql-Thu.log



Size based automatic rotation:

[postgres@pg1 pgdata]$ psql -p 6432  -c "show log_rotation_size;" postgres
 log_rotation_size
-------------------
 1MB
(1 row)

<generate some pgbench traffic with full statement logging>

[postgres@pg1 pgdata]$ ls -thl logs/
total 3.2M
-rw------- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log



If I switch the log_filename to something more specific like
postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates.

Re: BUG #5935: Log lotation not working for default log format

From
Korry Douglas
Date:
What would you expect the new log file to be named?  Your log_filename is s=
et to postgresql-%a.log.  The %a part expands to the current day of the wee=
k.  If it's Thursday and you already have a file for Thursday, what would t=
he new file name be?

        -- Korry

> The following bug has been logged online:
>=20
> Bug reference:      5935
> Logged by:          Brad Nicholson
> Email address:      bnicholson@hp.com
> PostgreSQL version: 9.0.3
> Operating system:   CentOS Release 5.5
> Description:        Log lotation not working for default log format
> Details:=20
>=20
> PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
> 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
> CentOS release 5.5 (Final)
>=20
> (using the standard PG package)
>=20
> The logfile will not rotate if the log_filename is set to postgresql-%a.l=
og,
> which is the default setting after an initdb on my platform.
>=20
>=20
> I have tested this by setting log_rotation_size and exceeding that thresh=
old
> and manually trying to rotate the logs with SELECT pg_rotate_logfile().=
=20
> Both fail to rotate the log file.
>=20
>=20
> Manual Rotate:
>=20
> [postgres@pg1 pgdata]$ ls -al logs/
> total 24
> drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 .
> drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 ..
> -rw------- 1 postgres postgres  150 Mar 17 13:11 postgresql-Thu.log
> [postgres@pg1 pgdata]$ psql -p 6432  -c "show log_filename;" postgres
>   log_filename
> -------------------
> postgresql-%a.log
> (1 row)
>=20
> [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()"
> postgres
> pg_rotate_logfile
> -------------------
> t
> (1 row)
>=20
> [postgres@pg1 pgdata]$ ls -al logs/
> total 24
> drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 .
> drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 ..
> -rw------- 1 postgres postgres  150 Mar 17 13:11 postgresql-Thu.log
>=20
>=20
>=20
> Size based automatic rotation:
>=20
> [postgres@pg1 pgdata]$ psql -p 6432  -c "show log_rotation_size;" postgres
> log_rotation_size
> -------------------
> 1MB
> (1 row)
>=20
> <generate some pgbench traffic with full statement logging>
>=20
> [postgres@pg1 pgdata]$ ls -thl logs/
> total 3.2M
> -rw------- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log
>=20
>=20
>=20
> If I switch the log_filename to something more specific like
> postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates.
>=20
> --=20
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

Re: BUG #5935: Log lotation not working for default log format

From
"Nicholson, Brad (Toronto, ON, CA)"
Date:
You could appending something onto the logfile to differentiate it, but I c=
an understand why you wouldn't want to as that would change the filename fo=
rmat.

If this is the case, the call to pg_rotate_logfile() should return false.

I'd also say that starting up and allowing a file size based rotation with =
a log filename that does not allow it to be rotated is also broken.

Brad.

> -----Original Message-----
> From: Korry Douglas [mailto:korry.douglas@enterprisedb.com]
> Sent: Thursday, March 17, 2011 4:00 PM
> To: Nicholson, Brad (Toronto, ON, CA)
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] BUG #5935: Log lotation not working for default log
> format
>=20
> What would you expect the new log file to be named?  Your log_filename
> is set to postgresql-%a.log.  The %a part expands to the current day of
> the week.  If it's Thursday and you already have a file for Thursday,
> what would the new file name be?
>=20
>         -- Korry
>=20
> > The following bug has been logged online:
> >
> > Bug reference:      5935
> > Logged by:          Brad Nicholson
> > Email address:      bnicholson@hp.com
> > PostgreSQL version: 9.0.3
> > Operating system:   CentOS Release 5.5
> > Description:        Log lotation not working for default log format
> > Details:
> >
> > PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC)
> > 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
> > CentOS release 5.5 (Final)
> >
> > (using the standard PG package)
> >
> > The logfile will not rotate if the log_filename is set to postgresql-
> %a.log,
> > which is the default setting after an initdb on my platform.
> >
> >
> > I have tested this by setting log_rotation_size and exceeding that
> threshold
> > and manually trying to rotate the logs with SELECT
> pg_rotate_logfile().
> > Both fail to rotate the log file.
> >
> >
> > Manual Rotate:
> >
> > [postgres@pg1 pgdata]$ ls -al logs/
> > total 24
> > drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 .
> > drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 ..
> > -rw------- 1 postgres postgres  150 Mar 17 13:11 postgresql-Thu.log
> > [postgres@pg1 pgdata]$ psql -p 6432  -c "show log_filename;" postgres
> >   log_filename
> > -------------------
> > postgresql-%a.log
> > (1 row)
> >
> > [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()"
> > postgres
> > pg_rotate_logfile
> > -------------------
> > t
> > (1 row)
> >
> > [postgres@pg1 pgdata]$ ls -al logs/
> > total 24
> > drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 .
> > drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 ..
> > -rw------- 1 postgres postgres  150 Mar 17 13:11 postgresql-Thu.log
> >
> >
> >
> > Size based automatic rotation:
> >
> > [postgres@pg1 pgdata]$ psql -p 6432  -c "show log_rotation_size;"
> postgres
> > log_rotation_size
> > -------------------
> > 1MB
> > (1 row)
> >
> > <generate some pgbench traffic with full statement logging>
> >
> > [postgres@pg1 pgdata]$ ls -thl logs/
> > total 3.2M
> > -rw------- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log
> >
> >
> >
> > If I switch the log_filename to something more specific like
> > postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates.
> >
> > --
> > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-bugs

Re: BUG #5935: Log lotation not working for default log format

From
Korry Douglas
Date:
> You could appending something onto the logfile to differentiate it, but I=
 can understand why you wouldn't want to as that would change the filename =
format.
>=20
> If this is the case, the call to pg_rotate_logfile() should return false.

pg_rotate_logfile() doesn't actually rotate the log file, it just schedules=
 the rotation to happen as soon as possible.  So it doesn't know whether or=
 not the rotation will actually succeed.  pg_rotate_logfile() only returns =
FALSE if you haven't started the log collector.

> I'd also say that starting up and allowing a file size based rotation wit=
h a log filename that does not allow it to be rotated is also broken.

That filename pattern *does* permit rotation, just no more frequently than =
every 24 hours.

Consider what would happen if you set log_filename to something like pg_log=
.%H (where %H is replaced with the hour) and your log size exceeded the log=
_rotation_size within a given hour - same problem.=20=20

The key is to pick a filename pattern that *can* rotate given the value you=
 set for log_rotation_size.

It might be nice if the logfile naming mechanism supported a new escape seq=
uence that meant "replace me with the next available sequence number... %n =
might translate to _1, _2, _3, ...).

            -- Korry

P.S. I'm not disagreeing with you, just explaining the code as it exists to=
day.

>=20
> Brad.
>=20
>> -----Original Message-----
>> From: Korry Douglas [mailto:korry.douglas@enterprisedb.com]
>> Sent: Thursday, March 17, 2011 4:00 PM
>> To: Nicholson, Brad (Toronto, ON, CA)
>> Cc: pgsql-bugs@postgresql.org
>> Subject: Re: [BUGS] BUG #5935: Log lotation not working for default log
>> format
>>=20
>> What would you expect the new log file to be named?  Your log_filename
>> is set to postgresql-%a.log.  The %a part expands to the current day of
>> the week.  If it's Thursday and you already have a file for Thursday,
>> what would the new file name be?
>>=20
>>         -- Korry
>>=20
>>> The following bug has been logged online:
>>>=20
>>> Bug reference:      5935
>>> Logged by:          Brad Nicholson
>>> Email address:      bnicholson@hp.com
>>> PostgreSQL version: 9.0.3
>>> Operating system:   CentOS Release 5.5
>>> Description:        Log lotation not working for default log format
>>> Details:
>>>=20
>>> PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc
>> (GCC)
>>> 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
>>> CentOS release 5.5 (Final)
>>>=20
>>> (using the standard PG package)
>>>=20
>>> The logfile will not rotate if the log_filename is set to postgresql-
>> %a.log,
>>> which is the default setting after an initdb on my platform.
>>>=20
>>>=20
>>> I have tested this by setting log_rotation_size and exceeding that
>> threshold
>>> and manually trying to rotate the logs with SELECT
>> pg_rotate_logfile().
>>> Both fail to rotate the log file.
>>>=20
>>>=20
>>> Manual Rotate:
>>>=20
>>> [postgres@pg1 pgdata]$ ls -al logs/
>>> total 24
>>> drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 .
>>> drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 ..
>>> -rw------- 1 postgres postgres  150 Mar 17 13:11 postgresql-Thu.log
>>> [postgres@pg1 pgdata]$ psql -p 6432  -c "show log_filename;" postgres
>>>  log_filename
>>> -------------------
>>> postgresql-%a.log
>>> (1 row)
>>>=20
>>> [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()"
>>> postgres
>>> pg_rotate_logfile
>>> -------------------
>>> t
>>> (1 row)
>>>=20
>>> [postgres@pg1 pgdata]$ ls -al logs/
>>> total 24
>>> drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 .
>>> drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 ..
>>> -rw------- 1 postgres postgres  150 Mar 17 13:11 postgresql-Thu.log
>>>=20
>>>=20
>>>=20
>>> Size based automatic rotation:
>>>=20
>>> [postgres@pg1 pgdata]$ psql -p 6432  -c "show log_rotation_size;"
>> postgres
>>> log_rotation_size
>>> -------------------
>>> 1MB
>>> (1 row)
>>>=20
>>> <generate some pgbench traffic with full statement logging>
>>>=20
>>> [postgres@pg1 pgdata]$ ls -thl logs/
>>> total 3.2M
>>> -rw------- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log
>>>=20
>>>=20
>>>=20
>>> If I switch the log_filename to something more specific like
>>> postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates.
>>>=20
>>> --
>>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-bugs
>=20

-----------------------------------------------------------------------
Korry Douglas
Senior Database Dude
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Phone: (804)241-4301
Mobile: (620) EDB-NERD