Re: Replication lag from transaction logs

> On Jun 18, 2018, at 9:56 AM, Debraj Manna <subharaj(dot)manna(at)gmail(dot)com> wrote:
>
> Thanks Keith this is useful.
>
> One more query if I need to know that if a have fallen too far behind and the WAL is not available. I guess I can do this. Let me know if my understanding is correct.
> • Run pg_controldata <DATA_DIR> on the slave node which has been down for long.
> • It will output the details about the WAL along with the WAL file name from where it will start the replication. (Field to look for in the output– 'Latest checkpoint's REDO WAL file')
> • Then check if the file mentioned in ` 'Latest checkpoint's REDO WAL file' is present in master `pg_wal` directory. If not then slave have fallen too far behind and will not be able to recover from WAL.

You could also try bringing the slave back up, and monitoring the log for the error about needed WAL file not being available--this avoids the race condition between checking that all WAL is available and restarting the slave.

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
https://www.linkedin.com/in/scottribe/