Обсуждение: [GENERAL] Logging at schema level

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

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