Thread: Finding last checkpoint time
Is there a way to find last checkpoint time via SQL command? I know I can grep xlogs by turning on log_checkpoints, but I'd prefer an SQL solution. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
2010/7/20 Devrim GÜNDÜZ <devrim@gunduz.org>: > > Is there a way to find last checkpoint time via SQL command? I know I > can grep xlogs by turning on log_checkpoints, but I'd prefer an SQL > solution. > > -- Or you can use pg_controldata /path/to/pgdata and look at "Time of latest checkpoint". I don't know of any other way, not to say there isn't one. Thom
Devrim GÜNDÜZ wrote: > Is there a way to find last checkpoint time via SQL command? I know I > can grep xlogs by turning on log_checkpoints, but I'd prefer an SQL > solution. > Not directly. Best you can do without linking in new server code is either import the logs via CVS to get them into a table, or shell out and look at what comes out of pg_controldata. There have been two works in progress to improve this situation that didn't make it through to commit yet. http://archives.postgresql.org/pgsql-patches/2008-04/msg00079.php tried to expose just that bit of data and was shot down for a number of reasons, from accuracy concerns to the UI used. The still pending one at http://archives.postgresql.org/message-id/4B959D7A.6010907@joeconway.com just adds a SQL interface to what comes out of pg_controldata. You might prefer to use something like that to calling the shell to parse the output from the utility, if you don't mind adding something new to the server code. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
On Tue, 2010-07-20 at 20:48 +0100, Thom Brown wrote: > > Or you can use pg_controldata /path/to/pgdata and look at "Time of > latest checkpoint". Right. Thanks :) -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Or you can use pg_controldata /path/to/pgdata and look > at "Time of latest checkpoint". Assuming your system is using English. Otherwise, you'll have to build a collection of .po strings as we did for check_postgres.pl. Needless to say, I'd greatly prefer some other way to grab the information! - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 201007220933 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkxISMwACgkQvJuQZxSWSsirbACfa3ujzyTLyzlPbG0QrDUC/0AB BCYAnRfP0E2CJQM+V0qNzgdsi47OjWKB =+XW4 -----END PGP SIGNATURE-----
On Tue, 2010-07-20 at 16:15 -0400, Greg Smith wrote: > Devrim GÜNDÜZ wrote: > > Is there a way to find last checkpoint time via SQL command? I know I > > can grep xlogs by turning on log_checkpoints, but I'd prefer an SQL > > solution. > > > > Not directly. Best you can do without linking in new server code is > either import the logs via CVS to get them into a table, or shell out > and look at what comes out of pg_controldata. > > There have been two works in progress to improve this situation that > didn't make it through to commit yet. <snip> What about adding a column to pg_stat_bgwriter, like "last_checkpoint" or similar? Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachment
Devrim GÜNDÜZ wrote: > What about adding a column to pg_stat_bgwriter, like "last_checkpoint" > or similar? > If you look at the messages I linked to, you'll find that's one of the ideas that's been proposed and shot down. We even had a patch... -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Excerpts from Greg Sabino Mullane's message of jue jul 22 13:34:25 UTC 2010: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: RIPEMD160 > > > Or you can use pg_controldata /path/to/pgdata and look > > at "Time of latest checkpoint". > > Assuming your system is using English. Otherwise, you'll > have to build a collection of .po strings as we did for > check_postgres.pl. Needless to say, I'd greatly prefer > some other way to grab the information! Hmm, wouldn't have it been easier to set LC_MESSAGES to C before calling pg_controldata?
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Hmm, wouldn't have it been easier to set LC_MESSAGES to C before > calling pg_controldata? To be honest, I can't remember why that wasn't working for me when I tried it some time ago. I just verified that it *will* work, however, when I set LANGUAGE (LC_MESSAGES has no effect). Specifically, LANGUAGE changes the headers of pg_controldata (but not the actual output, LC_ALL does that). Thanks for the nudge, I'll get to rewriting some code. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007231456 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkxJ6IgACgkQvJuQZxSWSsi3/QCg8U48WvgzqmN1edj+axXOHQp0 mAYAoNyBBfQ6FQ0yxCRtESpT2bMXa7tT =vRSD -----END PGP SIGNATURE-----
Excerpts from Greg Sabino Mullane's message of vie jul 23 19:08:27 UTC 2010: > > Hash: RIPEMD160 > > > Hmm, wouldn't have it been easier to set LC_MESSAGES to C before > > calling pg_controldata? > > To be honest, I can't remember why that wasn't working for me when > I tried it some time ago. I just verified that it *will* work, > however, when I set LANGUAGE (LC_MESSAGES has no effect). Hmm, now that you mention it, I think I remember different systems honoring different env vars. To be safe you probably want to set the lot of them -- LANGUAGE, LANG, LC_ALL. Note that LC_MESSAGES has no effect if LC_ALL is set. I think other vars also override LC_MESSAGES in some systems. Still, this is way better than a collection of translated strings; consider a translator fixing a typo, or choosing a different wording, or a new language being added.
Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> > Hmm, wouldn't have it been easier to set LC_MESSAGES to C before
> > calling pg_controldata?
>
> To be honest, I can't remember why that wasn't working for me when
> I tried it some time ago. I just verified that it *will* work,
> however, when I set LANGUAGE (LC_MESSAGES has no effect).
>
> Specifically, LANGUAGE changes the headers of pg_controldata
> (but not the actual output, LC_ALL does that). Thanks for the
> nudge, I'll get to rewriting some code.
pg_upgrade does this in controldata.c for this exact reason:
/*
* Because we test the pg_resetxlog output strings, it has to be in
* English.
*/
if (getenv("LANG"))
lang = pg_strdup(ctx, getenv("LANG"));
#ifndef WIN32
putenv(pg_strdup(ctx, "LANG=C"));
#else
SetEnvironmentVariableA("LANG", "C");
#endif
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> Greg Sabino Mullane wrote:
>> Specifically, LANGUAGE changes the headers of pg_controldata
>> (but not the actual output, LC_ALL does that). Thanks for the
>> nudge, I'll get to rewriting some code.
> pg_upgrade does this in controldata.c for this exact reason:
> /*
> * Because we test the pg_resetxlog output strings, it has to be in
> * English.
> */
> if (getenv("LANG"))
> lang = pg_strdup(ctx, getenv("LANG"));
> #ifndef WIN32
> putenv(pg_strdup(ctx, "LANG=C"));
> #else
> SetEnvironmentVariableA("LANG", "C");
> #endif
You do realize that's far from bulletproof? To be sure that that does
anything, you'd need to set (or unset) LC_ALL and LC_MESSAGES as well.
And I thought Windows spelled it LANGUAGE not LANG ...
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Greg Sabino Mullane wrote:
> >> Specifically, LANGUAGE changes the headers of pg_controldata
> >> (but not the actual output, LC_ALL does that). Thanks for the
> >> nudge, I'll get to rewriting some code.
>
> > pg_upgrade does this in controldata.c for this exact reason:
>
> > /*
> > * Because we test the pg_resetxlog output strings, it has to be in
> > * English.
> > */
> > if (getenv("LANG"))
> > lang = pg_strdup(ctx, getenv("LANG"));
> > #ifndef WIN32
> > putenv(pg_strdup(ctx, "LANG=C"));
> > #else
> > SetEnvironmentVariableA("LANG", "C");
> > #endif
>
> You do realize that's far from bulletproof? To be sure that that does
> anything, you'd need to set (or unset) LC_ALL and LC_MESSAGES as well.
> And I thought Windows spelled it LANGUAGE not LANG ...
Well, this has been tested on Windows in a Japanese environment. I see
test/regress/pg_regress.c does have all the settings you suggest. I
will add them too.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +