Обсуждение: Separating Databases on Different Partitions
The reason I am wanting to do this is that I have a database set up for each user on my server. I need to put their database under their home directory somewhere so I know how much disk space each user account is taking. I also need to put the database under the /home partition as it has a lot more space than the partition containing the data directory.
Thanks,
Aaron
Hi Aaron
I believe you could probably do this best with table spaces. You’ll have to look it up in the documentation as I haven’t done it for a while.
You symlink a directory under <pgdata>/pg_tblspc to another physical directory (eg. Your user’s home/db directory), then when you create the database, set its default table space to which directory you create.
There is a downside in that the user could create a new table and override the table space to that of another user.
You could always query the system catalogues for the usage for each user’s database and add that to the physical size of their home directory?
Regards,
Andy
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Aaron Bono
Sent: 30 May 2006 4:11 pm
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Separating Databases on Different Partitions
Is there a way to get Postgres to store each database in a separate directory? I first hoped I could just use symbolic links under the data directory but I have no idea which files are assigned to which database.
The reason I am wanting to do this is that I have a database set up for each user on my server. I need to put their database under their home directory somewhere so I know how much disk space each user account is taking. I also need to put the database under the /home partition as it has a lot more space than the partition containing the data directory.
Thanks,
Aaron
!DSPAM:14,447c60ea34499754912401!
"Andy Shellam" <andy.shellam@mailnetwork.co.uk> writes: > I believe you could probably do this best with table spaces. Right, that's the thing to use. > There is a downside in that the user could create a new table and override > the table space to that of another user. Not with appropriate permissions set on the tablespaces. regards, tom lane
Thanks for the help!
-Aaron
Hi Aaron
I believe you could probably do this best with table spaces. You'll have to look it up in the documentation as I haven't done it for a while.
You symlink a directory under <pgdata>/pg_tblspc to another physical directory (eg. Your user's home/db directory), then when you create the database, set its default table space to which directory you create.
There is a downside in that the user could create a new table and override the table space to that of another user.
You could always query the system catalogues for the usage for each user's database and add that to the physical size of their home directory?
Regards,
Andy
Ah, fair enough, table spaces are definitely the way to go then. I did use it about a year ago to put one database on a different physical drive, but I haven't used it for ages so cannot remember how to set it up. Andy Aaron Bono wrote: > I think the tablespace option will work best - I will check it out. I > do not give the users control of the database since I do the > development, I just need a way to allocate the space and charge for > space used. Since cPanel monitors user space, I don't want to rely on > having to query the database to get size information - I would have to > put together separate scripts to watch space usage which I would > rather not do. > > Thanks for the help! > > -Aaron > > On 5/30/06, *Andy Shellam* <andy.shellam@mailnetwork.co.uk > <mailto:andy.shellam@mailnetwork.co.uk> > wrote: > > Hi Aaron > > > > I believe you could probably do this best with table spaces. > You'll have to look it up in the documentation as I haven't done > it for a while. > > You symlink a directory under <pgdata>/pg_tblspc to another > physical directory (eg. Your user's home/db directory), then when > you create the database, set its default table space to which > directory you create. > > > > There is a downside in that the user could create a new table and > override the table space to that of another user. > > > > You could always query the system catalogues for the usage for > each user's database and add that to the physical size of their > home directory? > > > > Regards, > > > > Andy > > > !DSPAM:14,447c6d25256841812113538!