How to run pg_dump so that it doesn't slow down other connections?

Question

I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size:

pg_dump -Fc dbname > file

The problem I am having is that while pg_dump runs, which takes several hours, all the other database connections are extremely slow.

Is there any way to make the pg_dump consume less resources or is there a more proper way of creating periodic backups that doesn't slow down the db server so much?

E.g. would running pg_dump from a different server help the database I/O on its working server?

Answer 1

use atop and pg_activity to determine the bottlneck. That could be server resource together with not optimal PG configuration/ non-optimal query OR missing indexes.

to improve dump speed you run dump:

• from another server (that requires good network)

• use zero compress option -Z0 (reduces dump time, due to pg_dump can not parallel compressin, but increases requirements for IO, that could make your other requests even slower for time dump is running)

• use directory format for backup: -Fd
• use jobs: -j8 (depends on number of cores you server has)

Answer 2

several hours seems a bit long for only 50gigs as other have stated. This tells us the IO on the server, or destination is bogged down or needs to be up-sized. Or this is network issue causing the slowness.

I suggest do a pg_dump and monitor the server resources, hard-drive, and Network and the destination to see where slowness is

Another option that has shown to reduce backup times is turn off compression, this makes allot of sense if the data is mostly BLOBs or other binary data stored in the database.

On a server i manage that is a 65 gig database it was taking around 1 hour to do the dump, removed the compression dump took 30 minutes.

Answer 3

It depends a lot on details that you have not specified, particularly how the IO subsystem is arranged for this database instance. Single drive? Multiple? If multiple how is the data/uses spread over those drives? Drive types (spinning, SATA SSD, NVMe SSD, ..._)? Network rather than local and if net, what networking standards?

With 50Gb taking "several hours", either the database is under high load without the dump running or you have traditional (spinning surface & moving heads) style drive(s), or both.

If running the dump on an active machine it is going to compete with all other activity for IO bandwidth. You can minimise this by writing the data to another drive, assuming you are currently reading a database from one drive (or RAID array) and writing it to the same drive/array - by writing to another drive only the read activity of the dump is competing with everything else. This will be especially helpful if the storage is based traditional drives rather than solid state units because you are reducing extra head movements too, in fact as well as reducing the impact on other processes the dump itself will be faster for that reason. If there are not other drives in the machine (i.e. they are all part of the same RAID array, or are network mounts, etc...) then dumping to a drive connected via USB might be a solution at least temporarily.

Though this is at best an educated guess based on minimal details. If you improve your question by describing your I/O subsystem, you are likely to get more specific and helpful answers.

Answer 4

Little hard to say, because we don't know your database setup.

Does your database use schemas with views in it? You can use the -n option to dump only the data schemas and rebuild your views after the restore.

Maybe you can do it on a quiet moment on your server, with -j you can parallelize the dump. It will take less time, but uses more memory.

Otherwise the slave option is a good practice.