The metrics collection needs to be done by a local agent installed on the system, for the reason you gave, that's the only place the data is available.

The metrics storage is somewhere else.

Prometheus does the storage. For the collection, you still have to install collectd, statsd or similar on your hosts. Sure, prometheus could do a HTTP check remotely, but that won't get cover much of anything.

The Prometheus pattern with central things like a database (e.g. monitoring Postgres) is to let the exporter (the thing that acts as an intermediary to expose metrics for Prometheus to fetch) run anywhere it wants. It absolutely does not need to be a "local agent".

(In fact, if you're using something like Kubernetes, the only thing that needs local access to a node is the exporter that exposes node-specific metrics. Everything else can chat over the network.)

The benefit here is that if you have 10 Postgres databases, you can still run just a single exporter and have it extract data from all of them. Or you can run one exporter per database; there's conceptually little difference.

On Kubernetes, we usually run an exporter as a sidecar container, which means it can talk to Postgres or whatever on localhost and just live alongside the process that it's exporting metrics for, and we rely on Prometheus' automatic discovery to make Prometheus pull from it. Start a new Postgres instance and its metrics are almost immediately fed into Prometheus.

You don't need collectd etc. with Prometheus. There are exporters around for just about anything.

They can be called agents, exporters, collectors, whatever, the name is not important, the design pattern is.

A system that would be exclusively pull-based from the prometheus server does not work practically.

Unless you take the presence of node_exporter to invalidate the premise (which would be stupid) a system that's exclusively pull-based is entirely practical.