Do backups using pg_dump cause server outage if the database is too busy?

Question

I have a Postgres database in production environment, and it has millions of records in tables. So I wanted to take a backup using pg_dump for some investigation.

But this database is so busy. So I am afraid if backup operation is caused any server issue like slow down server or crash database etc. as it is busy database.

Can anyone share if there is any risk? And please give some idea about best practice to take a backup from Postgres with no risk.

Answer 1

Running pg_dump will not cause a server crash, but it will add some extra CPU and particularly I/O load. You can test if that is a problem, pg_dump can be canceled any time.

On a busy database, it can also lead to table bloat, because old row versions have to be retained for the duration of pg_dump and cannot be vacuumed.

There are some alternatives:

• Run pg_dump against a standby server.

• Use pg_basebackup to perform a physical backup. That can be throttled to reduce the I/O load.