Скачать или смотреть Loading Queries with psql and Capturing Logs in PostgreSQL

Learn how to run SQL queries from a file in PostgreSQL while capturing all output messages, including notices, in a log file.
---
This video is based on the question https://stackoverflow.com/q/71249782/ asked by the user 'Sandy' ( https://stackoverflow.com/u/315591/ ) and on the answer https://stackoverflow.com/a/71252269/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: psql : load query from file and output psql messages to log file

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering PostgreSQL: Loading Queries from a File and Logging Messages

If you're working with PostgreSQL and need to execute a SQL file while ensuring that all output messages, especially notices, are captured in a log file, you've come to the right place. This guide will walk you through the issue, provide a reliable solution, and break down the process in a clear and organized manner.

The Problem

You may find yourself wanting to run SQL scripts stored in external files using the psql command line tool. While this is straightforward, logging important messages such as notices can be a bit tricky. For instance, let's say your SQL file (named my_sql_file.sql) contains a multiline anonymous block that raises a notice. You execute your command as follows:

[[See Video to Reveal this Text or Code Snippet]]

You expect to see a nice output in your log file, particularly the notice from your SQL code:

[[See Video to Reveal this Text or Code Snippet]]

However, you find that your log file only outputs the first line, which is "DO", and none of the notable notices you raised. This can be frustrating when you're trying to capture all relevant information that can assist in debugging or audit trails.

The Solution

The issue here is that PostgreSQL emits notices to the standard error stream rather than standard output. Therefore, when you try to redirect the output to a log file, only standard output gets captured, leaving the important error messages and notices behind. To solve this problem, you will need to redirect standard error to standard output.

Redirecting Standard Error

To capture both standard output and standard error in your log file, modify your command to this:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Command:

psql: The command-line tool for PostgreSQL.

-h host: Specifies the database host.

-d database: Indicates the database name you want to connect to.

-U username: Denotes the username for connecting to the database.

-f my_sql_file.sql: Points to the SQL file that contains your queries and blocks.

> my_log_file.log: Redirects the standard output (normal messages) to my_log_file.log.

2>&1: This part is crucial, as it redirects standard error (file descriptor 2) to standard output (file descriptor 1), thus merging the two streams.

What to Expect:

After you implement the above command, your logs in my_log_file.log will include the notices emitted by your SQL file, as well as any other standard output messages that are generated during the execution of your psql command.

Example of Output:

Your log file should now include output similar to the following:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

With the above solution, you can effectively run SQL scripts while capturing all the relevant output messages, enhancing your ability to understand the execution results and debug any issues. Just remember to redirect both standard output and standard error when logging messages from PostgreSQL.

Now you're set to tackle your next PostgreSQL project equipped with this useful command. Happy querying!