On Error Stop Postgres
Contents |
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring
Psql On_error_rollback
developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ psql continue on error Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others
On_error_stop=1
in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top How to conditionally stop a psql exit code psql script (based on a variable value)? up vote 5 down vote favorite Let's consider the following example (from the start of a psql script): \c :db_to_run_on TRUNCATE the_most_important_table; -- tried to avoid similarities to anything that exists out there Now if it is run this by the command psql [connection details] -v db_to_run_on=\'dev_database\' then it just runs and the user is happy. But what if (s)he decides to specify -v db_to_run_on=production_database? (Let's assume that this can happen, just like people psql error code run rm -rf / # don't try this at home!!! ocassionally.) Hopefully there is a fresh backup of that table... So the question arises: how to check the variables passed to a script and stop further processing based on their value? postgresql psql share|improve this question edited Sep 19 '12 at 8:51 asked Sep 19 '12 at 8:32 dezso 14.3k73563 psql is quite limited as a batch tool, as it lacks control structures. I'd recommend writing scripts for this sort of thing in perl/python/whatever using the appropriate database driver. –Craig Ringer Sep 19 '12 at 10:59 @CraigRinger it is, unfortunately. I have an idea now, but it needs a bit of honing yet. –dezso Sep 19 '12 at 11:13 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote accepted There is an option in psql which stops executing commands on error, this is ON_ERROR_STOP. If we could raise an error somehow, this would do what we want. The problem is that we have to test the variable and produce an error somehow. Since one can't use control structures in psql (because there are none)*, my only idea was to use SQL for testing. Well, producing an error conditionally is something which pl/pgsql is quite good at, so I wrote a function which would generate an error. I can now call this function from a simple CASE structure. A simple ex
use psql in an interactive mode where we connect to the database and then type and execute queries. But sometimes it is useful to execute a whole SQL file. We can do this with: psql -U username -d database
Psql: Could Not Connect To Server: No Such File Or Directory
-f file.sql The problem with executing an sql file like this is that psql will exit psql run sql file with a status code 0 (which means everything is OK), even when there are errors while executing file.sql. It would also ignore all errors and
Psql Command Line
continue execution until the end of file, which is not what we would like every time. Because of this you cannot do simple checks in your scripts for the exit status with $? to know if the file was executed http://dba.stackexchange.com/questions/24518/how-to-conditionally-stop-a-psql-script-based-on-a-variable-value successfully. To resolve this, you could pass stdout and stderr to grep and search for ERROR: like this: psql -U username -d database -f file.sql 2>&1 | grep "ERROR:" This would work, but it's not a very clean solution and you also don't see the output of psql (like notices, etc). There is a simple and effective solution to this problem by using a variable ON_ERROR_STOP. If we set this variable to 1 or true, psql would stop on the http://blog.nordeus.com/dev-ops/psql-exit-on-first-error.htm first error and exit with status code 3, which makes it perfect for scripts. You can set this variable at the top of the sql file: \set ON_ERROR_STOP true or even better directly on the psql command line with parameter -v: psql -v ON_ERROR_STOP=1 -U username -d database -f file.sql Now we can easily check if the sql file was executed successfully like this: psql -v ON_ERROR_STOP=1 -U username -d database -f file.sql if [ $? -eq 0 ]; then echo Success else echo Failed fi I hope this short tip comes in handy. "psql - Exit on First Error" has been shared times on Facebook. Share it on Facebook too! "psql - Exit on First Error" has been shared 3 times on Twitter. Share it on Twitter too! "psql - Exit on First Error" has been shared times on Google+. Share it on Google+ too! "psql - Exit on First Error" has been shared 31 times on LinkedIn. Share it on LinkedIn too! Strahinja Kustudic System Engineer November 02, 2015 More about this post Tags bash postgres postgresql psql enable javascript to see the comments Search Search by keywords Subscribe Subscribe to Dev & Ops by email Subscribe Popular posts PostgreSQL Locking Revealed Power Failure Testing with SSDs Meet Pushkin - Nordeus’ Open Source Push Notification System Close Subscribe to our blog Email Address Sections Dev & Ops Games Art Marketing Life at Nordeus Visit nordeus.com © 2015
8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 9.1.23 Documentation Prev Up Next pg_ctl Namepg_ctl--initialize, start, stop, or control a PostgreSQL server Synopsis https://www.postgresql.org/docs/9.1/static/app-pg-ctl.html pg_ctl init[db] [-s] [-D datadir] [-o initdb-options] pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c] pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] http://serverfault.com/questions/22833/postgres-non-zero-exit-code-when-executing-a-sql-file | i[mmediate] ] pg_ctl restart [-w] [-t seconds] [-s] [-D datadir] [-c] [-m s[mart] | f[ast] | i[mmediate] ] [-o options] pg_ctl reload [-s] [-D datadir] pg_ctl status [-D datadir] pg_ctl promote [-s] on error [-D datadir] pg_ctl kill signal_name process_id pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-S a[uto] | d[emand] ] [-w] [-t seconds] [-s] [-o options] pg_ctl unregister [-N servicename] Description pg_ctl is a utility for initializing a PostgreSQL database cluster, starting, stopping, or restarting the PostgreSQL database server (postgres), or displaying the status of a running server. Although the server can be started manually, pg_ctl encapsulates on error stop tasks such as redirecting log output and properly detaching from the terminal and process group. It also provides convenient options for controlled shutdown. The init or initdb mode creates a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance. This mode invokes the initdb command. See initdb for details. In start mode, a new server is launched. The server is started in the background, and its standard input is attached to /dev/null (or nul on Windows). On Unix-like systems, by default, the server's standard output and standard error are sent to pg_ctl's standard output (not standard error). The standard output of pg_ctl should then be redirected to a file or piped to another process such as a log rotating program like rotatelogs; otherwise postgres will write its output to the controlling terminal (from the background) and will not leave the shell's process group. On Windows, by default the server's standard output and standard error are sent to the terminal. These default behaviors can be changed by using -l to append the server's output to a log file. Use of either -l or output redirection is recommended. In stop mode, the ser
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Postgres: Non zero exit code when executing a sql file? up vote 18 down vote favorite I am writing a shell script which makes calls to psql using 2 forms... one is by command (-c), the other is by file (-f). e.g. psql -c "create table foo (bar integer)" psql -f foobar.sql One difference between these forms is that a call by command (-c) returns a non-zero exit code if an error is encountered, while a call by file (-f) always seems to return zero. I'm wondering if there is a workaround for this behaviour? (i.e. return non-zero if an error occurs while executing a file). Thanks. database sql postgresql psql share|improve this question asked Jun 9 '09 at 18:39 Jin Kim 3731311 add a comment| 2 Answers 2 active oldest votes up vote 22 down vote You can use below statement. psql -v ON_ERROR_STOP=1 -f foobar.sql This will return the correct return code even foobar.sql file doesn't enable ON_ERROR_STOP at the top of the file. share|improve this answer edited Jun 27 '12 at 4:27 mgorven 22.3k43790 answered Jun 10 '11 at 9:11 Taye 22122 add a comment| up vote 11 down vote accepted I found out how to resolve this. I need to enable ON_ERROR_STOP at the top of the file. Example: \set ON_ERROR_STOP true share|improve this answer answered Jun 9 '09 at 18:51 Jin Kim 3731311 +1 for looking at the manpage :) –Server Horror Jun 9 '09 at 18:54 +1 for the best solution because it allows me to set the variable in the script, so I can trust it always runs correctly and don't have to worry about runtime context. –Brandon Dec 27 '14 at 4:48 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged database sql postgresql psql or ask your own question. asked 7 years ago viewed 4733 times active 4 years ago Visit Chat Related 14Postgre