database 在 psql 输出中禁用 NOTICES

disable NOTICES in psql output

提问by xenoterracide

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

如何阻止 psql（PostgreSQL 客户端）输出通知？例如

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

psql:schema/auth.sql:20: 注意：CREATE TABLE / PRIMARY KEY 将为表“users”创建隐式索引“users_pkey”

In my opinion a program should be silent unless it has an error, or some other reason to output stuff.

在我看来，程序应该是静默的，除非它有错误，或者有其他一些原因要输出内容。

回答by Milen A. Radev

SET client_min_messages TO WARNING;

Thatcould be set only for the session or made persistent with ALTER ROLEor ALTER DATABASE.

这只能为会话设置或成为永久性ALTER ROLE或ALTER DATABASE。

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

或者你可以把它放在你的".psqlrc" 中。

回答by Gavin

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

可能最全面的解释是在这里的Peter Eisentrauts博客条目

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

回答by Frank Heikens

Use --quietwhen you start psql.

--quiet在启动 psql 时使用。

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

通知不是无用的，但这是我的观点。

回答by KARASZI István

It can be set in the global postgresql.conffile as well with modifiying the client_min_messagesparameter.

它也可以postgresql.conf通过修改client_min_messages参数在全局文件中设置。

Example:

例子：

client_min_messages = warning

回答by Victoria Stuart

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

我尝试了该线程中建议的各种解决方案（及其排列），但我无法完全抑制 PSQL 输出/通知。

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

我正在执行一个claws2postgres.shBASH 脚本，该脚本进行一些初步处理，然后调用/执行 PSQL .sql 脚本，将 1000 个条目插入 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 行，我在其中重定向了 psql 输出：

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

The &>> /tmp/pg_output.txtredirect appends all output to an output file, that can also serve as a log file.

该&>> /tmp/pg_output.txt重定向追加所有输出到输出文件，也可以作为一个日志文件。

BASH terminal output

BASH 终端输出

[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  }'); 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.wav5 times
• phaser.wav: https://persagen.com/files/misc/phaser.wav

• pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }'获取脚本 PID，分配给 $PID
• while kill -0 $PID >/dev/null 2>&1; do ...: 在该脚本运行时，请执行...
• cat /tmp/pg_output.txt | wc -l: 使用输出文件行数作为进度指示器
• 完成后，播放phaser.wav5 次通知
• 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

• [re：上面的解决方案] PSQL：如何防止命令行上出现任何输出？
• [re: 这个 SO 线程]在 psql 输出中禁用 NOTICES

• [相关 SO 线程] Postgresql - 有没有办法在从文件读入时禁用 INSERT 语句的显示？

• [与解决方案相关] 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.