Обсуждение: 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

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

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

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