If the pgagent is installed, it is not possible to fully drop the pgagent schema once job tables are populated.
After dropping the schema or drop cascading the pgagent extension, if performing a pg_dumpall the original pgagent.* table data is left intact. The schema and tables are not visible in any of the catalogues but are present in the pg_dumpall output.
This prevents restoring data in the pgagent schema from a backup.
Tested with pgagent/xenial,now 3.4.1-2 amd64 + postgresql/xenial,xenial,now 9.5+173
and pgagent/xenial-pgdg,now 3.4.1-3.pgdg16.04+1 amd64 + postgresql-9.6/xenial-pgdg,now 9.6.3-1.pgdg16.04+1 amd64
Jobs created with pgAdmin3 1.22 and pgAdmin 3 LTS 1.23.0b
Reproduced with;
CREATE EXTENSION pgagent;
-- add a job with an arbitrary step and schedule
DROP SCHEMA pgagent CASCADE;
-- pg_dumpall and examine the output
Dumpall Example output;
--
-- Data for Name: pga_job; Type: TABLE DATA; Schema: pgagent; Owner: root
--
COPY pga_job (jobid, jobjclid, jobname, jobdesc, jobhostagent, jobenabled, jobcreated, jobchanged, jobagentid, jobnextrun, joblastrun) FROM stdin;
1 1 test job 1 t 2017-08-04 10:21:59.436306+10 2017-08-04 10:21:59.436306+10 \N 2017-08-07 00:00:00+10 \N
\.
Restore attempt output;
CREATE EXTENSION pgagent;
SET search_path = pgagent, pg_catalog;
INSERT INTO pga_job (jobid, jobjclid, jobname, jobdesc, jobhostagent, jobenabled, jobcreated, jobchanged, jobagentid, jobnextrun, joblastrun) VALUES (1, 1, 'test job 1', '', '', true, '2017-08-04 10:21:59.436306+10', '2017-08-04 10:21:59.436306+10', NULL, '2017-08-07 00:00:00+10', NULL);
ERROR: duplicate key value violates unique constraint "pga_job_pkey"
DETAIL: Key (jobid)=(1) already exists.
Workaround;
-- This seems to allow restore (tested in 9.6.3, once pgagent schema was manually created drop then inserts aren’t failing anymore)
CREATE SCHEMA pgagent;
CREATE EXTENSION pgagent;
SET search_path = pgagent, pg_catalog;
INSERT INTO pga_job (jobid, jobjclid, jobname, jobdesc, jobhostagent, jobenabled, jobcreated, jobchanged, jobagentid, jobnextrun, joblastrun) VALUES (1, 1, 'test job 1', '', '', true, '2017-08-04 10:21:59.436306+10', '2017-08-04 10:21:59.436306+10', NULL, '2017-08-07 00:00:00+10', NULL);
