RE: pgAgent Jobs on replica

[Greg Bryant]

Thanks for the reply.

I saw that as an option but I don’t really want to have to go modify every 
pgAgent job after a switch-over to the replica (not that I have that many jobs 
or that PostgreSQL hardly ever fails).  I’m more concerned that another 
administrator doesn’t know that all the jobs would need modified if a 
switch-over did occur.

Could pgAgent itself be modified to have an ‘enable on replica’ toggle similar 
to the ‘enable’ toggle in the job setup?  I’m not sure you’d ever want to run a 
pgAgent job on a replica but someone might want to (but the tables themselves 
are being updated by the master via replication so how would that work?).

I guess I can modify the jobs to check if the server is in recovery; pgAgent 
jobs do fail on the replicas since the database is in read-only; it’s just some 
jobs run bash scripts (I’ve set them up to check if the database is in recovery 
already).

Thanks,

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

From: Dave Page<mailto:dp...@pgadmin.org>
Sent: Monday, April 26, 2021 4:08 AM
To: Greg Bryant<mailto:gregabry...@hotmail.com>
Cc: 
pgadmin-support@lists.postgresql.org<mailto:pgadmin-support@lists.postgresql.org>
Subject: Re: pgAgent Jobs on replica

Hi

On Sat, Apr 24, 2021 at 6:47 PM Greg Bryant 
mailto:gregabry...@hotmail.com>> wrote:
I have several pgAgent jobs that run on the ‘master’ database.  They fail on 
the replica since it’s a ‘read-only’ connection.  I’m creating a couple jobs to 
run some bash scripts but I don’t want them to run on the replica server.

Is there a way to prevent pgAgent jobs from executing on the replica server?  
Wrap all the jobs in an ‘if’ statement to check if the server is in_recovery?  
Disable the daemon on the replica server?

I’m currently running PostgreSQL 11 with pgAgent 4.

You can use the Host Agent field to specify a particular node that a job should 
run on - see https://www.pgadmin.org/docs/pgadmin4/5.2/pgagent_jobs.html

--
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: https://www.enterprisedb.com

Re: pgAgent Jobs on replica

[Dave Page]

Hi

On Mon, Apr 26, 2021 at 3:44 PM Greg Bryant  wrote:

> Thanks for the reply.
>
>
>
> I saw that as an option but I don’t really want to have to go modify every
> pgAgent job after a switch-over to the replica (not that I have that many
> jobs or that PostgreSQL hardly ever fails).  I’m more concerned that
> another administrator doesn’t know that all the jobs would need modified if
> a switch-over did occur.
>
>
>
> Could pgAgent itself be modified to have an ‘enable on replica’ toggle
> similar to the ‘enable’ toggle in the job setup?  I’m not sure you’d ever
> want to run a pgAgent job on a replica but someone might want to (but the
> tables themselves are being updated by the master via replication so how
> would that work?).
>

Not easily - there might be multiple instances on the box, or there might
be none. pgAgent doesn't actually care if there's a Postgres instance on
the box it's running on right now, which is by design.


>
>
> I guess I can modify the jobs to check if the server is in recovery;
> pgAgent jobs do fail on the replicas since the database is in read-only;
> it’s just some jobs run bash scripts (I’ve set them up to check if the
> database is in recovery already).
>

Yes, that's probably the only way for non-SQL jobs.


>
>
> Thanks,
>
>
>
> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
> *From: *Dave Page 
> *Sent: *Monday, April 26, 2021 4:08 AM
> *To: *Greg Bryant 
> *Cc: *pgadmin-support@lists.postgresql.org
> *Subject: *Re: pgAgent Jobs on replica
>
>
> Hi
>
> On Sat, Apr 24, 2021 at 6:47 PM Greg Bryant 
> wrote:
>
>> I have several pgAgent jobs that run on the ‘master’ database.  They fail
>> on the replica since it’s a ‘read-only’ connection.  I’m creating a couple
>> jobs to run some bash scripts but I don’t want them to run on the replica
>> server.
>>
>>
>>
>> Is there a way to prevent pgAgent jobs from executing on the replica
>> server?  Wrap all the jobs in an ‘if’ statement to check if the server is
>> in_recovery?  Disable the daemon on the replica server?
>>
>>
>>
>> I’m currently running PostgreSQL 11 with pgAgent 4.
>>
>
> You can use the Host Agent field to specify a particular node that a job
> should run on - see
> https://www.pgadmin.org/docs/pgadmin4/5.2/pgagent_jobs.html
>
> --
> Dave Page
> Blog: https://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EDB: https://www.enterprisedb.com
>
>

-- 
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: https://www.enterprisedb.com

Re: pgAgent Jobs on replica

[Dave Page]

Hi

On Sat, Apr 24, 2021 at 6:47 PM Greg Bryant  wrote:

> I have several pgAgent jobs that run on the ‘master’ database.  They fail
> on the replica since it’s a ‘read-only’ connection.  I’m creating a couple
> jobs to run some bash scripts but I don’t want them to run on the replica
> server.
>
>
>
> Is there a way to prevent pgAgent jobs from executing on the replica
> server?  Wrap all the jobs in an ‘if’ statement to check if the server is
> in_recovery?  Disable the daemon on the replica server?
>
>
>
> I’m currently running PostgreSQL 11 with pgAgent 4.
>

You can use the Host Agent field to specify a particular node that a job
should run on - see
https://www.pgadmin.org/docs/pgadmin4/5.2/pgagent_jobs.html

-- 
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: https://www.enterprisedb.com

pgAgent Jobs on replica

[Greg Bryant]

I have several pgAgent jobs that run on the ‘master’ database.  They fail on 
the replica since it’s a ‘read-only’ connection.  I’m creating a couple jobs to 
run some bash scripts but I don’t want them to run on the replica server.

Is there a way to prevent pgAgent jobs from executing on the replica server?  
Wrap all the jobs in an ‘if’ statement to check if the server is in_recovery?  
Disable the daemon on the replica server?

I’m currently running PostgreSQL 11 with pgAgent 4.

Thanks,
Greg

Sent from Mail for Windows 10