Database Reference
In-Depth Information
Improving performance of backup/restore
Performance is often a concern in any medium or large database.
Backup performance is often a delicate issue, because the resource usage may need to be
limited to within certain boundaries. There may also be a restriction on the maximum run-time
for the backup, for example, if the backup runs each Sunday.
Again, restore performance may be more important than backup performance, even if backup
is the more obvious concern.
Getting ready
If performance is a concern or is likely to be one, then you should read the recipe about
planning first.
How to do it...
F
Physical backup: Improving performance of a physical backup can be done by taking
the backup in parallel. That is, copying away the files using more than one task. The
more tasks you use, the more it will impact the current system. When backing up, you
can skip certain files. You won't need (in order) the following:
any files placed there by DBA that shouldn't actually be there
any files in
pg_xlog
any old server log files in
pg_log
(even the current one)
Remember, it's safer not to try to exclude files at all, as if you miss something critical
you may get data loss. Also remember that your backup speed may be bottlenecked
by your disks or your network. Some larger systems have dedicated networks in place
purely for backups.
F
Logical backup: As explained in a previous recipe, if you want to backup all
databases in a database server, then you should use multiple pg_dump tasks
running in parallel. If you want to speed up the dump speed of a pg_dump task, there
really isn't an easy way of doing that right now. If you're using compression, look at
the notes at the bottom of this recipe.
F
Physical restore: Just as with physical backup, it's possible for us to put everything
back quicker if we use parallel restore.
F
Logical restore: Whether you use psql or
pg_restore
, you can speed up the
program by assigning
maintenance_work_mem
=
128MB
or more either in
postgresql.conf
or on the user that will run the restore. If neither of those ways
is easily possible, you can specify the option using the
PGOPTIONS
environment
variable, as follows:
