From: Tom Lane <tgl@sss.pgh.pa.us>
To: Cea Stapleton <cea@healthfinch.com>
Cc: pgsql-performance@postgresql.org
Cc: eli@healthfinch.com
Subject: Re: Failing Multi-Job Restores, Missing Indexes on Restore
Date: Thu, 29 Sep 2016 08:52:31 -0400
Message-ID: <4402.1475153551@sss.pgh.pa.us> (raw)
In-Reply-To: <70774A06-3FD9-42D2-BCCB-0CCC8E8D777F@healthfinch.com>
References: <70774A06-3FD9-42D2-BCCB-0CCC8E8D777F@healthfinch.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 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 (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, cea@healthfinch.com, eli@healthfinch.com
Subject: Re: Failing Multi-Job Restores, Missing Indexes on Restore
In-Reply-To: <4402.1475153551@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox