If the Postgres master has fallen and Patroni has switched it to a replica, how does the SQL server that pulls data from Postgres find out about this?

When Patroni switches the master to a replica (failover), the role of the node in the PostgreSQL cluster changes. The SQL server that connects to the database must learn about the new master to continue working with writes.

How it happens:

• Patroni manages the cluster state and stores information about the current master in a distributed storage (e.g., Etcd, Consul, or ZooKeeper).
• Clients (SQL servers or applications) usually do not connect directly to a specific host but use a virtual IP, DNS name, or proxy that redirects requests to the current master.
• During failover, Patroni updates the virtual IP or DNS record pointing to the new master.
• The SQL server, which fetches data, resolves the address again upon the next connection or connection error and connects to the new master.

If a proxy (e.g., HAProxy or PgBouncer) is used, it also updates routing to the new master.

Thus, the SQL server learns about the master change through the load balancing infrastructure or DNS/virtual IP update, not directly from PostgreSQL.