Thread: [GENERAL] Logging at schema level
Hello,
I am using postgresql schema feature for multi-tenancy. can we get postgresql logs at schema level. Currently it is for the whole database server (pg_log)
Best Regards,
Nikhil
On 7/20/2017 10:10 PM, Nikhil wrote:
I am using postgresql schema feature for multi-tenancy. can we get postgresql logs at schema level. Currently it is for the whole database server (pg_log)
if you /were/ able to split the logs by schema, and a query touched multiple schemas, then where would that get logged?
-- john r pierce, recycling bits in santa cruz
On Fri, Jul 21, 2017 at 7:10 AM, Nikhil <nikhilsmenon@gmail.com> wrote: > I am using postgresql schema feature for multi-tenancy. can we get > postgresql logs at schema level. Currently it is for the whole database > server (pg_log) Not directly. All the log-related parameters can be controlled by a superuser, so you could leverage things at application level with some SET commands to switch them dynamically. -- Michael
On 21 July 2017 07:10:42 GMT+02:00, Nikhil <nikhilsmenon@gmail.com> wrote: >Hello, > >I am using postgresql schema feature for multi-tenancy. can we get >postgresql logs at schema level. Currently it is for the whole database >server (pg_log) > What do you want to achieve? Logging of data-changes per tenant? Regards, Andreas. -- 2ndQuadrant - The PostgreSQL Support Company
Schema = tenant. So basically tenant level logging.
On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" <andreas@a-kretschmer.de> wrote:
On 21 July 2017 07:10:42 GMT+02:00, Nikhil <nikhilsmenon@gmail.com> wrote:
>Hello,
>
>I am using postgresql schema feature for multi-tenancy. can we get
>postgresql logs at schema level. Currently it is for the whole database
>server (pg_log)
>
What do you want to achieve? Logging of data-changes per tenant?
Regards, Andreas.
--
2ndQuadrant - The PostgreSQL Support Company
On 7/20/2017 11:11 PM, Nikhil wrote: > Schema = tenant. So basically tenant level logging. select .... from schema1.table1 join schema2.table2 on .... where .....; if you have per schema logging, where should that get logged ? you could implement per DATABASE logging, if you A) add the database name to the log_prefix, and B) feed your logs to a program that understands this and splits them out to a log file per database. you could also do this on a per user basis. but, schema is something very dynamic, its a namespace within a database, and queries can touch multiiple schemas. -- john r pierce, recycling bits in santa cruz
Am 21.07.2017 um 08:11 schrieb Nikhil: > Schema = tenant. So basically tenant level logging. > > On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" <andreas@a-kretschmer.de > <mailto:andreas@a-kretschmer.de>> wrote: > > On 21 July 2017 07:10:42 GMT+02:00, Nikhil <nikhilsmenon@gmail.com > <mailto:nikhilsmenon@gmail.com>> wrote: > >Hello, > > > >I am using postgresql schema feature for multi-tenancy. can we get > >postgresql logs at schema level. Currently it is for the whole > database > >server (pg_log) > > > > What do you want to achieve? Logging of data-changes per tenant? > > Regards, Andreas. > > > -- > 2ndQuadrant - The PostgreSQL Support Company > Logging can mean 'logging of errors' or 'logging of data changes'. For the latter i'm thinking on logical replication, https://blog.2ndquadrant.com/why-logical-replication/ (Please don't top-post) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
On Fri, Jul 21, 2017 at 8:21 AM, John R Pierce <pierce@hogranch.com> wrote: > if you have per schema logging, where should that get logged ? > > you could implement per DATABASE logging, if you A) add the database name to > the log_prefix, and B) feed your logs to a program that understands this and > splits them out to a log file per database. you could also do this on a > per user basis. but, schema is something very dynamic, its a namespace > within a database, and queries can touch multiiple schemas. Personally, I understand that as logging the query N times, once per schema, if it touches N schemas, making the exercise part of parsing. I think that it would be possible to use the parser hook to achieve that actually, as you need extra lookups for WITH clauses and such. -- Michael
On Fri, Jul 21, 2017 at 2:11 AM, Nikhil <nikhilsmenon@gmail.com> wrote:
Schema = tenant. So basically tenant level logging.
If each tenant uses a separate user, you could parse this by that user. You can't separate the logs by user, but, you could use a tool like pgBadger to parse reports for each individual user (tenant) in the system and present the logs that way:
From: https://github.com/dalibo/pgbadger
....
-u | --dbuser username : only report on entries for the given user.
From: https://github.com/dalibo/pgbadger
....
-u | --dbuser username : only report on entries for the given user.
On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" <andreas@a-kretschmer.de> wrote:On 21 July 2017 07:10:42 GMT+02:00, Nikhil <nikhilsmenon@gmail.com> wrote:
>Hello,
>
>I am using postgresql schema feature for multi-tenancy. can we get
>postgresql logs at schema level. Currently it is for the whole database
>server (pg_log)
>
What do you want to achieve? Logging of data-changes per tenant?
Regards, Andreas.
--
2ndQuadrant - The PostgreSQL Support Company