Even if you have your prod database on RDS, you might be able to spin up a local instance for testing. My Postgres-using applications have a script that sets up and runs a local Postgres instance for the duration of the test. It takes about 2 seconds on first run, and less than noticeable time when the DB has already been initialized.
We have been using RDS for production and normal Postgres during development for about 5 years and the only thing you have to watch out for we found is that RDS supports only certain extensions and they are fairly slow making new Postgres major versions available. So e.g. if you want to use TimescaleDB you either have to run it yourself or use a proprietary AWS alternative.
It is just a Postgres running, but you have limited control about its configuration.
So as long as you make sure during development that you are not doing something with the database that RDS doesn't support you should be fine.
I don't know about Aurora, but I reckon it is similar. Standard Postgres features are likely going to work fine, but more advanced things are not going to be available on Aurora.
Basically Postgres in Docker would be highly flexible in terms of what versions (major or minor), extensions, your own customisations for the configurations in pg.conf you want to go with.
You can upgrade/patch as soon as Postgres community makes these available. With RDS you have to wait and for Aurora surely significant waiting periods! e.g. Aurora Postgres is currently 2 major and 2 minor versions behind - meaning you would be missing the latest and greatest of Postgres!
I would suggest to use Postgres from community on EC2 or Containers or if you want Postgres as a service then use Postgres operator on EKS or any K8S distro available on AWS. You can easily go back and forth between this env and RDS or even any platform making your Postgres databases completely platform agnostic.
not necessarily. If the positives of the RDS environment are more important to me than running my own database on EC2 in a docker with the latest features, i am totally fine with giving up some DB related features.
https://github.com/sapcc/keppel/blob/master/testing/with-pos...