On the project I work on, we face a similar problem. We use H2 for an in memory test database, but now we're frequently running into the problem where we'd love to use a specific postgres feature (say, json support) that isn't supported by H2.
That, and occasionally we find syntax differences that cause a headache when doing a database setup/teardown. A single minor SQL difference requires us to create separate H2/Postgres flyway configurations.
I think a better option than H2 or SQLite, that we're currently investigating, is using Docker to bring up a local postgres instance for testing.
(All of that assumes a certain dedication to using Postgres. If you want to be database agnostic, then you may in fact be better off not using Postgres in dev/test just to force yourself to remain compatible with other DBs.)
That, and occasionally we find syntax differences that cause a headache when doing a database setup/teardown. A single minor SQL difference requires us to create separate H2/Postgres flyway configurations.
I think a better option than H2 or SQLite, that we're currently investigating, is using Docker to bring up a local postgres instance for testing.
(All of that assumes a certain dedication to using Postgres. If you want to be database agnostic, then you may in fact be better off not using Postgres in dev/test just to force yourself to remain compatible with other DBs.)