Обсуждение: [PERFORM] Log update query along with username who has executed the same.

Поиск
Список
Период
Сортировка

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

От
Dinesh Chandra 12108
Дата:

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@cyient.com

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

 

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

От
Rick Otten
Дата:
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@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 | Ext 1078 |dinesh.chandra@cyient.com

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

 


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

От
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.chandra@cyient.com

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

 

From: Rick Otten [mailto:rottenwindfish@gmail.com]
Sent: 23 May, 2017 6:19 PM
To: Dinesh Chandra 12108 <Dinesh.Chandra@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.Chandra@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 | Ext 1078 |dinesh.chandra@cyient.com

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