Обсуждение: pgagent in Debian sid
Hi, I've followed the instructions to get pgagent up and running in Debian sid. However, I do not see the "Jobs" node in pgadmin. Here's what I did: su postgres [PASSWORD] psql -d postgres < /usr/share/pgadmin3/pgagent.sql which ran successfully, AFAICT. Is there anything else that should be done to enable pgagent? Thanks. Cheers, -- Seb
On Sat, May 30, 2009 at 10:49 PM, Seb <spluque@gmail.com> wrote: > Hi, > > I've followed the instructions to get pgagent up and running in Debian > sid. However, I do not see the "Jobs" node in pgadmin. Here's what I > did: > > su postgres > [PASSWORD] > psql -d postgres < /usr/share/pgadmin3/pgagent.sql > > which ran successfully, AFAICT. Is there anything else that should be > done to enable pgagent? Thanks. Is pgAdmin connecting to the postgres database by default (ie. it's listed as the maintenance DB on the server properties dialogue)? Did you close and restart pgAdmin after running the SQL script? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Sat, 30 May 2009 23:23:57 +0100, Dave Page <dpage@pgadmin.org> wrote: > On Sat, May 30, 2009 at 10:49 PM, Seb <spluque@gmail.com> wrote: >> Hi, >> I've followed the instructions to get pgagent up and running in >> Debian sid. However, I do not see the "Jobs" node in >> pgadmin. Here's what I did: >> su postgres [PASSWORD] psql -d postgres < >> /usr/share/pgadmin3/pgagent.sql >> which ran successfully, AFAICT. Is there anything else that should >> be done to enable pgagent? Thanks. > Is pgAdmin connecting to the postgres database by default (ie. it's > listed as the maintenance DB on the server properties dialogue)? Did > you close and restart pgAdmin after running the SQL script? Thanks Dave, yes, postgres is listed as the maintenance DB on the server dialogue, and have restarted pgadmin many times since I've done the steps above. Any other ideas appreciated. -- Seb
On Sat, May 30, 2009 at 11:31 PM, Seb <spluque@gmail.com> wrote: >> Is pgAdmin connecting to the postgres database by default (ie. it's >> listed as the maintenance DB on the server properties dialogue)? Did >> you close and restart pgAdmin after running the SQL script? > > Thanks Dave, yes, postgres is listed as the maintenance DB on the server > dialogue, and have restarted pgadmin many times since I've done the > steps above. Any other ideas appreciated. Sure it's the same database cluster? pgAdmin will show the jobs node if it finds the schema in the maintenance database. If the schema is there and was created correctly there's not really much else to go wrong (at least to make the node show up). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Mon, 1 Jun 2009 08:52:25 +0100, Dave Page <dpage@pgadmin.org> wrote: > On Sat, May 30, 2009 at 11:31 PM, Seb <spluque@gmail.com> wrote: >>> Is pgAdmin connecting to the postgres database by default (ie. it's >>> listed as the maintenance DB on the server properties dialogue)? Did >>> you close and restart pgAdmin after running the SQL script? >> Thanks Dave, yes, postgres is listed as the maintenance DB on the >> server dialogue, and have restarted pgadmin many times since I've >> done the steps above. Any other ideas appreciated. > Sure it's the same database cluster? pgAdmin will show the jobs node > if it finds the schema in the maintenance database. If the schema is > there and was created correctly there's not really much else to go > wrong (at least to make the node show up). Thanks again. I have a single cluster, so no chance it's a different cluster. The only thing I can think of is that long ago the system was Debian "testing", and when it was upgraded to sid it switched to using port 5433. Could that be a problem? -- Seb
On Mon, Jun 1, 2009 at 4:08 PM, Seb <spluque@gmail.com> wrote: > Thanks again. I have a single cluster, so no chance it's a different > cluster. The only thing I can think of is that long ago the system was > Debian "testing", and when it was upgraded to sid it switched to using > port 5433. Could that be a problem? Not if pgAdmin is connecting to 5433, and that's where psql connected. Both will default to 5432 though - but if there's no server running there, you should have seen some fairly obvious errors. What does netstat -an show? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Jun 3, 2009 at 3:36 PM, Seb <spluque@gmail.com> wrote:
> I've resent my follow-up to the ng several times during the last 2 days,
> but some filter must be rejecting it, so am replying off-list.
> Hopefully this will make it here (thanks for having a look!):
Strange. The output from netstat looks reasonable though.
If you browse into the maintenance database using pgAdmin (I assume
you're using 'postgres'?), do you see the pgagent schema under the
catalogs node?
The code that displays the jobs node is this:
// Jobs // We only add the Jobs node if the appropriate objects
are the initial DB. if (settings->GetDisplayOption(_("pgAgent Jobs"))) { wxString
exists= conn->ExecuteScalar( wxT("SELECT cl.oid FROM pg_class cl JOIN
pg_namespace ns ON ns.oid=relnamespace\n") wxT(" WHERE relname='pga_job' AND nspname='pgagent'"));
if (!exists.IsNull()) { exists = conn->ExecuteScalar(wxT("SELECT
has_schema_privilege('pgagent', 'USAGE')"));
if (exists == wxT("t")) browser->AppendCollection(this, jobFactory);
} }
It would also be interesting to see the results of those two queries
run by hand:
SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
ns.oid=relnamespace WHERE relname='pga_job' AND nspname='pgagent';
SELECT has_schema_privilege('pgagent', 'USAGE');
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Jun 3, 2009 at 5:05 PM, Seb <spluque@gmail.com> wrote:
> It would also be interesting to see the results of those two queries
>> run by hand:
>
>> SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
>> ns.oid=relnamespace WHERE relname='pga_job' AND nspname='pgagent';
>
>> SELECT has_schema_privilege('pgagent', 'USAGE');
>
>
> Ok, if I do those as my normal user the first one goes fine:
>
>
> oid
> -----
> (0 rows)
No - it should return a row. That means that the schema doesn't exist
in the database you're connected to. The first query should work for
any user, regardless of what permissions you have on the schema
(because the query is looking at the catalogs, not the schema itself).
> Logged in as the postgres user:
>
> postgres=# SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
> postgres-# ns.oid=relnamespace WHERE relname='pga_job' AND nspname='pgagent';
> oid
> -------
> 46884
> (1 row)
Unfortunately you didn't show the psql prompt when you ran the query
using your normal user account, but I'll bet you're not connecting to
the postgres database like the postgres user is. That would explain
why you can't see the schema, but postgres can.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
On Wed, 3 Jun 2009 17:24:00 +0100,
Dave Page <dpage@pgadmin.org> wrote:
> On Wed, Jun 3, 2009 at 5:05 PM, Seb <spluque@gmail.com> wrote:
>> It would also be interesting to see the results of those two queries
>>> run by hand:
>>> SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
>>> ns.oid=relnamespace WHERE relname='pga_job' AND nspname='pgagent';
>>> SELECT has_schema_privilege('pgagent', 'USAGE');
>> Ok, if I do those as my normal user the first one goes fine:
>> oid ----- (0 rows)
> No - it should return a row. That means that the schema doesn't exist
> in the database you're connected to. The first query should work for
> any user, regardless of what permissions you have on the schema
> (because the query is looking at the catalogs, not the schema itself).
>> Logged in as the postgres user:
>> postgres=# SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
>> postgres-# ns.oid=relnamespace WHERE relname='pga_job' AND
>> nspname='pgagent'; oid ------- 46884 (1 row)
> Unfortunately you didn't show the psql prompt when you ran the query
> using your normal user account, but I'll bet you're not connecting to
> the postgres database like the postgres user is. That would explain
> why you can't see the schema, but postgres can.
Sorry, I didn't think that was relevant. For the postgres user, I did:
su - postgres
[PASSWORD]
psql -d postgres
For my normal user prompt I did:
psql test
and the prompt is:
test=>
Now if I switch (in psql, still as my normal user) to the postgres db:
test=> \c postgres
You are now connected to database "postgres".
postgres=> SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
postgres-> ns.oid=relnamespace WHERE relname='pga_job' AND nspname='pgagent'; oid
-------46884
(1 row)
postgres=> SELECT has_schema_privilege('pgagent', 'USAGE');has_schema_privilege
----------------------f
(1 row)
What's going on?
--
Seb
Seb; please keep the list CC'd - even if it takes a while to get
through it'll save me having to keep re-adding it.
More inline...
On Wed, Jun 3, 2009 at 5:42 PM, Seb <spluque@gmail.com> wrote:
>> Unfortunately you didn't show the psql prompt when you ran the query
>> using your normal user account, but I'll bet you're not connecting to
>> the postgres database like the postgres user is. That would explain
>> why you can't see the schema, but postgres can.
>
> Sorry, I didn't think that was relevant. For the postgres user, I did:
>
> su - postgres
> [PASSWORD]
> psql -d postgres
>
>
> For my normal user prompt I did:
>
>
> psql test
>
>
> and the prompt is:
>
>
> test=>
>
>
> Now if I switch (in psql, still as my normal user) to the postgres db:
>
>
> test=> \c postgres
> You are now connected to database "postgres".
> postgres=> SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
> postgres-> ns.oid=relnamespace WHERE relname='pga_job' AND nspname='pgagent';
> oid
> -------
> 46884
> (1 row)
> postgres=> SELECT has_schema_privilege('pgagent', 'USAGE');
> has_schema_privilege
> ----------------------
> f
> (1 row)
>
>
> What's going on?
When you connected using your account, you connected to the test
database which doesn't contain the pgagent schema. When you connected
to the postgres database, you do see it.
So, right-click the server in pgAdmin (the one that logs in using your
username), and double-check that the Maintenance database is
'postgres'. Assuming it is, then the results of the second query show
the problem - namely, you don't have permission to use the schema (and
probably the objects within it. You'll need to grant yourself usage
permissions on the schema, execute on the three functions, and select,
insert, update and delete on all the tables.
It may be easier to drop the schema altogether, grant your user
account access to the postgres database, and then re-create the
pgagent schema using your user account.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
On Wed, 3 Jun 2009 19:30:11 +0100,
Dave Page <dpage@pgadmin.org> wrote:
> Seb; please keep the list CC'd - even if it takes a while to get
> through it'll save me having to keep re-adding it.
Strange, I didn't remove the list (I simply kept you cc'd) -- on-list
only here!
> More inline...
> On Wed, Jun 3, 2009 at 5:42 PM, Seb <spluque@gmail.com> wrote:
>>> Unfortunately you didn't show the psql prompt when you ran the query
>>> using your normal user account, but I'll bet you're not connecting
>>> to the postgres database like the postgres user is. That would
>>> explain why you can't see the schema, but postgres can.
>> Sorry, I didn't think that was relevant. For the postgres user, I
>> did:
>> su - postgres [PASSWORD] psql -d postgres
>> For my normal user prompt I did:
>> psql test
>> and the prompt is:
>> test=>
>> Now if I switch (in psql, still as my normal user) to the postgres
>> db:
>> test=> \c postgres You are now connected to database "postgres".
>> postgres=> SELECT cl.oid FROM pg_class cl JOIN pg_namespace ns ON
postgres-> ns.oid=relnamespace WHERE relname='pga_job' AND
postgres-> nspname='pgagent';
>> oid ------- 46884 (1 row) postgres=> SELECT
>> has_schema_privilege('pgagent', 'USAGE'); has_schema_privilege
>> ---------------------- f (1 row)
>> What's going on?
> When you connected using your account, you connected to the test
> database which doesn't contain the pgagent schema. When you connected
> to the postgres database, you do see it.
> So, right-click the server in pgAdmin (the one that logs in using your
> username), and double-check that the Maintenance database is
> 'postgres'. Assuming it is, then the results of the second query show
> the problem - namely, you don't have permission to use the schema (and
> probably the objects within it. You'll need to grant yourself usage
> permissions on the schema, execute on the three functions, and select,
> insert, update and delete on all the tables.
Excellent, thanks for the helpful guidance!
> It may be easier to drop the schema altogether, grant your user
> account access to the postgres database, and then re-create the
> pgagent schema using your user account.
Yes, this sounds simpler at this point, although I'm not sure what
privileges to grant myself to the postgres database? I assume this
needs to be done while logged in as the postgres user (i.e. after 'su -
postgres; psql postgres'). Thanks.
--
Seb
On Wed, Jun 3, 2009 at 8:32 PM, Seb <spluque@gmail.com> wrote: >> It may be easier to drop the schema altogether, grant your user >> account access to the postgres database, and then re-create the >> pgagent schema using your user account. > > Yes, this sounds simpler at this point, although I'm not sure what > privileges to grant myself to the postgres database? I assume this > needs to be done while logged in as the postgres user (i.e. after 'su - > postgres; psql postgres'). Thanks. Yes, log in as postgres. GRANT CREATE ON DATABASE postgres TO myuser; should be enough. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, 3 Jun 2009 20:40:26 +0100, Dave Page <dpage@pgadmin.org> wrote: > On Wed, Jun 3, 2009 at 8:32 PM, Seb <spluque@gmail.com> wrote: >>> It may be easier to drop the schema altogether, grant your user >>> account access to the postgres database, and then re-create the >>> pgagent schema using your user account. >> Yes, this sounds simpler at this point, although I'm not sure what >> privileges to grant myself to the postgres database? I assume this >> needs to be done while logged in as the postgres user (i.e. after 'su >> - postgres; psql postgres'). Thanks. > Yes, log in as postgres. GRANT CREATE ON DATABASE postgres TO myuser; > should be enough. Great, I can see the Jobs node now using my normal user account. However, right-clicking on the jobs doesn't present an option to create new jobs; it only shows "_Object list report" and "_Run now" options under "Refresh" in the context menu. Any further advice appreciated. Thanks. -- Seb
On Wed, Jun 3, 2009 at 9:03 PM, Seb <spluque@gmail.com> wrote: > Great, I can see the Jobs node now using my normal user account. > However, right-clicking on the jobs doesn't present an option to create > new jobs; it only shows "_Object list report" and "_Run now" options > under "Refresh" in the context menu. Any further advice appreciated. Aww, nuts. Seems you've run into a bug. I've fixed it in SVN - can you try the latest code from there? If not, you can hang on for the next beta/RC release. BTW, the broken menu strings quoted above will be fixed if you update wxWidgets to 2.8.9 or 2.8.10 if memory serves. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Thu, 4 Jun 2009 10:35:03 +0100, Dave Page <dpage@pgadmin.org> wrote: > On Wed, Jun 3, 2009 at 9:03 PM, Seb <spluque@gmail.com> wrote: >> Great, I can see the Jobs node now using my normal user account. >> However, right-clicking on the jobs doesn't present an option to >> create new jobs; it only shows "_Object list report" and "_Run now" >> options under "Refresh" in the context menu. Any further advice >> appreciated. > Aww, nuts. Seems you've run into a bug. I've fixed it in SVN - can you > try the latest code from there? If not, you can hang on for the next > beta/RC release. > BTW, the broken menu strings quoted above will be fixed if you update > wxWidgets to 2.8.9 or 2.8.10 if memory serves. Thanks for all the pointers Dave. I will wait for these changes to make it to Debian sid -- I prefer to leave as much as possible up to the package manager so as to keep things in the system more tractable and easier to maintain on the long run. -- Seb