5 ms·

One issue I've seen with this is that if you have a single, very large database, it can take a very, very long time to restore from backups. Or for that matter

by thayne 4y ago

One issue I've seen with this is that if you have a single, very large database, it can take a very, very long time to restore from backups. Or for that matter just taking backups. I'd be interested to know if anyone has a good solution for that.

mike_hearn 4y ago
Presumably it doesn't matter if you break your DB up into smaller DBs, you still have the same amount of data to back up no matter what. However, now you also have the problem of snapshot consistency to worry about. If you need to backup/restore just one set of tables, you can do that with a single DB server without taking the rest offline.
- thayne 4y ago
  > you still have the same amount of data to back up no matter what But you can restore/back up the databases in parallel. > If you need to backup/restore just one set of tables, you can do that with a single DB server without taking the rest offline. I'm not aware of a good way to restore just a few tables from a full db backup. At least that doesn't require copying over all the data (because the backup is stored over the network, not on a local disk). And that may be desirable to recover from say a bug corrupting or deleting a customer's data.
Svenstaro 4y ago
I found this approach pretty cool in that regard: https://github.com/pgbackrest/pgbackrest https://github.com/pgbackrest/pgbackrest
dsr_ 4y ago
Here's the way it works for, say, Postgresql: - you rsync or zfs send the database files from machine A to machine B. You would like the database to be off during this process, which will make it consistent. The big advantage of ZFS is that you can stop PG, snapshot the filesystem, and turn PG on again immediately, then send the snapshot. Machine B is now a cold backup replica of A. Your loss potential is limited to the time between backups. - after the previous step is completed, you arrange for machine A to send WAL files to machine B. It's well documented. You could use rsync or scp here. It happens automatically and frequently. Machine B is now a warm replica of A -- if you need to turn it on in an emergency, you will only have lost one WAL file's worth of changes. - after that step is completed, you give machine B credentials to login to A for live replication. Machine B is now a live, very slightly delayed read-only replica of A. Anything that A processes will be updated on B as soon as it is received. You can go further and arrange to load balance requests between read-only replicas, while sending the write requests to the primary; you can look at Citus (now open source) to add multi-primary clustering.
- mgiampapa 4y 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.
  - 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.
nick__m 4y ago
On mariadb you can tell the replica to enter into a snapshotable state[1] and take a simple lvm snapshot, tell the the database it's over, backup your snapshot somewhere else and finally delete the snapshot. 1) https://mariadb.com/kb/en/storage-snapshots-and-backup-stage-commands/ https://mariadb.com/kb/en/storage-snapshots-and-backup-stage...
rszorness 4y ago
Try out pg_probackup. It works on database files directly. Restore is as fast as you can write on your ssd. I've setup a pgsql server with timescaledb recently. Continuing backup based on WAL takes seconds each hour and a complete restore takes 15 minutes for almost 300 GB of data because the 1 GBit connection to the backup server is the bottleneck.
- xuki 4y ago
  For MySQL there is xtrabackup - https://www.percona.com/software/mysql-database/percona-xtrabackup https://www.percona.com/software/mysql-database/percona-xtra....
raarts 4y ago
Not a solution but using event sourcing would have prevented this.

by thayne 4y ago

mike_hearn 4y ago

Presumably it doesn't matter if you break your DB up into smaller DBs, you still have the same amount of data to back up no matter what. However, now you also have the problem of snapshot consistency to worry about. If you need to backup/restore just one set of tables, you can do that with a single DB server without taking the rest offline.

thayne 4y ago
> you still have the same amount of data to back up no matter what But you can restore/back up the databases in parallel. > If you need to backup/restore just one set of tables, you can do that with a single DB server without taking the rest offline. I'm not aware of a good way to restore just a few tables from a full db backup. At least that doesn't require copying over all the data (because the backup is stored over the network, not on a local disk). And that may be desirable to recover from say a bug corrupting or deleting a customer's data.

Svenstaro 4y ago

I found this approach pretty cool in that regard: https://github.com/pgbackrest/pgbackrest https://github.com/pgbackrest/pgbackrest

dsr_ 4y ago

Here's the way it works for, say, Postgresql: - you rsync or zfs send the database files from machine A to machine B. You would like the database to be off during this process, which will make it consistent. The big advantage of ZFS is that you can stop PG, snapshot the filesystem, and turn PG on again immediately, then send the snapshot. Machine B is now a cold backup replica of A. Your loss potential is limited to the time between backups. - after the previous step is completed, you arrange for machine A to send WAL files to machine B. It's well documented. You could use rsync or scp here. It happens automatically and frequently. Machine B is now a warm replica of A -- if you need to turn it on in an emergency, you will only have lost one WAL file's worth of changes. - after that step is completed, you give machine B credentials to login to A for live replication. Machine B is now a live, very slightly delayed read-only replica of A. Anything that A processes will be updated on B as soon as it is received. You can go further and arrange to load balance requests between read-only replicas, while sending the write requests to the primary; you can look at Citus (now open source) to add multi-primary clustering.

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

nick__m 4y ago

On mariadb you can tell the replica to enter into a snapshotable state[1] and take a simple lvm snapshot, tell the the database it's over, backup your snapshot somewhere else and finally delete the snapshot. 1) https://mariadb.com/kb/en/storage-snapshots-and-backup-stage-commands/ https://mariadb.com/kb/en/storage-snapshots-and-backup-stage...

rszorness 4y ago

Try out pg_probackup. It works on database files directly. Restore is as fast as you can write on your ssd. I've setup a pgsql server with timescaledb recently. Continuing backup based on WAL takes seconds each hour and a complete restore takes 15 minutes for almost 300 GB of data because the 1 GBit connection to the backup server is the bottleneck.

xuki 4y ago
For MySQL there is xtrabackup - https://www.percona.com/software/mysql-database/percona-xtrabackup https://www.percona.com/software/mysql-database/percona-xtra....

raarts 4y ago

Not a solution but using event sourcing would have prevented this.