Thanks Tom!
We’re using pg_restore (PostgreSQL) 9.5.4 for the restores. We’ve used
variations on the job number:
/usr/bin/pg_restore -j 6 -Fc -O -c -d DBNAME RESTORE_FILE”
We’ll take a look at the memory overcommit - would that also explain the index
issues we were seeing before we were seeing the crashes?
Cea Stapleton
Operations Engineer
http://www.healthfinch.com
> On Sep 29, 2016, at 7:52 AM, Tom Lane <[email protected]> wrote:
>
> Cea Stapleton <[email protected]> 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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
