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