PostgreSQL: How to create databases in to different directories?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marko - 04 Sep 2004 11:35 GMT

Hi.

I have been trying to use PostgreSQL and the manual does not explain
clearly enough how to make databases into different directories, or is
it even possible?

So, for example there are two users (Foo and Bar) in linux environment.
Their home directories would then be:

/home/foo
/home/bar

So, both them would need a their own database. And Foo would like to
have it under '/home/foo/db/' and Bar under '/home/bar/db/'. There is a
simple reason for it. They both have their own quota, and I would like
to include their own databases in to the quota.

Any suggestions?

Reply to this Message

Christopher Browne - 04 Sep 2004 14:52 GMT

Oops! Marko <marko@foo.bar> was seen spray-painting on a wall:

> Hi.
>
[quoted text clipped - 14 lines]
>
> Any suggestions?

The way to handle this is to run "initdb" twice, once for each user,
and for the respective users' databases to run under their user IDs as
opposed to as the "postgres" user.

See the initdb documentation for details, paying particular attention
to the "-D" option.

This means assigning each of those users their own port number for a
quite completely separate database cluster.

Signature

output = reverse("gro.mca" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
If you find this set of fortunes entertaining, and would like a copy,
see <http://cbbrowne.com/downloads/>

Reply to this Message

Marko - 04 Sep 2004 20:02 GMT

> The way to handle this is to run "initdb" twice, once for each user,
> and for the respective users' databases to run under their user IDs as
[quoted text clipped - 5 lines]
> This means assigning each of those users their own port number for a
> quite completely separate database cluster.

Oh no... This is no good... I was hoping to find similar kind of
solutions as in MySQL. I mean there each database is in separate
directory, which can then, in my understanding, to be symlinked to
somewhere else. I wouldn't like to use MySQL, but it seems I have no choice.

Reply to this Message

Christopher Browne - 04 Sep 2004 23:52 GMT

>> The way to handle this is to run "initdb" twice, once for each user,
>> and for the respective users' databases to run under their user IDs as
[quoted text clipped - 3 lines]
>> This means assigning each of those users their own port number for a
>> quite completely separate database cluster.

> Oh no... This is no good... I was hoping to find similar kind of
> solutions as in MySQL. I mean there each database is in separate
> directory, which can then, in my understanding, to be symlinked to
> somewhere else. I wouldn't like to use MySQL, but it seems I have no
> choice.

PostgreSQL Version 8, presently beta testing, supports tablespaces,
which allows data to be rearranged to go pretty much anywhere.

Mind you, you haven't explained why the previous answer was "no good,"
and absent of that, it is unlikely that anyone can help you.

Signature

wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www.ntlug.org/~cbbrowne/x.html
"But life wasn't yes-no, on-off. Life was shades of gray, and
rainbows not in the order of the spectrum."
-- L. E. Modesitt, Jr., _Adiamante_

Reply to this Message

Marko - 05 Sep 2004 19:48 GMT

> PostgreSQL Version 8, presently beta testing, supports tablespaces,
> which allows data to be rearranged to go pretty much anywhere.
>
> Mind you, you haven't explained why the previous answer was "no good,"
> and absent of that, it is unlikely that anyone can help you.

Well, the problem was that the two users were merely an example. In
reality there could be dozens of users, and I can't start assigning
diffent ports to each one. Anyway, I found Firebird database, which
suits to my needs perfectly. But, thank you for you help :)

Reply to this Message

michael newport - 06 Sep 2004 13:21 GMT

use Ingres, its mature and its free !

Reply to this Message

Karl A. Krueger - 08 Sep 2004 03:50 GMT

> use Ingres, its mature and its free !

Considering the recent content-free Slashdot interview regarding Ingres,
I have to wonder if you're from CA?

Signature

Karl A. Krueger <kkrueger@example.edu>
Woods Hole Oceanographic Institution
Email address is spamtrapped. s/example/whoi/
"Outlook not so good." -- Magic 8-Ball Software Reviews

Reply to this Message

michael newport - 10 Sep 2004 20:35 GMT

if you check out my comments in comp.databases.ingres
you will see that I am definitely NOT from CA

but I stand by my comments, if you know any different please let me know !

Regards
Michael Newport

Reply to this Message

Gene Wirchenko - 11 Sep 2004 04:29 GMT

>> use Ingres, its mature and its free !
>
>Considering the recent content-free Slashdot interview regarding Ingres,
>I have to wonder if you're from CA?

Canada, California, Computer Associates?

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Reply to this Message

Laconic2 - 11 Sep 2004 12:44 GMT

> Canada, California, Computer Associates?

Ah! it's another argument against the OTLT! Case closed!

Reply to this Message

Karl A. Krueger - 11 Sep 2004 17:30 GMT

>>> use Ingres, its mature and its free !
>>
>>Considering the recent content-free Slashdot interview regarding Ingres,
>>I have to wonder if you're from CA?
>
> Canada, California, Computer Associates?

Competitor Acquisitions.

Signature

Karl A. Krueger <kkrueger@example.edu>
Woods Hole Oceanographic Institution
Email address is spamtrapped. s/example/whoi/
"Outlook not so good." -- Magic 8-Ball Software Reviews

Reply to this Message

Koei Kim - 28 Sep 2004 18:34 GMT

> Hi.
>
[quoted text clipped - 14 lines]
>
> Any suggestions?

If you need one postgres daemon and have multi database, use separately
database,
use initlocation with environment variable

PostgreSQL 8.0 beta support Tablespace.
but your question is HOW-TO-Create-Database-Separately-Location

HOWTO example :

$ initdb # with your encoding
$ export PG_DATA_MYDB1=/var/lib/pgsql/mydb1
# please regiser it pgsql(postgres user)' bashrc
$ initlocation PG_DATA_MYDB1 # now, created /var/lib/pgsql/mydb1
$ createdb MYDB1 --location=PG_DATA_MYDB1 # db create DDL (alternative
CREATE DATABASE ... )

Reply to this Message