1

I got my database dump (tables, functions, triggers etc) in *.sql files. At this moment I am deploying them via jenkins, by passing execute shell command:

sudo -u postgres psql -d my_db < /[path_to_my_file].sql

The problem is, that if something is wrong in my sql file, build finishes as SUCCESS. I would like to got information immediately if something fails, without looking into log and checking if every command executed succesfully.

Is it possible (and how if the answer is 'yes') to deploy postgres database via jenkins other way?

Maciej Treder
  • 11,866
  • 5
  • 51
  • 74

2 Answers2

1

Make sure you have set set -e Before running the command. If that does not work, I'd look at the return code from the command above. That can be done by running echo $? right after the command.

If that gives you a zero when it fails it's postgres fault (sice it should return with something else than 0 on fail).

Perhaps there is a postgres flag to fail on wrong input.

EDIT: -v ON_ERROR_STOP=1 As a flag to postgres should make postgres fail on errors

Gabriel Tigerström
  • 174
  • 1
  • 9
  • Both solutions doesn't work: ERROR: insert or update on table "quantity_audit" violates foreign key constraint "quantity_audit_product_id_fkey" DETAIL: Key (product_id)=(5) is not present in table "products". INSERT 0 1 + echo 0 0 Finished: SUCCESS – Maciej Treder Jul 17 '16 at 19:47
  • Then postgres is to blame. Here is a thread where they might have your solution: http://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error – Gabriel Tigerström Jul 17 '16 at 19:53
  • Works :) Thank you – Maciej Treder Jul 17 '16 at 19:57
1

I changed my execution command to:

sudo -u postgres psql -v ON_ERROR_STOP=1 -d my_db < [path_to_file].sql
Maciej Treder
  • 11,866
  • 5
  • 51
  • 74