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_dbAccording 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.
- 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?
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.
