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
