RE: pgAgent Jobs on replica
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
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
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