I want to manage the copy of archiving to remote location. For that I want to delete all the archives I copied unless they are required by the standby.

So, for running pg_archivecleanup $ARCLOC $CHECKPOINT I need to find the most recent checkpoint which is ok to delete. I don't look on the recent .backup files as it is too old for my archive capacity. What I do want to look on is the replay_location \ replay_lsn(depend on the version) from pg_stat_replication.

However, I do not know how to convert from replay_location value to checkpoint value. I.e. from something like 7/2DBCED18 to 000000020000000700000029.


Function is: pg_xlogfile_name


When you start a backup or stop a backup you'll get a WAL location, if you want to know it's physical location on disk use the following function: select pg_xlogfile_name('7/2DBCED18');

| improve this answer | |
  • Please explain a bit more how this works. – ypercubeᵀᴹ Jul 2 '18 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.