Master-master replication is asynchronous, hence it will definitely break if you write to both servers at once.
Even if the auto-increments are working, any other unique index and many other situations can break it - it's too brittle to be used.
But it is possible to use master-master as PART of a HA solution, you just need to ensure that applications only ever write to one of the pair and in a "clean" failover situation, e.g. admin failing over, it waits for the slave to catch up before switching.
This is not extremely difficult in practice, but a bit inconvenient.
Your main other option is to use DRBD, which is also not massively difficult to set up - but in this case, the 2nd machine is not even usable as a read replica - it just sits there being a hot spare. DRBD synchronously replicates the underlying storage, so everything is written safely to both machines.
There are some applications which are specially designed to tolerate the multi-master problems - these need to be designed VERY carefully with that exact situation in mind - in which case, it's ok. You can't use applications not designed for it though.
auto-increment is not the only, or the main problem.
CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!
This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.
What you should do is follow Chris James's answer:
sudo -u postgres psql postgres
# \password postgres
Enter new password:
To explain it a little bit. There are usually two default ways to login to PostgreSQL server:
By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql
. Note that sudo -u
does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).
So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.
Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.
Best Answer
Have you considered Bucardo? It's asynchronous multimaster. It hasn't completely caught on and is not a general solution, but it might be worth a try.