Thread: How can I have 2 completely seperated databases in PostgreSQL?
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, 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.
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.
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. >
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. >
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. > > > > >
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.
> 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.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.
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.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)
Attachment
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@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
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@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
Attachment
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, Pennsylvania19073
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@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
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