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@xxxxxxxxxxx> wrote:
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
Regards,
Raghavendra Rao J S V
Mobile- 8861161425
Raghavendra Rao J S V
Mobile- 8861161425
