3 ms·
> A dedicated, virtual server that runs your postgres version in replica mode. It continuously receives changes from your primary database server, becoming an o
by koolba 10y ago
> A dedicated, virtual server that runs your postgres version in replica mode. It continuously receives changes from your primary database server, becoming an off-site backup.
This isn't an offsite backup. This an offsite replica that will faithfully replicate a "DELETE FROM account WHERE true".
Marrying that with a scheduled logical pg_dump and/or physical pg_basebackup that runs against the local copy (for performance and not impacting the master) would create a true offsite backup.
Until then, it's just one more place that will get wiped out when the source data is accidentally destroyed.
- EmielMols 10y agoYes. Timely base-backups and PITR are obvious next features. We're really trying to determine if the idea resonates with people before throwing developer-years at it (or perhaps choosing a technically different solution to fix the problem altogether).
- mey 10y agoMy current backup solution for my personal server is on a daily basis run sudo -u postgres pg_dumpall > /var/base/backups/db/postgres/backup.sql and then ship that plus many other things off to tarsnap. This is a naive approach because the data set is small, but it could be tuned and improved to be delta based at some point. The joy is Tarsnap is managing the history of the data for me on that file and retains it as long as I wish to pay for. I have a separate script that manages cleaning up of backups older than a certain age, etc.
- koolba 10y agoWithin the realm of pg_dump completing in a reasonable amount of time, this is a fine strategy. Bonus points if the server that runs pg_dump and uploads to tarsnap has write-only access (to tarsnap), uses unique unguessable prefixes for the backups, and is distinct from the server that manages cleaning out old backups. Course that's probably way overkill for a personal server :D
- mey 10y agoOnce I have a private cloud running in my house I'll head in that direction, but need to get around to seriously upgrading my networking environment and building out a new NAS. :) Edit: Not sure why'd you want unique unguessable prefixes, the data is accessed with the key. I actually encode timestamp information into my backup names for easy display.
- koolba 10y ago> Edit: Not sure why'd you want unique unguessable prefixes, the data is accessed with the key. I actually encode timestamp information into my backup names for easy display. The random prefix is to prevent overwriting an existing backup. See the sibling comment from cperciva and my reply to it.
- gleb 10y agoIf you had ever set this up - a separate server to rotate/clean old tarsnap backups - I'd be curious how you deal with tarsnap local cache getting out of date. tarsnap fsck on every run?
- cperciva 10y agouses unique unguessable prefixes for the backups Why? If you're worried about someone overwriting old backups, don't: You can't overwrite or modify archives in tarsnap. Once created they can only be read (if you have the read keys) or deleted (if you have the delete keys).
- 10y ago
- snuxoll 10y agoHonestly, if you wanted to offer backup as a service for postgresql just using barman would be a much better solution than replication (which still involves shipping transaction logs anyway). If you don't support point-in-time recovery then your backup solution is effectively useless in my opinion, though even daily backups are a better solution than replication for proper backups.
- AznHisoka 10y agoWhat a shame. A true backup as a service would be useful. A replication scheme could easily be implemented within a day by yourself, and is not really a backup. Have you looked at WAL-E (sp?) They transfer the wal files to S3, and works especially work if you have a high load database where pg_dump isn't feasible.
- ioltas 10y agoTrue. Note as well that 9.4 has introduced recovery_min_apply_delay to somewhat leverage such errors by delaying replication (well actually it delays the moment when WAL records doing transaction commit are applied at recovery, and does so after a consistent point has been reached at recovery). Still this does not replace the fact that a robust backup strategy should combine both physical and logical backups.