For most databases (like Postgres), you typically run a single database (per shard, possibly), and replicate changes to a live read-only backup as fast as possible. If the live R/W database fails, you quickly switch the backup to R/W, and point traffic there instead.
Then, there's a class of databases that tries to actively commit across multiple geographies. You pay a cost (in terms of latency, and typically also $$$), but when a commit succeeds, it has been written durably and reliably, using some consensus protocol, across multiple geographies.
The exemplar is probably Spanner, which uses atomic clocks to get very specific about time to narrow the latency gap as much as possible. Cockroach is broadly in the same class, although without atomic clocks I believe it's using network roundtrip measurements and/or some kind of mathematical time abstraction (like counters of come kind) to do the same thing. Can't ever be quite as fast, but you don't need atomic clocks!
What's _really_ funny is when people start out choosing Spanner because of its global replication, then decide it's too expensive, and settle on regional non-replicated Spanner DBs to save cost. Like, that's just a database, man. (Or maybe something slightly above a single database, like Aurora replicated across Availability Zones in the same Region).
Other folks can chime in, but there are a growing number of databases in this class. TiDB I believe is one. I _thought_ PlanetScale was just sharded mysql (Vitess+MySQL = clever auto-(re-)sharding), but perhaps it does replicated writes too - I see it getting mentioned here a bunch.