-1

I've currently a setup where 10 database clients access one (Postgres)-SQL server on the same network. The applications have a very low db footprint. They do load a few tables upon startup and do quite few operations during runtime (maybe 100 ops per hour).

But the database server is vital for system operations. All other clients can perform the same task redundantly from each other... as long as the db server lives. And I don't what to just build one redundant db server.

What I'm trying to do: let every client also be a database server and remove the dedicated server from the system. I want do form a distributed database system (maybe a dynamically distributed mesh) across every client. The syncing should be done more or less transparently. And as long as one client lives: one should have a working dataset. And is it not vital that this client has the latest working set from the last dying client, but that the structure remains valid and he can do his next operation.

I said earlier: the current system has a very low db ops footprint and the db code can easily be redone. What's important: finding a database system that is best in building this kind of redundancy.

Any ideas about implementations or at least something I should read while working on that topic. Since I've never done such a thing before.

Thank you

Hhut
  • 133
  • 6
  • 2
    May I guess - you rally have not a lot of experiences with databases. The "mesh, no maintenance" is a nightmare, as is backup/restore. Things like that never work on clients. The best you could do is local databases replicated wit a central one, but seriously, making one database not block 10 people via uptime is trivial - unless you environment has zero servers. This is much more a programming question that one about server administration as you want to build a very advanced replication engine that automatically maintains a mesh without being a security risk. – TomTom Nov 02 '15 at 07:45
  • Thanks, I don't have many experiences with databases, that's why I'm asking. But backup & restore is not required—the data is thrown away every few days anyway. – Hhut Nov 11 '15 at 14:04

1 Answers1

1

You can install DB server on each client, let application connect to localhost and set multimaster replication across clients, but:

  1. What about backups? Central DB server is really easier to backup and restore.
  2. What about scaling? Database on clients is not scalable very well.
  3. What about network latence? You didn't write that all clients are on same LAN, but if there is some not-so-reliable connection, database can get serious problems
  4. Don't forget to situations like splitbrain and so on. What application will do if DB crashes on some node? Should connect to different node? How it will detect another nodes?

There is really lot of drawbacks and I cannot recommend you to do this. Just setup two/three mirrored DB servers and let clients connect to them, ideally via haproxy or another loadbalancer.

Ondra Sniper Flidr
  • 2,623
  • 11
  • 18
  • Of course you can write your own replication/backup utilities and use Postgres only as query engine, but it is not so easy as it looks. – Ondra Sniper Flidr Nov 02 '15 at 07:54
  • Thank you! multimaster replication is already an interesting topic. (1) not important for this system. (2) I probably have to test this (3) They are all on two switches linked with Intel SFT mode (4) Yes, that has to be handle by the application services. – Hhut Nov 11 '15 at 14:06
  • And a load-balancer with redundant DB servers is probably a good idea, but 10 or 5 or 1 end user system should be able to act as it has a fully functional db server. So the loadbalancer becomes a redundancy issue? – Hhut Nov 11 '15 at 14:20
  • What would probably be sufficient if every client had a database server. One is a master and all clients connect to the master. All other 9 servers are in standby mode and replicate from the master. If the client with the master server dies there has to be a fast, and distributed system to the remaining clients negotiate on the new master. Could be something simple like smallest mac address in the LAN. But it should be transparent to the application. And that is probably the tricky part. – Hhut Nov 11 '15 at 14:27