Comment by mgiampapa

3 years ago

This isn't really a backup, it's redundancy which is good thing but not the same as a backup solution. You can't get out of a drop table production type event this way.

11 comments

mgiampapa

Reply
Twisell  3 years ago

The previous commenter was probably unaware of the various way to backup recent postgresql release.

For what you describe a "point in time recovery" backup would probably be the more adequate flavor https://www.postgresql.org/docs/current/continuous-archiving...

It was first release around 2010 and gained robustness with every release hence not everyone is aware of it.

The for instance I don't think it's really required anymore to shutdown the database to do the initial sync if you use the proper tooling (for instance pg_basebackup if I remember correctly)

maxclark  3 years ago

Going back 20 years with Oracle DB it was common to use "triple mirror" on storage to make a block level copy of the database. Lock the DB for changes, flush the logs, break the mirror. You now have a point in time copy of the database that could be mounted by a second system to create a tape backup, or as a recovery point to restore.

It was the way to do it, and very easy to manage.

gav  3 years ago

If you add a delay of say 30 minutes for one of your replicas, you have another option in a "drop table" type event.

hamandcheese  3 years ago

If you stop at the first bullet point then you have a backup solution.

  • thayne  3 years ago

    It doesn't solve the problem that sending that snapshot to a backup location takes a long time.

    • dsr_  3 years ago

      No, it doesn't.

      It takes exactly the time that it takes, bottlenecked by:

      * your disk read speed on one end and write speed on the other, modulo compression

      * the network bandwidth between points A and B, modulo compression

      * the size of the data you are sending

      So, if you have a 10GB database that you send over a 10Gb/s link to the other side of the datacenter, it might be as little as 10 seconds. If you have a 10TB database that you send over a nominally 1GB/s link but actually there's a lot of congestion from other users, to a datacenter on the other side of the world, that might take a hundred hours or so.

      rsync can help a lot here, or the ZFS differential snapshot send.