My understanding is that the third-party licensing policy page is simply guidelines for how to interpret ASLv2. The resolved page insists that LGPL shouldn't be included in apache projects. I do think that extends to any project with ASLv2 license since it seems like an interpretation of the license itself.
LGPL is a great license. I can understand why LGPL was chosen for postgresql and its various subprojects. It makes perfect sense to control the rights of a project and guide users to contribute back to the original code base. psycopg2 is, how ever, a client. It seems less likely that a client would be forked than the postgresql code base itself. Also, making a client packageable in every other project seems like a great goal, irrespective of licensing.
Just to be clear, PostgreSQL does *not* use LGPL. It uses the Postgresql License, which is a permissive license similar to the MIT license.
Subprojects choose their own licenses of course. Some adopt the same license, some use one of the other standard licenses.