pg_restore terminates with "out of memory" error

Question

Trying to restore a 45mb pg_dump file but it keeps failing with an "out of memory" error.

Content of my postgresql-10-main.log:

2018-01-16 06:14:50.692 WAT [32405] postgres@app_development LOG:  

could not receive data from client: Connection reset by peer
2018-01-16 06:14:50.692 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.692 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development LOG:  incomplete message from client
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development ERROR:  unexpected EOF on client connection with an open transaction
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development CONTEXT:  COPY assessment_scores, line 15347
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development STATEMENT:  COPY assessment_scores (id, student_id, assessment_id, score, exempt, late, collected, missing, comment, deleted_at, created_at, updated_at) FROM stdin;

2018-01-16 06:14:50.693 WAT [32405] postgres@app_development FATAL:  terminating connection because protocol synchronization was lost
2018-01-16 06:14:50.693 WAT [32405] postgres@app_development LOG:  could not send data to client: Broken pipe

I checked with HTOP, my RAM is at about 4GB/8GB and SWAP space is unused when this happens.

DB server is localhost.

Answer 1

Figured out the problem.

I was trying to import a DB dump from Postgres 9.6 to Postgres 10.

Downgraded to Postgres 9.6 and it imported successfully.

Answer 2

As there is no mention of 'out of memory' in the server side logs, and apparently your client (pg_restore in this case) dies, I'd bet the OOM happens on your local machine. Solving this might depend on many factors - try to close processes that consume a lot of RAM, add more swap, or other.

Answer 3

I have encountered the same issue while trying to restore some dumps, in -Fc custom format. I'm not sure the problem will or can occur with any of the other dump formats.

However, I found that the "out of memory" error that pg_restore laconically drops on exit, is program-internal and not actually an OOM. It also occured without any DB interaction, when 'restoring' the dump into another dump using the pg_restore -f output.sql input.dump form.

Unlike in your instance, it didn't help to use an older pg_restore or an older database and I found that the (very big, 200-400+GB uncompressed) dumps were simply corrupt in some places, due to a badly aging disk I used for the storage.
Since I didn't use the -Fc internal compression, but bzip2 on the whole dump, I could recover pretty much everything but the few records inside broken bz2 segments (something not possible with xz or other container formats, thanks bzip).

Recovery requires some knowledge about pg_dump's custom dump format. In my instance the errors all occured in the data segment of the dump which was a COPY statement. Without internal compression this looks just like the plain-sql COPY format, including newline-delimiters after each row. Though the custom format additionally prepends each line by a 4-byte integer (little-endian here) containing the line-length in bytes and a 0x00, then ends it with another 0x00.
I expect the "out of memory" error occurs when the line-length indicated by the int doesn't fit the broken row's length, and pg_restore doesn't find the terminating NULL it expects but reads into the next row.

But due to the newline from COPY preceding the NULL on every line, I could actually open the dumps in a pager like less, and use head and tail to exclude the broken rows and stitch the rest back together for a restore.

Using the earlier mentioned pg_restore -f output.sql input.dump form, I dumped the backup until the error, then would tail this output to get the last good / first broken row, using that row's data as a regex to csplit the original dump, drop the last row from the first segment, the first row from the second segment, and stitch the rest together again.
That final command then looked something like:
cat <(head -n -1 dump.xx00) <(tail -n +2 dump.xx01) | pg_restore -C -d postgres.

I hope this might help someone else with broken dumps. I conclude that using -Fc custom format isn't in some cases the best thing to use (recovery using plain SQL format would have been much simpler), and that pg_restore could really use some better error reporting.

Answer 4

I tried using the below without tweaking any buffer size and it worked.

psql -U <db_user_name> -d <db_name> -f <file_name>.sql