Re: [PERFORM] Log update query along with username who has executed the same.

[Dinesh Chandra 12108]

Thank you so much Rick,

It’s working fine.

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
------------------------------------------------------------------
Mobile: +91-9953975849 | Ext 1078 
|dinesh.chan...@cyient.com<mailto:%7cdinesh.chan...@cyient.com>
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.

From: Rick Otten [mailto:rottenwindf...@gmail.com]
Sent: 23 May, 2017 6:19 PM
To: Dinesh Chandra 12108 <dinesh.chan...@cyient.com>
Cc: pgsql-performance@postgresql.org
Subject: [EXTERNAL] Re: [PERFORM] Log update query along with username who has 
executed the same.

You need to include "%u" in the log_line_prefix setting in your 
postgresql.conf.  Like this:

log_line_prefix = '%m %d %h %u '
#log_line_prefix = ''           # special values:
                    #   %a = application name
                    #   %u = user name
                    #   %d = database name
                    #   %r = remote host and port
                    #   %h = remote host
                    #   %p = process ID
                    #   %t = timestamp without milliseconds
                    #   %m = timestamp with milliseconds
                    #   %n = timestamp with milliseconds (as a Unix epoch)
                    #   %i = command tag
                    #   %e = SQL state
                    #   %c = session ID
                    #   %l = session line number
                    #   %s = session start timestamp
                    #   %v = virtual transaction ID
                    #   %x = transaction ID (0 if none)
                    #   %q = stop here in non-session
                    #        processes
                    #   %% = '%'
                    # e.g. '<%u%%%d> '


Also 9.1 is pretty old.  You should think about upgrading as soon as is 
practical.


On Tue, May 23, 2017 at 8:42 AM, Dinesh Chandra 12108 
<dinesh.chan...@cyient.com<mailto:dinesh.chan...@cyient.com>> wrote:
Dear Expert,

May you please provide the solution for below query.

I have to create a log for all the update query executed in database along with 
its username who has executed that query.
However, I am able to log all the update queries in my pg_log file but it’s not 
showing particular user who has run the query.

I am using PostgreSQL 9.1 with Linux platform.

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.
------------------------------------------------------------------
Mobile: +91-9953975849<tel:+91%2099539%2075849> | Ext 1078 
|dinesh.chan...@cyient.com<mailto:%7cdinesh.chan...@cyient.com>
Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.