Wal-E: Continuous Archiving for Postgres
github.com
github.com
Where I was, we investigated wal-e and determined it doesn't do anything that the raw curl commands wouldn't be able to do for us.
What we decided to do was to utilize HDFS for continuous archival, and we patched a version of `pg_receivexlog` to actually stream the WAL files out to HDFS, with flushing support that acknowledged transactions over the wire when the flushes were complete.
With this, you could treat this patched pg_receivexlog_hdfs command as a standby postgres database, and even add it to `synchronous_standby_names`, and postgres would effectively have its transaction logs synchronously written out to durable storage. We combined it with a lot of plumbing and we were able to get postgres running in mesos with docker without any actual persistent storage volumes (just used local disk.)
The best part is, you'd do a COMMIT and it would essentially block until the data was in HDFS. No periodic snapshots where you'd lose transactions that happened seconds after the snapshot... if the client sees a transaction as committed, it's on durable storage.
Worked pretty well, I'm surprised WAL-E doesn't support something similar (it only checkpoints at predefined intervals, not on transaction commit time.)
Do you have any problems with freezes or timeouts during high loads?
Also the way I see it, turning on 'synchronous_standby_names' was a nice added safety guarantee, in practice leaving the hdfs receiver asynchronous would be a reasonable alternative if you're confident things will work as you expect.
Wal-E has been used for a number of years to provide disaster recovery for Heroku Postgres, for over a million databases. It enables their follow and fork functionality, and we're using it at Citus as well for Citus Cloud given we have the person that authored it.
As for exact differences I'm less familiar as I've not seriously run barman in production so perhaps someone that's run both can chime in.
Doesn't WAL replication only let you restore a full cluster, not a single DB? How do they get around that?
(Read: a new 10MB file to S3 every 10sec, even when the DB is 100% idle).
Apparently PG 10 will improve your use case, though: http://paquier.xyz/postgresql-2/postgres-10-checkpoint-skip/
EDIT: Also, wal-e compresses by default, so even those regular WAL files should be much smaller than 10MB. Are you sure the DB is really idle?
Compression is enabled indeed. Surprisingly, the compression ratio for "nothing going on" is terrible. (still multiple MBytes).
The next version of postgre will redo the transaction log to have dynamic adaptive sizing, with new settings to control it. Not there yet.
Or is this something else you're talking about?
That's the maximum age of a WAL segment before rotation, and Postgres will force the log rotation even if there's no data in the WAL segment.
If you're worried about keeping the last n seconds of data in replication, streaming replication is a far better tactic than log-shipping. (Though log shipping is useful for longer term storage)
barman stores data on a filesystem.
I'm using wal-e, for me a no brainer since I don't have elastic filesystems available but do have scalable object storage. Other sites will have the opposite problem.
One way to continuously test that your files are being pushed up correctly is to have a hot standby pulling WAL files from S3 and check periodically that the data in the standby looks sane.
Although not a replacement for full "restore from backups" tests (because that process involves a base backup too), it's a good way to quickly notice issues preventing WAL files from being stored on S3 or from being decrypted.
When backing up to S3, KMS has a ton of advantages.
Though in that vein, maybe the native S3 encryption at at rest with KMS is sufficient?