> Can I have a two-node cluster of PostgreSQL machines where a failure in the first results in failing over to the second automatically? It's okay if I have to run something manual on the first just to bring that back up to speed and act as the new secondary.
You can do this with repmgr from 2ndquadrant, see the documentation [0] for details on the configuration. Since repmgrd doesn't require anything other than postgres to be running, it doesn't add any new failure points.
One thing you need to make sure you do right in such a setup is fencing the failed node, pgBouncer or pgPool is a good way to go about this - in your failover script you can modify your configuration on the pgBouncer server to point at the new master before the failover takes place, this will prevent clients from talking to the failed master and prevent a split-brain.
Alternatively, you can use something like keepalived and STONITH, but I'm not comfortable relying on somehow shutting down or cutting off the other machine from the network as it is less reliable than modifying a configuration file if things are going really wrong.
[0]: https://github.com/2ndQuadrant/repmgr