One of the main guidelines I keep seeing for successfully building a microservice application is to use a separate database for each microservice. In diagrams these are usually depicted as physically separate databases.
My question is, could a single Postgres process that manages several separate databases be an acceptable solution in a microservice application, or should you aim for having a dedicated Postgres process managing each database as well?
Short answer: Yes, a single Postgres server can serve multiple (micro)services, no problem.
Long answer: The goal of having a separate database for each service, is to ensure that each service is as decoupled as possible.
Lets look at the inverse situation: multiple services use the same database. This means every time you change anything in service A that impacts the database (schema changes, but also process changes), you need to know and understand everything that service B does with that database to ensure things keep working. It also means that if service A needs to scale up, service B is also impacted because it needs to scale along (in the sense that if you move to a DB cluster, B now also needs to be able to communicate with a cluster).
Those problems are prevented as soon as the services use different databases. It doesn't matter if those databases are on different physical servers or not. The separation happens when you have a different database.
As a side-note, having the databases on the same hardware can give different advantages at different scales. As long as one server is enough to serve both, it is easier and cheaper to have just that one server. Now say you have two services, both of which need 1 DB server most of the time, but a second one at peak loads - if those peak loads don't overlap, having a single three server cluster is easier than having two clusters of two servers each.
Regarding the diagrams you've seen, think about the goal of the diagram. Most of the time, especially in articles on the internet, the goal isn't to present a deployment diagram that goes into the specifics of what hardware runs what parts of the application, it is a architecture diagram that shows what parts exist and how they are connected. So that "one database server with one database on it" can just be a separate database on a shared server, or it can be the single database on a geo-redundant 100 server cluster.
