Re: pg_basebackup is taking more time than expected

On Mon, Jan 15, 2018 at 7:57 AM, Raghavendra Rao J S V <
raghavendrajsv(at)gmail(dot)com> wrote:

> I am looking for the help to minimise the time taken by the pg_basebackup
> utility.
>
> As informed Earlier we are taking the backup of the database using
> pg_basbackup utility using below command.
>
> $PGHOME/bin/pg_basebackup -p 5433 -U postgres -P -v -x --format=tar --gzip
> --compress=6 --pgdata=- -D /opt/backup_db
>
> According to our previous discussion, pg_basebackup is not depend on any
> of the postgresql configuration parameters. If I go for gzip format we need
> to compromise on time.
>
> We are planning to take by following below steps. Please correct me if I
> am wrong.
>
>
> 1. Identify the larger indexes(whose size is above 256MB) and drop
> those indexes. Due to this size of the database will reduce.
>
> ​I'm with Stephen on this one - going into standalone mode and dropping
indexes seems odd...​

I am new to postgres database. Could you help me to construct the query to
> drop and create the indexes, please?
>

​https://www.postgresql.org/docs/10/static/sql-commands.html

see "CREATE INDEX" and "DROP INDEX" in that listing.​

​You haven't explained the purpose of the backups but its for availability
and you are this concerned about speed you should investing it learning and
install a hot-standby replication configuration.

Additionally, consider whether you can improve the IO of the setup you are
using since, as Stephen said, and I am far from experienced in this area,
18hours for less than 500GB of data seem extraordinarily long.

David J.