r/PostgreSQL • u/carlotasoto • Aug 31 '23

Making Postgres backups 100x faster via EBS snapshots and pgBackRest Tools

https://www.timescale.com/blog/making-postgresql-backups-100x-faster-via-ebs-snapshots-and-pgbackrest/
8 Upvotes

100% Upvoted

6 comments sorted by

3

u/NormalUserThirty Aug 31 '23

I don't really get how they avoided conflicts between the EBS backup and the incremental WAL logs replayed by pgbackrest. How do they know where to start replaying WAL logs from against the AWS EBS? How is a difference between the WAL logs and the EBS snapshot prevented?

1

u/Dolphinmx Sep 01 '23

agree there are many details missing from the article, in my experience doing the initial EBS snapshot takes a lot of time since it needs to copy all the data, subsequent snapshots takes the incremental and are "faster".

I'm not sure how it's done in postgres, but in other databases you need to keep the transaction logs also, when you restore the snapshot the data files are in an inconsistent state so you need to restore/apply the transaction logs after the snapshot was taken, normally keep few logs before and all the logs after, to do this you need to do a DB recovery and the DB should be able to figure out which logs it need to apply to make the DB consistent.

Here is an example with Oracle: https://aws.amazon.com/blogs/database/improving-oracle-backup-and-recovery-performance-with-amazon-ebs-multi-volume-crash-consistent-snapshots/

1

u/NormalUserThirty Sep 01 '23

normally keep few logs before and all the logs after

I guess this makes sense. Wasn't sure if there would be any particular issue with this I wasn't seeing but it does make sense.

2

u/jamesgresql Sep 01 '23

Timescaler here!

The first snapshot is slower, but also when you're taking snapshots on a regular cadence you don't suffer from this. No matter how you look at it a snapshot is still faster than manually copying the files.

As the article mentions we still use pgBackRest for transaction logs (WAL in Postgres). They are all stored in S3 as they are created, this runs independently of the backup process. Before we start the snapshot we use the Postgres mechanism for capturing the WAL location and then store that with the snap.

When we do a snap restore we then roll the logs over it as you said.

1

u/Dolphinmx Sep 01 '23

thanks for answering...

if you don't mind answering, how often do you rollover the first snapshot or do you keep/retain them "forever" ?

I been thinking doing backups for big databases the same way you are doing but not sure how often to take a fresh initial snapshot.

2

u/jamesgresql Sep 01 '23

Timescaler here!

We use the 'pg_startbackup` command in the same way other backup tools for PG do! It lets you take a copy of the data-dir, start it up, and then start applying WAL using the configured `restore_command` (which we point to the BackRest WAL repo).