Thread: Database size
Dear community, I would be very grateful if someone could answer me, where to find instructions how to set the size ( in megabytes ) for particular database. best regards, N.Radakovic
select pg_database_size('<dbname>')/1024/1024;
http://www.postgresql.org/docs/8.1/interactive/functions-admin.html
http://www.postgresql.org/docs/8.1/interactive/functions-admin.html
On 11/6/06, Nikola Radakovic <achillea@hi.t-com.hr> wrote:
Dear community,
I would be very grateful if someone could answer me,
where to find instructions how to set the size ( in megabytes ) for
particular database.
best regards,
N.Radakovic
---------------------------(end of broadcast)---------------------------
TIP 1: 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
> I would be very grateful if someone could answer me, > where to find instructions how to set the size ( in megabytes ) for > particular database. I don't understand the question, are you looking to limit the size of your database? Regards, Richard Broersma Jr.
Nikola, I'm not exactly sure what you are asking. The size of the database is usually determined by the amount of data stored and not determined beforehand. Are you refering of "tablespaces", as other DBMSs use them? If so, Postgres has a tablespace command, but it simply points to a filesystem location. There is no "size" specified. The size of the database is simply limited by the available filesystem capacity (I suppose there may be some restrictions at the high-end). Mike On Mon, 2006-11-06 at 14:48 -0600, Nikola Radakovic wrote: > Dear community, > > I would be very grateful if someone could answer me, > where to find instructions how to set the size ( in megabytes ) for > particular database. > > > best regards, > N.Radakovic > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: 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 -- Mike Goldner Vice President Networks and Technology AG Mednet, Inc. The Pilot House Lewis Wharf Boston, MA 02110 617.854.3225 (office) 617.909.3009 (mobile)
> I appreciate your help, You don't understand me because my English is > awful. > I would try to paraphrase the sentence-maybe I succeed to correctly > express myself.I want to limit database space for each database user on > my system.For example, One user can hold one or more databases, but I > want to limit him in order that he isn't able to store data which will > exceed some limit(in megabytes) which I'll set. > Something like in webhosting, where each user has limited database space > on usage. I see what you are trying to achieve. Also, dont forget to reply-all when you reply to email from the postgresql lists so that everyone on the lists are copied with your emails. This way everyone on the list can participate in this discussion. This is important since, I can not think of an answer for your question. :o) Regards, Richard Broersma Jr.
Setting user's quota on my linux machine wont affect database quota.It will have impact on users directories.So, it must be a trick how to set PostgreSQL quota. Or how to link PostgreSQL database with directories which already have set quotas. On Mon, 2006-11-06 at 16:52 -0500, Mike Goldner wrote: > On Mon, 2006-11-06 at 15:41 -0600, Nikola Radakovic wrote: > > Or, briefly how to set Postgresql user quota? > > > > On Mon, 2006-11-06 at 16:01 -0500, Mike Goldner wrote: > > > Nikola, > > > > > > I'm not exactly sure what you are asking. > > > > > > The size of the database is usually determined by the amount of data > > > stored and not determined beforehand. > > > > > > Are you refering of "tablespaces", as other DBMSs use them? If so, > > > Postgres has a tablespace command, but it simply points to a filesystem > > > location. There is no "size" specified. > > > > > > The size of the database is simply limited by the available filesystem > > > capacity (I suppose there may be some restrictions at the high-end). > > > > > > Mike > > > > > > On Mon, 2006-11-06 at 14:48 -0600, Nikola Radakovic wrote: > > > > Dear community, > > > > > > > > I would be very grateful if someone could answer me, > > > > where to find instructions how to set the size ( in megabytes ) for > > > > particular database. > > > > > > > > > > > > best regards, > > > > N.Radakovic > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > TIP 1: 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 > > > > Nikola, > > Setting quotas is operating system-dependent. If you are using linux, > take a look at http://yolinux.com/TUTORIALS/LinuxTutorialQuotas.html >
On Mon, 2006-11-06 at 16:29, Nikola Radakovic wrote: > Setting user's quota on my linux machine wont affect database > quota.It will have impact on users directories.So, it must > be a trick how to set PostgreSQL quota. Or how to link PostgreSQL > database with directories which already have set quotas. PostgreSQL itself has no ability to set quotas. You can limit the amount of data a particular table space uses, either by drive size or quota. But for individual users, I don't think there's a solution.
On Mon, 2006-11-06 at 16:29 -0600, Nikola Radakovic wrote: > Setting user's quota on my linux machine wont affect database > quota.It will have impact on users directories.So, it must > be a trick how to set PostgreSQL quota. Or how to link PostgreSQL > database with directories which already have set quotas. > > > > > On Mon, 2006-11-06 at 16:52 -0500, Mike Goldner wrote: > > On Mon, 2006-11-06 at 15:41 -0600, Nikola Radakovic wrote: > > > Or, briefly how to set Postgresql user quota? > > > > > > On Mon, 2006-11-06 at 16:01 -0500, Mike Goldner wrote: > > > > Nikola, > > > > > > > > I'm not exactly sure what you are asking. > > > > > > > > The size of the database is usually determined by the amount of data > > > > stored and not determined beforehand. > > > > > > > > Are you refering of "tablespaces", as other DBMSs use them? If so, > > > > Postgres has a tablespace command, but it simply points to a filesystem > > > > location. There is no "size" specified. > > > > > > > > The size of the database is simply limited by the available filesystem > > > > capacity (I suppose there may be some restrictions at the high-end). > > > > > > > > Mike > > > > > > > > On Mon, 2006-11-06 at 14:48 -0600, Nikola Radakovic wrote: > > > > > Dear community, > > > > > > > > > > I would be very grateful if someone could answer me, > > > > > where to find instructions how to set the size ( in megabytes ) for > > > > > particular database. > > > > > > > > > > > > > > > best regards, > > > > > N.Radakovic > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > > > TIP 1: 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 > > > > > > > Nikola, > > > > Setting quotas is operating system-dependent. If you are using linux, > > take a look at http://yolinux.com/TUTORIALS/LinuxTutorialQuotas.html > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org Assuming that you are running Postgres on a linux system, you could do the following: 1) Create a partition with quotas enabled (via standard linux OS procedures). The postgres user is the user for which quotas should be enforced since postgresql always runs under the postgres uid. 2) Create a postgres tablespace using the newly created partition: psql> CREATE TABLESPACE dbspace LOCATION '/data/dbs'; 3) Create the database on that tablespace: psql> CREATE DATABASE sales OWNER salesapp TABLESPACE dbspace; I have not tried this myself. As a side note, I'd say that limiting the size of a database to an arbitrary amount is problematic. Should a user actually exhaust the disk quota, Postgres will be unable to commit the transaction. Furthermore, it is difficult to recover from such a situation without major effort. -- Mike Goldner Vice President Networks and Technology AG Mednet, Inc. The Pilot House Lewis Wharf Boston, MA 02110 617.854.3225 (office) 617.909.3009 (mobile)
> Assuming that you are running Postgres on a linux system, you could do > the following: > > 1) Create a partition with quotas enabled (via standard linux OS > procedures). The postgres user is the user for which quotas should be > enforced since postgresql always runs under the postgres uid. > > 2) Create a postgres tablespace using the newly created partition: > > psql> CREATE TABLESPACE dbspace LOCATION '/data/dbs'; > > 3) Create the database on that tablespace: > > psql> CREATE DATABASE sales OWNER salesapp TABLESPACE dbspace; > > I have not tried this myself. > > As a side note, I'd say that limiting the size of a database to an > arbitrary amount is problematic. Should a user actually exhaust the > disk quota, Postgres will be unable to commit the transaction. > Furthermore, it is difficult to recover from such a situation without > major effort. These links supplement the above comments: http://www.postgresql.org/docs/8.1/interactive/disk-full.html http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html Regards, Richard Broersma Jr.