pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Separating Databases on Different Partitions
5+ messages / 4 participants
[nested] [flat]

* Separating Databases on Different Partitions
@ 2006-05-30 15:11 Aaron Bono <postgresql@aranya.com>
  2006-05-30 15:28 ` Re: Separating Databases on Different Partitions Andy Shellam <andy.shellam@mailnetwork.co.uk>
  0 siblings, 1 reply; 5+ messages in thread

From: Aaron Bono @ 2006-05-30 15:11 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Separating Databases on Different Partitions
  2006-05-30 15:11 Separating Databases on Different Partitions Aaron Bono <postgresql@aranya.com>
@ 2006-05-30 15:28 ` Andy Shellam <andy.shellam@mailnetwork.co.uk>
  2006-05-30 15:44   ` Re: Separating Databases on Different Partitions Tom Lane <tgl@sss.pgh.pa.us>
  2006-05-30 16:04   ` Re: Separating Databases on Different Partitions Aaron Bono <postgresql@aranya.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Andy Shellam @ 2006-05-30 15:28 UTC (permalink / raw)
  To: pgsql-admin

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! 

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Separating Databases on Different Partitions
  2006-05-30 15:11 Separating Databases on Different Partitions Aaron Bono <postgresql@aranya.com>
  2006-05-30 15:28 ` Re: Separating Databases on Different Partitions Andy Shellam <andy.shellam@mailnetwork.co.uk>
@ 2006-05-30 15:44   ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Tom Lane @ 2006-05-30 15:44 UTC (permalink / raw)
  To: andy.shellam@mailnetwork.co.uk; +Cc: pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Separating Databases on Different Partitions
  2006-05-30 15:11 Separating Databases on Different Partitions Aaron Bono <postgresql@aranya.com>
  2006-05-30 15:28 ` Re: Separating Databases on Different Partitions Andy Shellam <andy.shellam@mailnetwork.co.uk>
@ 2006-05-30 16:04   ` Aaron Bono <postgresql@aranya.com>
  2006-05-30 16:36     ` Re: Separating Databases on Different Partitions Andy Shellam <andy@andycc.net>
  1 sibling, 1 reply; 5+ messages in thread

From: Aaron Bono @ 2006-05-30 16:04 UTC (permalink / raw)
  To: andy.shellam@mailnetwork.co.uk; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Separating Databases on Different Partitions
  2006-05-30 15:11 Separating Databases on Different Partitions Aaron Bono <postgresql@aranya.com>
  2006-05-30 15:28 ` Re: Separating Databases on Different Partitions Andy Shellam <andy.shellam@mailnetwork.co.uk>
  2006-05-30 16:04   ` Re: Separating Databases on Different Partitions Aaron Bono <postgresql@aranya.com>
@ 2006-05-30 16:36     ` Andy Shellam <andy@andycc.net>
  0 siblings, 0 replies; 5+ messages in thread

From: Andy Shellam @ 2006-05-30 16:36 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-admin

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! 




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2006-05-30 16:36 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-30 15:11 Separating Databases on Different Partitions Aaron Bono <postgresql@aranya.com>
2006-05-30 15:28 ` Andy Shellam <andy.shellam@mailnetwork.co.uk>
2006-05-30 15:44   ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-30 16:04   ` Aaron Bono <postgresql@aranya.com>
2006-05-30 16:36     ` Andy Shellam <andy@andycc.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox