0

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.

  • How many objects are in the database being dumped? What is shared_buffers set to? – jjanes Dec 7 '17 at 3:02
1

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.

  • If I understand you correctly, the reason the postgres process keeps growing in memory is that it's reading from the database at a much faster rate than it can write to the disk. Essentially the only way to successfully run pg_dump on a resource intensive server is to shutdown the running applications while pg_dump is doing the backup and bring them back online once the backup is complete. Is that correct? – quickblueblur Dec 6 '17 at 17:29
  • I would never consider doing anything but that. I don't dump live databases. That said, if it was an actual issue I would look at WAL log shipping as a better solution. (There is no data-consistency problem with dumping a live database, it'll work fine) – Evan Carroll Dec 6 '17 at 17:31
  • At this point WAL log shipping isn't a viable option and I don't really seeing it ever becoming a viable option. I will mark this as the accepted answer as it looks like I will have to go the non-live backup route. Thank-you for clearing this up as an IO issue. – quickblueblur Dec 6 '17 at 17:49
  • @quickblueblur I would investigate it, but it seems likely to me. Try Plugging in a USB 3.0 SSD and writing to that. I assume you're writing to the same drive or array you're reading from. – Evan Carroll Dec 6 '17 at 17:51
  • Yes, it's writing to the same drive which makes even more sense toward it being IO related. It's an industrial grade box so expansion isn't an option. No open ports at all. – quickblueblur Dec 6 '17 at 18:23

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.