pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPG_Base Backup take 8 to 9Hrs
11+ messages / 9 participants
[nested] [flat]
* PG_Base Backup take 8 to 9Hrs
@ 2022-02-08 11:36 Ram Pratap Maurya <ram.maurya@lavainternational.in>
0 siblings, 2 replies; 11+ messages in thread
From: Ram Pratap Maurya @ 2022-02-08 11:36 UTC (permalink / raw)
To: 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org>; +Cc: Ashish Chugh <ashish.chugh@lavainternational.in>
Hi Team,
We are using Postgres -11 version and current PG DB size is 450 GB .
Archive log enable in Database and we are running Full PG_Base backup and it will take 8 to 9 Hrs to complete the backup ,
Please suggest how to reduce the full DB backup time.
OS Version : RHEL6
DB size -450 GB
Postgresql Version -11
Regards,
Ram Pratap.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-08 13:02 Julien Rouhaud <rjuju123@gmail.com>
parent: Ram Pratap Maurya <ram.maurya@lavainternational.in>
1 sibling, 1 reply; 11+ messages in thread
From: Julien Rouhaud @ 2022-02-08 13:02 UTC (permalink / raw)
To: Ram Pratap Maurya <ram.maurya@lavainternational.in>; +Cc: 'pgsql-admin@lists.postgresql.org' <pgsql-admin@lists.postgresql.org>; Ashish Chugh <ashish.chugh@lavainternational.in>
Hi,
Please don't try to recall messages sent on a mailing list. That won't work
and will spam everyone.
On Tue, Feb 08, 2022 at 11:36:19AM +0000, Ram Pratap Maurya wrote:
>
> We are using Postgres -11 version and current PG DB size is 450 GB .
>
> Archive log enable in Database and we are running Full PG_Base backup and it
> will take 8 to 9 Hrs to complete the backup ,
>
> Please suggest how to reduce the full DB backup time.
That's ~16MB/s, so many times slower than a single old 7200RPM drive. The
problem is likely not with postgres, you should get a better storage or a
better network.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-08 14:53 MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: MichaelDBA@sqlexec.com @ 2022-02-08 14:53 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Ram Pratap Maurya <ram.maurya@lavainternational.in>; pgsql-admin@lists.postgresql.org, Ashish Chugh <ashish.chugh@lavainternational.in>
Use pgbackrest instead
Sent from my iPhone
> On Feb 8, 2022, at 8:02 AM, Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> Hi,
>
> Please don't try to recall messages sent on a mailing list. That won't work
> and will spam everyone.
>
>> On Tue, Feb 08, 2022 at 11:36:19AM +0000, Ram Pratap Maurya wrote:
>>
>> We are using Postgres -11 version and current PG DB size is 450 GB .
>>
>> Archive log enable in Database and we are running Full PG_Base backup and it
>> will take 8 to 9 Hrs to complete the backup ,
>>
>> Please suggest how to reduce the full DB backup time.
>
> That's ~16MB/s, so many times slower than a single old 7200RPM drive. The
> problem is likely not with postgres, you should get a better storage or a
> better network.
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-10 00:42 Mladen Gogala <gogala.mladen@gmail.com>
parent: Ram Pratap Maurya <ram.maurya@lavainternational.in>
1 sibling, 1 reply; 11+ messages in thread
From: Mladen Gogala @ 2022-02-10 00:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 2/8/22 06:36, Ram Pratap Maurya wrote:
>
> We are using Postgres -11 version and current *PG DB size is 450 GB* .
>
> Archive log enable in Database and we are running Full *PG_Base
> backup* and it will take 8 to 9 Hrs to complete the backup ,
>
> Please suggest how to reduce the full DB backup time.
>
Use storage snapshots, faster disks, faster Ethernet adapters and backup
software that supports deduplication. You are asking the list to
architect your backup solution. For that, some more information is
needed: what kind of machine are you using, what OS, how much memory,
what kind of storage is your cluster running on, what kind of storage
are you backing it up to, how is the backup storage connected (FC/AL,
Ethernet, local SATA drives), is there any degree of parallelism and
things of that nature. In the absence of that information, I can only
recommend attempting with the --run-faster switch to pg_basebackup. You
may also try using the Force.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-10 14:31 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Mladen Gogala <gogala.mladen@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: MichaelDBA @ 2022-02-10 14:31 UTC (permalink / raw)
To: Mladen Gogala <gogala.mladen@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Please use a third party backup solution that provides faster and more
flexible backup alternatives than the built in, pg_basebackup(), which
is not usefult for large databases.
pgbackrest (full, differential, and incremental)
barman (full and incremental)
Regards,
Michael Vitale
Mladen Gogala wrote on 2/9/2022 7:42 PM:
> On 2/8/22 06:36, Ram Pratap Maurya wrote:
>>
>> We are using Postgres -11 version and current *PG DB size is 450 GB* .
>>
>> Archive log enable in Database and we are running Full *PG_Base
>> backup* and it will take 8 to 9 Hrs to complete the backup ,
>>
>> Please suggest how to reduce the full DB backup time.
>>
> Use storage snapshots, faster disks, faster Ethernet adapters and
> backup software that supports deduplication. You are asking the list
> to architect your backup solution. For that, some more information is
> needed: what kind of machine are you using, what OS, how much memory,
> what kind of storage is your cluster running on, what kind of storage
> are you backing it up to, how is the backup storage connected (FC/AL,
> Ethernet, local SATA drives), is there any degree of parallelism and
> things of that nature. In the absence of that information, I can only
> recommend attempting with the --run-faster switch to pg_basebackup.
> You may also try using the Force.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-10 14:36 Wells Oliver <wells.oliver@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 1 reply; 11+ messages in thread
From: Wells Oliver @ 2022-02-10 14:36 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Mladen Gogala <gogala.mladen@gmail.com>; pgsql-admin@lists.postgresql.org
Can you just try good ole pg_dump with --format=directory and --jobs=X
where X is some decent number, e.g. 16? We just packed up a 700GB DB using
16 jobs and it took maybe 80 minutes.
On Thu, Feb 10, 2022 at 6:31 AM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> Please use a third party backup solution that provides faster and more
> flexible backup alternatives than the built in, pg_basebackup(), which is
> not usefult for large databases.
> pgbackrest (full, differential, and incremental)
> barman (full and incremental)
>
> Regards,
> Michael Vitale
>
> Mladen Gogala wrote on 2/9/2022 7:42 PM:
>
> On 2/8/22 06:36, Ram Pratap Maurya wrote:
>
> We are using Postgres -11 version and current *PG DB size is 450 GB* .
>
> Archive log enable in Database and we are running Full *PG_Base backup*
> and it will take 8 to 9 Hrs to complete the backup ,
>
> Please suggest how to reduce the full DB backup time.
>
> Use storage snapshots, faster disks, faster Ethernet adapters and backup
> software that supports deduplication. You are asking the list to architect
> your backup solution. For that, some more information is needed: what kind
> of machine are you using, what OS, how much memory, what kind of storage is
> your cluster running on, what kind of storage are you backing it up to, how
> is the backup storage connected (FC/AL, Ethernet, local SATA drives), is
> there any degree of parallelism and things of that nature. In the absence
> of that information, I can only recommend attempting with the --run-faster
> switch to pg_basebackup. You may also try using the Force.
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-10 15:16 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: MichaelDBA @ 2022-02-10 15:16 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Mladen Gogala <gogala.mladen@gmail.com>; pgsql-admin@lists.postgresql.org
Just keep in mind that logical dumps and loads do not support Point In
Time Recovery (PITR).
Wells Oliver wrote on 2/10/2022 9:36 AM:
> Can you just try good ole pg_dump with --format=directory and --jobs=X
> where X is some decent number, e.g. 16? We just packed up a 700GB DB
> using 16 jobs and it took maybe 80 minutes.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-10 16:45 Mladen Gogala <gogala.mladen@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 0 replies; 11+ messages in thread
From: Mladen Gogala @ 2022-02-10 16:45 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: pgsql-admin@lists.postgresql.org
Please don't top-post.
On 2/10/22 09:31, MichaelDBA wrote:
> Please use a third party backup solution that provides faster and more
> flexible backup alternatives than the built in, pg_basebackup(), which
> is not usefult for large databases.
> pgbackrest (full, differential, and incremental)
> barman (full and incremental)
>
> Regards,
> Michael Vitale
>
> Mladen Gogala wrote on 2/9/2022 7:42 PM:
>> On 2/8/22 06:36, Ram Pratap Maurya wrote:
>>>
>>> We are using Postgres -11 version and current *PG DB size is 450 GB* .
>>>
>>> Archive log enable in Database and we are running Full *PG_Base
>>> backup* and it will take 8 to 9 Hrs to complete the backup ,
>>>
>>> Please suggest how to reduce the full DB backup time.
>>>
>> Use storage snapshots, faster disks, faster Ethernet adapters and
>> backup software that supports deduplication. You are asking the list
>> to architect your backup solution. For that, some more information is
>> needed: what kind of machine are you using, what OS, how much memory,
>> what kind of storage is your cluster running on, what kind of storage
>> are you backing it up to, how is the backup storage connected (FC/AL,
>> Ethernet, local SATA drives), is there any degree of parallelism and
>> things of that nature. In the absence of that information, I can only
>> recommend attempting with the --run-faster switch to pg_basebackup.
>> You may also try using the Force.
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217
>> https://dbwhisperer.wordpress.com
>
I can wholeheartedly recommend Commvault backup suite. It can seamlessly
utilize storage snapshot and has built in parallelism, deduplication and
compression.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-10 17:08 Avihai Shoham <avihai.shoham@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 2 replies; 11+ messages in thread
From: Avihai Shoham @ 2022-02-10 17:08 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Wells Oliver <wells.oliver@gmail.com>; Mladen Gogala <gogala.mladen@gmail.com>; pgsql-admin@lists.postgresql.org
Hi All ,
lately i'm getting this error when open base.tar.
Any idea why pg_basebackup created bad tar file? and how to fix it?
Thanks!
Avihai
tar xOf base.tar > /dev/null
tar: Unexpected EOF in archive
tar: rmtlseek not stopped at a record boundary
tar: Error is not recoverable: exiting now
On Thu, Feb 10, 2022 at 5:16 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> Just keep in mind that logical dumps and loads do not support Point In
> Time Recovery (PITR).
>
> Wells Oliver wrote on 2/10/2022 9:36 AM:
> > Can you just try good ole pg_dump with --format=directory and --jobs=X
> > where X is some decent number, e.g. 16? We just packed up a 700GB DB
> > using 16 jobs and it took maybe 80 minutes.
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-10 17:55 Ron <ronljohnsonjr@gmail.com>
parent: Avihai Shoham <avihai.shoham@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Ron @ 2022-02-10 17:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Please don't hijack threads.
On 2/10/22 11:08 AM, Avihai Shoham wrote:
> Hi All ,
>
> lately i'm getting this error when open base.tar.
> Any idea why pg_basebackup created bad tar file? and how to fix it?
>
> Thanks!
> Avihai
>
> tar xOf base.tar > /dev/null
> tar: Unexpected EOF in archive
> tar: rmtlseek not stopped at a record boundary
> tar: Error is not recoverable: exiting now
>
>
>
> On Thu, Feb 10, 2022 at 5:16 PM MichaelDBA <MichaelDBA@sqlexec.com
> <mailto:MichaelDBA@sqlexec.com>> wrote:
>
> Just keep in mind that logical dumps and loads do not support Point In
> Time Recovery (PITR).
>
> Wells Oliver wrote on 2/10/2022 9:36 AM:
> > Can you just try good ole pg_dump with --format=directory and --jobs=X
> > where X is some decent number, e.g. 16? We just packed up a 700GB DB
> > using 16 jobs and it took maybe 80 minutes.
>
>
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: PG_Base Backup take 8 to 9Hrs
@ 2022-02-11 17:21 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Avihai Shoham <avihai.shoham@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Scott Ribe @ 2022-02-11 17:21 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On Feb 11, 2022, at 12:22 AM, Dischner, Anton <Anton.Dischner@med.uni-muenchen.de> wrote:
>
> lately i'm getting this error when open base.tar.
> Any idea why pg_basebackup created bad tar file? and how to fix it?
So... When asked not to hijack threads, your response is to apologize, wait a few hours, then do it again???
Please send your own question with a subject that matches.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2022-02-11 17:21 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-08 11:36 PG_Base Backup take 8 to 9Hrs Ram Pratap Maurya <ram.maurya@lavainternational.in>
2022-02-08 13:02 ` Julien Rouhaud <rjuju123@gmail.com>
2022-02-08 14:53 ` MichaelDBA@sqlexec.com <michaeldba@sqlexec.com>
2022-02-10 00:42 ` Mladen Gogala <gogala.mladen@gmail.com>
2022-02-10 14:31 ` MichaelDBA <MichaelDBA@sqlexec.com>
2022-02-10 14:36 ` Wells Oliver <wells.oliver@gmail.com>
2022-02-10 15:16 ` MichaelDBA <MichaelDBA@sqlexec.com>
2022-02-10 17:08 ` Avihai Shoham <avihai.shoham@gmail.com>
2022-02-10 17:55 ` Ron <ronljohnsonjr@gmail.com>
2022-02-11 17:21 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-02-10 16:45 ` Mladen Gogala <gogala.mladen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox