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.
Answer:
Function is: pg_xlogfile_name
Best Answer
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');