6 ms·

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 t

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.

hamandcheese 4y ago
If you stop at the first bullet point then you have a backup solution.
- dsr_ 4y ago
  Precisely so.
- thayne 4y ago
  It doesn't solve the problem that sending that snapshot to a backup location takes a long time.
  - nightshift1 4y ago
    Unless your storage is already mirrored off-site. Ex: EMC srdf
    - thayne 4y ago
      so say the disk fails on your main DB. or for some reason a customer needs data from 6 months ago, which is no longer in your local snapshots. In order to restore the data, you have to transfer the data for the full database back over. With multiple databases, you only have to transfer a single database, not all of your data.
      - hamandcheese 4y ago
        pg_dump has an option to output one-table-per-file. You can use this for selective restores later.
  - dsr_ 4y 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.
maxclark 4y 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.
Twisell 4y 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.html 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)
- deleted 4y ago
  [deleted]
gav 4y 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.

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.

hamandcheese 4y ago

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

dsr_ 4y ago
Precisely so.
thayne 4y ago
It doesn't solve the problem that sending that snapshot to a backup location takes a long time.
- nightshift1 4y ago
  Unless your storage is already mirrored off-site. Ex: EMC srdf
  - thayne 4y ago
    so say the disk fails on your main DB. or for some reason a customer needs data from 6 months ago, which is no longer in your local snapshots. In order to restore the data, you have to transfer the data for the full database back over. With multiple databases, you only have to transfer a single database, not all of your data.
    - hamandcheese 4y ago
      pg_dump has an option to output one-table-per-file. You can use this for selective restores later.
- dsr_ 4y 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.

maxclark 4y 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.

Twisell 4y 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.html 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)

deleted 4y ago
[deleted]

gav 4y 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.