Thread: Monitoring Transaction Log size
Hi,
how can I monitor the size of the transaction log files using SQL Statements?
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
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
Attachment
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/
archive_timeout (came in ver 8.2) might help you with customizing the size for log files.
-----------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
-----------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 1/17/07, Joshua D. Drake <jd@commandprompt.com> 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.
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/
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
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
Suggested in case he wants to do a log switch after certain amount of time...
-----------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
-----------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 1/18/07, Simon Riggs <simon@2ndquadrant.com> wrote:
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