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