Thread: limit the database size
Ola,
is it possible to limit the database size,
ie like for example i create a database and then limit somehow the maximum database size.
scripts for arranging this?
tnx,
Hi Wim, It is possible to compile PostgreSQL so that it can keep each users database in their home dir. ( or another dir. of your choice. ) Then you can put a quota on the user's home dir. To do this the only way I know is to compile from source. It was about 2 years ago I did this last so I've forgetten the compile flags which you need to pass in to 'configure' or 'make'. You'll need to search the online installation docs or perhaps someone on this list can point out the required C flags. I found this method works very well. For Mysql we had to write scripts which regularly checked quota's and disk size which was no where need as elegent at PostgreSQL. Hope this helps Cheers Rudi. Wim Bertels wrote: > Ola, > > is it possible to limit the database size, > ie like for example i create a database and then limit somehow the > maximum database size. > > scripts for arranging this? > > tnx, >
Hi! I believe you don`t need to recompile, just export PGDATA=/home/me/pgdata pg_initdb pg_ctl start and ready to go. Laci Rudi Starcevic wrote: > Hi Wim, > > It is possible to compile PostgreSQL so that it can keep each users > database in their home dir. ( or another dir. of your choice. ) > Then you can put a quota on the user's home dir. > > To do this the only way I know is to compile from source. > It was about 2 years ago I did this last so I've forgetten the compile > flags which you need > to pass in to 'configure' or 'make'. > > You'll need to search the online installation docs or perhaps someone > on this list can point > out the required C flags. > > I found this method works very well. > For Mysql we had to write scripts which regularly checked quota's and > disk size which was > no where need as elegent at PostgreSQL. > > Hope this helps > Cheers > Rudi. > > Wim Bertels wrote: > >> Ola, >> >> is it possible to limit the database size, >> ie like for example i create a database and then limit somehow the >> maximum database size. >> >> scripts for arranging this? >> >> tnx, >> > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Fri, 2003-10-10 at 07:13, Laszlo Hornyak wrote: > Hi! > > I believe you don`t need to recompile, just > export PGDATA=/home/me/pgdata > pg_initdb > pg_ctl start > and ready to go. If you do this, each separate instance needs a different port. That can be set in $PGDATA/postgresql.conf. When you connect to the server (with psql or another frontend) you specify the correct port for the server you want. psql -p 5433 -d mydatabase or export PGPORT=5433 psql -d mydatabase -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "He that covereth his sins shall not prosper; but whoso confesseth and forsaketh them shall have mercy." Proverbs 28:13
Hi, If you compile/re-compile all databases are accesssible from one port. The system table pg_database stores the path to the 'base' dir which can be in the user's dir. Also in the pg_database table you can update the datdba field to change the owner of the database. An example where this is very cool is if the user logs into their database via a web interface like phpPgAdmin they can log into just their database and not anyone else's. Pretty much ideal for a hosting situation. Does anyone have the C compile flags handy ? Cheers Best regards Rudi. Oliver Elphick wrote: >On Fri, 2003-10-10 at 07:13, Laszlo Hornyak wrote: > > >>Hi! >> >>I believe you don`t need to recompile, just >>export PGDATA=/home/me/pgdata >>pg_initdb >>pg_ctl start >>and ready to go. >> >> > >If you do this, each separate instance needs a different port. That can >be set in $PGDATA/postgresql.conf. > >When you connect to the server (with psql or another frontend) you >specify the correct port for the server you want. > > psql -p 5433 -d mydatabase > >or > > export PGPORT=5433 > psql -d mydatabase > > > >
Correct me if I am wrong but limiting storage size under the DB is not always the right thing to do. Once my db ran out of space when vaacum-ing at night, and one of the indexes damaged, so, I could not do selects using that index, i had to drop it, add some storage, recreate, vacumanalyze, etc. Laszlo Hornyak Rudi Starcevic wrote: > Hi, > > If you compile/re-compile all databases are accesssible from one port. > The system table pg_database stores the path to the 'base' dir which > can be in the user's dir. > > Also in the pg_database table you can update the datdba field to > change the owner of the database. > An example where this is very cool is if the user logs into their > database via a web interface like > phpPgAdmin they can log into just their database and not anyone else's. > Pretty much ideal for a hosting situation. > > Does anyone have the C compile flags handy ? > > Cheers > Best regards > Rudi. > > > > > Oliver Elphick wrote: > >> On Fri, 2003-10-10 at 07:13, Laszlo Hornyak wrote: >> >> >>> Hi! >>> >>> I believe you don`t need to recompile, just >>> export PGDATA=/home/me/pgdata >>> pg_initdb >>> pg_ctl start >>> and ready to go. >>> >> >> >> If you do this, each separate instance needs a different port. That can >> be set in $PGDATA/postgresql.conf. >> >> When you connect to the server (with psql or another frontend) you >> specify the correct port for the server you want. >> >> psql -p 5433 -d mydatabase >> >> or >> >> export PGPORT=5433 >> psql -d mydatabase >> >> >> >> > > > > ---------------------------(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
On Fri, 2003-10-10 at 08:13, Rudi Starcevic wrote: > Hi, > > If you compile/re-compile all databases are accesssible from one port. > The system table pg_database stores the path to the 'base' dir which can > be in the user's dir. We were talking about separate database structures (as created by initdb), not separate databases within one structure. Each separate structure requires a postmaster. You cannot run different postmaster instances simultaneously unless you differentiate them by using different ports. The point of the original enquiry was for people to have their own separate database structures, which could be subject to disk quotas; they will not want to queue up to use them, will they? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "He that covereth his sins shall not prosper; but whoso confesseth and forsaketh them shall have mercy." Proverbs 28:13
> We were talking about separate database structures (as created by > initdb), not separate databases within one structure. Each separate > structure requires a postmaster. > > You cannot run different postmaster instances simultaneously unless you > differentiate them by using different ports. The point of the original > enquiry was for people to have their own separate database > structures, which could be subject to disk quotas; they will not > want to queue up to use them, will they? Yeah I see, sorry I must be thinking too much of the similarities to another situation instead of the original enquiry. I'd like to add a note on how something similar works for me. Hope that's OK. In this case we provide some hosting for uses with Postgresql/PHP/Perl. So a new user gets a new home dir. and Pg creates the new user a database which is the their new home dir. - the 'base' folder. The owner is Postgresql so the new user cannot write into the 'base' folder in their home dir. So the user has one linux account and one Postgresql account. Then change ownership of the database, which was created by Postgres, to the new user's Postgresql account by updating the pg_database table. So now the new user has a Linux shell account with a sub folder 'www'. The user can ssh log in and write PHP/Perl scripts and use PSQL to access their own database using their Postgresql account. We add a quota, say 100 meg, which will limit everything including their database. Sorry I can't report on what happens if the database hits the quota as I haven't had to deal with that yet. So far any user who comes close to filling their quota is alerted and takes appropriate measures, which may include upgrading to a higher plan. I've got regular vacuum's happening on all databases with some cron jobs using the Postgres account but each user can add their own custom cron jobs if they like. So far it's all good. Maybe I will be pointed out to be in error somewhere but I find this technique to be simple,elegant and powerful. Thanks. Regards Rudi.
There's also the initlocation $ENVAR solution: http://www.postgresql.org/docs/7.3/static/manage-ag-alternate-locs.html On Fri, 10 Oct 2003, Rudi Starcevic wrote: > Hi Wim, > > It is possible to compile PostgreSQL so that it can keep each users > database in their home dir. ( or another dir. of your choice. ) > Then you can put a quota on the user's home dir. > > To do this the only way I know is to compile from source. > It was about 2 years ago I did this last so I've forgetten the compile > flags which you need > to pass in to 'configure' or 'make'. > > You'll need to search the online installation docs or perhaps someone on > this list can point > out the required C flags. > > I found this method works very well. > For Mysql we had to write scripts which regularly checked quota's and > disk size which was > no where need as elegent at PostgreSQL. > > Hope this helps > Cheers > Rudi. > > Wim Bertels wrote: > > > Ola, > > > > is it possible to limit the database size, > > ie like for example i create a database and then limit somehow the > > maximum database size. > > > > scripts for arranging this? > > > > tnx, > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
> There's also the initlocation $ENVAR solution: > > http://www.postgresql.org/docs/7.3/static/manage-ag-alternate-locs.html Yes this document has the C flags to use: gmake CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS all The last line, I kept overlooking it - doh!. My last rambling email is based exactly on this document buts adds things like Database ownership/access and quota/limits. Nice one. Cheers Rudi.