pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
A way to query last checkpoint record and WAL log through SQL?
2+ messages / 2 participants
[nested] [flat]

* A way to query last checkpoint record and WAL log through SQL?
@ 2007-05-02 14:58 Keaton Adams <kadams@mxlogic.com>
  2007-05-05 08:16 ` Re: A way to query last checkpoint record and WAL log through SQL? Hannes Dorbath <light@theendofthetunnel.de>
  0 siblings, 1 reply; 2+ messages in thread

From: Keaton Adams @ 2007-05-02 14:58 UTC (permalink / raw)
  To: pgsql-admin

I have looked in the archives for an answer to this and have not found
one as of yet, so I guess I'll pose the question here.

 

I'm working to set up a high-availability PostgreSQL server using WAL
shipping.  Everything works very well with the set of scripts I have
developed and I'm down to my final issue to resolve.  To ensure that the
failover server can come up even if there is a catastrophic failure on
the primary server I need to keep archived, shipped logs around long
enough to get back to a good checkpoint record for recovery, but I can't
keep the archived log files around too long because of space
considerations.  

 

I need to know if there is a sure-fire way to identify the last
checkpoint record and the associated log file name without having to
rely on sed/greping for strings out of pg_controldata output.  I have
looked through the views, control tables and functions and nothing is
apparent on what I could possibly query through SQL to arrive at this
answer.  I understand I can do something like this with pg_controldata:

 

$ pg_controldata | grep -e"Prior checkpoint"

Prior checkpoint location:          0/66CA0174

 

But I'm not confident in the ability to parse out the correct xlog file
name from this string each and every time I need to.  I want to be able
to positively identify the log with the prior checkpoint value and be
able to remove archived, shipped WAL logs older than this one.  I would
rather not have to rely on wall clock time or some other mechanism that
doesn't include the ability to ensure that the WAL log with the prior
checkpoint record is preserved.  

 

Of course, if the primary sever suffers a catastrophic failure where the
last set of WAL logs are lost, without the prior checkpoint record (and
related log file) available the standby database won't come online.  I
can reset the log with pg_resetxlog to bring the DB online, but I have
experienced data corruption as a result.

 

Any info you can provide would be appreciated.

 

Thanks,

 

Keaton

 

 

 

 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: A way to query last checkpoint record and WAL log through SQL?
  2007-05-02 14:58 A way to query last checkpoint record and WAL log through SQL? Keaton Adams <kadams@mxlogic.com>
@ 2007-05-05 08:16 ` Hannes Dorbath <light@theendofthetunnel.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Hannes Dorbath @ 2007-05-05 08:16 UTC (permalink / raw)
  To: Keaton Adams <kadams@mxlogic.com>

Keaton Adams wrote:
> I'm working to set up a high-availability PostgreSQL server using WAL
> shipping. 

Well, I have no answer to your question, but IMHO DRBD is usually the
better approach for PG-HA. It's easy to setup, you don't lose a single
commit, no such headaches, no custom scripts and no hacks required. You
can still use PITR with it.


-- 
Best regards,
Hannes Dorbath



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2007-05-05 08:16 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-02 14:58 A way to query last checkpoint record and WAL log through SQL? Keaton Adams <kadams@mxlogic.com>
2007-05-05 08:16 ` Hannes Dorbath <light@theendofthetunnel.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox