Обсуждение: Monitoring Transaction Log size

Ziegelwanger, Silvio wrote:
> Hi,
>
>
>
> how can I monitor the size of the transaction log files using SQL Statements?

You can't. You would have to write a custom function to heck the size of
the xlog directory.

Sincerely,

Joshua D. Drake

>
>
>
> Best Regards
>
>
>
> Silvio
>
>
>
> Silvio Ziegelwanger
> Research & Development
>
> Fabalabs Software GmbH
> Honauerstraße 4
> 4020 Linz
> Austria
> Tel: [43] (732) 60 61 62
> Fax: [43] (732) 60 61 62-609
> E-Mail: silvio.ziegelwanger@fabasoft.com
> www.fabasoft.com <http://www.fabasoft.com/>
>
>   <http://www.fabasoft.at/fabasofthomepage2006/fabasoftgruppe/veranstaltungen/fabasoftegovdays07.htm>
>
> Fabasoft egovday 07
> Trends im E-Government, Innovationen für eine
> zukunftsorientierte Verwaltung und digitale Geschäftsprozesse.
>
> 23. Januar 2007, Bern
>
> 30. Januar 2007, Berlin
>
> 6. Februar 2007, Bonn
>
>
>
>


--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake wrote:
> Ziegelwanger, Silvio wrote:
>> Hi,
>>
>>
>>
>> how can I monitor the size of the transaction log files using SQL Statements?
>
> You can't. You would have to write a custom function to heck the size of
> the xlog directory.

wel in recent versions of pg it should be pretty easy to do that from
within SQL by using pg_ls_dir() and pg_stat_file().

maybe something(rough sketch) along the line of:

select sum((pg_stat_file('pg_xlog/' || file)).size) from
pg_ls_dir('pg_xlog') as file where file ~ '^[0-9A-F]';

might do the trick


Stefan

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Ziegelwanger, Silvio wrote:
>> how can I monitor the size of the transaction log files using SQL Statements?

> You can't. You would have to write a custom function to heck the size of
> the xlog directory.

Perhaps more to the point, why do you think you need to?  pg_xlog should
stay pretty level at approximately 2*checkpoint_segments xlog files
(once it's ramped up to that size, which might take a heavy burst of
activity if checkpoint_segments is large).

            regards, tom lane

On Wed, 2007-01-17 at 23:03 +0500, Shoaib Mir wrote:
> archive_timeout (came in ver 8.2) might help you with customizing the
> size for log files.

I'm not sure that it will.

If anything it could produce more log files, which could lead to a
backlog if the archive_command isn't functioning for some reason.

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com