I/O error on aatams_acoustic_reporting on 4-nec-hob

[xhoenner]

Copied the harvester log below, looks like a PostgreSQL connection issue

begin;2018-09-10 09:52:54+10:00
Start of try block
tempFile_cid /tmp/talend-temp-2566627941602982069/changelog.xml
***********************
conn host:db-rc.aodn.org.au, port:5432, schema:aatams_acoustic_reporting, name:harvest?loginTimeout=1000&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory, user:aatams_aco
ustic_reporting
url_cid:jdbc:postgresql://db-rc.aodn.org.au:5432/harvest?loginTimeout=1000&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
schema_cid:aatams_acoustic_reporting
userName_cid:aatams_acoustic_reporting
INFO 10/09/18 9:52 AM:liquibase: Successfully acquired change log lock
INFO 10/09/18 9:53 AM:liquibase: Reading from aatams_acoustic_reporting.databasechangelog
INFO 10/09/18 9:53 AM:liquibase: Reading from aatams_acoustic_reporting.databasechangelog
INFO 10/09/18 9:53 AM:liquibase: Successfully released change log lock
***** completed finally block ***********
In finally block
Exception in component tPostgresqlInput_1
org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:281)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
        at aatams_acoustic_reporting.populate_reporting_views_0_1.Populate_reporting_views.tPostgresqlInput_1Process(Populate_reporting_views.java:1238)
        at aatams_acoustic_reporting.populate_reporting_views_0_1.Populate_reporting_views.tPostgresqlConnection_1Process(Populate_reporting_views.java:660)
        at aatams_acoustic_reporting.populate_reporting_views_0_1.Populate_reporting_views.runJobInTOS(Populate_reporting_views.java:11731)
        at aatams_acoustic_reporting.populate_reporting_views_0_1.Populate_reporting_views.runJob(Populate_reporting_views.java:11606)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.tRunJob_2Process(AATAMS_ACOUSTIC_REPORTING.java:2003)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.tRunJob_1Process(AATAMS_ACOUSTIC_REPORTING.java:1883)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.iPostgresqlDbUpdate_1Process(AATAMS_ACOUSTIC_REPORTING.java:1711)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.runJobInTOS(AATAMS_ACOUSTIC_REPORTING.java:3980)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.main(AATAMS_ACOUSTIC_REPORTING.java:3772)
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:271)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1700)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        ... 12 more
Exception in component iPostgresqlOutput_11
org.postgresql.util.PSQLException: This connection has been closed.
        at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:822)
        at org.postgresql.jdbc3.AbstractJdbc3Connection.createStatement(AbstractJdbc3Connection.java:229)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.createStatement(AbstractJdbc2Connection.java:294)
        at aatams_acoustic_reporting.populate_reporting_views_0_1.Populate_reporting_views.tPostgresqlInput_11Process(Populate_reporting_views.java:11074)
        at aatams_acoustic_reporting.populate_reporting_views_0_1.Populate_reporting_views.runJobInTOS(Populate_reporting_views.java:11740)
        at aatams_acoustic_reporting.populate_reporting_views_0_1.Populate_reporting_views.runJob(Populate_reporting_views.java:11606)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.tRunJob_2Process(AATAMS_ACOUSTIC_REPORTING.java:2003)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.tRunJob_1Process(AATAMS_ACOUSTIC_REPORTING.java:1883)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.iPostgresqlDbUpdate_1Process(AATAMS_ACOUSTIC_REPORTING.java:1711)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.runJobInTOS(AATAMS_ACOUSTIC_REPORTING.java:3980)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.main(AATAMS_ACOUSTIC_REPORTING.java:3772)
Exception in component tRunJob_2
java.lang.RuntimeException: Child job running failed
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.tRunJob_2Process(AATAMS_ACOUSTIC_REPORTING.java:2014)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.tRunJob_1Process(AATAMS_ACOUSTIC_REPORTING.java:1883)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.iPostgresqlDbUpdate_1Process(AATAMS_ACOUSTIC_REPORTING.java:1711)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.runJobInTOS(AATAMS_ACOUSTIC_REPORTING.java:3980)
        at aatams_acoustic_reporting.aatams_acoustic_reporting_0_1.AATAMS_ACOUSTIC_REPORTING.main(AATAMS_ACOUSTIC_REPORTING.java:3772)
finish;2018-09-10 12:26:55+10:00 154 minutes

[jonescc]

postgres was killed:

2018-09-10 02:26:55 GMT    29727 00000LOG:  server process (PID 3053) was terminated by signal 9: Killed
2018-09-10 02:26:55 GMT    29727 00000DETAIL:  Failed process was running: WITH a AS (   SELECT DISTINCT transmitter_id FROM valid_detection   UNION ALL    SELECT DISTINCT transmitter_id FRO
M sensor),   sub AS (SELECT DISTINCT transmitter_id FROM a)   SELECT DISTINCT sub.transmitter_id,         d.id AS tag_id,         su.release_id,  p.name AS project_name,         CASE WHEN sp
.common_name IS NULL THEN sp.name ELSE sp.common_name END AS common_name,   CASE WHEN d.id IS NULL THEN FALSE ELSE TRUE END AS registered,  p.is_protected AS protected,    CASE WHEN capture_
location IS NULL AND release_location IS NULL THEN FALSE ELSE TRUE END AS releaselocation_info,       date(ar.embargo_date) AS embargo_date,  COUNT(vd.timestamp) AS no_detections,   date(min
(vd.timestamp)) AS first_detection,     date(max(vd.timestamp)) AS last_detection,      round((date_part('days', max(vd.timestamp) - min(vd.timestamp)) + date_part('hours', max(vd.timestamp)
 - min(vd.timestamp))/24)::numeric, 1) AS coverage_duration,      tt.transmitter_type_name AS transmitter_type   FROM sub   LEFT JOIN valid_detection vd ON sub.transmitter_id = vd.transmitt
2018-09-10 02:26:55 GMT    29727 00000LOG:  terminating any other active server processes

[jonescc]

Can't see an OOM message so the process was possibly manually killed using -9 which brings down the server. Should use pg_terminate_backend instead (sometimes doesn't work and need to use kill - but never -9)

[ghost]

Closing, as very old.