I'm trying to run pg_dump on a relatively small database (2G), but pg_dump keeps crashing with an out of memory error.

du -h --max-depth=0 9.4
1.9G    9.4

The command:

pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress;

Any ideas on how to allow the dump to happen?

****  Edit *****
-bash-4.2$ pg_dump -t new_answers -F tar -b -f new_answers.dump mydatabase
out of memory

the new_answers table has a TEXT column, but all the objects in are < 1k. Also,there is only about 300 rows. "dmesg | grep OOM" turned up nothing.

  • Any reason you aren't just using the built-in compressed format offered by pg_dump. I would think that would save memory since sql files are huge. So something like: pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" -f mydb.backup "$DATABASE" – LR1234567 Jan 31 '15 at 4:04
  • @LR1234567 A pipeline doesn't buffer the whole lot in memory, and pg_dump outputs the SQL in a stream without buffering tables, etc. It should not consume much RAM, and the pipeline to gzip shouldn't make a meaningful difference. – Craig Ringer Feb 1 '15 at 10:54
  • Are you certain it's pg_dump, not the PostgreSQL backend it's connected to, that's OOMing? When you say it "Crashes with an out-of-memory error" what is the exact text of the error? Copy and paste it into an edit to your question please. Also check the PostgreSQL server logs. Finally, is VM overcommit enabled on your VPS? Check dmesg, see if there are any messages from the "OOM killer" there (just "dmesg | grep OOM") – Craig Ringer Feb 1 '15 at 10:55
  • See edits above - even a small table with a binary column causes the crash – John P Feb 2 '15 at 8:25
  • What version of PostgreSQL are you using? I recall there have been some fixes for pg_dump memory/time consumption recently. – Josh Kupershmidt Feb 2 '15 at 16:58

Your Answer

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

Browse other questions tagged or ask your own question.