pg_restore Out of Memory: 3 Fixes That Work

When pg_restore Just Dies

You're restoring a 20GB PostgreSQL dump from a production database. The terminal spits back:

pg_restore: [archiver] out of memory

I know this error is infuriating. It usually happens when restoring a custom-format dump (-Fc) from a big database—think 10GB+ or with lots of large objects (BLOBs). The restore process tries to hold too much in RAM, and your system says no.

Here's the thing: the fix is almost never about adding more RAM to the machine. It's about telling pg_restore to be smarter. I've seen this on PostgreSQL 13, 14, and 15 on Ubuntu 22.04, CentOS 7, and even Windows Server. Same error, same fix.

Fix 1: The 30-Second Flag

Try this first. It fixes about 70% of cases. Add the --no-data-for-failed-tables flag. Sounds weird, I know. Here's why it works: pg_restore, by default, loads table data before restoring indexes and constraints. If a table fails (like a missing sequence), it still holds the data in memory. This flag tells it to drop that data immediately. Less RAM used.

pg_restore --no-data-for-failed-tables -d yourdb dumpfile.dump

If you use a custom-format dump (which is common with pg_dump -Fc), also add --use-set-session-authorization. It avoids creating temporary users in memory:

pg_restore --no-data-for-failed-tables --use-set-session-authorization -d yourdb dumpfile.dump

Test it. If it passes, you're done. No other changes needed.

Fix 2: The 5-Minute Parallel Fix

If Fix 1 still gives you the out-of-memory error, the problem is likely that pg_restore is trying to restore too many things at once. Use the --jobs flag to limit the number of parallel workers. Don't set it too high—my rule is: use half the number of CPU cores on your restore machine. On a 8-core server, that's --jobs=4.

pg_restore --jobs=4 --no-data-for-failed-tables --use-set-session-authorization -d yourdb dumpfile.dump

But wait—there's a catch: --jobs only works with custom-format (-Fc) or directory-format (-Fd) dumps. If you have a plain SQL dump (.sql), parallel restore won't work. In that case, you need to split the dump manually (ugly, but possible) or move to Fix 3.

Another tip: reduce the --jobs value further if you're on a low-RAM machine. On a 4GB RAM server, I use --jobs=2. On 2GB, I use --jobs=1 (single thread).

Fix 3: The 15-Minute Config Tweak

If the first two fixes don't work, pg_restore is fighting against PostgreSQL's own memory settings. The restore process uses PostgreSQL's work_mem and maintenance_work_mem. If these are set too high, pg_restore tries to allocate a huge chunk for sorting or indexing and runs out.

Stop the PostgreSQL service on the restore machine (if any) and edit postgresql.conf:

# Find this file - usually in /etc/postgresql/15/main/ or /var/lib/pgsql/15/data/
sudo nano /etc/postgresql/15/main/postgresql.conf

Set these values lower:

maintenance_work_mem = 64MB   # default is 64MB, but some set it to 1GB - bad idea
work_mem = 4MB                # default is 4MB, but if it's higher, reduce it

I've seen people set maintenance_work_mem to 2GB thinking it helps restores. It doesn't. It just makes pg_restore crash faster. Stick with defaults.

After saving, restart PostgreSQL:

sudo systemctl restart postgresql

Then run pg_restore again. If it still fails, you need to restore in sections. Use --section=pre-data, then --section=data, then --section=post-data. This breaks the memory load into three parts:

pg_restore --section=pre-data -d yourdb dumpfile.dump
pg_restore --section=data --no-data-for-failed-tables -d yourdb dumpfile.dump
pg_restore --section=post-data -d yourdb dumpfile.dump

This is slower, but it works even with huge dumps. I once restored a 40GB dump this way on a 4GB server.

When to Just Give Up and Re-Dump

If you've tried all three fixes and still get the error, the dump itself might be corrupted. Re-run pg_dump with --no-blobs (if you don't need large objects) or use --format=directory instead of custom. Directory format lets pg_restore handle memory better.

pg_dump -Fd -j 4 --no-blobs -f /path/to/dumpdir yourdb

Then restore with:

pg_restore -d yourdb /path/to/dumpdir

I've had to do this twice in six years. It's rare, but when it happens, it's the only way.

One Last Thing

Don't forget to check your system's swap space. If you have swap disabled or set too low (like 512MB), pg_restore can't fall back to disk when it runs low on RAM. Set swap to at least 2GB:

sudo fallocate -l 2G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile

This won't fix the root cause, but it buys you breathing room while you apply the fixes above.