Re: Failing Multi-Job Restores, Missing Indexes on Restore

Cea Stapleton <cea(at)healthfinch(dot)com> writes:
> We are having a baffling problem we hope you might be able to help with. We were hoping to speed up postgres restores to our reporting server. First, we were seeing missing indexes with pg_restore to our reporting server for one of our databases when we did pg_restore with multiple jobs (a clean restore, we also tried dropping the database prior to restore, just in case something was extant and amiss). The indexes missed were not consistent, and we were only ever seeing errors on import that indicated an index had not yet been built. For example:

> pg_restore: [archiver (db)] could not execute query: ERROR: index "index_versions_on_item_type_and_item_id" does not exist
> Command was: DROP INDEX public.index_versions_on_item_type_and_item_id;

Which PG version is that; particularly, which pg_restore version?
What's the exact pg_restore command you were issuing?

> We decided to move back to a multi-job regular restore, and then the restores began crashing thusly:
> [2016-09-14 02:20:36 UTC] LOG: server process (PID 27624) was terminated by signal 9: Killed

This is probably the dreaded Linux OOM killer. Fix by reconfiguring your
system to disallow memory overcommit, or at least make it not apply to
Postgres, cf
https://www.postgresql.org/docs/9.5/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

regards, tom lane