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

[Dinesh Chandra 12108 <Dinesh.Chandra@xxxxxxxxxx>]

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.chandra@xxxxxxxxxx

Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.

 

From: Rick Otten [mailto:rottenwindfish@xxxxxxxxx]
Sent: 23 May, 2017 6:19 PM
To: Dinesh Chandra 12108 <Dinesh.Chandra@xxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
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.Chandra@xxxxxxxxxx> 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 | Ext 1078 |dinesh.chandra@xxxxxxxxxx

Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.