Re: Performance issues during backup

What about sending the backup to a different server? through ssh / rsync or
something, that would save lots of IO activity

2018-02-20 2:02 GMT-06:00 Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>:

> Dylan Luong wrote:
> > We perform nighty base backup of our production PostgreSQL instance. We
> have a script that basically puts the instance
> > into back mode and then backs up (tar) the /Data directory and then
> takes it out of backup mode.
> > Ie,
> > psql -c "SELECT pg_start_backup('${DATE}');"
> > tar -cvf - ${DATA_DIR} --exclude ${DATA_DIR}/pg_log | split -d -b
> $TAR_SPLIT_SIZE - ${BACKUP_DIR}/${BACKUP_NAME}
> > psql -c "SELECT pg_stop_backup();"
> >
> > The size of our database is about 250GB and it usually takes about 1
> hour to backup.
> > During this time, we have performance issue where queries can take up to
> 15secs to return where normally it takes 2 to 3 seconds.
> > During this time (1:30am) usage is low (less than 10 users) on the
> system.
> >
> > Has anyone experience the same problem and any suggestions where to look
> at to resolve the problem?
>
> The "tar" is probably taking up too much I/O bandwidth.
>
> Assuming this is Linux, you could run it with
>
> ionice -c 2 -n 7 tar ...
>
> or
>
> ionice -c 3 tar ...
>
> Of course then you can expect the backup to take more time.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/