> We were tired of constantly configuring a postgres replica for each (small) project. And then being pretty unsure if the backup would still be up-to-date by the time the primary server crashes.
> When talking with other postgres users about backups, we also found that primary and backup servers often run in a single data center or at a single (budget) provider. In effect, exposing these users to non-neglible risk of loosing database+backup.
There's some - obvious - challenges in getting new users to trust you enough to send them their database copy. At the same time, people host very sensitive data at (virtual) budget servers without thinking twice. What do you think?
Seeing a service like this makes me wonder if Postgres could support encrypted replication. That would allow using a service like this to provide reliable backup without having to trust it with customer data.
What exactly do you mean with encrypted replication?
Just stream the WAL in an encrypted format, and store it there? Easy enough. But that'll mean that it'll potentially take a long time to re-apply all those changes to a base backup.
Actually apply the changes, even though the database and WAL in encrypted? Unlikely, don't really see how that'd be possible. And even if, I'd bet that it'd be fairly easy to generate compression type attacks.
You'd need some sort of encryption of the raw data storage, and that would also prevent backing up any indexes. And yes, you'd expose some amount of information, such as row activity. Seems plausible, though, depending on your threat model.
If I were to do that I'd use logical decoding (https://www.postgresql.org/docs/current/static/logicaldecodi...) and just write a custom apply mechanism. I think you'll, in any case, end up exposing a lot of information. INSERT/UPDATE/DELETE are going to be discernible, and you'd have to be very careful about compression not exposing things.
The backup wouldn't actually be particularly useful though. So I'm not really sure what the use-case here is.
> 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.
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).
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?
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.
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.
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.
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.
We might add proxy-over-ssh/vlan options later on, but it would be incompatible with the 2-clicks-and-your-done interface right now.
As far as I know, postgres authentication is rather simple and exposing the port does not easily add huge liability.
This is really a trade-off between ease-of-setup and better security. We're eager to talk to (a lot of) users to see what there current setup is and how PGBackup could add some value for them.
The comment caught me off guard as well. It seems as a customer you're placing a lot of trust in this company as you're replicating your data to them and then trusting that they will handle and store it securely.
Until then (or additionally) you could add a FAQ entry with your server IP range(s), so people can allow them in their firewall for the specific port (usually 5432).
For customers with stronger security concerns, they could issue your service a client cert and only allow replication connections from your service's IP range that auth with certificate.
Even if not concerned with security, it opens a pretty easy DOS window. Authentication happens after forking of a new backend. That's not exactly cheap...
Edit to add why: You'll have full reachability between pgbackup and your customer's servers without opening ports inbound on either side. Traffic is also encrypted :-)
Technically, yes. But we figured the name "backup" is easier to remember, and this would allow us to add a bunch of other 'backup related' services later on :).
It is highly confusing. If i hadn't read the comments here i - after a first glaze - would have also thought this was "snapshot, backup and forget" service.
The product you should be building is replication (what you have actually built) and wal-e configured to backup to amazon s3 (where I provide the bucket).
you guarantee that the backups are happening, build in some intelligence to make sure catastrophic "DELETE" statements would trigger a backup first, etc.
Give me the ability to spawn a replica using the exact backup that I choose, etc.
I can definitely see the demand for something like this, but the engineering on it becomes quite difficult. Without the ability to cancel long running queries there can be some replication lag and the WAL disk could fill up. Without actual administrative access to the instance it becomes very hard to fully guarantee such replication.
If you do want this more out of the box the real options today are either built it yourself or choose a provider that's delivering it.
I don't think this is a huge problem. Yes, long running queries might introduce lag. When this lag hits a threshold of your choosing, PGBackup (or any service that would offer it) would then gladly send out pager alerts.
The same would happen if the WAL could not be retrieved in time (because wal storage full): PGBackup would start a new base_backup and send out pager alerts.
good point. make your tech so that you can guarantee that you will never have access to customers data. If there is a replication failure.. so you kill the machine and respawn it kind of a thing.
where would you backup if not s3 ? your own ? that would be a BIG oops...
Great feedback here, but also through the on-site chat. Again, the current version is built with very limited resources mainly to check if/how the idea would resonate.
- We will try to add Point-in-time-recovery asap (saving base backups+xlogs). These would run of the replica, not putting load on your database server. As koolba correctly points out, this will make it more "backup" than "replica". Will have to figure out how this fits in the pricing model.
- I would personally love to offer better (guarantees of) encryption of the backups, ideally encrypt the data pages on the primary server. We would have to see how this would technically work.
- First couple of backups are currently replicating :)!!
Not sure if the cost of this is worth it. I set up WAL-E (https://github.com/wal-e/wal-e) to do server-side encrypted backups (it does a daily base backup + continuous WAL delivery) in about half a day. If you setup lifecycle rules on the S3 bucket you can have an automatic deletion policy handle cleanup.
48 comments
[ 4.2 ms ] story [ 121 ms ] thread> Why did you build PGBackup?
> We were tired of constantly configuring a postgres replica for each (small) project. And then being pretty unsure if the backup would still be up-to-date by the time the primary server crashes.
> When talking with other postgres users about backups, we also found that primary and backup servers often run in a single data center or at a single (budget) provider. In effect, exposing these users to non-neglible risk of loosing database+backup.
There's some - obvious - challenges in getting new users to trust you enough to send them their database copy. At the same time, people host very sensitive data at (virtual) budget servers without thinking twice. What do you think?
It's technically rather challenging, but when enough users would ask for it, I know putting some resources into it might be viable.
https://github.com/wal-e/wal-e
Just stream the WAL in an encrypted format, and store it there? Easy enough. But that'll mean that it'll potentially take a long time to re-apply all those changes to a base backup.
Actually apply the changes, even though the database and WAL in encrypted? Unlikely, don't really see how that'd be possible. And even if, I'd bet that it'd be fairly easy to generate compression type attacks.
The backup wouldn't actually be particularly useful though. So I'm not really sure what the use-case here is.
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.
From a security standpoint the former would be a lot stronger though it would be a lot more resource intensive on your end.
I have a separate script that manages cleaning up of backups older than a certain age, etc.
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
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.
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.
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.
As far as I know, postgres authentication is rather simple and exposing the port does not easily add huge liability.
This is really a trade-off between ease-of-setup and better security. We're eager to talk to (a lot of) users to see what there current setup is and how PGBackup could add some value for them.
Famous last words of security on the internet.
Edit to add why: You'll have full reachability between pgbackup and your customer's servers without opening ports inbound on either side. Traffic is also encrypted :-)
Disclaimer: I am part of Wormhole Network.
The product you should be building is replication (what you have actually built) and wal-e configured to backup to amazon s3 (where I provide the bucket).
you guarantee that the backups are happening, build in some intelligence to make sure catastrophic "DELETE" statements would trigger a backup first, etc.
Give me the ability to spawn a replica using the exact backup that I choose, etc.
This is something I could totally pay for!
If you do want this more out of the box the real options today are either built it yourself or choose a provider that's delivering it.
The same would happen if the WAL could not be retrieved in time (because wal storage full): PGBackup would start a new base_backup and send out pager alerts.
The base_backup+xlog backup is going to be our next feature. Backing those up to s3: possibly. Encrypted?
where would you backup if not s3 ? your own ? that would be a BIG oops...
- We will try to add Point-in-time-recovery asap (saving base backups+xlogs). These would run of the replica, not putting load on your database server. As koolba correctly points out, this will make it more "backup" than "replica". Will have to figure out how this fits in the pricing model.
- I would personally love to offer better (guarantees of) encryption of the backups, ideally encrypt the data pages on the primary server. We would have to see how this would technically work.
- First couple of backups are currently replicating :)!!
Thanks, HN!