Re: pg_basebackup is taking more time than expected

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