PSQL: How can I prevent any output on the command line?

Question

My problem: I'm trying to run a database generation script at the command line via a batch file as part of a TFS build process to enable nightly testing on a known dataset.

The scripts we run are outputting Notices, Warnings and some Errors on the command line. I would like to suppress at least the Notices and Warnings, and if possible the Errors as they don't seem to have an impact on the overall success of the scripts. This output seems to be affecting the success or failure of the process as far as the TFS build process is concerned. It's highlighting every line of output from the scripts as errors and failing the build.

As our systems are running on Windows, most of the potential solutions I've found online don't work as they seem to target Linux.

I've changed the client_min_messages to error in the postgresql.conf file, but when looking at the same configuration from pgAdmin (tools > server configuration) it shows the value as Error but the current value as Notice.

All of the lines in the batch file that call psql use the -q flag as well but that only seems to prevent the basics such as CREATE TABLE and ALTER TABLE etc.

An example line from the batch file is:

psql -d database -q < C:\Database\scripts\script.sql

Example output line from this command:

WARNING: column "identity" has type "unknown" DETAIL: Proceeding with relation creation anyway.

Specifying the file with the -f flag makes no difference.

I can manually run the batch file on my development machine and it produces the expected database regardless of what errors or messages show on the command prompt.

So ultimately I need all psql commands in my batch files to run silently.

Ramadan

See my answer here: https://mcmap.net/q/269510/-disable-notices-in-psql-output – Comanche

Answer 1

psql COMMAND &> output.txt

Or, using your example command:

psql -d database -q < C:\Database\scripts\script.sql &> output.txt

Stricklan

Thanks for your reply, but unfortunately it doesn't work. The syntax is incorrect in you example. Replacing the &> with -o creates the output file but doesn't write anything to it. Then outputs to the command prompt as usual. – Ramadan

This lead a colleague of mine to suggest 2> output.txt and this seems to work. Replacing with 2> nul will completely suppress all output, but may be useful to have a log just in case. – Ramadan

I suspect there is something in your script that is causeing this. Sending 1> to a file sends STDOUT to the file and 2> sends STDERR to the file. Using &> should send both. I verified that this works: psql -d database -h host -p port -U user -f test.sql &> output.txt – Stricklan

Thanks for the response. I can understand this that should output to both but it doesn't seem to work for me. The following is the command and output I see when I run it. c:\Database>psql -d database -q < C:\Database\scripts\script.sql &> C:\Database\output.txt The syntax of the command is incorrect. – Ramadan

if you're using windows, try putting quotes around the file – Stricklan

use of &> is a relatively new feature in linux shell scripting. It won't work in most old-line unix implementations (using the std installed shells) AND the error message from Windows indicates it doesn't work there either. Good luck to all. – Ynez

Finally I've gone with a slightly different approach of using ">> output.txt 2>&1" to save all output from each command to a text file. It's not the ideal solution I was hoping for but it does the trick and allows me to complete the database generation without any errors breaking the build. – Ramadan

Use &>> to save (append: >>) output to specified file. For reference: askubuntu.com/questions/350208/what-does-2-dev-null-mean – Comanche

Answer 2

use psql -o flag to send the command output to the filename you wish or /dev/null if you don't care about it.

Jadajadd

Answer 3

The -q option will not prevent the query output.

-q, --quiet run quietly (no messages, only query output)

to avoid the output you have to send the query result to a file

psql -U username -d db_name -pXXXX -c "SELECT * FROM table_name LIMIT 5;" > C:\test.csv

use 1 > : create new file each time
use 2 >> : will create and keep adding

Mydriatic