[ADMIN] VACUUM possible bug
Hi to all, After upgrading to 7.1, I have noticed that when doing a vacuum, the postmaster usually crashes. Noticed also that the available space is 0 bytes free. The pg_xlog directory then contains a large number fo WAL-files. Tables size are about 7GB for a total database size of over 50GB. Question is why do the WAL-files increase during the VACUUM ? I normally have aroung 15GB free space on the drive, so even if a table is re-copied, there should still be space ? Does not vacuum commit after each table, or does it commit after the whole operation ? If so, is it possible to make vacuum commit after each table (without doing it manually by vacuuming one table at a time) ? I'm currently upgrading to 7.1.2, maybe the bug has been fixed in this version. Thanks Robert ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[ADMIN] data/base file structure
Hi all, can somebody explain to me, or point to an explanation of, the way, PostGreSQL names databases?I have files like 18732 and 7503034 - and they are big but I have no idea which data lives in what file: /home/pgsql/data/base/18720/ -rw--- 1 postgres postgres 70385664 May 24 13:55 7503034-rw--- 1 postgres postgres 10788864 May 24 13:54 8748869-rw--- 1 postgres postgres 458752 May 24 12:21 8939786-rw--- 1 postgres postgres 158334976 May 24 13:51 8945104 Best regards, Chris
Re: [ADMIN] data/base file structure
Chris Ruprecht wrote: Hi all, can somebody explain to me, or point to an explanation of, the way, PostGreSQL names databases?I have files like 18732 and 7503034 - and they are big but I have no idea which data lives in what file: /home/pgsql/data/base/18720/ -rw---1 postgres postgres 70385664 May 24 13:55 7503034 -rw---1 postgres postgres 10788864 May 24 13:54 8748869 -rw---1 postgres postgres 458752 May 24 12:21 8939786 -rw---1 postgres postgres 158334976 May 24 13:51 8945104 In 7.1 and later, there is one database per directory and the directories are named $PGDATA/base/oid, where oid is the oid of the pg_database entry. Regards, Oliver ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] installation of 7.1.2 with tcl/tk
Hi, all, I need your help. If I config without tcl, compile seems fine. However, when I config with tcl/tk, I always got some error, similar case occurs when I tried to install it via ports. my OS is FreeBSD4.3 and postgresql-7.1.2, any idea?? I couldn't find tcl.h I got following when installing: ./configure --with-perl --with-tcl --with-tk --with-CXX --with-setproctitle --with-tclconfig=/usr/local/lib/tcl8.3/ --with-tkconfig=/usr/local/lib/tk8.3/ gmake gmake[3]: Leaving directory `/usr/local/postgresql-7.1.2/src/interfaces/libpq++' gmake[3]: Entering directory `/usr/local/postgresql-7.1.2/src/interfaces/libpgtcl' /usr/local/bin/gmake -C ../../../src/interfaces/libpq all gmake[4]: Entering directory `/usr/local/postgresql-7.1.2/src/interfaces/libpq' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/usr/local/postgresql-7.1.2/src/interfaces/libpq' gcc -pipe -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -fpic -DPIC -I../../../src/interfaces/libpq -I../../../src/include -c -o pgtcl.o pgtcl.c In file included from pgtcl.c:19: libpgtcl.h:19: tcl.h: No such file or directory In file included from pgtcl.c:20: pgtclCmds.h:17: tcl.h: No such file or directory In file included from pgtcl.c:19: libpgtcl.h:21: syntax error before `*' libpgtcl.h:22: syntax error before `*' In file included from pgtcl.c:20: pgtclCmds.h:44: syntax error before `Tcl_Interp' pgtclCmds.h:80: syntax error before `cData' pgtclCmds.h:82: syntax error before `cData' pgtclCmds.h:84: syntax error before `cData' pgtclCmds.h:86: syntax error before `cData' pgtclCmds.h:88: syntax error before `cData' pgtclCmds.h:90: syntax error before `cData' pgtclCmds.h:92: syntax error before `cData' pgtclCmds.h:94: syntax error before `cData' pgtclCmds.h:96: syntax error before `cData' pgtclCmds.h:108: syntax error before `cData' pgtclCmds.h:110: syntax error before `cData' pgtclCmds.h:114: syntax error before `cData' pgtclCmds.h:116: syntax error before `cData' pgtclCmds.h:118: syntax error before `cData' pgtclCmds.h:120: syntax error before `cData' pgtclCmds.h:122: syntax error before `cData' pgtclCmds.h:124: syntax error before `cData' pgtclCmds.h:126: syntax error before `cData' In file included from pgtcl.c:21: pgtclId.h:18: syntax error before `*' pgtclId.h:37: syntax error before `*' pgtclId.h:39: syntax error before `cData' pgtclId.h:40: syntax error before `cData' pgtclId.h:41: syntax error before `cData' pgtclId.h:42: syntax error before `*' pgtclId.h:43: syntax error before `*' pgtclId.h:44: syntax error before `*' pgtclId.h:45: syntax error before `*' pgtclId.h:49: syntax error before `clientData' pgtclId.h:63: syntax error before `Pg_ConnType' pgtclId.h:43: syntax error before `*' pgtclId.h:44: syntax error before `*' pgtclId.h:45: syntax error before `*' pgtclId.h:49: syntax error before `clientData' pgtclId.h:63: syntax error before `Pg_ConnType' pgtclId.h:63: warning: type defaults to `int' in declaration of `Pg_ConnType' pgtclId.h:63: warning: data definition has no type or storage class pgtcl.c:30: syntax error before `*' pgtcl.c:31: warning: no previous prototype for `Pgtcl_Init' pgtcl.c: In function `Pgtcl_Init': pgtcl.c:43: warning: implicit declaration of function `Tcl_CreateCommand' pgtcl.c:43: `interp' undeclared (first use in this function) pgtcl.c:43: (Each undeclared identifier is reported only once pgtcl.c:43: for each function it appears in.) pgtcl.c:46: `ClientData' undeclared (first use in this function) pgtcl.c:46: syntax error before `0' pgtcl.c:51: syntax error before `0' pgtcl.c:56: syntax error before `0' pgtcl.c:61: syntax error before `0' pgtcl.c:66: syntax error before `0' pgtcl.c:71: syntax error before `0' pgtcl.c:76: syntax error before `0' pgtcl.c:81: syntax error before `0' pgtcl.c:86: syntax error before `0' pgtcl.c:102: syntax error before `0' pgtcl.c:107: syntax error before `0' pgtcl.c:113: syntax error before `0' pgtcl.c:118: syntax error before `0' pgtcl.c:123: syntax error before `0' pgtcl.c:128: syntax error before `0' pgtcl.c:133: syntax error before `0' pgtcl.c:138: syntax error before `0' pgtcl.c:143: syntax error before `0' pgtcl.c:145: warning: implicit declaration of function `Tcl_PkgProvide' pgtcl.c:147: `TCL_OK' undeclared (first use in this function) pgtcl.c:148: warning: control reaches end of non-void function pgtcl.c: At top level: pgtcl.c:152: syntax error before `*' pgtcl.c:153: warning: no previous prototype for `Pgtcl_SafeInit' pgtcl.c: In function `Pgtcl_SafeInit': pgtcl.c:154: `interp' undeclared (first use in this function) gmake[3]: *** [pgtcl.o] Error 1 gmake[3]: Leaving directory `/usr/local/postgresql-7.1.2/src/interfaces/libpgtcl' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/usr/local/postgresql-7.1.2/src/interfaces' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/usr/local/postgresql-7.1.2/src' gmake: *** [all] Error 2 *** Error code 2 Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121
Re: [ADMIN] installation of 7.1.2 with tcl/tk
Jie Liang [EMAIL PROTECTED] writes: ./configure --with-perl --with-tcl --with-tk --with-CXX --with-setproctitle --with-tclconfig=/usr/local/lib/tcl8.3/ --with-tkconfig=/usr/local/lib/tk8.3/ Looks like you need --with-includes (and probably --with-libs, as well). tclConfig.sh does not give any hints about where to find Tcl's includes... so if you don't have them in the standard include search path, it's up to you to tell configure where to look. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] installation of 7.1.2 with tcl/tk
yes, I solved it. thanks anyway. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 4 Jun 2001, Tom Lane wrote: Jie Liang [EMAIL PROTECTED] writes: ./configure --with-perl --with-tcl --with-tk --with-CXX --with-setproctitle --with-tclconfig=/usr/local/lib/tcl8.3/ --with-tkconfig=/usr/local/lib/tk8.3/ Looks like you need --with-includes (and probably --with-libs, as well). tclConfig.sh does not give any hints about where to find Tcl's includes... so if you don't have them in the standard include search path, it's up to you to tell configure where to look. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Limiting simultaneous connections
Is there a way to limit the number of simultaneous connections that any given username can make to the database? For instance, I'd like user1 to be able to have no more than 5 simultaneous connections to the database server, but user2 can have 15 simultaneous connections. Also, can these connections be limited on a per user for the entire database server basis? Or can they be limited on a per user per database basis? By this I mean, can a user create up to 5 connections to one of their databases and then another 5 to one of their other databases (total of 10 connections across the whole database server). Or can they be limited to a total of 5 for any connections to any of their databases? I will be using either 7.1.1 from RPM or 7.1.2 from source on Red Hat Linux 6.2 and 7.1. Thanks! Tauren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Alternate database locations
Peter Eisentraut [EMAIL PROTECTED] writes: However, this page indicates that there are security risks in doing this. What are those risks? If I prevent users other than the superuser postgres from being able to create and drop databases, are there any security risks? No. The risks are related to the fact that non-superusers can also be allowed to create databases. If the user's DB area is in his home directory, then he can presumably rename it, leading to nasty problems when operations like CHECKPOINT try to write to files in it. At the very least you'd have potential for denial of service to all the other users. Lastly, I've even tried creating a database normally, then moving it to the user's area and creating a symlink to it. But this didn't seem to work. It should, since that is what the official mechanisms do as well. Yes, I'd have thought that would work. Define didn't seem to work, please. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [ADMIN] Alternate database locations
Thanks for the feedback! However, this page indicates that there are security risks in doing this. What are those risks? If I prevent users other than the superuser postgres from being able to create and drop databases, are there any security risks? No. The risks are related to the fact that non-superusers can also be allowed to create databases. If the user's DB area is in his home directory, then he can presumably rename it, leading to nasty problems when operations like CHECKPOINT try to write to files in it. At the very least you'd have potential for denial of service to all the other users. OK, this makes sense. In the user's home directory, I was going to create a directory called something like .db. It would be owned by postgres:root and have 700 permissions. Within it, would be the base directory as postgres:postgres and 700. This is very similar to the way that we deploy MySQL. But this does still allow the user to change the directory name. With MySQL, it would just cause their database to not work, it wouldn't cause problems with the server. It sounds like this is not a good idea with PostgreSQL. Is there any way to keep each customer's database within their user area without the chance of breaking the database server? Lastly, I've even tried creating a database normally, then moving it to the user's area and creating a symlink to it. But this didn't seem to work. It should, since that is what the official mechanisms do as well. Yes, I'd have thought that would work. Define didn't seem to work, please. Based on what you said, I went back and tried it again. It seems to work correctly now. I think I must not have had the proper permissions set the first time I tried it. Even though this is exactly the solution I was looking for, I'm now reluctant to use it since a user could break things by simply changing a directory name. I guess I'll have to keep the databases separate from the user's home area. Thanks, Tauren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [ADMIN] Alternate database locations
Tauren Mills [EMAIL PROTECTED] writes: Is there any way to keep each customer's database within their user area without the chance of breaking the database server? If you want completely decoupled service for each user, then give each of them their own independent installation, running as their own process. Anything short of that raises risks of cross-user denial of service. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
