Finding last checkpoint time

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

-----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-----

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?

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

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

-----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. +