Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Introducing WAL-G: Faster Disaster Recovery for Postgres

citusdata.com

Post reply on HN
Print view View original

61–66 of 66 posts

Re: Introducing WAL-G: Faster Disaster Recovery for Postgres

#61

Post by andruby »

ngrilly wrote: post #44
andruby wrote: post #40

Earlier quoted context omitted.

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 d…

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.

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.

Re: Introducing WAL-G: Faster Disaster Recovery for Postgres

#62

Post by ngrilly »

andruby wrote: post #61
ngrilly wrote: post #44

Earlier quoted context omitted.

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.

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?

Re: Introducing WAL-G: Faster Disaster Recovery for Postgres

#63

Post by tatersolid »

fdr wrote: post #45
upbeatlinux wrote: post #42

Earlier quoted context omitted.

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

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.

Re: Introducing WAL-G: Faster Disaster Recovery for Postgres

#64

Post by jarym »

wmf wrote: post #25
jarym wrote: post #21

"WAL-E compresses using lzop as a separate process, as well as the command cat to prevent disk I/O from blocking." 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!

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.

Re: Introducing WAL-G: Faster Disaster Recovery for Postgres

#65

Post by andruby »

ngrilly wrote: post #62
andruby wrote: post #61

Earlier quoted context omitted.

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?

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.

Re: Introducing WAL-G: Faster Disaster Recovery for Postgres

#66

Post by ngrilly »

andruby wrote: post #65
ngrilly wrote: post #62

Earlier quoted context omitted.

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?

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.

2 GB/week is so much better than 161 GB/week! It looks like compression helps a lot. Thanks for sharing these numbers.
Post reply on HN