Re: pg_basebackup is taking more time than expected

Please let me know below details.

pg_basebackup utility depends on which are the parameters?

Is there any possibility to run the pg_basebackup in multi thread?

To improve the speed of the backup of database using pg_basebackup utility
we shutdown the database and started alone database services. Till that
time other sevices won't run. We observed some improvement but any other
things we need to perform to reduce the time taken by the pg_basebackup
utility.

We are using below command to take the backup of the database. Any
improvements to reduce the time taken by backup statement.

$PGHOME/bin/pg_basebackup -p 5433 -U postgres -P -v -x --format=tar --gzip
--compress=6 --pgdata=- -D /opt/backup_db

On Fri, Jan 12, 2018 at 6:37 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Raghavendra Rao J S V (raghavendrajsv(at)gmail(dot)com) wrote:
> > We have database with the size of *425GB*. Out of this 425 GB, Around 40
> to
> > 60% of space occupied by the indexes. Ram allocated to this machine is
> > 32GB and configuration parameters below.
> [...]
> > In order to take the backup of the database we are using pg_basebackup
> > utility. But it is taking several hours (Around 18hours). Please guide me
> > what are the configuration parameters need to modify to reduce the time
> > taken by the pg_basebackup utility.
>
> Unfortunately, there aren't a lot of great options with pg_basebackup,
> but part of the question is- why is it taking so long? That looks to be
> a rate of less than 60Mb/s, assuming I did my calculations right, and
> that's pretty slow. How are you running pg_basebackup? If you're doing
> compression and the pg_basebackup process is consistently at 100% then
> that's just the rate which a single CPU can compress data for you. If
> you're running the pg_basebackup across a WAN, then perhaps the
> throughput available is only 60Mb/s.
>
> > Is there any possibility to exclude the index data while taking the
> > pg_basebackup?
>
> This isn't currently possible, no, and it would seriously increase the
> time required to restore the system. If you aren't concerned about
> restore time at all (though, really, you probably should be) then you
> could consider using pg_dump instead, which can be run in parallel and
> wouldn't include the indexes. The format is a logical point-in-time
> dump though, so you aren't able to do point-in-time recovery (playing
> WAL forward) and reloading the data and rebuilding the indexes will take
> quite a while.
>
> Lastly, if the issue is that pg_basebackup is single-threaded, or that
> you need multiple TCP connections to get higher throughput, then you
> should consider one of the alternative physical (file-based) backup
> solutions available for PostgreSQL, e.g.: pgBackRest, barman, or WAL-G.
>
> Thanks!
>
> Stephen
>

--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425