Обсуждение: pgBackRest configuration with DDBoost
Hi Experts,
I need to implement the full and incremental backups of postgreSQL cluster with a very large database (6 TB). Currently we are using DDBoost for Oracle database backups. So I was curious if we can use some postgres backup tool which is compatible or configurable with DDBoost. I googled and seems like pgBackRest or pbRMAN are pretty nice tools but not sure if these can be configurable with DDBoost. Please if someone can provide me the guidance about which tool is the best for full and incremental backups?
Best Regards
-Naveed-
On Mon, Jun 3, 2024 at 10:23 PM Naveed Iftikhar <naveed27c@yahoo.com> wrote:
Hi Experts,I need to implement the full and incremental backups of postgreSQL cluster with a very large database (6 TB). Currently we are using DDBoost for Oracle database backups. So I was curious if we can use some postgres backup tool which is compatible or configurable with DDBoost. I googled and seems like pgBackRest or pbRMAN are pretty nice tools but not sure if these can be configurable with DDBoost. Please if someone can provide me the guidance about which tool is the best for full and incremental backups?
We use PgBackRest on three multi-TB instances (sizes 3.5TB, 4.4TB, 6.1TB) and many smaller ones. They're ESX VMs with 32 CPUs and mounted SAN disks.
Takes 2 hr 45 min for us to do a weekly full backup of the 6.1TB instance using integrated gzip compression (it's an old RHEL6 system, so nothing better) and 24 threads. Size 3.3T after compression, which is surprising based on how much already-compressed bytea data there is.
Other-day incremental backups only take 10-45 minutes depending on activity.
PgBackRest also does continuous WAL (the PG version of rollback segments) backups.
AES-256 encryption, too, which is important for PCI.
Thus, while it doesn't support DDBoost, I'm very pleased with PgBackRest.
> On Jun 3, 2024, at 9:06 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote: > > Takes 2 hr 45 min for us to do a weekly full backup of the 6.1TB instance using integrated gzip compression (it's an oldRHEL6 system, so nothing better) and 24 threads. Size 3.3T after compression, which is surprising based on how much already-compressedbytea data there is. FYI, Naveed, there's a tradeoff available in current versions of pgbackrest. It can be configured to use lz4 compression.For us, that means close to 50% larger backups than gzip, but many times faster. 3.2TB in 30 minutes, using 8processes, 725GB backup size--no already compressed bytea, but a good bit of incompressible UUID columns. Good enough that I stopped doing daily incrementals, and just take full backups daily.
On Mon, Jun 3, 2024 at 11:22 PM Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jun 3, 2024, at 9:06 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> Takes 2 hr 45 min for us to do a weekly full backup of the 6.1TB instance using integrated gzip compression (it's an old RHEL6 system, so nothing better) and 24 threads. Size 3.3T after compression, which is surprising based on how much already-compressed bytea data there is.
FYI, Naveed, there's a tradeoff available in current versions of pgbackrest. It can be configured to use lz4 compression. For us, that means close to 50% larger backups than gzip, but many times faster. 3.2TB in 30 minutes, using 8 processes, 725GB backup size--no already compressed bytea, but a good bit of incompressible UUID columns.
Good enough that I stopped doing daily incrementals, and just take full backups daily.
How big were the incrementals? (I'm thinking more weeks of backup set retention when using full+incr.)
Go with pgbackrest tool it's provide wide functionality related with backup and restore.
On Tue, 4 Jun 2024, 07:53 Naveed Iftikhar, <naveed27c@yahoo.com> wrote:
Hi Experts,I need to implement the full and incremental backups of postgreSQL cluster with a very large database (6 TB). Currently we are using DDBoost for Oracle database backups. So I was curious if we can use some postgres backup tool which is compatible or configurable with DDBoost. I googled and seems like pgBackRest or pbRMAN are pretty nice tools but not sure if these can be configurable with DDBoost. Please if someone can provide me the guidance about which tool is the best for full and incremental backups?Best Regards-Naveed-
Thanks Scott for your input, Our database is keep growing and application owner want to have incremental backups daily. Can you share how you have configured the lz4 compression with pgbackrest. Any special storage requirements?
Best Regards
-Naveed-
On Monday, June 3, 2024 at 10:22:28 PM CDT, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
> On Jun 3, 2024, at 9:06 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> Takes 2 hr 45 min for us to do a weekly full backup of the 6.1TB instance using integrated gzip compression (it's an old RHEL6 system, so nothing better) and 24 threads. Size 3.3T after compression, which is surprising based on how much already-compressed bytea data there is.
FYI, Naveed, there's a tradeoff available in current versions of pgbackrest. It can be configured to use lz4 compression. For us, that means close to 50% larger backups than gzip, but many times faster. 3.2TB in 30 minutes, using 8 processes, 725GB backup size--no already compressed bytea, but a good bit of incompressible UUID columns.
Good enough that I stopped doing daily incrementals, and just take full backups daily.
>
> Takes 2 hr 45 min for us to do a weekly full backup of the 6.1TB instance using integrated gzip compression (it's an old RHEL6 system, so nothing better) and 24 threads. Size 3.3T after compression, which is surprising based on how much already-compressed bytea data there is.
FYI, Naveed, there's a tradeoff available in current versions of pgbackrest. It can be configured to use lz4 compression. For us, that means close to 50% larger backups than gzip, but many times faster. 3.2TB in 30 minutes, using 8 processes, 725GB backup size--no already compressed bytea, but a good bit of incompressible UUID columns.
Good enough that I stopped doing daily incrementals, and just take full backups daily.
On Tue, Jun 4, 2024 at 10:44 AM Naveed Iftikhar <naveed27c@yahoo.com> wrote:
Thanks Scott for your input, Our database is keep growing and application owner want to have incremental backups daily. Can you share how you have configured the lz4 compression with pgbackrest.
It's all in the Fine Manuals, of which a lot of effort was placed in writing.
Any special storage requirements?
What do you mean (other than that lz4 is tuned for speed, rather than high compression)? PGBR supports zstd and bz2, as well as lz4 and gzip.
On Monday, June 3, 2024 at 10:22:28 PM CDT, Scott Ribe <scott_ribe@elevated-dev.com> wrote:> On Jun 3, 2024, at 9:06 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> Takes 2 hr 45 min for us to do a weekly full backup of the 6.1TB instance using integrated gzip compression (it's an old RHEL6 system, so nothing better) and 24 threads. Size 3.3T after compression, which is surprising based on how much already-compressed bytea data there is.
FYI, Naveed, there's a tradeoff available in current versions of pgbackrest. It can be configured to use lz4 compression. For us, that means close to 50% larger backups than gzip, but many times faster. 3.2TB in 30 minutes, using 8 processes, 725GB backup size--no already compressed bytea, but a good bit of incompressible UUID columns.
Good enough that I stopped doing daily incrementals, and just take full backups daily.
It's just --compress-type=lz4 and storage should be fast--that's all
Thanks a lot.
Best Regards
-Naveed-
On Tuesday, June 4, 2024 at 10:23:06 AM CDT, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
It's just --compress-type=lz4
and storage should be fast--that's all
and storage should be fast--that's all