PostgreSQL – How to Limit Resources Used by pg_dump

memorypg-dumppostgresqlpostgresql-9.4

Is it possible to limit the system resources used up by pg_dump?

I have a fairly resource intensive server that uses a PostgreSQL database. and it is running on FreeBSD 10.2. The server has 8 GB of memory.

I have a backup run with the following command.

pg_dump -h <address> -U <user> -Fc <database> -f <location>

As the backup is being completed the associated postgres process keeps rising in memory usage. It gets to the point of 2.2 GB for that process alone. At this point memory is all but used up and FreeBSD starts killing processes.

I've tried to change how the backup is done. For example, I tried using multiple jobs (and thus changed the format to directory).

pg_dump -j 4 -h <address> -U <user> -Fd <database> -f <location>

No differences. Eventually the memory usage will cap and FreeBSD will kill the database backup (among other processes).

I've also tried tuning some system variables. There was Managing Kernel Resources and also Resource Consumption. These have affected the running postgres processes that are used up by the server, but it had no impact on the process pg_dump uses. It still rose to 2.2 GB before FreeBSD started killing processes.

I've read through the pg_dump manual and haven't really seen anything that will help besides the multiple jobs.

At this point I'm not really sure what to do. Is there a way to cap the resources that pg_dump is allowed to use? I don't mind if the backup is slower. Just don't run the system out of resources where the OS has to start killing processes.

Best Answer

My assumption is that RAM isn't your problem. But that you're so IO-bogged down that you can't write to a file as a quick as you can read from the database.

There is no solution here, pg_dump is made it work as fast as it can. If it can read data faster than it can write it, your ram usage may go up. Until the table is flushed to disk. You may want to enable compression, see man psql and look for -Z,

   -Z 0..9
   --compress=0..9
       Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data
       segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file
       to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support
       compression at all.

As far as I know, processes don't know how much ram a system has or how much they should take. Normally, setting a limit just does what happened here and kills the process. If it didn't kill the process, they process may just keep drinking ram until it starts paging, and then beyond.

Related Question