postgresqlpg-dumppgagent

pgAgent won't launch after a database restore

I've installed pgAgent in our PostgreSQL database for scheduling our jobs, which really works like a charm!

However, after I restored a backup from our database in a test server, pgAgent simply won't launch. Interestingly enough, it seems that pgAgent ignores the current state of the data in this log tables and tries to populate them from zero.

See error message at the log file:

postgres@postgres ERROR:  duplicate key value violates unique constraint "pga_jobsteplog_pkey"
postgres@postgres DETAIL:  Key (jslid)=(1) already exists.
postgres@postgres STATEMENT: INSERT INTO pgagent.pga_jobsteplog(jslid, jsljlgid, jsljstid, jslstatus) SELECT 1, 25, 3, 'r'  FROM pgagent.pga_jobstep WHERE jstid=3

In case you're wondering how the backup is performed:

pg_dumpall --file "/media/jones/Daten/fulldump.sql" --host "address-to-my-server.de" --port "5432" --username "myuser" --no-password --database "mydb" --clean --if-exists --verbose

Environment:

Ubuntu 16.04
PostgreSQL 9.5
pgAgent 3.4.1-2

Any ideas how to make pgAgent come back to life?

Solution

  • I'm definitely not happy with the solution, but so far I couldn't find anything better than the following options:

    1. Truncating the log table does the trick, but deletes all job history you had (no big deal in most use cases):
    TRUNCATE TABLE pgagent.pga_jobsteplog;
    1. Alternatively, updating the sequences manually also works, e.g.:
    SELECT SETVAL('pgagent.pga_exception_jexid_seq', max(jexid)) FROM pgagent.pga_exception;
SELECT SETVAL('pgagent.pga_job_jobid_seq', max(jobid)) FROM pgagent.pga_job;
SELECT SETVAL('pgagent.pga_jobclass_jclid_seq', max(jclid)) FROM pgagent.pga_jobclass;
SELECT SETVAL('pgagent.pga_joblog_jlgid_seq', max(jlgid)) FROM pgagent.pga_joblog;
SELECT SETVAL('pgagent.pga_jobstep_jstid_seq', max(jstid)) FROM pgagent.pga_jobstep;
SELECT SETVAL('pgagent.pga_jobsteplog_jslid_seq', max(jslid)) FROM pgagent.pga_jobsteplog;
SELECT SETVAL('pgagent.pga_schedule_jscid_seq', max(jscid)) FROM pgagent.pga_schedule;

    If anyone has a more elegant solution, please let me know in the comments.