We have a billing database that uses a PostgreSQL backend, and our billing manager has insisted that we do backups hourly, because of how much work we'd lose from daily backups.

My inexperience at managing databases larger than a bunch of Wordpress sites is starting to show, since "make backups hourly" has turned into "why is our database so sloooow" as our billing database has grown. So in search of solutions to this problem, largely in the form of some kind of scheme where I replicate the database to another server that does the backups, I found this unique feature of Postgres. (Or at least, I can't find any such scheme for MySQL, which I'm more familiar with)

Is this what I'm looking for? Or is there a better solution to our problem? Are we going to lose any of our data should someone accidentally nuke a table or a large subset of our customers? Is it also a solution for "we borked the VM, or the hard drive failed"? How hard is it to restore our database to one particular point in time?

  • Is the database slowness a result of the backups?
    – joeqwerty
    Mar 20 '18 at 21:17
  • Absolutely. We're doing regular pgsql dumps every hour. The size of these dumps is now 15 gigs and it takes about 15 minutes or so to do the dump. Which raises the CPU usage, makes the disk busy, and makes Postgres super busy for the duration as well, so everything runs slower.
    – Ernie
    Mar 21 '18 at 15:47
  • In that case, the feature you linked to sounds like a solution.
    – joeqwerty
    Mar 21 '18 at 16:02
  • If you'd put that as an answer, you get brownie points. :)
    – Ernie
    Mar 21 '18 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.