Hi!
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 seemed like a reasonable error to us. We had no errors on insertion to indicate that index creation was a
problem.
We believed this might be a race condition, so we attempted to do a schema-only restore followed by a data-only restore
justfor this database. This worked a few times, and then began growing exponentially in completion time before it
becameunsustainable. We figured we were using too many jobs, so we decreased them. Nothing helped.
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
[2016-09-14 02:20:36 UTC] LOG: terminating any other active server processes
[2016-09-14 02:20:36 UTC] postgres [local] DBNAME WARNING: terminating connection because of crash of another server
process
[2016-09-14 02:20:36 UTC] postgres [local] DBNAME DETAIL: The postmaster has commanded this server process to roll
backthe current transaction and exit, because another server process exited abnormally and possibly corrupted shared
memory.
The restore crashed this way for all job numbers except for one. We’re now stuck back where we were prior to increasing
jobnumbers, at one job for this restore in order to prevent errors and crashes.
Background:
• 3 ec2 instances with postgres
• 1 used for reporting, on Postgresql 9.5.4
• Reporting server is a c4.2xlarge, and should have been able to handle multiple jobs (8cpu /
https://aws.amazon.com/ec2/instance-types/)
• 2 production servers; one leader and one follower, both on Postgresql 9.5.3.
We have one very large database, 678GB, and several others, but the largest is our concern.
I have attached our postgresql.conf file. Thank you so much for your time.
Best,
Cea Stapleton
Operations Engineer
http://www.healthfinch.com
