How do you handle databases backup when (some) important data is involved?

Hello everybody,

It’s not really a Phoenix problem (and might even be a devops concern) but I’m asking in the context of a Phoenix App.
Until now I never really thought about database redundancy or backup (besides what it’s already done in the file system level by the VPS provider in case of a disk failure).

In my current project, payments will be involved and I’m wondering how I can make the payments data redundant so that if something bad happen I can start fresh with at least the data of who made payments. (In fact this particular scenario is already covered by the payment provider itself e.g. stripe which provide the history of the transactions).

I don’t really know what are the best practices for this kind of use-case and wanted to know what are the best practices?

  • Is it simply managed at the OS/FS level for disk failure handling? But what if it’s the VPS itself which is broken?
  • Do people use two (or more) databases at once (for each write/update/delete requests) which will run ideally on different provider?
  • Does another solution might be to still use only one DB and perform regular dump? (it’s an easy task when doing it manually from time to time but I saw that quickly becoming complex if it’s needed to do it more often and ideally by transferring the dumped data into another location)
  • What about an “event store” or some kind of dedicated DB/queue/pubsub system which will only store the payments events (with the associated data like email of the payer, etc.)?
  • Maybe people are using DB systems which have built-in support for write replicas? (IIRC mongodb have this kind of feature)

I’m really interested to hear how you guys are handling that or if you have some resources explaining what are the best practices.

Thank you!

VPS probably has some kind of RAID setup for preventing disk failures, you do not need to worry much there. But remember - RAID is not backup.

There is concept of hot/warm/cold-replica that replicates the data from the master and sometimes is used for reads as well.

You can use dumps or if your DB supports it, you can also use streaming of write-ahead-log (if your DB supports this). The latter is a little bit more complex to set up, but will provide you with point-of-time recovery (so you can recover state of the DB at any point you like).

It is log that will contain events as they happen in your application. That is it.

Multi-master replication is quite hard to get right, but most of the DBs have support for such (either built in or 3rd party). But if all you want is redundancy, then master-replica is simpler and easier approach.

1 Like

Consider using an external service like Datadog or Papertrail or Logly for monitoring and log management. It’s useful to get alerts about the status of your server anyway.

Then, if you log everything needed to recover (like user x bought plan y) you can download the logs and write a little script to restore the db if you need to.

1 Like

Please don’t ever make this your “plan A”. That is not a DR strategy, that’s what you do under duress to save a capsizing business because you have no other choice. Logs are not a system of record, as many many things have to go just right for them to even get collected and retained.