Hacker News Reader

Comment by koolba | Hacker News Reader

> 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.

View on HN

My 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.

Within 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

Once 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.

> 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.

If 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?

uses 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).

> 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).

Nice. Some reason I thought a write key would be able to overwrite as well. If that's not possible then yes it's not necessary to have random prefixes. Fyi, most of my tarsnap usage is in "set it and forget" mode so been a while since I perused the docs.

The random prefixes would apply if you're using something that doesn't provide overwrite protection like pushing blobs directly to S3.

Yes. 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).

Is the backup process running in a VM or container?

From a security standpoint the former would be a lot stronger though it would be a lot more resource intensive on your end.

A pretty strictly configured lxc container.

Honestly, 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.

True. 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.

What 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.

Reply on news.ycombinator.com