Re: log_truncate_on_rotation=on is not truncating
От | Viswanatham Kiran Kumar |
---|---|
Тема | Re: log_truncate_on_rotation=on is not truncating |
Дата | |
Msg-id | 009901cd4fad$4b5ff9d0$e21fed70$@kirankumar@huawei.com обсуждение исходный текст |
Ответ на | log_truncate_on_rotation=on is not truncating (Chan Pham <chan.pham@iovation.com>) |
Ответы |
Re: log_truncate_on_rotation=on is not truncating
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
Hi Chan, I think this is configuration issue. This issue is happening because log files will always append when server is restarted. What I can see from your configuration, log_rotation_age=3D1d (1 day) which is greater than daily schedule shutdown/restart time. So this is the reason why log files are growing. You can change the log_rotation_age=3D8h (i.e. any time less than the schedule shutdown/restart time) as log file name=20 Is postgreql-%a.log this is sufficient to preserve till last week's log, as at the time of rotation after 8 hours if the day is not changed then log file name will be same as previous then truncation will not happen. =A0 Thanks & Regards, Kiran Kumar =A0=A0=A0 -------------------------- VISWANATHAM=A0 KIRAN KUMAR SENIOR TECHNICAL LEADER. HUAWEI TECHNOLOGIES INDIA PVT. LTD. EXTN: 7560; LEVEL-7 C & D TOWER DIAMOND DISTRICT, OLD AIRPORT ROAD BANGALORE-08 MOBILE: +91-9945115987 =A0 From: pgsql-bugs-owner@postgresql.org [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Chan Pham Sent: Wednesday, June 20, 2012 11:54 PM To: pgsql-bugs@postgresql.org Subject: [BUGS] log_truncate_on_rotation=3Don is not truncating We discovered that on our servers where we do daily shutdown and startup for a cold backup, the pg_log logs are not truncated when it switches to the new day, when log_truncation_on_rotation=3Don. The shutdown/startup of postgres= ql happens daily at 16:00 system time. Also observed that only when the shutdown event happen at 16:00 pm, it is only when the log is rotated for the current day, and the event message is appended to last week's postgreql-%a.log.=A0 On our other servers where there is not a daily shutdown/startup, the postgresql-%a.log is rotated to the new day at 00:00 hour and is truncated of all last week's content. We first noticed this only our standby servers where we do daily cold backup and restart postgresql, and no other activities. Then I was able to reproduce this issue on a standalone server by scheduling a daily shutdown/restart. Here are settings and log details: Postgresql is shutdown/startup via service: =A0 =A0/sbin/service postgresql-9.1 stop/start =A0-- calls pg_ctl OS versions: =A0centos-release-notes-5.5-0 =A0 and =A0centos-release-6-2.el6.centos.7.x86_64 PostgreSQL version: =A0 =A0PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) = 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit =A0 =A0and =A0 =A0PostgreSQL 9.1.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) = 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit Postgresql.conf settings for pg_logs related: =A0 =A0logging_collector=3Don =A0 =A0log_directory=3D'pg_log' =A0 =A0log_filename=3D'postgresql-%a.log' =A0 =A0log_truncate_on_rotation=3Don =A0 =A0log_rotation_age=3D1d =A0 =A0log_rotation_size=3D0 =A0 =A0timezone =3D 'UTC' Service timezone: =A0 >date Tue Jun 19 11:25:56 PDT 2012 Content of a postgresql-Tue.log in its entirety. Note this is a standby slave so there are not much activities other than shutdown/restart performed for a cold backup. The bold date is just to de-mark the current day where the entries were appended. The entries of 2012-06-19 was an intentional syntax error to get the an entry for the current day. 2012-06-12 16:00:02 PDT =A0 23761 4fd67fc4.5cd1 LOG: =A0received fast shutd= own request 2012-06-12 16:00:02 PDT =A0 23761 4fd67fc4.5cd1 LOG: =A0aborting any active transactions 2012-06-12 16:00:02 PDT =A0 23800 4fd67fc8.5cf8 FATAL: =A0terminating walreceiver process due to administrator command 2012-06-12 16:00:02 PDT =A0 23765 4fd67fc5.5cd5 LOG: =A0shutting down 2012-06-12 16:00:02 PDT =A0 23765 4fd67fc5.5cd5 LOG: =A0database system is = shut down 2012-06-12 16:31:08 PDT =A0 30458 4fd7d13c.76fa LOG: =A0database system was= shut down in recovery at 2012-06-12 16:00:02 PDT 2012-06-12 16:31:08 PDT =A0 30458 4fd7d13c.76fa LOG: =A0entering standby mo= de 2012-06-12 16:31:08 PDT =A0 30458 4fd7d13c.76fa LOG: =A0redo starts at 6B/D3E1EA70 2012-06-12 16:31:10 PDT =A0 30458 4fd7d13c.76fa LOG: =A0consistent recovery state reached at 6B/DB8FDFE8 2012-06-12 16:31:10 PDT =A0 30458 4fd7d13c.76fa LOG: =A0unexpected pageaddr 69/D68FE000 in log file 107, segment 219, offset 9428992 2012-06-12 16:31:10 PDT =A0 30454 4fd7d13a.76f6 LOG: =A0database system is = ready to accept read only connections 2012-06-12 16:31:11 PDT =A0 30494 4fd7d13e.771e LOG: =A0streaming replicati= on successfully connected to primary 2012-06-19 11:22:04 PDT postgres [local] 30698 4fe0c34c.77ea ERROR: =A0synt= ax error at or near ":" at character 17 2012-06-19 11:22:04 PDT postgres [local] 30698 4fe0c34c.77ea STATEMENT: =A0select version(): Listing of pg_log directory. You can see here that the last entry of each day (file timestamp) is when postgresql is restarted at 16:30. Except June 19, I just force a syntax error to create an entry for today, June 19: -rw------- 1 postgres postgres 1403 Jun 13 16:30 postgresql-Wed.log -rw------- 1 postgres postgres 2868 Jun 14 16:31 postgresql-Thu.log -rw------- 1 postgres postgres 2418 Jun 15 16:31 postgresql-Fri.log -rw------- 1 postgres postgres 2222 Jun 16 16:31 postgresql-Sat.log -rw------- 1 postgres postgres 2208 Jun 17 16:30 postgresql-Sun.log -rw------- 1 postgres postgres 2832 Jun 18 16:31 postgresql-Mon.log -rw------- 1 postgres postgres 1355 Jun 19 11:22 postgresql-Tue.log Here's normal behavior on a server that does NOT get restarted daily. The logs are all rotated and truncated at 00:00 as expected: $ pwd /data/postgres/9.1/pg_log ls -ltr total 4 -rw------- 1 postgres postgres =A0 =A00 Jun 13 00:00 postgresql-Wed.log -rw------- 1 postgres postgres =A0 =A00 Jun 14 00:00 postgresql-Thu.log -rw------- 1 postgres postgres 1563 Jun 15 14:12 postgresql-Fri.log -rw------- 1 postgres postgres =A0 =A00 Jun 16 00:00 postgresql-Sat.log -rw------- 1 postgres postgres =A0 =A00 Jun 17 00:00 postgresql-Sun.log -rw------- 1 postgres postgres =A0 =A00 Jun 18 00:00 postgresql-Mon.log -rw------- 1 postgres postgres =A0 =A00 Jun 19 00:00 postgresql-Tue.log --=20 Chan Pham Database Administrator Direct: 503.943.6773 Fax: 503.224.1581 // AIM: ioChanny // Twitter: iovation www.iovation.com
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: BUG #6699: pg_restore with -j -- doesn't restore view that groups by primary key
Следующее
От: "Kevin Grittner"Дата:
Сообщение: Re: BUG #6701: IS NOT NULL doesn't work on complex composites