Horton

Thread

  1. How can I have 2 completely seperated databases in PostgreSQL?

    hong.ge@yale.edu — 2004-02-11T21:36:42Z

    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

  2. Re: How can I have 2 completely seperated databases in

    scott.marlowe <scott.marlowe@ihs.com> — 2004-02-11T22:16:24Z

    On Wed, 11 Feb 2004 hong.ge@yale.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.

  3. Re: How can I have 2 completely seperated databases in

    Rod Taylor <pg@rbt.ca> — 2004-02-11T22:24:18Z

    On Wed, 2004-02-11 at 16:36, hong.ge@yale.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.

  4. Re: How can I have 2 completely seperated databases in PostgreSQL?

    hong.ge@yale.edu — 2004-02-11T22:48:51Z

    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.marlowe@ihs.com>:

> On Wed, 11 Feb 2004 hong.ge@yale.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.
>

  5. Re: How can I have 2 completely seperated databases in PostgreSQL?

    hong.ge@yale.edu — 2004-02-11T22:51:54Z

    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@rbt.ca>:

> On Wed, 2004-02-11 at 16:36, hong.ge@yale.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.
>

  6. Re: How can I have 2 completely seperated databases in

    scott.marlowe <scott.marlowe@ihs.com> — 2004-02-11T22:58:22Z

    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.ge@yale.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.marlowe@ihs.com>:
> 
> > On Wed, 11 Feb 2004 hong.ge@yale.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.
> > 
> 
> 
>

  7. Re: How can I have 2 completely seperated databases in

    scott.marlowe <scott.marlowe@ihs.com> — 2004-02-12T14:43:02Z

    On Wed, 11 Feb 2004 hong.ge@yale.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.

  8. Re: How can I have 2 completely seperated databases in

    Rod Taylor <pg@rbt.ca> — 2004-02-12T14:58:19Z

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

  9. Re: How can I have 2 completely seperated databases in PostgreSQL?

    Bruno Wolff III <bruno@wolff.to> — 2004-02-12T15:19:16Z

    On Wed, Feb 11, 2004 at 17:48:51 -0500,
  hong.ge@yale.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.

  10. Re: How can I have 2 completely seperated databases in

    Andrew Dunstan <andrew@dunslane.net> — 2004-02-12T15:35:18Z

    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

  11. Re: How can I have 2 completely seperated databases in

    Joshua D. Drake <jd@commandprompt.com> — 2004-02-12T15:35:22Z

    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.ge@yale.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@rbt.ca>:
> 
> 
>>On Wed, 2004-02-11 at 16:36, hong.ge@yale.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@postgresql.org)

  12. Re: How can I have 2 completely seperated databases in

    scott.marlowe <scott.marlowe@ihs.com> — 2004-02-12T15:37:06Z

    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.

  13. Re: How can I have 2 completely seperated databases in

    Rod Taylor <rbt@rbt.ca> — 2004-02-12T16:02:47Z

    > 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

  14. log_line_info

    Andrew Dunstan <andrew@dunslane.net> — 2004-02-17T17:45:20Z

    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

  15. Re: log_line_info

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-02-17T19:50:09Z

    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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  16. log_line_info plan

    Andrew Dunstan <andrew@dunslane.net> — 2004-02-18T02:14:28Z

    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

  17. Re: log_line_info plan

    Joshua D. Drake <jd@commandprompt.com> — 2004-02-18T03:08:42Z

    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@postgresql.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@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

  18. Re: log_line_info plan

    Bruce Momjian <pgman@candle.pha.pa.us> — 2004-02-18T03:54:08Z

    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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  19. Re: log_line_info plan

    Andrew Dunstan <andrew@dunslane.net> — 2004-02-18T04:11:04Z

    
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

  20. Re: log_line_info plan

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-02-18T04:17:28Z

    Andrew Dunstan <andrew@dunslane.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

  21. Re: log_line_info plan

    Andrew Dunstan <andrew@dunslane.net> — 2004-02-18T11:03:34Z

    Tom Lane said:
> Andrew Dunstan <andrew@dunslane.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