log_line_info plan

Hi, all

What should I do if I want to have 2 completely seperated databases in
PostgreSQL? I want each database to have its own data, log and
everything needed to access that database. I don't want them to share
anything. Has anyone done this before? Or,
could anyone give me some clue of how to do this?

Thank you very much.

Hong Ge

On Wed, 11 Feb 2004 hong(dot)ge(at)yale(dot)edu wrote:

> Hi, all
>
> What should I do if I want to have 2 completely seperated databases in
> PostgreSQL? I want each database to have its own data, log and
> everything needed to access that database. I don't want them to share
> anything. Has anyone done this before? Or,
> could anyone give me some clue of how to do this?

You're telling us what you want, but not the why. The why may help us to
figure out both how to do it, and whether or not it's a good idea.

On Wed, 2004-02-11 at 16:36, hong(dot)ge(at)yale(dot)edu wrote:
> Hi, all
>
> What should I do if I want to have 2 completely seperated databases in
> PostgreSQL? I want each database to have its own data, log and
> everything needed to access that database. I don't want them to share
> anything. Has anyone done this before? Or,
> could anyone give me some clue of how to do this?

You will need to initdb 2 locations and start 2 instances on separate ports.

Thank you very much for your reply. I'd like to discuss the why.

I don't think letting them share data and logs could gain me something.
And if I have 2 databases totally not relevant, I think the most natural
way is to make them totally seperated. Does the sharing buys me
anything? If not, what's the reason of doing it?

Thank you very much.

--Hong Ge

Quoting "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com>:

> On Wed, 11 Feb 2004 hong(dot)ge(at)yale(dot)edu wrote:
>
> > Hi, all
> >
> > What should I do if I want to have 2 completely seperated databases
> in
> > PostgreSQL? I want each database to have its own data, log and
> > everything needed to access that database. I don't want them to
> share
> > anything. Has anyone done this before? Or,
> > could anyone give me some clue of how to do this?
>
> You're telling us what you want, but not the why. The why may help
> us to
> figure out both how to do it, and whether or not it's a good idea.
>

Thank you very much for your reply.

Yes, that's true. But it seems not a good idea if I have many databases
and I want them totally seperated with each other.

What's your opinion? Thanks.

--Hong Ge

Quoting Rod Taylor <pg(at)rbt(dot)ca>:

> On Wed, 2004-02-11 at 16:36, hong(dot)ge(at)yale(dot)edu wrote:
> > Hi, all
> >
> > What should I do if I want to have 2 completely seperated databases
> in
> > PostgreSQL? I want each database to have its own data, log and
> > everything needed to access that database. I don't want them to
> share
> > anything. Has anyone done this before? Or,
> > could anyone give me some clue of how to do this?
>
> You will need to initdb 2 locations and start 2 instances on separate
> ports.
>

Well, in postgresql you have a cluster, and inside the cluster, you have
databases, and inside the databases you have schemas.

You cannot cross database boundaries with transactions. i.e. you can
begin a transaction, insert into two databases and roll it back.
Transactions live within a single database, and each database is basically
a completely seperate entity.

However, they would shared the same postmaster process, and both be
accessed by going to port 5432. They would share log files, and most
likely share a directory (though there are a couple of different ways to
do that, it's not done most of the time, and doesn't gain most users a
lot.

So, you'll likely want one cluster / postmaster, with 2 or more databases
inside it.

Our main server at work handles 80 databases, some large, most small or
medium, and they are all seperate entities that I can dump seperately or
together, and give ownership to the user who needs to own that particular
db.

Will that get you what you want?

On Wed, 11 Feb 2004 hong(dot)ge(at)yale(dot)edu wrote:

> Thank you very much for your reply. I'd like to discuss the why.
>
> I don't think letting them share data and logs could gain me something.
> And if I have 2 databases totally not relevant, I think the most natural
> way is to make them totally seperated. Does the sharing buys me
> anything? If not, what's the reason of doing it?
>
> Thank you very much.
>
> --Hong Ge
>
>
> Quoting "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com>:
>
> > On Wed, 11 Feb 2004 hong(dot)ge(at)yale(dot)edu wrote:
> >
> > > Hi, all
> > >
> > > What should I do if I want to have 2 completely seperated databases
> > in
> > > PostgreSQL? I want each database to have its own data, log and
> > > everything needed to access that database. I don't want them to
> > share
> > > anything. Has anyone done this before? Or,
> > > could anyone give me some clue of how to do this?
> >
> > You're telling us what you want, but not the why. The why may help
> > us to
> > figure out both how to do it, and whether or not it's a good idea.
> >
>
>
>

On Wed, 11 Feb 2004 hong(dot)ge(at)yale(dot)edu wrote:

> Thank you very much for your reply.
>
> Yes, that's true. But it seems not a good idea if I have many databases
> and I want them totally seperated with each other.
>
> What's your opinion? Thanks.

OK, here's the issue. Postgresql uses certain resources in a shared
manner, and other resources are completely seperate. For instance, the
shared memory buffers are shared within a single instance or cluster, by
all the databases. One data directory, and one collation, and one set of
logs are also shared by one instance.

The individual databases within a cluster share a set of global users.
I.e if I create a user in one database, he can then be granted access to
the other databases (or denied access) with a simple change to
pg_hba.conf. So, it's very easy to add / remove people's access to
individual databases.

If you seperate out each database into it's own instance, you now have two
(or more) postgresql instances, each with a different data directory,
shared memory buffers, user list and passwords. I.e. now nothing passes
between them, at all.

If you would have allocated 128 megs of shared buffer memory for a single
cluster which contained 4 databases, and you split those out into
individual instances, then you'd need to give each of the four cluster /
instances 32 megs of shared buffer or you'd risk using up more memory than
before.

With the single cluster, if one database has a lot of data to sling
around, and the others are handling a few k at a time, it has 128 Megs to
work in. With four clusters, no matter how little the other three are
working, you'd only have 32 meg to play in.

Taking this to the logical extreme of having n databases, where n is
fairly good size, say 20, 40, or 100, then you have issues that if you set
up each database with enough shared memory to do its job when it needed
to, you risk starting a swap storm should a couple dozen of those
databases have a few large result sets open, thus using up all the shared
memory they'd have alloted.

Lowering the shared memory for each database low enough to prevent this
would result in individual databases that each had very small amounts of
shared memory.

Also, maintainance gets harder. You now have to vacuum multiple seperate
clusters, and you need to schedule it so that you don't have two or three
running at once and swamping your storage subsystem.

For certain setups, multiple clusters are a great thing. I've used them
as "hot backups" where I put a secondary instance online, placed its
storage on a NAS, backed up to it, and then shut it down to have a cold
live spare, or used it for testing administrative procedures one shouldn't
practice on a live database.

But for seperating out applications from each other, there's really
nothing to be gained by putting each seperate database application into
it's own cluster.

> But for seperating out applications from each other, there's really
> nothing to be gained by putting each seperate database application into
> it's own cluster.

I believe the initial email requested individual logs, and presumably
the ability to grant superuser access without risking a user crossing
into another clients space.

The only way to accomplish this is to run 2 copies. Last time I looked,
you couldn't get the database name in the log files to allow for
mechanical filtering, and a number of abilities are still only available
to superusers -- not through grants or specific permissions.

On Wed, Feb 11, 2004 at 17:48:51 -0500,
hong(dot)ge(at)yale(dot)edu wrote:
> Thank you very much for your reply. I'd like to discuss the why.
>
> I don't think letting them share data and logs could gain me something.
> And if I have 2 databases totally not relevant, I think the most natural
> way is to make them totally seperated. Does the sharing buys me
> anything? If not, what's the reason of doing it?

They would both use the same shared memory segment. If you are short on
memory running two clusters may make the problem worse.

Rod Taylor wrote:

>Last time I looked,
>you couldn't get the database name in the log files to allow for
>mechanical filtering
>

Watch this space.When my log_disconnections patch makes it through the
filter process it will be followed up with a patch that allows tagging
of log lines with a printf-style string.

My current thinking is to provide the following escapes:

%U = user
%D = database
%T = timestamp
%P = pid
%L = session log line number
%C = sessionid cookie (hex encoded session start time + pid)
%S = session start timestamp

and I'm willing to entertain other suggestions.

cheers

andrew

Hello,

Depending on your needs and transaction load per database you can easily
run 30 databases on a machine with 2 Gig of RAM. You will of course have
to use initdb for each cluster and change the tcp port for each cluster
but it works just fine.

Sincerely,

Joshua D. Drake

hong(dot)ge(at)yale(dot)edu wrote:
> Thank you very much for your reply.
>
> Yes, that's true. But it seems not a good idea if I have many databases
> and I want them totally seperated with each other.
>
> What's your opinion? Thanks.
>
> --Hong Ge
>
>
> Quoting Rod Taylor <pg(at)rbt(dot)ca>:
>
>
>>On Wed, 2004-02-11 at 16:36, hong(dot)ge(at)yale(dot)edu wrote:
>>
>>>Hi, all
>>>
>>>What should I do if I want to have 2 completely seperated databases
>>
>>in
>>
>>>PostgreSQL? I want each database to have its own data, log and
>>>everything needed to access that database. I don't want them to
>>
>>share
>>
>>>anything. Has anyone done this before? Or,
>>>could anyone give me some clue of how to do this?
>>
>>You will need to initdb 2 locations and start 2 instances on separate
>>ports.
>>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

On Thu, 12 Feb 2004, Rod Taylor wrote:

> > But for seperating out applications from each other, there's really
> > nothing to be gained by putting each seperate database application into
> > it's own cluster.
>
> I believe the initial email requested individual logs, and presumably
> the ability to grant superuser access without risking a user crossing
> into another clients space.

Well, I got the feeling it was only the logs he wanted seperate, and if
that's all he wanted, then seperating will get him that, but there's a
cost, and I wanted to make sure he understood that as well.

I'm not sure he was intimating seperate super users, but I could
definitely see situations where that would be useful.

> and I'm willing to entertain other suggestions.

Very nice, but you missed the most important. Command Tag.

--
Rod Taylor <rbt [at] rbt [dot] ca>

Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc

Rod Taylor wrote:

>>and I'm willing to entertain other suggestions.
>>
>>
>
>Very nice, but you missed the most important. Command Tag.
>
>

I've had a brief look at this proposal (to allow reporting of the
command tag along with username, database, session cookie etc on each
log line). I'm wondering where the best place to collect it might be. I
thought that having it set in set_ps_display() might work. Thoughts?

cheers

andrew

Andrew Dunstan wrote:
> Rod Taylor wrote:
>
> >>and I'm willing to entertain other suggestions.
> >>
> >>
> >
> >Very nice, but you missed the most important. Command Tag.
> >
> >
>
> I've had a brief look at this proposal (to allow reporting of the
> command tag along with username, database, session cookie etc on each
> log line). I'm wondering where the best place to collect it might be. I
> thought that having it set in set_ps_display() might work. Thoughts?

Command tag? Interesting addition. Yes, I would grab it just like the
ps_display does.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I am about to redo the patch that would allow tagging of log lines with
info via a printf-style string.

Current plans are to call the config parameter "log_line_info" and
implement the following escapes:

%U = user
%D = database
%T = timestamp
%P = pid
%L = session log line number
%C = sessionid cookie (hex encoded session start time + pid)
%S = session start timestamp
%I = Command Tag (e.g. "CREATE TABLE")

Any comments or suggestions before I start?

cheers

andrew

Andrew Dunstan wrote:
>
> I am about to redo the patch that would allow tagging of log lines with
> info via a printf-style string.
>
> Current plans are to call the config parameter "log_line_info" and
> implement the following escapes:
>
> %U = user
> %D = database
> %T = timestamp
> %P = pid
> %L = session log line number
> %C = sessionid cookie (hex encoded session start time + pid)
> %S = session start timestamp
> %I = Command Tag (e.g. "CREATE TABLE")
>
> Any comments or suggestions before I start?

My be kind of cool if we a a duration variable in there, especially if
combined with %I

J

>
> cheers
>
> andrew
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Joshua D. Drake wrote:
> Andrew Dunstan wrote:
> >
> > I am about to redo the patch that would allow tagging of log lines with
> > info via a printf-style string.
> >
> > Current plans are to call the config parameter "log_line_info" and
> > implement the following escapes:
> >
> > %U = user
> > %D = database
> > %T = timestamp
> > %P = pid
> > %L = session log line number
> > %C = sessionid cookie (hex encoded session start time + pid)
> > %S = session start timestamp
> > %I = Command Tag (e.g. "CREATE TABLE")
> >
> > Any comments or suggestions before I start?
>
> My be kind of cool if we a a duration variable in there, especially if
> combined with %I

Duration of what? We could show the duration within the session, but we
don't know the query duration because we print this before the query is
executed, so folks can look in the logs to see what is currently
running.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Joshua D. Drake wrote:

> Andrew Dunstan wrote:
>
>>
>> I am about to redo the patch that would allow tagging of log lines
>> with info via a printf-style string.
>>
>> Current plans are to call the config parameter "log_line_info" and
>> implement the following escapes:
>>
>> %U = user
>> %D = database
>> %T = timestamp
>> %P = pid
>> %L = session log line number
>> %C = sessionid cookie (hex encoded session start time + pid)
>> %S = session start timestamp
>> %I = Command Tag (e.g. "CREATE TABLE")
>>
>> Any comments or suggestions before I start?
>
>
> My be kind of cool if we a a duration variable in there, especially if
> combined with %I
>

Duration of what?

This patch will not generate a single extra log line. It is intended to
tag existing log lines, particularly to make creating log analysis tools
and loading logs to tables easier.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> I am about to redo the patch that would allow tagging of log lines with
> info via a printf-style string.

> Any comments or suggestions before I start?

I think Bruce already applied the previous version of your patch. No
problem with yanking it out for a better version --- but please supply
the update as a diff from CVS tip. Fewer chances for error that way.

regards, tom lane

Tom Lane said:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> I am about to redo the patch that would allow tagging of log lines
>> with info via a printf-style string.
>
>> Any comments or suggestions before I start?
>
> I think Bruce already applied the previous version of your patch.

Not that I can see. He did just apply the log_disconnections patch, but I
had split that out from this facility - back in August I did them together
in one never-applied patch.

> No
> problem with yanking it out for a better version --- but please supply
> the update as a diff from CVS tip. Fewer chances for error that way.
>

I always do a CVS update before making a patch (unless I forget).

cheers

andrew