Greetings, * Raghavendra Rao J S V (raghavendrajsv@xxxxxxxxx) 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
Attachment:
signature.asc
Description: PGP signature
