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

Cea Stapleton <cea@healthfinch.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
toour reporting server. First, we were seeing missing indexes with pg_restore to our reporting server for one of our
databaseswhen 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
seeingerrors 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