snippetsqlMinor
How to run pg_dump so that it doesn't slow down other connections?
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:
The problem I am having is that while
Is there any way to make the
E.g. would running pg_dump from a different server help the database I/O on its working server?
pg_dump -Fc dbname > fileThe 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)
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.