Finding tables & columns at risk of hitting max value of int
As you mention auto-incrementing, it sounds like you want to check for sequence exhaustion - check_postgres has a sequence check which will warn about this.
Sequence metadata is stored separately from the associated tables, so is relatively easy to access without having to check each table individually. If you're using PostgreSQL 10 or later, the pg_sequences view provides a useful overview of the current state of all sequences. The default sequence naming makes it obvious which table/column the sequence is associated with (though it's also possible to create a query which returns the actual table/column name as well).