How to force the Notification Event - On Failure

Hi,

I am using Rundeck to schedule and run batch jobs in a Postgres database. I am executing the Postgres command line program psql.exe on a Unix node.
If the executable file example: script_name.sql has some type of Postgres error, I would like to trigger the Rundeck Notification Event - On Failure, so that I will receive an email.
Is this possible? If yes, how would I do that?

Thank you…
Tony

Project background: We have converted an Oracle database to Postgres and from Oracle’s DBMS_Scheduler to Rundeck. There are 29 jobs with 800 executions a day in each of three instances: development, pre-production, and production.

Hi Tony,

First, you need to configure the Rundeck email SMTP settings (take a look at this).
Then, in your job, you can set in the notification tab: “On Failure” > “Notification Type” > “Send Email” with the correct data. After some job failure, you will receive an email.

PD: An excellent tool to test the email config is mailtrap.

Greetings!

Hi Reiner,
The email process is already set up and running correctly. Emails are sent out for all of the different events.
The problem I have is that an internal error in the Postgres database does not trigger the on-failure event.

I want to cause an error in this job by calling a program that does not exist. The job below was flagged as “Succeeded”.
I want the “On Failure” notification to be triggered because the Postgres job failed.
Question: Is it possible to do this?

Here is the workflow:

export PGHOST=@globals.url_host@
export PGPASSWORD=@option.user_password@
#–
psql -p 5432 -U @globals.any_user_id@ -d @globals.database_name@ -w <<’!’

\conninfo

DO $$
DECLARE

BEGIN
RAISE NOTICE ‘------------OUTPUT---------------------------------------------------------’;
RAISE NOTICE ‘Start of program: utility.error_testing %’, current_timestamp;

CALL utility.error_testing2();

RAISE NOTICE ‘------------Job Complete---------------------------------------------------’;

EXCEPTION
WHEN OTHERS then
RAISE EXCEPTION ‘Error in Postgres % %’, SQLERRM, SQLSTATE;

END $$;

\q
!

This is the results of the Postgres job.

NOTICE: ------------OUTPUT---------------------------------------------------------
NOTICE: Start of program: utility.error_testing 2022-12-22 08:53:32.361827-05
ERROR: Error in Postgres procedure utility.error_testing2() does not exist 42883
CONTEXT: PL/pgSQL function inline_code_block line 14 at RAISE

Hi Tony,

I see. Rundeck only fails if the return code is not zero; so, “wrapping” the SQL script in a bash script to properly “catch” the exit code would be a smart strategy (and you can play “forcing” exit codes if you like).

If the script receives a non-zero code, the “on failure” notification must be triggered.

This example could work (the first example shows how to pass SQL exit codes to the bash script).

Greetings!

Thank you so much for your help!

1 Like