HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
pg_dumpslowdoesnthatdownhowotherconnectionsrun

Problem

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?

Solution

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)

Context

StackExchange Database Administrators Q#165807, answer score: 3

Revisions (0)

No revisions yet.