disable NOTICES in psql output_问答_开发者_运维开发者技术经验分享

How do I stop psql (PostgreSQL client) from outputting notices? e.g.

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

In my opinion a program sho开发者_StackOverflow中文版uld be silent unless it has an error, or some other reason to output stuff.

SET client_min_messages TO WARNING;

That could be set only for the session or made persistent with ALTER ROLE or ALTER DATABASE.

Or you could put that in your ".psqlrc".

Probably the most comprehensive explanation is on Peter Eisentrauts blog entry here (Archive)

I would strongly encourage that the original blog be studied and digested 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

Use --quiet when you start psql.

A notice is not useless, but that's my point of view.

It can be set in the global postgresql.conf file as well with modifiying the client_min_messages parameter.

Example:

client_min_messages = warning

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

I am executing a claws2postgres.sh BASH script that does some preliminary processing then calls/executes a PSQL .sql script, to insert 1000's of entries into PostgreSQL.

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

Output

[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 ... ]

SOLUTION

Note 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 &>> /tmp/pg_output.txt redirect appends all output to an output file, that 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]$

Monitor progress:

In another terminal, execute

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 }' gets the script PID, assigned to $PID
while kill -0 $PID >/dev/null 2>&1; do ... : while that script is running, do ...
cat /tmp/pg_output.txt | wc -l : use the output file line count as a progress indicator
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

References

[re: solution, above] PSQL: How can I prevent any output on the command line?
[re: this SO thread] disable NOTICES in psql output
[related SO thread] Postgresql - is there a way to disable the display of INSERT statements when reading in from a file?
[relevant to 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.

Offering a suggestion that is useful for a specific scenario I had:

Windows command shell calls psql.exe call to execute one essential SQL command
Only want to see warnings or errors, and suppress NOTICES

Example:

psql.exe -c "SET client_min_messages TO WARNING; DROP TABLE IF EXISTS mytab CASCADE"

(I was unable to make things work with PGOPTIONS as a Windows environment variable--couldn't work out the right syntax. Tried multiple approaches from different posts.)