Thread: PG_Base Backup take 8 to 9Hrs

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.

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.
>
>

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

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

--

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.

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

Please don't hijack threads.

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.

> 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.