Disable NOTICE in psql output

Question

How to stop psql (PostgreSQL client) from displaying notifications? eg.

psql: schema / auth.sql: 20: NOTICE: CREATE TABLE / PRIMARY KEY will create an implicit users_pkey index for the users table

In my opinion, the program should be quiet if it has no error or any other reason for the output of the material.

Answer 1

SET client_min_messages TO WARNING; 

This parameter can be set only for the session or made persistent with ALTER ROLE or ALTER DATABASE .

Or you can put this in your ". Psqlrc" .

Answer 2

Most likely, the most complete explanation is here, for example, in the Peter Eisentrauts blog

I would highly recommend learning and digesting the original blog, but the final recommendation is something like:

 PGOPTIONS='--client-min-messages=warning' psql -X -q -a -1 -v ON_ERROR_STOP=1 --pset pager=off -d mydb -f script.sql 

Answer 3

Use --quiet when starting psql.

Notification is not useless, but my point is.

Answer 4

It can be installed in the global postgresql.conf file, as well as with the modification of the client_min_messages parameter.

Example:

 client_min_messages = warning 

Answer 5

I tried the various proposed solutions (and their permutations) suggested in this thread, but I was not able to completely suppress PSQL output / notifications.

I execute the claws2postgres.sh BASH script which does some preprocessing and then calls / executes the PSQL.sql script to insert 1000 records into PostgreSQL.

 ... PGOPTIONS="-c client_min_messages=error" psql -d claws_db -f claws2postgres.sql 

Exit

 [victoria@victoria bash]$ ./claws2postgres.sh pg_terminate_backend ---------------------- DROP DATABASE CREATE DATABASE You are now connected to database "claws_db" as user "victoria". CREATE TABLE SELECT 1 INSERT 0 1 UPDATE 1 UPDATE 1 UPDATE 1 Dropping tmp_table DROP TABLE You are now connected to database "claws_db" as user "victoria". psql:/mnt/Vancouver/projects/ie/claws/src/sql/claws2postgres.sql:33: NOTICE: 42P07: relation "claws_table" already exists, skipping LOCATION: transformCreateStmt, parse_utilcmd.c:206 CREATE TABLE SELECT 1 INSERT 0 1 UPDATE 2 UPDATE 2 UPDATE 2 Dropping tmp_table DROP TABLE [ ... snip ... ] 

DECISION

Pay attention to this modified PSQL line, where I redirect the psql output:

 psql -d claws_db -f $SRC_DIR/sql/claws2postgres.sql &>> /tmp/pg_output.txt 

The redirection &>>/tmp/pg_output.txt adds all output to the output file, which can also serve as a log file.

BASH terminal output

 [victoria@victoria bash]$ time ./claws2postgres.sh pg_terminate_backend ---------------------- DROP DATABASE CREATE DATABASE 2:40:54 ## 2 h 41 min [victoria@victoria bash]$ 

Progress Monitoring:

In another terminal, run

 PID=$(pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }'); while kill -0 $PID >/dev/null 2>&1; do NOW=$(date); progress=$(cat /tmp/pg_output.txt | wc -l); printf "\t%s: %i lines\n" "$NOW" $progress; sleep 60; done; for i in seq{1..5}; do aplay 2>/dev/null /mnt/Vancouver/programming/scripts/phaser.wav && sleep 0.5; done ... Sun 28 Apr 2019 08:18:43 PM PDT: 99263 lines Sun 28 Apr 2019 08:19:43 PM PDT: 99391 lines Sun 28 Apr 2019 08:20:43 PM PDT: 99537 lines [victoria@victoria output]$ 

---

• pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }' pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }' pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }' pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }' pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }' gets the script PID assigned by $ PID
• while kill -0 $PID >/dev/null 2>&1; do... while kill -0 $PID >/dev/null 2>&1; do... while kill -0 $PID >/dev/null 2>&1; do... while kill -0 $PID >/dev/null 2>&1; do... : while this script is running, do ...
• cat/tmp/pg_output.txt | wc -l cat/tmp/pg_output.txt | wc -l cat/tmp/pg_output.txt | wc -l cat/tmp/pg_output.txt | wc -l : use the number of lines of the output file as an indicator of progress
• when done, notify by playing phaser.wav 5 times
• phaser.wav: https://persagen.com/files/misc/phaser.wav

---

Output file:

 [victoria@victoria ~]$ head -n22 /tmp/pg_output.txt You are now connected to database "claws_db" as user "victoria". CREATE TABLE SELECT 1 INSERT 0 1 UPDATE 1 UPDATE 1 UPDATE 1 Dropping tmp_table DROP TABLE You are now connected to database "claws_db" as user "victoria". psql:/mnt/Vancouver/projects/ie/claws/src/sql/claws2postgres.sql:33: NOTICE: 42P07: relation "claws_table" already exists, skipping LOCATION: transformCreateStmt, parse_utilcmd.c:206 CREATE TABLE SELECT 1 INSERT 0 1 UPDATE 2 UPDATE 2 UPDATE 2 Dropping tmp_table DROP TABLE 

---

Recommendations

• [re: solution, above] PSQL: How can I prevent any output on the command line?
• [re: this SO stream] disable NOTifications in psql output

• [linked SO branch] Postgresql - is there a way to disable the display of INSERT statements when reading from a file?

• [refers to the solution] https://askubuntu.com/questions/350208/what-does-2-dev-null-mean

 The > operator redirects the output usually to a file but it can be to a device. You can also use >> to append. If you don't specify a number then the standard output stream is assumed but you can also redirect errors > file redirects stdout to file 1> file redirects stdout to file 2> file redirects stderr to file &> file redirects stdout and stderr to file /dev/null is the null device it takes any input you want and throws it away. It can be used to suppress any output.