thanks! just to be clear, you do mean host standby with streaming replication right ? And why did you specifically recommend 9.3 - I cant google for anything specific to standby fixes.
The authentication defaults for postgres and mysql are vastly different - I am always tempted to move all authentication to md5 (pretty much the same as mysql). Am I doing it wrong ?
You want either 9.3.2 or 9.2 >= 9.2.5. Before either of those, there was a bug in the streaming replication code such that, on failover, the last few outstanding transactions on the master might not be replayed on the slave, resulting in data loss.
And, no, you aren't doing it wrong. You generally want to use MD5 auth.
The reason I recommend 9.3 specifically is that when a migration occurs, Postgres switches to a new historical branch. As of 9.2 this change gets written only to the WAL directory, and you must use something like Ceph / Gluster in order to have these migration files available to each replicating server.
In 9.3 at some point this is slated for inclusion in the WALSender. This allows you to do full streaming replication without the requirement for a cluster filesystem to hold your binary logs.
but this is bleeding edge stuff ( never mind that it will work perfectly given past experience).
What did hackers (not whole development + ops) teams do before 9.3.2 ?
with all due respect, is this the reason why startups still default to mysql over postgres ?
Postgres historically has been tricky to configure with 'proper' replication. This is partially because replication is not a remotely simple problem to fix as it appears. The reason people still default to mysql is simply because it occupies more mindshare and is marginally easier to set up. I still don't think it even allows such things as DDL rollbacks so it's hard to talk about it next to Postgres when you're talking about safety.
People did custom scripting or used third party tools for easy failover before 9.2/9.3. What PostgreSQL has improved the last years is simplicity of setting up replication and speed of failover.
> with all due respect, is this the reason why startups still default to mysql over postgres ?
I do not these particular issues. But if we go back before streaming replication (when people had to transfer WAL files with rsync) then I am pretty sure this was part of the reason for why small companies chose MySQL over PostgreSQL:
The authentication defaults for postgres and mysql are vastly different - I am always tempted to move all authentication to md5 (pretty much the same as mysql). Am I doing it wrong ?