Can split test regime so that as much as possible is covered with SQLite, and then have a second test phase with a heavyweight db only if the first phase passes. So code errors, malformed SQL, etc. cause it to fail fast and early, and you only test with the real DB once you know everything else is working.
Or along similar lines you could divide it such that developers can test things locally on their machines with SQLite, but once it gets pushed into CI (and passes code review etc.) it's tested against the heavy db.
That still doesn't fix the compatibility issues. Postgres has features/syntax that sqlite does not have, so you can't test postgres syntax with sqlite sometimes
I meant in situations like parent comment where you're using an ORM such as hibernate that supports multiple databases, you can test as much of the non-DB specific stuff with SQLite in-memory and then do a separate batch of tests with DB specific behaviour.
