Postgresql Run ‘Jobs’ At Specific Times

If you have installed postgresql with brew there's one createdb that comes with it and that will connect to brew's postgresql.

Then if you have also installed Postgres.app there's another createdb at a different location that will connect to Postgres.app's postgresql.

Since OS X also ships with its own createdb in /usr/bin, that's a third createdb that expects to connect to Apple's postgresql, which is not running unless it's OS X Server Edition. According to the error message, this is the one that you're trying to use and which fails.

The solution is to use the createdb command that comes with the PostgreSQL you're actually running. Locate its directory and type the full path of createdb, as in /Applications/Postgres.app/Contents/MacOS/bin/createdb, and/or change your $PATH as suggested in postgresapp's doc.

This is a common problem for PostgreSQL on Mac OS X due the existence of many concurrent packages and of client-side programs pre-installed by Apple.

Here is a query I use to get job information. I have also used SQLJobVis in the past but their website appears to be down at the moment

with jobs as(
select a.name,a.[description], a.enabled,   case c.freq_type  -- Daily, weekly, Monthly
            when 1    then 'Once'
                    when 4    then 'Daily'
                    when 8    then 'Wk ' -- For weekly, add in the days of the week
                    +    case      freq_interval & 2 when 2 then 'M' else '' end  -- Monday
                    +    case      freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
                    +    case      freq_interval & 8 when 8 then 'W' else '' end  -- etc
                    +    case      freq_interval & 16 when 16 then 'Th' else '' end
                    +    case      freq_interval & 32 when 32 then 'F' else '' end
                    +    case      freq_interval & 64 when 64 then 'Sa' else '' end
                    +    case      freq_interval & 1 when 1 then 'Su' else '' end
                    when 16   then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day
                    when 32   then 'Mthly '  -- The most complicated one, "every third Friday of the month" for example
                    + case c.freq_relative_interval 
                        when 1 then 'Every First '
                        when 2 then 'Every Second '
                        when 4 then 'Every Third '
                        when 8 then 'Every Fourth '
                        when 16 then 'Every Last '
                    end
                    + case c.freq_interval  
                        when 1 then 'Sunday' 
                        when 2 then 'Monday'
                        when 3 then 'Tuesday' 
                        when 4 then 'Wednesday' 
                        when 5 then 'Thursday' 
                        when 6 then 'Friday' 
                        when 7 then 'Saturday' 
                        when 8 then 'Day' 
                        when 9 then 'Week day'
                        when 10 then 'Weekend day'
                    end 
                when 64   then 'Startup'    -- When SQL Server starts
                when 128 then 'Idle'        -- Whenever SQL Server gets bored
                else 'Err'          -- This should never happen
                end as schedule,
                case c.freq_subday_type     -- FOr when a job funs every few seconds, minutes or hours
                    when 1    then 'Runs once at:'
                    when 2    then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds'
                    when 4    then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes'
                    when 8    then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
                end as frequency
                ,       substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.active_start_time), 6), 1, 2)
                +    ':'
                + substring (
                right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_start_time), 6) ,3 ,2)
                +    ':'
                + substring (
                right (stuff (' ', 1, 1, '000000') + convert(varchar(6),c.active_start_time), 6) ,5 ,2) as start_at

            ,case   c.freq_subday_type
                when 1  then NULL  -- Ignore the end time if not a recurring job
                else    substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6), 1, 2)
                +    ':'
                + substring (
                right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6) ,3 ,2)
                +    ':'
                + substring (
                right (stuff (' ', 1, 1, '000000') + convert(varchar(6), c.active_end_time), 6) ,5 ,2) end as end_at
             from msdb.dbo.sysjobs a
            inner join msdb.dbo.sysjobschedules b
            on a.job_id = b.job_id
            inner join msdb.dbo.sysschedules c
            on b.schedule_id = c.schedule_id)

select * from jobs
where start_at between '23:30' and '23:59'
or start_at between '00:00' and '04:30'