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?

  • 5
    You could setup a slave and take the dump from the slave. – a_horse_with_no_name Mar 1 '17 at 7:56

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 you views after the restore.

maybe you can do it on a quit 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.