escape_string_warning has been on by default for about twenty years, and on a modern PostgreSQL configured normally it will never once speak to you. That is not a malfunction. The condition it warns about stopped being the default in 2011.
Default on, context user. What it watches for is narrow: a backslash inside an ordinary '...' string literal, and only while standard_conforming_strings is off. Meet both conditions and you get a WARNING about the nonstandard use of a backslash in a string literal, with a nudge toward the E'...' escape syntax. Miss either, and it stays quiet.
To see why it exists you have to go back to a genuine wart. The SQL standard says a backslash in an ordinary string literal is just a backslash: '\n' is two characters, a backslash and an n. PostgreSQL, for most of its early life, did the C thing instead and treated that backslash as an escape, so '\n' was a newline. Convenient, non-standard, and a portability trap. The fix arrived in two pieces: E'...' syntax, which explicitly asks for escape processing, and standard_conforming_strings, which controls whether ordinary literals follow the standard. Its default flipped to on in PostgreSQL 9.1. escape_string_warning was the tool that made the transition survivable: with the old behavior still in force, it lit up every ordinary literal containing a backslash, so you could find the code that assumed escapes and move it to E'...' before the meaning of those strings changed underneath it.
This was never really about standards pedantry. Treating backslash as an escape in ordinary literals is a SQL-injection hazard: in some client encodings a multibyte character’s last byte is numerically equal to ASCII backslash, so it can absorb the quote meant to close a string, and client code that escapes quotes by hand can be maneuvered into an injection. That is the same danger backslash_quote and standard string handling exist to remove, and it is the real reason the project pushed everyone off the old behavior. escape_string_warning was the detection half of that effort: it did not close the hole, it helped you find the code sitting in it.
Today the migration is long over and standard_conforming_strings is on out of the box, so on a normally configured server this warning has nothing to report. Which makes the day it does start firing worth noticing. It means something in your stack has set standard_conforming_strings off: a legacy application’s connection settings, an old dump loaded without adjustment, an ORM running a compatibility mode. Your string literals are being read the old, non-standard, injection-prone way again. The warning is not the problem; the off is. Fix that, use E'...' where you actually want escapes, and the warning goes quiet on its own, because a standard literal has no nonstandard backslash to complain about.
So leave escape_string_warning on. It costs nothing, it says nothing while your database is behaving, and on the rare occasion it speaks it is pointing straight at a real misconfiguration. It is less a setting than a tripwire left behind by a decision the project made in 2011, and one you have no reason to be quietly un-making.