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

Massively Parallel Postgres Backups

planetscale.com

Post reply on HN
Print view View original

11–18 of 18 posts

Re: Massively Parallel Postgres Backups

#11

Post by adastral »

imjosh-dev wrote: post #10
gandreani wrote: post #5

Earlier quoted context omitted.

Hello! Very nice article. I have a couple of questions if you don't mind. When doing the last streaming of the wal from the primary the article mentions that the nodes will catch up to replication time `T`. How do the nodes coordinate this time `T`? Is it simply just choosing a time in the future (after the backup has started) and waiting till they all catch up or is there more realtime coordination happening? Also,…

(Also PlanetScale employee here) Each shard finishes a backup at it's own time T, so two different shards could finish minutes apart (or even more, depending on the difference in size). for pretty much any use of a backup, you'll effectively be doing a PITR, not a raw restore from the cluster. The PITR timestamp is what unifies all the clusters together, regardless on when each backup finishes. think of backups as ju…

How do you cope with distributed transactions? (I'm not sure if you support them?)

I know that Citus has a `citus_create_restore_point()` (or so) function that, when called, guarantees that no 2PC commits are in flight and creates a WAL restore point in every shard. Therefore, restoring shards to that point will leave the DB in a consistent state. Do you do something similar?

Re: Massively Parallel Postgres Backups

#12

Post by bddicken »

adastral wrote: post #11
imjosh-dev wrote: post #10

Earlier quoted context omitted.

(Also PlanetScale employee here) Each shard finishes a backup at it's own time T, so two different shards could finish minutes apart (or even more, depending on the difference in size). for pretty much any use of a backup, you'll effectively be doing a PITR, not a raw restore from the cluster. The PITR timestamp is what unifies all the clusters together, regardless on when each backup finishes. think of backups as ju…

How do you cope with distributed transactions? (I'm not sure if you support them?) I know that Citus has a `citus_create_restore_point()` (or so) function that, when called, guarantees that no 2PC commits are in flight and creates a WAL restore point in every shard. Therefore, restoring shards to that point will leave the DB in a consistent state. Do you do something similar?

Neki will support cross-shard ACID transactions with a combination of an external transaction coordinator and some changes to PostgreSQL itself to support this (either by engine modifications or extensions).

I expect as part of that, we'll allow users to leverage something similar to make sure that we don't get transaction tearing in backups.

Re: Massively Parallel Postgres Backups

#13

Post by gregsadetsky »

bddicken wrote: post #3

What I emphasized in this article (author here) is scaling postgres backups. This works because there's already rock-solid systems built into postgres + surrounding tooling to build from. The broader takeaway is the principle of, "how do I take something that doesn't scale on its own and make it so?" This applies to backups, compute, storage layers, proxies. It's why Neki and Vitess are so powerful for everything fro…

hey, just curious - would you consider using or recommending https://pgbackrest.org/ (in non massive postgres backup scenarios)

And do you think PlanetScale would consider supporting that project? (it was shut down but resurrected thanks to support from sponsors)

Re: Massively Parallel Postgres Backups

#14

Post by bddicken »

gregsadetsky wrote: post #13
bddicken wrote: post #3

What I emphasized in this article (author here) is scaling postgres backups. This works because there's already rock-solid systems built into postgres + surrounding tooling to build from. The broader takeaway is the principle of, "how do I take something that doesn't scale on its own and make it so?" This applies to backups, compute, storage layers, proxies. It's why Neki and Vitess are so powerful for everything fro…

hey, just curious - would you consider using or recommending https://pgbackrest.org/ (in non massive postgres backup scenarios) And do you think PlanetScale would consider supporting that project? (it was shut down but resurrected thanks to support from sponsors)

pgBaseBackup seems very cool. It's great that they got support to continue building. We just don't use it presently.

Re: Massively Parallel Postgres Backups

#16

Post by pritambaral »

CodesInChaos wrote: post #9
bddicken wrote: post #6

Earlier quoted context omitted.

Broadly for Pg, minor version upgrades are straightforward as the data on disk is guaranteed to be compatible. All it takes is a restart or switchover to upgrade. Major versions are more challenging for "vanilla" Postgres because that's not the case. Storage/catalog formats may change. There needs to be an explicit upgrade process for the data (eg, a database created with v18 won't work out of the box with v19). The…

I believe most major versions of postgres only change metadata, so the downtime only scales with the size of the schema (usually small) and not the size of the data.

Note: this only applies if the `--link` flag is used with `pg_upgrade` (which, I recommend), but the default is to copy all data.

Re: Massively Parallel Postgres Backups

#17

Post by gandreani »

imjosh-dev wrote: post #10
gandreani wrote: post #5

Earlier quoted context omitted.

Hello! Very nice article. I have a couple of questions if you don't mind. When doing the last streaming of the wal from the primary the article mentions that the nodes will catch up to replication time `T`. How do the nodes coordinate this time `T`? Is it simply just choosing a time in the future (after the backup has started) and waiting till they all catch up or is there more realtime coordination happening? Also,…

(Also PlanetScale employee here) Each shard finishes a backup at it's own time T, so two different shards could finish minutes apart (or even more, depending on the difference in size). for pretty much any use of a backup, you'll effectively be doing a PITR, not a raw restore from the cluster. The PITR timestamp is what unifies all the clusters together, regardless on when each backup finishes. think of backups as ju…

Ah that makes a lot of sense! Thanks for taking the time to implement. I like the design of the system.

Re: Massively Parallel Postgres Backups

#18

Post by CodesInChaos »

pritambaral wrote: post #16
CodesInChaos wrote: post #9

Earlier quoted context omitted.

I believe most major versions of postgres only change metadata, so the downtime only scales with the size of the schema (usually small) and not the size of the data.

Note: this only applies if the `--link` flag is used with `pg_upgrade` (which, I recommend), but the default is to copy all data.

Assuming you're on a filesystem that supports reflinks (Btrfs, XFS), `--clone` is an interesting option as well. Though assuming you create a volume backup before starting migration, hardlinks don't really have downsides and will probably have better performance after the upgrade since the avoid copy-on-write.
Post reply on HN