I am trying to determine the optimal Standby for automated failover based on the highest value of pg_last_wal_receive_lsn() of all slaves. But what if max_wal_size is reached, does pg_lsn values go to 0?
Please suggest if there's any better approach to determine node with the best RPO out of all secondaries.
The log sequence number (LSN) is never reset to 0, so you don't have to worry about that. There is no limit, and there is no connection to the parameter max_wal_size: that parameter governs when checkpoints are triggered and has no direct connection with the size of pg_wal or the LSN.
Laurenz's answer is mostly correct, but is technically wrong on one important point. There is a limit, which is at the end of unsigned 64-bit address space. This means you have around 18 exabytes of life-time write capacity on a single PostgreSQL database. After that point, I assume that PostgreSQL would be unable to write log segments after and effectively stop working. We can call that limit "the end of history."
Before you reach that point, you could logically replicate the database to another cluster and start over. However to my knowledge nobody has ever even come close to that limit. The limit is currently well out of reach of all current users of Postgres, but at some point I assume this could change and then I assume PostgreSQL will come up with a workaround.
