3

I am working on a project to stand up a central database to replace a current massive file-sharing/syncing system of physical log files and text configurations for the primary application I work on.

The app I help develop runs on around 40 computers, each of which can generate hundreds of records per minute while actively operating. This is currently written to log files which are synced between computers using rsync in a system I am working hard to replace.

I was surprised to find it hard to research how to protect/store POST requests to a database when the database is not available. If our new central database ever goes down I would like each individual computer to store the records it was trying to push to the database in some persistent way until the database comes back. Currently these hundreds of records per minute would be pushed into the void as if they never happened, or our application would crash.

I've looked into some options but it seems like there must be some good common choices since this seems like a common problem, but I could not find good advice.

We could continue to write logs as well, and use these to populate the database when it becomes available again. I would like to completely get rid of logs, or at least no longer interface with them in the future however.

When the central database is missing, each computer could write to a local database and have some system to replicate this to the central once it becomes available again. This seems a little convoluted though, I don't really want a database stood-up on each edge computer as well as a central database.

We could queue all SQL POST operations to some sort of persistent queue or stream that waits until it can communicate with the central database. I have looked into Kafka quite a bit as a possibility, but it doesn't seem to fit perfectly for a use case like this. Maybe I just need to learn more about it. I feel like there must be something simpler meant to solve a problem exactly like this. We could obviously implement our own persistent queue system as well, maybe it's as simple as that? I wanted to see if there is an industry-standard way to approach this problem first though.

4
  • Have you considered high availability options built-in to the database itself? The idea of a database server going down sounds as if you're assuming that would ever be likely to happen at all, but may just be something that could be relatively cheap and easy to avoid with one of the many different built-in options for clustering/sync/replication, and possibly using a cloud platform where the availability/infrastructure tend to be a matter of configuration. Feb 21, 2023 at 20:16
  • 1
    The question itself however seems to cover quite a few separate problems however; I don't see that logs, configuration management and database availability really have much in common; these seem to be separate concerns that need looking at in isolation rather than trying to find a one-size-fits-all. Feb 21, 2023 at 20:21
  • Check out this wikipedia page. I think you may be operating under the 1st invalid assumption.
    – JimmyJames
    Feb 21, 2023 at 20:43
  • @BenCottrell we likely will be going with a cloud database once all of our prototyping is done. The problem is the records being pushed to this database are essentially proof of physical operations being completed that need to not be lost if the computer somehow loses its network connection or the database is unavailable for whatever reason (even if it SHOULD always be up in a cloud-based situation). Feb 21, 2023 at 20:49

2 Answers 2

5

The problem you will have with any solution, ie queues, is what happens when that is unavailable.

You need to return errors to whatever is pushing the messages when you can't process them. You also need to think what else might not work if you have lost connectivity.

In terms of logging that means the log should have several fall back options which should work locally. Most logging libraries will support this.

HTTP isn't a great choice for logging, you could go...

  1. elastic
  2. syslog
  3. file
1
  • 2
    Precisely. There's no way out of having the client deal with potential errors. That is, what happens if the web server is down or unreachable? You can layer in as many fallbacks on the server-side as you want but you can't avoid the fact that the client must account for the possibility of an error.
    – JimmyJames
    Feb 21, 2023 at 20:39
1

Rather than a fall-back system when an error is detected, you should have a failsafe procedure that runs all the time. Build a persistent, local queue of transactions to be posted, with a separate process to post, verify, and remove items from the local queue. Something like sqlite would be a good choice as the local database to host the queue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.