cancel
Showing results for 
Search instead for 
Did you mean: 

How to send output/error codes generated by the sql query to the log file?

This can be achieved by using Output redirection ">>" in the shell to send output/error codes to the log file.   In the below example we are running SQL query "test1.sql" and sending its output and error codes to log file "/tmp/test1_execution.logs" by using output redirection symbol >>

 


Please find below example for reference & log output :

 

      -bash-4.1$ psql -d edb -a -x -f test1.sql -L script.log -o script.out >>/tmp/test1_execution.logs 2>&1


      -bash-4.1$ cat /tmp/test1_execution.logs

       select * from pg_user;


     -bash-4.1$ cat script.log

     ********* QUERY **********
      select * from pg_user;
     **************************

      usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useaccountstatus | uselockdate | usepasswordexpire | useconfig
    --------------+----------+-------------+----------+---------+--------------+----------+----------+------------------+-------------+-------------------+-----------
    enterprisedb | 10 | t | t | t | t | ******** | | 0 | | |
    ppxstage | 16480 | f | t | f | f | ******** | | 0 | | |
    (2 rows)

-bash-4.1$ cat script.out

-[ RECORD 1 ]-----+-------------
usename | enterprisedb
usesysid | 10
usecreatedb | t
usesuper | t
userepl | t
usebypassrls | t
passwd | ********
valuntil |
useaccountstatus | 0
uselockdate |
usepasswordexpire |
useconfig |
-[ RECORD 2 ]-----+-------------
usename | ppxstage
usesysid | 16480
usecreatedb | f
usesuper | t
userepl | f
usebypassrls | f
passwd | ********
valuntil |
useaccountstatus | 0
uselockdate |
usepasswordexpire |
useconfig |

 

 Also if you want to redirect the output from psql prompt to a file below can be used.

 

     step 1 : postgres=# \o test1_run.txt
step 2 : postgres=# select * from pg_database;
step 3 : postgres=# \o


-bash-4.1$ cat test1_run.txt

datname | datdba | encoding | datcollate | datctype | daticu | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datredwoodlike | datacl
-----------+--------+----------+-------------+-------------+--------+---------------+--------------+--------------+---------------+--------------+------------+---------------+----------------+-------------------------------------------------
postgres | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | | f | t | -1 | 14987 | 1187 | 1 | 1663 | f |
edb | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | | f | t | -1 | 14987 | 1187 | 1 | 1663 | f |
template1 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | | t | t | -1 | 14987 | 1187 | 1 | 1663 | f | {=c/enterprisedb,enterprisedb=CTc/enterprisedb}
template0 | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | | t | f | -1 | 14987 | 1187 | 1 | 1663 | f | {=c/enterprisedb,enterprisedb=CTc/enterprisedb}
(4 rows)

 

Version history
Revision #:
6 of 6
Last update:
‎05-07-2019 03:14 PM
Updated by: