5

I have a PostgreSQL text dump file approximatley 4.5GB in size (uncompressed) that I am trying to restore, but always fails due to running out of memory.

Interestingly enough, no matter what I try it always fails at the exact same line number of the dump file, which leads me to believe the changes I have attempted have had no effect. (I did look at this line number in the file and it is just another row of data, nothing significant is occurring at that point in the file.)

I am using psql with the -f option, as I read that can be better than the standard input. Both methods fail, however.

I have tried the following:

  • increase work_mem from 4MB to 128MB
  • increase shared buffers from 128MB to 2GB
  • increase VM memory from 8GB to 16GB

Using both Top and PG_Top I can see (what I believe shows) both the OS and database still have memory available when psql fails. I'm not doubting that something somewhere is running out of memory, I just wish I had a better way of telling what exactly that was.

Other information that may be helpful:

  • PostgreSQL 10.5
  • Ubuntu 16.04 LTS running on VMWare Workstation
Bob Pusateri
  • 722
  • 5
  • 15
  • What is in the dump file? What format is it? how was it created? How do youknow that the command ran out of memory ? (could have been a SSH timeout/quotum) `... with the -f option, as I read that can be better than the standard input.` Nonsense – wildplasser Aug 22 '18 at 20:54
  • 1
    The dump file is a text file created by pg_dump. The first lines of the file say exactly that: "dumped by pg_dump version 10.4..." All I have to go on is that when I run psql and pass in the file, the terminal will eventually stop and say "out of memory." – Bob Pusateri Aug 22 '18 at 21:09
  • pg_dump can generate 4 different formats (one of them is plaintext) . And there is also --column-inserts and --inserts – wildplasser Aug 22 '18 at 21:31
  • You should investigate which statement fails (probably a `CREATE INDEX`). Are you using PostGIS? That is known to be a memory hog. – Laurenz Albe Aug 23 '18 at 06:39

0 Answers0