Introducing WAL-G: Faster Disaster Recovery for Postgres(citusdata.com)
citusdata.com
Introducing WAL-G: Faster Disaster Recovery for Postgres
https://www.citusdata.com/blog/2017/08/18/introducing-wal-g-faster-restores-for-postgres/
65 comments
I don't know if you're being sarcastic, because the point of the article is that the Unix philosophy was a performance bottleneck and they replaced all the Unixy stuff with Go libraries.
I wasn't being sarcastic - I like the fact that they replaced what needed replacing and kept what worked well.
Why would this be a better option than a simple zfs snapshot, zfs send/recv backup and recovery strategy?
Hello everyone, I'm the primary author for WAL-G and would be happy to answer any questions.
Thanks! Does WAL-G provides some kind of "continuous backup" where changes committed to the database are continuously streamed to the backup storage? Or does it work "step by step", for example by backing up every 5 minutes or every 10 MB?
It does continuous backup like WAL-E.
Both back up PG's WAL files (Write Ahead Log) and allow restoring your database state as it was at a specific time or after a specific transaction committed. This is known as point-in-time recovery (PITR) [0]
Users and admins make mistakes, and accidentally delete or overwrite data. With PITR you can restore in a new environment, just before the mistake occurred and recover the data from there.
[0] https://www.postgresql.org/docs/9.6/static/continuous-archiv...
Both back up PG's WAL files (Write Ahead Log) and allow restoring your database state as it was at a specific time or after a specific transaction committed. This is known as point-in-time recovery (PITR) [0]
Users and admins make mistakes, and accidentally delete or overwrite data. With PITR you can restore in a new environment, just before the mistake occurred and recover the data from there.
[0] https://www.postgresql.org/docs/9.6/static/continuous-archiv...
What I meant is that the archive_command is run only when a WAL segment is completed or when archive_timeout is reached. In the meantime, nothing is backed up. On a low traffic database, this can be a problem. I'm wondering if there is a way to continuously stream the WAL to an object storage like S3, without waiting to have a complete segment.
S3 is a block store; not something you can really stream to.
However it might be interesting to stream WAL logs to e.g. AWS Kinesis....
However it might be interesting to stream WAL logs to e.g. AWS Kinesis....
You're right. S3 is an object store and doesn't support the append operation, which is required for what I want to do. Thanks!
You can open multi-part transfers and close out the transfer when you're ready, which can be used so that it is very close to streaming; for this case perhaps it's close enough to try with wall-g if it otherwise supports it.
That's the usecase for archive_timeout. I set it to 60 seconds. So at most I'll have lost 60s + the time to transfer the file to s3, which shouldn't be more than a couple seconds.
According to PostgreSQL documentation, "archived files that are archived early due to a forced switch are still the same length as completely full files".
I'm afraid to use a lot of storage for WAL segments that are mostly empty:
16 MB per segment x 60 minutes x 24 hours x 7 days = 161 GB/week
Does WAL-G/WAL-E compression help?
I'm afraid to use a lot of storage for WAL segments that are mostly empty:
16 MB per segment x 60 minutes x 24 hours x 7 days = 161 GB/week
Does WAL-G/WAL-E compression help?
Yes, the lzop compression helps a lot, and I imagine that mostly "empty files" will be more compressible.
On a staging server with little activity, the compressed WAL-E wal files go as low as 1.9MB per 10 minutes. (~2GB/week)
The production server has files between 4 and 12MB per 1 minute or less. (~220GB/week)
WAL-E has a good `wal-e delete retain` command that removes older base backups and wal files.
On a staging server with little activity, the compressed WAL-E wal files go as low as 1.9MB per 10 minutes. (~2GB/week)
The production server has files between 4 and 12MB per 1 minute or less. (~220GB/week)
WAL-E has a good `wal-e delete retain` command that removes older base backups and wal files.
2 GB/week is so much better than 161 GB/week! It looks like compression helps a lot. Thanks for sharing these numbers.
Thanks for making this! To someone who's unfamiliar with Postgres tooling, what's the difference between WAL-G and Barman? What're the advantages of using one over the other?
I was kafkes's guide on this project, and the WAL-E author and maintainer...
I'd say the differences between WAL-G and Barman are similar to WAL-E and Barman, which comes up relatively frequently. https://news.ycombinator.com/item?id=13573481
In summary, WAL-E is simpler program all around that focuses on cloud storage, barman does more around inventories of backups and file-based backups and configuring Postgres. There are integrative downsides to its span. WAL-E also happens to predate Barman.
I'd say the differences between WAL-G and Barman are similar to WAL-E and Barman, which comes up relatively frequently. https://news.ycombinator.com/item?id=13573481
In summary, WAL-E is simpler program all around that focuses on cloud storage, barman does more around inventories of backups and file-based backups and configuring Postgres. There are integrative downsides to its span. WAL-E also happens to predate Barman.
WAL-G (and WAL-E) are expected to run next to the main database, while barman is to be run on a separate machine. Barman can also backup many databases. It is essentially the difference between a central backup service and local backups.
Seriously awesome work on this! I was expecting some solid improvement when I heard you were rewriting this in Go, but this is beyond what I could have expected. 7x improvement on high end instance types!
Also, what an impressive project to have on the resume as a college intern. I don't think many interns get to tackle something so meaningful.
Also, what an impressive project to have on the resume as a college intern. I don't think many interns get to tackle something so meaningful.
Nor would many choose to, given the opportunity. Way to pick an interesting and challenging project! :-)
Can you elaborate on how much automated testing is behind this? When it comes to backup tools, I am very cautious.
WAL-G has a number of unit tests, and has been tested manually in a staging environment for a number of weeks without issues. We are looking to implement more integration tests in the future.
Is this production ready, or just an early dev snapshot?
We've currently been testing it at Citus, but have not flipped it to be live for our disaster recovery yet.
We're going to start rolling it out for Forks/point-in-time recoveries first, which present less risk to start. Later we'll explore either parallel restores from WAL-E and WAL-G or possibly just flip the switch based on the results.
On restoration there's really no risk to data. Further we page our on call for any issues that happen such as WAL not progressing, or servers not coming online out of restore.
We're going to start rolling it out for Forks/point-in-time recoveries first, which present less risk to start. Later we'll explore either parallel restores from WAL-E and WAL-G or possibly just flip the switch based on the results.
On restoration there's really no risk to data. Further we page our on call for any issues that happen such as WAL not progressing, or servers not coming online out of restore.
WAL-G is not yet production ready, but it has been used in a staging environment for the past few weeks without any issues. Once fdr adds parallel WAL support, he plans to take it into production.
Cool cool.
is google cloud storage on the roadmap?
is google cloud storage on the roadmap?
WAL-G looks like it should be able to talk to Minio (minio.io, S3 compatible thing, also written in go) as a backend instead of S3 itself.
Minio has an interesting feature where it can be a "gateway" to other cloud storage. Google Cloud Storage is one of their specific examples:
https://docs.minio.io/docs/minio-gateway-for-gcs
So WAL-G would talk to Minio, and Minio would transparently proxy that to GCS.
Minio has an interesting feature where it can be a "gateway" to other cloud storage. Google Cloud Storage is one of their specific examples:
https://docs.minio.io/docs/minio-gateway-for-gcs
So WAL-G would talk to Minio, and Minio would transparently proxy that to GCS.
Neat. My concern out of the gate is what would be the perf hit.
I assume I am switching from WAL-E to WAL-G for more perf. But WAL-E speaks GCS. If WAL-G needs an extra hop to do so, may lose some of the point of it..
I assume I am switching from WAL-E to WAL-G for more perf. But WAL-E speaks GCS. If WAL-G needs an extra hop to do so, may lose some of the point of it..
Yeah, no idea personally. Haven't used the gateway functionality in Minio at all.
That being said, the Minio team seem pretty good with writing performance optimised code. Frank Wessels (on Minio team), has been writing articles about Go assembler and other Go optimisation things recently. eg:
• https://blog.minio.io/accelerating-blake2b-by-4x-using-simd-...
• https://blog.minio.io/golang-internals-part-2-nice-benefits-...
So the performance impact might not be such a problem. :)
That being said, the Minio team seem pretty good with writing performance optimised code. Frank Wessels (on Minio team), has been writing articles about Go assembler and other Go optimisation things recently. eg:
• https://blog.minio.io/accelerating-blake2b-by-4x-using-simd-...
• https://blog.minio.io/golang-internals-part-2-nice-benefits-...
So the performance impact might not be such a problem. :)
There was some mention about resumable uploads in the blogpost which sadly each provider handles differently (that is the GCS layer that supports the S3 API does not accept resumable uploads).
Disclosure: I work on Google Cloud (so I'd love to see this tool point at GCS).
Disclosure: I work on Google Cloud (so I'd love to see this tool point at GCS).
I answered this question in a couple of other places, but: unknown because I don't have use for that yet. https://news.ycombinator.com/item?id=15049527
Do you have plans to support encrypted backups?
Maybe. Depends what you mean by that: https://news.ycombinator.com/item?id=15049691
It would be nice if you could sign the tarball with the binary. I see the tag is already signed so hopefully it's not much trouble: https://wiki.debian.org/Creating%20signed%20GitHub%20release...
Done.
Any plans to support backup to Google Cloud Storage instead of just S3?
Or some sort of pluggable storage system.
For now, since I'm also on GCP, I'm using PGHoard: https://github.com/ohmu/pghoard
For now, since I'm also on GCP, I'm using PGHoard: https://github.com/ohmu/pghoard
Unknown, but I don't have an immediate plan to implement them: https://news.ycombinator.com/item?id=15049527
I've used WAL-E (the predecessor of this) for backing up Postgres's DB for years and it's been a very pleasant experience. From what I've read so far this looks like it's superior in every way. Lower resource usage, faster operation, and the switch to Go for WAL-G (v.s. Python for WAL-E) means no more mucking with Python versions either.
Great job to everybody that's working on this. I'm looking forward to trying it out.
Great job to everybody that's working on this. I'm looking forward to trying it out.
python versioning can be a bitch, but virtualenv/virtualenvwrapper make 99.999999% of the problems go away.
i have a huge ETL pipeline at twitch that relies heavily on wal-e, and installs it on worker nodes and things like that.
that being said, if WAL-G is faster, i don't care waht it is written in, and am happy to use it
i have a huge ETL pipeline at twitch that relies heavily on wal-e, and installs it on worker nodes and things like that.
that being said, if WAL-G is faster, i don't care waht it is written in, and am happy to use it
Every time I saw that mentioned, my mind goes to the movie. https://g.co/kgs/d8G9u5
I don't think it's a coincidence WAL-E is named as it is :)
For those interested in the repo directly to give it a try you can find it here: https://github.com/wal-g/wal-g
Fantastic intern project, and fantastic work by the intern!
Wow, great work! I am definitely going to test this out over the weekend. However AFAICT the `aws.Config` approach breaks certain backwards compatibility w/how wal-e handles credentials. Also wal-g does not currently support encryption. FWIW, I would love to simply drop-in wal-g without having to make any configuration changes.
Do you want GPG based or some other client side encryption, or S3's encryption support? The latter could probably just be turned on. The former is a feature requiring code.
Ideally the presence of the `WALE_GPG_KEY_ID` env var should enable encrypted backups https://github.com/wal-e/wal-e#encryption.
Put differently to be a "successor" it needs to be a drop in replacement ;)
Put differently to be a "successor" it needs to be a drop in replacement ;)
I have to be selective about maintenance of features. I'll consider GPG support.
Please consider libsodium or a similar "modern" crypto library instead. There's a lot of ugly 90s crypto in GPG and the API is terrible.
Libsodium makes it hard for non-crypto devs to shoot themselves in the foot, and is much less code to write.
Alternatively you could use a combination of AES and RSA similar how pghoard implements it: https://github.com/ohmu/pghoard
The RSA keys (or path to them) would be passed as environment variables. It would be a little easier to setup than gpg (especially for automatic backup restoration).
The RSA keys (or path to them) would be passed as environment variables. It would be a little easier to setup than gpg (especially for automatic backup restoration).
This is great. Can't wait to be using it.
We've been using WAL-E for years and this looks like a big improvement. The steady, high throughput is a big deal – our prod base backups take 36 hours to restore, so if the recovery speed improvements are as advertised, that's a big win. In the kind of situation in which we'd be using these, the difference between 9 hours and 36 hours is major.
Also, the quality of life improvements are great. Despite deploying WAL-E for years, we _still_ have problems with python, pip, dependencies, etc, so the switch to go is a welcome one. The backup_label issue has bitten us a half dozen times, and every time it's very scary for whoever is on-call. (The right thing to do is to rm a file in the database's main folder, so it's appropriately terrifying.) So switching to the new non-exclusive backups will also be great.
We're on 9.5 at the moment but will be upgrading to 10 after it comes out. Looking forward to testing this out. Awesome work!
We've been using WAL-E for years and this looks like a big improvement. The steady, high throughput is a big deal – our prod base backups take 36 hours to restore, so if the recovery speed improvements are as advertised, that's a big win. In the kind of situation in which we'd be using these, the difference between 9 hours and 36 hours is major.
Also, the quality of life improvements are great. Despite deploying WAL-E for years, we _still_ have problems with python, pip, dependencies, etc, so the switch to go is a welcome one. The backup_label issue has bitten us a half dozen times, and every time it's very scary for whoever is on-call. (The right thing to do is to rm a file in the database's main folder, so it's appropriately terrifying.) So switching to the new non-exclusive backups will also be great.
We're on 9.5 at the moment but will be upgrading to 10 after it comes out. Looking forward to testing this out. Awesome work!
Same here, perf is obviously great, but no more crazy dependencies is just great! So much time I wasted trying to make it fit into a docker container.
Can you share your dockerfiles?
Hey Dan, nice to hear from you!
> our prod base backups take 36 hours to restore, so if the recovery speed improvements are as advertised, that's a big win.
Yes, if you attach 16TB of storage to each instance, your back-up restores may take a while. :))
> our prod base backups take 36 hours to restore, so if the recovery speed improvements are as advertised, that's a big win.
Yes, if you attach 16TB of storage to each instance, your back-up restores may take a while. :))
Is it possible to run a continuous restore in parallel with normal operation so that there's a warm standby (almost) ready to go? Especially in another data center?
Yeah, just have a read replica. Can promote it to master later
Would standard postgres streaming replication do what you are looking for?
https://wiki.postgresql.org/wiki/Streaming_Replication
We use a combination of streaming replication and wal-e backups. A separate machine performs multiple restores per hour and verifies restores work ok and that the data is recent.
We use a combination of streaming replication and wal-e backups. A separate machine performs multiple restores per hour and verifies restores work ok and that the data is recent.
Will WAL-G eventually support the same archive targets as WAL-E (S3 and work-alikes, Azure Blob Store, Google Storage, Swift, File System)?
As I'm probably the steward on this going forward: unknown. I don't intend to implement them unless I need them. Would I take a patch with good coverage that implemented those.
Would you be willing to own the abstraction for multiple backends? The code is currently only a bit hardcoded to S3/AWS, but I assume most of the "work" will be discussing how to abstract different transports, exponential backoff, resumable uploads, and so on.
Fwiw, the GCS client for go (import "cloud.google.com/go/storage") is very straightforward. Though as others have pointed out, it might be worthwhile to just try to use minio-go if you want to gain Ceph as well.
Disclosure: I work on Google Cloud (and if the way is paved, we will contribute here; seems like a great project)
Fwiw, the GCS client for go (import "cloud.google.com/go/storage") is very straightforward. Though as others have pointed out, it might be worthwhile to just try to use minio-go if you want to gain Ceph as well.
Disclosure: I work on Google Cloud (and if the way is paved, we will contribute here; seems like a great project)
I wonder where python will end up in the next five or so years if Go is continually chosen for concurrent or high perf code things like this.
Good to see people sticking to the unix philosophy of doing one thing well and delegating other concerns - cat and lzop are both fine choices!