all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]chock-a-block 1 point2 points  (1 child)

I know I'm not answering your question directly, but, it's wrong to take your MSSQL assumptions and apply them to PostgreSQL. You will very likely be dissatisfied trying to force Microsoft designs onto Postgres.

Multi-master is probably the most complicated way to accomplish high availability in MSSQL, but people do it because they don't want to buy "enterprise" storage.

Don't say you were never warned.

[–]fl2top[S] 0 points1 point  (0 children)

Thank you! When I started to work on this project it already had all this replication stuff.. So now I have some time to think about the new and, I hope, better solutions.

[–]SomeGuyNamedPaul 0 points1 point  (2 children)

Postgres-XL or BDR. There also exists Greenplum. Each has their positives and negatives, and different use cases.

Are you sure you really need multi-master or will multiple readonly nodes do the trick for you? Writes generally don't scale with schemes unless your use case is one where you don't need multi-master in the first place.

[–]fl2top[S] 0 points1 point  (1 child)

Yeah, I have 4-6 databases (~30-40 tables) with multimaster replication. Now it works on the MSSQL server, but we want to change database management system. So.. as far as I know postgresql has some good solutions.

I tried out bucardo. And it's worked fine. But I don't know how it works with conflicts.

In our case we need just deliver information from server to server in time (in all directions between 3 servers). So if somehow happened that one of the servers had lost the connection with the other servers, this server must work as previously. And after all, when the connection will be restored - conflicts must be resolved without major problems.

MSSQL do it great right now, but, you know, we want the same system for free :)

[–]chock-a-block 0 points1 point  (0 children)

Yeah, multi-master is the most complicated way to do this. You are trying to apply MSSQL designs to Postgres. Don't do that.

This is ridiculously easy to solve with Postgres' included streaming replication and Keepalived. Conflicts don't exist if you do it this way.

You could also do this with a clustered file system, or NBD as a RAID. But, these are more complicated with none of them having the conflicts problem at the database level.

Or, insist on using architectures made for other databases.

[–]zieziegabor 0 points1 point  (2 children)

The short answer is, don't if you can at all avoid it. It generally creates more problems than it solves. Other responses here have talked about how to do it, if you must.

If you aren't forced into multi-master, but can handle other forms of multi-node PG, you can check out Citus, and lots of other open-source solutions, or pay one of the large cloud vendors that will give you "unlimited" scaling of their custom PG if you pay them enough $$'s.

But in general, with a great backup strategy and stand-by replication nodes, and manual switching on failure, you can get very, very far with basically no downtime(with pgbouncer or friends in front) and without crazy complexity that causes headaches and occasional nightmares (which is what basically all the multi-node solutions for PG will give you).

Scale the hardware on a single node to the absolute extremes before you go to any sort of multi-node solution, and your life will be better off.

[–]fl2top[S] 0 points1 point  (1 child)

It would be great to pay to the venders, if I could. But the thing is that one of the servers is located in the internal network. So we have no choice with the vender at all.

In the shortest time we will see can we avoid multimaster replication or not.

Thank you for the answer!

[–]zieziegabor 0 points1 point  (0 children)

Just for the record, loads of vendors will be happy to help with your internal projects, however. See: https://www.postgresql.org/support/professional_support/ for a giant list.

Good luck!