Logging at schema level

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Schema = tenant. So basically tenant level logging.

On 21-Jul-2017 11:21 AM, "Andreas Kretschmer" <andreas@a-kretschmer.de>
wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.
....

--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com&gt;*
http://openscg.com