Обсуждение: How to log query's from servers ?
Hello ppl, any one can tell me how i make my postgresql server to log commands coming from another postgres server ? I have one postgresql and give to 2 other sql server to access my db. I want to log all query's coming only from one of the servers. Is that possibly ? -- Regards, Condor
On 6/06/2011 4:14 PM, Condor wrote: > Hello ppl, > any one can tell me how i make my postgresql server to log commands > coming from another postgres server ? I have one postgresql and give to > 2 other sql server to access my db. I want to log all query's coming > only from one of the servers. Is that possibly ? If the different servers log in with different user accounts or log into different databases you could use database-level or user-level SET commands to set log_statement for only one of them. Otherwise: just grep the logs. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/
On Mon, 06 Jun 2011 18:53:57 +0800, Craig Ringer wrote: > On 6/06/2011 4:14 PM, Condor wrote: >> Hello ppl, >> any one can tell me how i make my postgresql server to log commands >> coming from another postgres server ? I have one postgresql and give >> to >> 2 other sql server to access my db. I want to log all query's coming >> only from one of the servers. Is that possibly ? > > If the different servers log in with different user accounts or log > into different databases you could use database-level or user-level > SET commands to set log_statement for only one of them. > > Otherwise: just grep the logs. Can you explain little more how i can use database-level or user-level SET commands to set log_statement for only one of them ? From server that i want to log his query i log in to db on another server with: psql -U user -h 192.168.1.1 master_db. Here maybe I should say that I wanna monitor users that I gave them access to DB. Every server use different username to connect. I can't use grep on logs because other two servers generate around 500 MB to 1 GB log file per day. Probably, a little filter or patch on postgresql source code file which manage logs to write log file only if ip is the ip that i want will save me. -- Regards, Condor
On Mon, Jun 6, 2011 at 7:26 AM, Condor <condor@stz-bg.com> wrote: > On Mon, 06 Jun 2011 18:53:57 +0800, Craig Ringer wrote: >> >> On 6/06/2011 4:14 PM, Condor wrote: >>> >>> Hello ppl, >>> any one can tell me how i make my postgresql server to log commands >>> coming from another postgres server ? I have one postgresql and give to >>> 2 other sql server to access my db. I want to log all query's coming >>> only from one of the servers. Is that possibly ? >> >> If the different servers log in with different user accounts or log >> into different databases you could use database-level or user-level >> SET commands to set log_statement for only one of them. >> >> Otherwise: just grep the logs. > > Can you explain little more how i can use database-level or user-level > SET commands to set log_statement for only one of them ? > From server that i want to log his query i log in to db on another server > with: psql -U user -h 192.168.1.1 master_db. Here maybe I should say that I > wanna > monitor users that I gave them access to DB. > Every server use different username to connect. I can't use grep on logs > because > other two servers generate around 500 MB to 1 GB log file per day. > > Probably, a little filter or patch on postgresql source code file which > manage logs to write log file > only if ip is the ip that i want will save me. What you are looking for are alter statements. On pretty new psql, they'll be show with \h commands. you'll want something like this: alter database mydb set log_statement='all'; alter user bubba set log_statement='all';
On 06/06/2011 09:26 PM, Condor wrote: > Can you explain little more how i can use database-level or user-level > SET commands to set log_statement for only one of them ? http://www.postgresql.org/docs/current/static/sql-set.html http://www.postgresql.org/docs/current/static/runtime-config-logging.html http://www.postgresql.org/docs/current/static/sql-alteruser.html http://www.postgresql.org/docs/current/static/sql-alterdatabase.html You might, for example: ALTER USER user1 SET log_statement = 'all'; ALTER USER user2 SET log_statement = 'none'; or do the same with ALTER DATABASE ... SET if you wanted to log on a per-database level. > Probably, a little filter or patch on postgresql source code file which > manage logs to write log file > only if ip is the ip that i want will save me. Rather than patching PostgreSQL I would recommend configuring PostgreSQL to log through a smarter syslog daemon like rsyslogd or syslogd-ng . You should then be able to use regular expression filters in the syslog daemon to discard log messages you are not interested in before they are written to disk. -- Craig Ringer
On Tue, 07 Jun 2011 06:44:02 +0800, Craig Ringer wrote: > On 06/06/2011 09:26 PM, Condor wrote: > >> Can you explain little more how i can use database-level or >> user-level >> SET commands to set log_statement for only one of them ? > > http://www.postgresql.org/docs/current/static/sql-set.html > > http://www.postgresql.org/docs/current/static/runtime-config-logging.html > http://www.postgresql.org/docs/current/static/sql-alteruser.html > http://www.postgresql.org/docs/current/static/sql-alterdatabase.html > > You might, for example: > > ALTER USER user1 SET log_statement = 'all'; > ALTER USER user2 SET log_statement = 'none'; > > or do the same with ALTER DATABASE ... SET if you wanted to log on a > per-database level. > >> Probably, a little filter or patch on postgresql source code file >> which >> manage logs to write log file >> only if ip is the ip that i want will save me. > > > Rather than patching PostgreSQL I would recommend configuring > PostgreSQL to log through a smarter syslog daemon like rsyslogd or > syslogd-ng . You should then be able to use regular expression > filters > in the syslog daemon to discard log messages you are not interested > in > before they are written to disk. > > -- > Craig Ringer Thank you to you and Scott for help. -- Regards, Condor