pgagent 4 does not start when upgraded from pgagent 3

[gozdal]

If you upgrade pgagent from 3 to 4 it fails to start with:

Wed Sep 5 15:44:38 2018 ERROR: Unsupported schema version: 3. Version 4 is required - please run pgagent_upgrade.sql.

The message is not really helpful as the package does not contain pgagent_upgrade.sql at all. I believe it's a leftover from previous release of pgagent which had pgagent_upgrade.sql. There is /usr/share/postgresql/9.6/extension/pgagent--3.4--4.0.sql which has a helpful hint:

\echo Use "CREATE EXTENSION pgagent UPDATE" to load this file. \quit

Unfortunately:

postgres=# CREATE EXTENSION pgagent UPDATE;
ERROR:  syntax error at or near "UPDATE"
LINE 1: CREATE EXTENSION pgagent UPDATE

@dpage are you the correct person to tackle this?

[barkingfoodog]

Try using ALTER EXTENSION:

ALTER EXTENSION pgagent UPDATE;

The SQL script's instructions do need to be corrected, however.

[gozdal]

@barkingfoodog thanks!

[xabolcs]

If you get the error below ... :

postgres=# ALTER EXTENSION pgagent UPDATE;
ERROR:  extension "pgagent" does not exist

... and ... :

postgres=# CREATE EXTENSION pgagent;
ERROR:  relation "pga_jobagent" already exists

Then you need to use the FROM keyword, like below:

postgres=# CREATE EXTENSION pgagent FROM "3.4";
CREATE EXTENSION

👍

[repo-lockdown[bot]]

Thanks for your Pull Request! :smile: This repo on GitHub is just a mirror of our real git repositories though, and can't really handle PRs. :frowning: Hopefully you can redo the PR, and direct it to the git.postgresql.org repos? We have a developer guide, if that helps: https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F. If this was a PR for pgAdmin, please visit https://www.pgadmin.org/docs/pgadmin4/dev/submitting_patches.html.