[ADMIN] configure error with C compiler
I've just downloaded the 6.4.2 source and was trying to install it on RedHat 5.2 running on Pentium system. When I did ./configure --with-perl --with-odbc, it failed during compiler check: checking whether the C compiler (gcc -O2) works... no configure: error: installation or configuration problem: C compiler cannot create executables. Can anyone point me some directions? Thanks. Daniel
[ADMIN] install problem
Hello, This actually has to do with a module(DBD-Pg-o.89), so I'm not sure if this is the right place to ask. But, I'm having trouble building it. I'm using Perl 5.004.04 and I was able to compile DBI-1.05 just fine. If this isn't the right place to ask please point me in the right direction. Thanks. Here is the error I'm getting: gcc -c -I/home/postgres/include -I/usr/local/include/pgsql -I/usr/include/pgsql -I/usr/local/lib/perl5/site_perl/sun4-solaris/auto/DBI -I/usr/local/lib/perl5/sun4-solaris/5.00404/DBI -I/auto/DBI -I/DBI -I/usr/local/include -O -DVERSION=\"0.89\" -DXS_VERSION=\"0.89\" -fpic -I/usr/local/lib/perl5/sun4-solaris/5.00404/CORE Pg.c In file included from /usr/local/lib/perl5/sun4-solaris/5.00404/CORE/perl.h:271, from /usr/local/lib/perl5/site_perl/sun4-solaris/auto/DBI/DBIXS.h:14, from Pg.h:32, from Pg.xs:15: /usr/local/lib/gcc-lib/sparc-sun-solaris2.4/2.7.2/include/string.h:58: parse error before `]' *** Error code 1 make: Fatal error: Command failed for target `Pg.o'
[ADMIN]
Please help with the following issue: The postmaster process is being restarted automatically and I don't know why. Initially it appears as "/usr/bin/postmaster -i -D /var/lib/psql" in the PS table. When it is restarted the pid is different and the -i is no longer. It now looks like this: /usr/bin/postmaster -S -D /var/lib/psql My configuration is as follows: postgresql ver. 6.5.2 release C2 Red Hat running Linux kernel 2.2.12C3 Thank you very much. daniel kern [EMAIL PROTECTED]
[ADMIN] PostgreSQL authentication via PAM/Radius available?
Hi all, we want to authenticate PostgreSQL users using a Win2k IAS (appears like radius), and I am searching a way to do so. I found several ways to make pam or different radius servers use a Postgres database for user identification, but nothing vice versa. The easiest way would be a patch for the src/backend/libpq/auth.[ch] to add another authentication scheme like pam or radius, has anybody written something like that? -- MfG Daniel Tepas Evangelisches u. Johanniter Klinikum Duisburg/Dinslaken/Oberhausen gGmbH Abteilung für Krankenhausinformatik, Gerrickstr. 21, 47137 Duisburg Tel. 0203-508-5990/, Fax 0203-451-31434
[ADMIN] Backup when the database is missing all of the pg_* tables
How can I backup a database (for restoration) when it is missing all of the pg_* tables? I've been bitten by the "SELinux silently ruins initdb" bug as reported by Tom Lane: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=149237 and another user: http://www.mail-archive.com/[email protected]/msg11191.html Unfortunately, I didn't notice the problem until after the database had new data added to it. I need to backup the database and recreate it with initdb, but pg_dump doesn't work: relation "pg_user" does not exist I tried "--data-only --no-owner --no-privileges" to no avail. I can still do '\d ' and 'COPY to ...', which is enough to get the information needed for a minimal dump, so it's theoretically possible. If there isn't a way to do this already, I'm going to build the schema from a backup, and then write a script that will COPY each table, then COPY them back after the initdb. Thanks, -- Daniel Browning <[EMAIL PROTECTED]> - Kavod Technologies. Random Fortune: Consider well the proportions of things. It is better to be a young June-bug than an old bird of paradise. -- Mark Twain, "Pudd'nhead Wilson's Calendar" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Schema sizes with large objects
Hi all For auditory reasons, we need to report monthly the size of some of our databases. We have been making it with database_size, but now we have a database with diferent schemas for wich we need to know these sizes. I've seen some functions here to do this, but we have a problem with the large objects, because it seems that they're stored in a separate schema (pg_catalog) and I can't find how to relate them with their schema in a manner easy to make this operation. Someone knows how I could do it? -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] What does log mean
Hi, If I ask detailed information about statements in log file, I got something like: ... 2005-08-29 10:19:15 CEST ...(...)DETAIL: ! system usage stats: ! 101.659118 elapsed 0.64 user 0.95 system sec ! [0.67 user 0.97 sys total] ... What does "elapsed" value contain exactly? Does it include client-server communication time or just the query running time? How can I examine what is the network overhead between the client and the server? Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] Problem with SSL - Server went 100% Busy - Part II
Hi there, I experienced the same problem as Alex eight months ago (reference below), but as there was no answer to his question. So I hope I have more luck: I got the error "SSL SYSCALL error: A blocking operation was interrupted by a call to WSACancelBlockingCall." which appears to end in a loop, as, when I discovered it, there have been written already several Gigabytes of Log-Files with just that entry. The error occured already two times, unfortunately I just got the log of the 2nd time. It occured right after some autovacuum calls, but I guess they don't have anything to do with it, as autovacuum runs everyday. The effect is simply a busy server with having all postgres.exe processes sharing the 100% of CPU time. The server is a v8.1.1 running on XP Pro. Part I of this problem you find under From: "Alex Shepherd" To: Subject: Problem with SSL - Server went 100% Busy Date: Wed, 11 May 2005 12:41:34 +1200 Thank in advance Daniel Michulke _ Você sabia que com o seu MSN Messenger você faz ligações de PC-papa- PC, grátis e para qualquer lugar do mundo? É só acessar http://imagine-msn.com/messenger/default2.aspx?locale=pt-br ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] recover of data directory
Hi, During machine maintenance I've made 'rm -rf *' on postgres data directory by a (very stupid) mistake. Postmaster was not running that time. Using e2undel I dumped out contents of deleted files (3728 files have been deleted by that command). Because of "rm -r", sizes of recovered directory files are 0, so I have no filenames and directory structure. Is there any chance of recover data directory if only dumped file contents are available? Daniel ---(end of broadcast)--- TIP 1: 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
Re: [ADMIN] recover of data directory
On Mon, 29 May 2006, Tom Lane wrote: If you could identify which file is which, you could manually reconstruct the directories, but I'm afraid the odds of doing that Postgresql data files do not identifying themselves (in their content) so it's an ext2fs problem. I'm trying to solve it, just for fun, and to get experience how can it be done. Daniel ---(end of broadcast)--- TIP 1: 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
Re: [ADMIN] [SQL] PostgreSQL server terminated by signal 11
> De : [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] De la part de Jeff Frost > Envoyé : jeudi, juillet 27, 2006 12:07 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > On Thu, 27 Jul 2006, Daniel Caune wrote: > > > My PostgreSQL server running on a Linux machine is terminated by signal > > 11 whenever I try to create some indexes on a table, which contains > > quite a lot of data. However I succeeded in creating some other indexes > > without having the PostgreSQL server terminated: > > Daniel, > > I would guess this is more appropriate for the -admin list so I cc'd it. > > I think you are most likely running out of memory or running up against a > ulimit on memory. I would first check my ulimit settings on the postgres > user > and see if they are a bit small. > Actually ulimit is unlimited for user postgres and when the server was terminated the memory used was not that much compared to the machine capacity: Mem: 2075860k total, 1663520k used, 412340k free, 5620k buffers Swap: 369452k total,0k used, 369452k free, 1626176k cached I feel a bit lost, because it was running pretty well for the last couple months. This table is a data stage table where some data are imported in from OLTP databases, this every day. Indexes are dropped before inserting data and created when insertion has finished. The structure didn't change since a while. Regards, -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] Index row maximum size and crash
Hi, I try to create an index on character varying(64) column but PostgreSQL complains that the index row size reaches the maximum limit: CREATE INDEX IDX_GSLOG_EVENT_ARCHIVE_GAME_CLIENT_VERSION ON GSLOG_EVENT_ARCHIVE(GAME_CLIENT_VERSION); ERROR: index row requires 198468548 bytes, maximum size is 8191 I was curious to know the maximum length I have for this column: select max(game_client_version) from gslog_event_archive; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. The server crashes again! Sniff! :-( Seems that the database is corrupted somewhere, no? -- Daniel CAUNE Ubisoft Online Technology (514) 490 2040 ext. 3613 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] De la part de Jeff Frost > Envoyé : jeudi, juillet 27, 2006 15:36 > À : Daniel Caune > Cc : [email protected] > Objet : Re: [ADMIN] [SQL] PostgreSQL server terminated by signal 11 > > On Thu, 27 Jul 2006, Daniel Caune wrote: > > > Actually ulimit is unlimited for user postgres and when the server was > terminated the memory used was not that much compared to the machine > capacity: > > > > Mem: 2075860k total, 1663520k used, 412340k free, 5620k buffers > > Swap: 369452k total,0k used, 369452k free, 1626176k cached > > > > I feel a bit lost, because it was running pretty well for the last > couple months. This table is a data stage table where some data are > imported in from OLTP databases, this every day. Indexes are dropped > before inserting data and created when insertion has finished. The > structure didn't change since a while. > > Daniel, > > Was this 'free' output before or after the crash? > Yes, it was before the crash. > Also, have you combed through /var/log/messages for anything interesting > that happened around the time of the crash? > Actually nothing from the last boot... Jul 27 18:30:40 localhost kernel: FDC 0 is a post-1991 82077 Jul 27 18:30:40 localhost kernel: NET: Registered protocol family 10 Jul 27 18:30:40 localhost kernel: Disabled Privacy Extensions on device c031f0c0(lo) Jul 27 18:30:40 localhost kernel: IPv6 over IPv4 tunneling driver Jul 27 18:30:41 localhost lpd[2394]: restarted Jul 27 18:50:40 localhost -- MARK -- Jul 27 19:10:41 localhost -- MARK -- Jul 27 19:30:41 localhost -- MARK -- Jul 27 19:50:41 localhost -- MARK -- It's easy for me to make the postgres server crashing and tailing the /var/log/messages. Nothing is logged. > Which postgresql version is this? > PostgreSQL 8.1.4 I suspect a database corruption or something like this, but I don't know how/what to check and how to fix it if any. -- Daniel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 16:06 > À : Daniel Caune > Cc : [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > My PostgreSQL server running on a Linux machine is terminated by signal > > 11 whenever I try to create some indexes on a table, which contains > > quite a lot of data. > > Judging from your examples it's got something to do with the partial > index WHERE clause. What PG version is this exactly? If you leave out > different parts of the WHERE, does it still crash? Does the crash > happen immediately after you give the command, or does it run for > awhile? It might be worth getting a stack trace from the failure > (best way is to attach to the running backend with gdb, provoke the > crash, and do "bt" --- search for "gdb" in the archives if you need > details). > > regards, tom lane Quite a long time I didn't use gdb! :-) Anyway I proceed as described hereafter; correct me if I was wrong. > ps -eaf | grep postgres postgres 2792 2789 0 21:50 pts/200:00:00 su postgres postgres 2793 2792 0 21:50 pts/200:00:00 bash postgres 2902 1 7 22:17 ?00:01:10 postgres: dbo agora [local] idle postgres 2952 1 2 22:32 ?00:00:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c unix_socket_directory=/var/run/postgresql -c config_file=/etc/postgresql/8.1/main/postgresql.conf -c hba_file=/etc/postgresql/8.1/main/pg_hba.conf -c ident_file=/etc/postgresql/8.1/main/pg_ident.conf postgres 2954 2952 0 22:32 ?00:00:00 postgres: writer process postgres 2955 2952 0 22:32 ?00:00:00 postgres: stats buffer process postgres 2956 2955 0 22:32 ?00:00:00 postgres: stats collector process I connected to the postgres server using psql and I retrieved the backend pid by executing the statement "SELECT pg_backend_pid();" I started gdb under the UNIX account postgres and I attached the backend process providing the pid returned by the statement. I run the command responsible for creating the index and I entered "continue" in gdb for executing the command. After a while, the server crashes: Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) Continuing. Program terminated with signal SIGSEGV, Segmentation fault. The program no longer exists. I can't do "bt" since the program no longer exists. How can I provide more information, stack trace, and so on? -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > De la part de Tom Lane > Envoyé : jeudi 27 juillet 2006 19:26 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > I run the command responsible for creating the index and I entered > "continue" in gdb for executing the command. After a while, the server > crashes: > > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > (gdb) > > Continuing. > > > Program terminated with signal SIGSEGV, Segmentation fault. > > The program no longer exists. > > > I can't do "bt" since the program no longer exists. > > I think you typed one carriage return too many and the thing re-executed > the last command, ie, the continue. Try it again. > OK, I'll try that tomorrow morning. Perhaps can I set a conditional breakpoint to function slot_attisnull when parameter slot is null (or slot->tts_tupleDescriptor is null). > The lack of arguments shown for slot_attisnull suggests that all we're > going to get is a list of function names, without line numbers or > argument values. If that's not enough to figure out the problem, can > you rebuild with --enable-debug to get a more useful stack trace? > Well, I installed PostgreSQL using apt-get but that won't be a problem to get the source from the CVS repository and to build a postgres binary using the option you provide to me. Just let me the time to do that. :-) Thanks, -- Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > De la part de D'Arcy J.M. Cain > Envoyé : jeudi 27 juillet 2006 19:49 > À : Daniel Caune > Cc : [EMAIL PROTECTED]; [email protected]; pgsql- > [EMAIL PROTECTED] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > On Thu, 27 Jul 2006 19:00:27 -0400 > "Daniel Caune" <[EMAIL PROTECTED]> wrote: > > I run the command responsible for creating the index and I entered > "continue" in gdb for executing the command. After a while, the server > crashes: > > > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > That's a pretty small function. I don't see much room for error. This > diff in src/backend/access/common/heaptuple.c seems like the most > likely place to catch it. > > RCS file: /cvsroot/pgsql/src/backend/access/common/heaptuple.c,v > retrieving revision 1.110 > diff -u -p -u -r1.110 heaptuple.c > --- heaptuple.c 14 Jul 2006 14:52:16 - 1.110 > +++ heaptuple.c 27 Jul 2006 23:37:54 - > @@ -1470,8 +1470,13 @@ slot_getsomeattrs(TupleTableSlot *slot, > bool > slot_attisnull(TupleTableSlot *slot, int attnum) > { > - HeapTuple tuple = slot->tts_tuple; > - TupleDesc tupleDesc = slot->tts_tupleDescriptor; > + HeapTuple tuple; > + TupleDesc tupleDesc; > + > + assert(slot != NULL); > + > + tuple = slot->tts_tuple; > + tupleDesc = slot->tts_tupleDescriptor; > > /* > * system attributes are handled by heap_attisnull > > Of course, you still have to find out what's calling it with slot set > to NULL if that turns out to be the problem. It may also be that slot > is not NULL but set to garbage. You could also add a notice there. > Two, in fact. One to display the address of slot and one to display > the value of slot->tts_tuple or slot->tts_tupleDescriptor. If the > first shows a non NULL value and the second causes your crash that > tells you that the value of slot is probably trashed before > calling the function. > Yes, I was afraid to go that deeper, but it's time! :-)) Actually it seems, from the source code, that a null slot->tts_tuple won't lead to a segmentation fault in function slot_attisnull, while slot and slot->tts_tupleDescriptor will. I will trace the function trying to discover what goes wrong behind the scene. > Do this in conjunction with Tom Lane suggestion of "--enable-debug" for > more information. > OK -- Daniel ---(end of broadcast)--- TIP 1: 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
Re: [ADMIN] [SQL] PostgreSQL server terminated by signal 11
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : jeudi, juillet 27, 2006 19:26 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > I run the command responsible for creating the index and I entered > "continue" in gdb for executing the command. After a while, the server > crashes: > > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > (gdb) > > Continuing. > > > Program terminated with signal SIGSEGV, Segmentation fault. > > The program no longer exists. > > > I can't do "bt" since the program no longer exists. > > I think you typed one carriage return too many and the thing re-executed > the last command, ie, the continue. Try it again. > You were right. Program received signal SIGSEGV, Segmentation fault. 0x08079e2a in slot_attisnull () (gdb) bt #0 0x08079e2a in slot_attisnull () #1 0x0807a1d0 in slot_getattr () #2 0x080c6c73 in FormIndexDatum () #3 0x080c6ef1 in IndexBuildHeapScan () #4 0x0809b44d in btbuild () #5 0x0825dfdd in OidFunctionCall3 () #6 0x080c4f95 in index_build () #7 0x080c68eb in index_create () #8 0x08117e36 in DefineIndex () #9 0x081db4ee in ProcessUtility () #10 0x081d8449 in PostgresMain () #11 0x081d99d5 in PortalRun () #12 0x081d509e in pg_parse_query () #13 0x081d6c33 in PostgresMain () #14 0x081aae91 in ClosePostmasterPorts () #15 0x081ac14c in PostmasterMain () #16 0x08168f22 in main () -- Daniel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] [SQL] PostgreSQL server terminated by signal 11
> De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : vendredi, juillet 28, 2006 09:38 > À : Daniel Caune > Cc : [email protected]; [email protected] > Objet : Re: [SQL] PostgreSQL server terminated by signal 11 > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > Program received signal SIGSEGV, Segmentation fault. > > 0x08079e2a in slot_attisnull () > > (gdb) bt > > #0 0x08079e2a in slot_attisnull () > > #1 0x0807a1d0 in slot_getattr () > > #2 0x080c6c73 in FormIndexDatum () > > #3 0x080c6ef1 in IndexBuildHeapScan () > > #4 0x0809b44d in btbuild () > > #5 0x0825dfdd in OidFunctionCall3 () > > #6 0x080c4f95 in index_build () > > #7 0x080c68eb in index_create () > > #8 0x08117e36 in DefineIndex () > > Hmph. gdb is lying to you, because slot_getattr doesn't call > slot_attisnull. > This isn't too unusual in a non-debug build, because the symbol table is > incomplete (no mention of non-global functions). > > Given that this doesn't happen right away, but only after it's been > processing for awhile, we can assume that FormIndexDatum has been > successfully iterated many times already, which seems to eliminate > theories like the slot or the keycol value being bogus. I'm pretty well > convinced now that we're looking at a problem with corrupted data. Can > you do a SELECT * FROM (or COPY FROM) the table without error? > > regards, tom lane The statement "copy gslog_event to stdout;" leads to "ERROR: invalid memory alloc request size 4294967293" after awhile. (...) 354964834 2006-07-19 10:53:42.813+00 (...) 354964835 2006-07-19 10:53:44.003+00 (...) ERROR: invalid memory alloc request size 4294967293 I tried then "select * from gslog_event where gslog_event_id >= 354964834 and gslog_event_id <= 354964900;": 354964834 | 2006-07-19 10:53:42.813+00 | (...) 354964835 | 2006-07-19 10:53:44.003+00 | (...) 354964837 | 2006-07-19 10:53:44.113+00 | (...) 354964838 | 2006-07-19 10:53:44.223+00 | (...) (...) (66 rows) The statement "select * from gslog_event;" leads to "Killed"... Ouch! The psql client just exits (the postgres server crashes too)! The statement "select * from gslog_event where gslog_event_id <= 354964834;" passed. I did other tests on some other tables that contain less data but that seem also corrupted: copy player to stdout ERROR: invalid memory alloc request size 1918988375 select * from player where id >=771042 and id<=771043; ERROR: invalid memory alloc request size 1918988375 select max(length(username)) from player; ERROR: invalid memory alloc request size 1918988375 select max(length(username)) from player where id <= 771042; max - 15 select max(length(username)) from player where id >= 771050; max - 15 select max(length(username)) from player where id >= 771044 and id <= 771050; max - 13 Finally: select * from player where id=771043; ERROR: invalid memory alloc request size 1918988375 select id from player where id=771043; id 771043 (1 row) agora=> select username from player where id=771043; ERROR: invalid memory alloc request size 1918988375 I'm also pretty much convinced that there are some corrupted data, especially varchar row. Before dropping corrupted rows, is there a way to read part of corrupted data? Thanks Tom for your great support. I'm just afraid that I wasted your time... Anyway I'll write a FAQ that provides some information about this kind of problem we have faced. Regards, -- Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Unsubscribe
> -Message d'origine- > De : [EMAIL PROTECTED] [mailto:pgsql-admin- > [EMAIL PROTECTED] De la part de [EMAIL PROTECTED] > Envoyé : mardi, août 15, 2006 11:14 > À : [email protected] > Objet : [ADMIN] Unsubscribe > > > I would like to be checked out (UNSUBSCRIBE) from a mailing list. > I have already send e-mail with that kind of request before and I am still > getting a lot emails. > Our security office does not allow to do this any more. > It makes me a target of shutting down and clean up my computer at any time > while I am working. > Please deactivate my subscription ASAP. > DO UNSUBSCRIBE ME !!! > http://www.postgresql.org/community/lists/subscribe Select action "Unsubscribe" -- Daniel ---(end of broadcast)--- TIP 1: 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] unsubscribe
Thanks. ** Nota: La informacion transmitida a traves de este correo electronico es confidencial y esta dirigida unicamente a su destinatario. Su reproduccion, lectura o uso esta prohibido a cualquier persona o entidad diferente. Aunque Computec S.A. ha realizado su mejor esfuerzo para asegurar que el presente mensaje y sus archivos anexos se encuentran libre de virus y defectos que puedan llegar a afectar los computadores o sistemas que lo reciban, no se hace responsable por la eventual transmision de virus o programas daninos por este conducto, y por lo tanto es responsabilidad del destinatario confirmar la existencia de este tipo de elementos al momento de recibirlo y abrirlo. Ni Computec S.A. ni ninguna de sus divisiones o dependencias aceptan responsabilidad alguna por eventuales danos o alteraciones derivados de la recepcion o uso del presente mensaje. ** Note: The information transmitted through this E-mail is confidential and is intended to reach only its addressee. Reproduction and usage of this message or its contents, in whole or in part, are strictly forbidden without the prior written consent of Computec S.A. Computec S.A. has made its best efforts to ensure that this message and any attached files are free of any virus or other potentially harmful content, but makes no representations to this effect. Addressee shall assume full responsibility for ensuring that opening or reading this message and any attachments will not result in harm to their system. Neither Computec nor any of its divisions accepts any responsibility for damages arising from opening or reading this message or any attachments thereto. ** ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[ADMIN] Security Problem
Hi to all I'm mantaining a Postgres installation on an ISP, and I see a relativly important security issue. I can connect to postgres with a normal user login and take a look to the database structure of all the other databases on the installation. This is very visual with PGadmin. I connect as a simple user, and I can't create or select data from other's database, but i can see which databases exists on the system and look at the tables and columns of each of them (and i don't want it). I've tried to solve it with: REVOKE ALL PRIVILEGES ON DATABASE x FROM PUBLIC; REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC; REVOKE ALL PRIVILEGES ON SCHEMA public FROM GROUP users; ... And I still can to look at the others database structure (connecting as a non privileged user) ... How I could solve this? Thanks -- **** Daniel Rubio Rodríguez ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] pg_dump still requires password
When I issue the command it is asking for passwd which I cannot used for cronjob to run at midnight. Execute the task as user postgres (crontab for postgres, not root) and modify pg_hba.conf to permit access in trust mode (without asking for password) e.g : local all postgrestrust You can subtitute postgres for the user you need and the database too (if the dump is not pg_dumpall) Please help. How I do solve this problem. I have posted this earlier the suggestion was to use pgpass but that does not help. What other way can I achive this. Regards, Ganesan -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] pg_lo_import alternative ...
Hi all. One of our clients wants to store images into his postgres database hosted by us. To do this is trying to use the PHP function pg_lo_import, receiving this error message from server: Waning: pg_query() [function.pg-query]: Query failed: ERROR: You must have Postgres superuser privilege to use server-side lo_import(). Anyone can use the client-side lo_import() provided by libpq. . in /apps/web/html/funcions.php on line 121 I can read that there's a client-side function in libpq, but I can't find any information about it and their use. Anyone knows something? -- ******** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] Randomly error using php persistent connections
Hi all One of our clients (sometimes I think that all freak clients come to our ISP) is using persistent connections via PHP (pg_pconnect) to our 7.3.2 postgres database. Randomly their scripts fails, and doing a Reload on navigator their work fine again. I've put debug4 on logs and I've seen this: 2003-11-28 13:28:13 DEBUG: ProcessQuery 2003-11-28 13:28:13 DEBUG: ProcessUtility 2003-11-28 13:28:13 DEBUG: CommitTransactionCommand 2003-11-28 13:28:14 LOG: pq_recvbuf: unexpected EOF on client connection 2003-11-28 13:28:14 DEBUG: proc_exit(0) 2003-11-28 13:28:14 DEBUG: shmem_exit(0) 2003-11-28 13:28:14 DEBUG: exit(0) 2003-11-28 13:28:14 DEBUG: reaping dead processes 2003-11-28 13:28:14 DEBUG: child process (pid 10541) exited with exit code 0 2003-11-28 13:28:14 DEBUG: StartTransactionCommand 2003-11-28 13:28:14 DEBUG: ProcessQuery It says that is a problem on the client side, but i can't find nothing that helps me. The web server is a Netscape Enterprise 3.6, and PHP version is 4.3.2. Someone has been the same problem or knows what is happening? Thanks in advance. -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Randomly error using php persistent connections
We found it! The database host was over the firewall which had the port for postgres opened, but the persistent connections were killed by the firewall due to timeouts. :D Daniel Rubio wrote: Hi all One of our clients (sometimes I think that all freak clients come to our ISP) is using persistent connections via PHP (pg_pconnect) to our 7.3.2 postgres database. Randomly their scripts fails, and doing a Reload on navigator their work fine again. I've put debug4 on logs and I've seen this: 2003-11-28 13:28:13 DEBUG: ProcessQuery 2003-11-28 13:28:13 DEBUG: ProcessUtility 2003-11-28 13:28:13 DEBUG: CommitTransactionCommand 2003-11-28 13:28:14 LOG: pq_recvbuf: unexpected EOF on client connection 2003-11-28 13:28:14 DEBUG: proc_exit(0) 2003-11-28 13:28:14 DEBUG: shmem_exit(0) 2003-11-28 13:28:14 DEBUG: exit(0) 2003-11-28 13:28:14 DEBUG: reaping dead processes 2003-11-28 13:28:14 DEBUG: child process (pid 10541) exited with exit code 0 2003-11-28 13:28:14 DEBUG: StartTransactionCommand 2003-11-28 13:28:14 DEBUG: ProcessQuery It says that is a problem on the client side, but i can't find nothing that helps me. The web server is a Netscape Enterprise 3.6, and PHP version is 4.3.2. Someone has been the same problem or knows what is happening? Thanks in advance. -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Can someone tell me the default installation directory for PostgreSQL 7.4 on linux ?
I'm a newbie. I just installed it ( from RPMs at the .org for SUSE 8.2 ) and I can't find it anywhere on my hard drive. I can see the package listed in YaST and psql is in the /usr/bin directory. I tried 'find' and 'whereis' to no avail. I installed the docs RPM as well and haven't a clue how to even open them as well. Any help at all is appreciated. Thank you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] unexpected EOF on client connection + createdb
When I tried to create a database with createdb I receive this message: "unexpected EOF on client connection". I need your help! Thanks, Daniel ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] possible bug with sequences
Hi!
I've had the same problem while restoring one postgres database with
changed table names during the migrations from 7.3.2 to 7.4.1.
I sent the message to the list on 03/15/2004, but it seems it hasn't
arrived.I hope it can help, Here is:
---
Hi all.
Yesterday I planned the migration from a couple of databases from a
7.3.2 postgres box to a 7.4.1.
I did the pg_dump with the -C command, and substituted on the dump 2
aparitions of $libdir for the path of my libraries ( on the previous
tests, postgres didn't found this variable value, despite i had the
variable set: dynamic_library_path = '/apps/pg/lib', where pg is a
dinamic link)
¿Some idea on how to solve this?
Ok, now I'm on the new host, ready to import my database, via psql -d
template1 -f filename, it works fine, excepting from these errors:
psql:file1:879: ERROR: relation "usuari_codi_seq" does not exist
psql:file1:887: ERROR: relation "idioma_codi_seq" does not exist
psql:file1:895: ERROR: relation "portada_codi_seq" does not exist
psql:file1:903: ERROR: relation "nivell1_codi_seq" does not exist
Looking at the dump, for these lines, I see that postgres is trying to
set the values from these sequences, e.g.
-- Name: nivell1_codi_seq; Type: SEQUENCE SET; Schema: public; Owner:
riudoms
SELECT pg_catalog.setval ('nivell1_codi_seq', 14, true);
The problem resides in that the database user updated the table names
(but it seems that when it occurs, postgres doesn't change the sequence
names associated to them), and when importing, postgres automatically
created the sequences with the new names prefix, and then failed the
value setting
Fortunatelly the database had a few tables and sequences, and I could
solve this via PGAdmin, but I think it could be a disaster dumping for
example all databases on the system (we make hosting, and every user can
change the name of a table when he wants ...) or big-structured databases.
Someone has some idea of how this issue could be solved? Is a known
"bug" (If can be considered a bug ...)?
Until one more "collateral damage", I updated the sequences value to the
number the setval was telling, e.g ( 14 in SELECT pg_catalog.setval
('nivell1_codi_seq', 14, true);), but when the used tried to make a
insert it failed because the database told it was duplicated key values
(the second insert worked fine, because the sequence had incremented
this value), why this error happened? I would have to put the value from
setval+1 ? why?
Thanks in advance
--
Victor Sudakov wrote:
Colleagues,
What is the internal difference between an implicit sequence (created
automatically by the "serial" data type) and an explicit sequence
(created manually)?
I think I have hit something that can qualify as a bug. How to
reproduce:
== cut here =
reports=# CREATE TABLE sometable (id serial, name text);
NOTICE: CREATE TABLE will create implicit sequence "sometable_id_seq" for "serial" column "sometable.id"
CREATE TABLE
reports=# ALTER TABLE sometable RENAME TO othername;
ALTER TABLE
reports=# \d othername
Table "admin.othername"
Column | Type |Modifiers
+-+--
id | integer | not null default nextval('admin.sometable_id_seq'::text)
name | text|
== cut here =
Voila! pg_restore will now return an error on trying to execute
SELECT pg_catalog.setval('sometable_id_seq', 4, true);
because this sequence has not been created either explicitly or implicitly!
It is 100% reproducible on postgresql-7.4.1
--
Daniel Rubio Rodríguez
OASI (Organisme Autònom Per la Societat de la Informació)
c/ Assalt, 12
43003 - Tarragona
Tef.: 977.244.007 - Fax: 977.224.517
e-mail: drubio a oasi.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [ADMIN] [PERFORM] Quad processor options
It appears that your CPU is 'slow' while your disk subsystem is 'fast'. I had once such situation with 15 kRPM drives and ~500MHz Pentium III. On that system, the best solution was to either increase effective_cache_size or decrease random_page_cost (the latter obviously has to do with the fast disk, the former with the lots of RAM). In any case, proper optimization of queries always helps. :-) Daniel >>>Halford Dace said: > > On 12 May 2004, at 12:17 PM, Manfred Koizar wrote: > > > On Tue, 11 May 2004 15:46:25 -0700, Paul Tuckfield <[EMAIL PROTECTED]> > > wrote: > > > >> - I'll bet you have a low value for shared buffers, like 1. On > >> your 3G system > >> you should ramp up the value to at least 1G (125000 8k buffers) > > > > In most cases this is almost the worst thing you can do. The only > > thing > > even worse would be setting it to 1.5 G. > > > > Postgres is just happy with a moderate shared_buffers setting. We > > usually recommend something like 1. You could try 2, but don't > > increase it beyond that without strong evidence that it helps in your > > particular case. > > > > This has been discussed several times here, on -hackers and on > > -general. > > Search the archives for more information. > > We have definitely found this to be true here. We have some fairly > complex queries running on a rather underpowered box (beautiful but > steam-driven old Silicon Graphics Challenge DM). We ended up using a > very slight increase to shared buffers, but gaining ENORMOUSLY through > proper optimisation of queries, appropriate indices and the use of > optimizer-bludgeons like "SET ENABLE_SEQSCAN = OFF" > > Hal > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] Upgrade problems with OID type ...
Hi all! I'm trying to upgrade from 7.3.2 to 7.4.3 but I've a problem with a database (1 I know, there could be more) containing a table with an oid type field. I run the migration doing: ../bin/pg_dumpall -o -p 5432 | /apps/pgs-7.4.3/bin/psql -d template1 -p 5433 1>/tmp/out 2>/tmp/err The command runs fine, with no ERRORS on the output files, but when the user accesses their application (web) the images he was storing does'nt appear, and I can see errors like this on the logfile: 2004-07-06 09:54:52 ERROR: large object 417988 does not exist First I was doing the dump without the -o, but now I use it and the problem persists ... Ideas? Thanks in advance ... -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Upgrade problems with OID type ...
[EMAIL PROTECTED] wrote: Hello Daniel, Have you tried using the -Ft option when doing the dump, and pg_restore instead of psql ? I've seen the thread you started with a similar problem, and it has been useful to solve the problem with the concrete database, but my problem is that we host 20 different databases (and growing)for hosting clients and we don't know who's using OID's and who's not. I think that the Chapter 9.3 (Migration between releases) would have to concrete that the OIDs doesn't work with the method explained (despite pg_dumpall has the -o option) and how to make the migration process in this case. I have not encoutered too much documentation about it, and finally I've followed this process (could be useful for someone): 1.-Search the databases having OIDs ( \dl command on each one), this is easy to automate via shell script ( but I did'nt make it). 2.-With the pg_dumplo utility (contrib directory), backup the OID objects for each database discovered in 1 ../../pg-7.4.3/bin/pg_dumplo -s /tmp/dumpoids -d database_name -e 3.-Run the pg_dumpall ../bin/pg_dumpall -o -c -p 5432 | /apps/pg-7.4.3/bin/psql -d template1 -p 5433 1>/tmp/sortidaimport 2>/tmp/sortidaerror 4.-Import the OIDs to the new postgres using pg_dumplo (for each database with OIDs) ../../pg-7.4.3/bin/pg_dumplo -s /tmp/dumpoids -d pragenda -o 5433 -i It worked for me ... If anyone knows a better, faster or automatic process to perform the upgrade I'll be pleased to know it ... And sorry for my poor english ... -- Mensaje original -- Date: Tue, 06 Jul 2004 11:06:25 +0200 From: Daniel Rubio <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [ADMIN] Upgrade problems with OID type ... Hi all! I'm trying to upgrade from 7.3.2 to 7.4.3 but I've a problem with a database (1 I know, there could be more) containing a table with an oid type field. I run the migration doing: ../bin/pg_dumpall -o -p 5432 | /apps/pgs-7.4.3/bin/psql -d template1 -p 5433 1>/tmp/out 2>/tmp/err The command runs fine, with no ERRORS on the output files, but when the user accesses their application (web) the images he was storing does'nt appear, and I can see errors like this on the logfile: 2004-07-06 09:54:52 ERROR: large object 417988 does not exist First I was doing the dump without the -o, but now I use it and the problem persists ... Ideas? Thanks in advance ... -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] [PHP] Secure DB Systems - How to
> Keeping the system administrator from seeing the data while making it
> searchable is difficult. To do this you need to encrypt the data on
> the client side using a key the client has (and this key has to be
> protected from loss) and the only searches you can do are equality
> searches using a hash or encrypted value.
You can also perform regex searches.
Here is an example to get you started:
CREATE TABLE crypto (
id SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_contentBYTEA
);
INSERT INTO crypto VALUES
(1,'test1',encrypt_iv('daniel','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
INSERT INTO crypto VALUES
(2,'test2',encrypt_iv('test','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
INSERT INTO crypto VALUES
(3,'test3',encrypt_iv('struck','fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes'));
SELECT *,decrypt_iv(crypted_content,
'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes') FROM crypto;
-- equality search
SELECT
*,decrypt_iv(crypted_content,'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes')
FROM crypto WHERE decrypt_iv(crypted_content,
'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes')='struck';
-- regex search
SELECT
*,decrypt_iv(crypted_content,'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'),'aes')
FROM crypto WHERE encode(decrypt_iv(crypted_content,
'fooz',decode('9MlPeZtpuxKo5m4O4+pd4g==','base64'), 'aes'),'escape')
~* 'daniel';
"fooz" is the password and "9MlPeZtpuxKo5m4O4+pd4g==" is the IV (initialization
vector) stored in base64 format. I choose base64 because it is more convenient to
create queries with it.
In the real database I do use a different IV for every row, so I do also store the IV
with the row.
In my case I do generate the IV by PHP with /dev/urandom as a random source.
Greetings,
Daniel Struck
--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg
phone: +352-44116105
fax: +352-44116113
web: http://www.retrovirology.lu
e-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [PHP] Secure DB Systems - How to
> If you decrypt the data on the database, the sysadmin can see it. Hm, you are right. If one does decrypt the data on the database you have to sent the password to postgresql and so a administrator of the database could easily grasb the password. So the only way to go, would be to perform en/decryption on the client side? > If you are willing to take that chance (e.g. if you primary concern is > some third party getting a snapshot of the DB), then you can do lots of > things. I wonder now; if somebody could achieve to get a snapshot of the database, they could also be able to get the log-file of postgresql. So one would also have to make attention that the information like sql statements don't leak that way. Are there other places where this kind of information could leak? Greetings, Daniel Struck -- Retrovirology Laboratory Luxembourg Centre Hospitalier de Luxembourg 4, rue E. Barblé L-1210 Luxembourg phone: +352-44116105 fax: +352-44116113 web: http://www.retrovirology.lu e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] Problem with permanent connections from Tomcat
Hi all Our developers are making some kind of DoS on my postgres installation. They use Tomcat to develop in java and they have an instance on every computer (only 3). It seems that tomcat makes permanent connections to the configured database, but they have this number limited to 5, and max_connections in postgresql.conf is 40, and no more connections are made to the database because is a development environment. The number of connections starts increasing and when arrives to 40 ( ps -ef | grep postm | wc -l ) the server doesn't accept more connections but it seems that neither finish these that are open, and no one can acces to the database (here is the DoS I say). Here is what the log says 2005-02-07 10:13:26 LOG: connection received: host=127.0.0.1 port=55834 2005-02-07 10:13:26 LOG: connection authorized: user=test database=test 2005-02-07 10:13:26 FATAL: connection limit exceeded for non-superusers I try to stop the postmaster with pg_ctl but it says that postmaster doesn't stops and I have to make kill -9 for the processes to stop :( What could I make to solve this? I've tried with: statement_timeout = 10 but nothing happens ... :( -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] schema sizes
Hi all! We want to obtain the size of the diferent schemas of some databases. We've installed dbsize and it works fine, but it returns the size of all the database. Exists some method to determine the size of a concrete schema? Thanks in advance. -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] schema sizes
Thanks, it works fine!! Morus Walter wrote: Daniel Rubio writes: Hi all! We want to obtain the size of the diferent schemas of some databases. We've installed dbsize and it works fine, but it returns the size of all the database. Exists some method to determine the size of a concrete schema? I use SELECT nspname, sum(relpages * cast( 8192 AS bigint )) as "table size", sum( ( select sum(relpages) from pg_class i, pg_index idx where i.oid = idx.indexrelid and t.oid=idx.indrelid ) ) * cast( 8192 AS bigint ) as "index size", sum ( relpages * cast( 8192 AS bigint ) + ( select sum(relpages) from pg_class i, pg_index idx where i.oid = idx.indexrelid and t.oid=idx.indrelid ) * cast( 8192 AS bigint ) ) as "size" FROM pg_class t, pg_namespace WHERE relnamespace = pg_namespace.oid and pg_namespace.nspname not like 'pg_%' and pg_namespace.nspname != 'information_schema' and relkind = 'r' group by nspname; to sum over all tables/indices of a schema. Note that the input for the sum is relpages in pg_class and this value is only updated by VACUUM, ANALYZE und CREATE INDEX. So you should analyze all tables before you execute the statement. -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Too many clients----A big problem for my team
I had the same problem a few weeks ago. Was happening that our developers using Tomcat, were using connection pools, and stoping and restarting their aplications which causes to leave these connection opened and opening some new. Look in the servlet engine (or what they use) for the number of connections they made (in Tomcat it's a parameter), say them not to leave the connections opened, and not to stop and restart the java application when they make changes, they must restart the "engine" (it closes all the opened connections). It worked for me ... ganapatiram wrote: Hi Experts, I work with postgresql 8.0 version. I have a team of 14 developers. When all the team connects the DB i see this error very very frequently which is a severe problem for me and team all the day. The error is: *Please report this exception: java.sql.SQLException: FATAL: sorry, too many clients already* ** *when i do trial and error changes i see this error differently but the essense of the error remains same. * ** **I have max_connections=200 and i have 5 users for my DB. I dont understand what to do in order to resolve this. Please suggest me something by which i can overcome this problem. I referred few threads but none of them are of my case. Waiting for your valuable suggestion.. Thanks and Regards Ram -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Clustering
Hy guys, where can I find some information over clustering postgreSQL ? Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!
[ADMIN] tabla pg_user Ayuda......
Por algun motivo desconocido en la tabla pg_user esta dos veces registrado en usuario "postgres", por esto no me deja realizar backups con el comando pg_dump, como hago para eliminar uno de los dos registros teniendo en cuenta que los datos son identicos y que es el usuario admin del sistema y porque paso esto Saludos Gracias. El jue, 15-02-2007 a las 12:22 -0500, jeferson alvarez escribió: > Hola amigos esta es la idea de lo que quiero hacer > > select identity(int4,1, 1), CODIGO from articulo > > quiero sacar algo como eso hacer un listado de codigos x ej y que me > aparesca la lista numerada > > gracias > > ---(fin del mensaje)--- > TIP 4: No hagas 'kill -9' a postmaster -- Daniel Ricardo Medina R. Ing. de Desarrollo Computec S.A. - Documentos tel: 2607111 ext. 140 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] tabla pg_user Ayuda......
> Por algun motivo desconocido en la tabla pg_user esta dos veces > registrado en usuario "postgres", por esto no me deja realizar backups > con el comando pg_dump, como hago para eliminar uno de los dos registros > teniendo en cuenta que los datos son identicos y que es el usuario admin > del sistema > > y porque paso esto > > Saludos > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] tabla pg_user Ayuda......
estos campos no hacen parte de la tabla pg_user, utilizo la version 7.4.8 y regularmente ejecuto los comandos vacuum y vacuum full por el tamaño de la base El sáb, 10-02-2007 a las 01:55 -0500, Jaime Casanova escribió: > On 2/9/07, Daniel Medina <[EMAIL PROTECTED]> wrote: > > Por algun motivo desconocido en la tabla pg_user esta dos veces > > registrado en usuario "postgres", por esto no me deja realizar backups > > con el comando pg_dump, como hago para eliminar uno de los dos registros > > teniendo en cuenta que los datos son identicos y que es el usuario admin > > del sistema > > > > y porque paso esto > > > > Saludos > > > > por favor, podrias mostrar el resultado de este query? > > set enable_indexscan to off ; > SELECT xmin, xmax, cmin, cmax, ctid, oid > FROM pg_user WHERE usename = 'postgres'; > > que version de postgres es esta? > ejecutas vacuum o vacuum full regularmente? > -- Daniel Ricardo Medina R. Ing. de Desarrollo Computec S.A. - Documentos tel: 2607111 ext. 140 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] tabla pg_user Ayuda......
El problema es que si borro una fila la otra tambien se borrar porque nos datos del usuarios postgres son identicos en los dos registros, lo otros es que la base es de 150 Gigas y no tengo espacio para copiarla Gracias . El sáb, 10-02-2007 a las 00:28 -0500, Eduardo J. Ortega escribió: > Yo sugeriría hacer lo siguiente: > 1) detener postgres > 2) copiar el directorio de datos de postgres a un nuevo directorio > preservando > permisos > 3) iniciar el postgres sobre el directorio copia recién creado. > 4) Realizar el delete de una de las filas en la tabla de usuarios y probaro > que todo funcione bien. si es así, problema solucionado. Si no, todavía se > tiene la versión original del directorio de datos de postgres. > > En cuanto a por qué pasó, lo siento, ni idea. > > On Friday 09 February 2007 16:20, Daniel Medina wrote: > > Por algun motivo desconocido en la tabla pg_user esta dos veces > > registrado en usuario "postgres", por esto no me deja realizar backups > > con el comando pg_dump, como hago para eliminar uno de los dos registros > > teniendo en cuenta que los datos son identicos y que es el usuario admin > > del sistema > > > > y porque paso esto > > > > Saludos > > > > > > Gracias. > > > > El jue, 15-02-2007 a las 12:22 -0500, jeferson alvarez escribió: > > > Hola amigos esta es la idea de lo que quiero hacer > > > > > > select identity(int4,1, 1), CODIGO from articulo > > > > > > quiero sacar algo como eso hacer un listado de codigos x ej y que me > > > aparesca la lista numerada > > > > > > gracias > > > > > > ---(fin del mensaje)--- > > > TIP 4: No hagas 'kill -9' a postmaster > -- Daniel Ricardo Medina R. Ing. de Desarrollo Computec S.A. - Documentos tel: 2607111 ext. 140 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-es-ayuda] [ADMIN] tabla pg_user Ayuda......
la tabla pg_user realmente es una vista de la tabla pg_shadow El vie, 09-02-2007 a las 17:10 -0600, GABRIEL COLINA escribió: > Te comento lo que haria, nunca me paso con esa tabla, pero si con > otras. > > Creo una tabla con identica estructura. > > pg_user_2 > > insert into pg_user_2 select > usename,usesysid,usecreatetable,usesuper,usecatupd, > passwd,,valuntil,useconfig from pg_user > group by usename,usesysid,usecreatetable,usesuper,usecatupd, > passwd,,valuntil,useconfig > > deleted from pg_user > insert into pg_user select * from pg_user_2. > > Quiza sea una locura pero es lo que se me ocurre. > > > > > Daniel Medina <[EMAIL PROTECTED]> escribió: > > > Por algun motivo desconocido en la tabla pg_user esta dos > veces > > registrado en usuario "postgres", por esto no me deja > realizar backups > > con el comando pg_dump, como hago para eliminar uno de los > dos registros > > teniendo en cuenta que los datos son identicos y que es el > usuario admin > > del sistema > > > > y porque paso esto > > > > Saludos > > > > > > > > ---(fin del > mensaje)--- > TIP 2: puedes desuscribirte de todas las listas > simultáneamente > (envíe "unregister TuDirecciónDeCorreo" a > [EMAIL PROTECTED]) > > > > __ > Correo Yahoo! > Espacio para todos tus mensajes, antivirus y antispam ¡gratis! > Regístrate ya - http://correo.espanol.yahoo.com/ > -- Daniel Ricardo Medina R. Ing. de Desarrollo Computec S.A. - Documentos tel: 2607111 ext. 140 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-es-ayuda] [ADMIN] tabla pg_user Ayuda......
borre accidentalmente de la tabla pg_shadow el usurio postgres ,
existian dos registros de este usuario la diferencia era el pass
pero se borraron los dos
al tratar de crearlo con create user
create user postgres with SYSID 1 password '';
ERROR: must be superuser to create users
o con el comando insert..
insert into pg_shadow
(usename,usesysid,usecreatedb,usesuper,usecatupd,passwd) values
('postgres',1,true,true,true,md5('*'));
ERROR: user with ID 1 does not exist
ahora si estoy peor que antes
Como hago para craear el usuario admin de postgres nuevamente .
El vie, 09-02-2007 a las 16:21 -0500, Daniel Medina escribió:
> > Por algun motivo desconocido en la tabla pg_user esta dos veces
> > registrado en usuario "postgres", por esto no me deja realizar backups
> > con el comando pg_dump, como hago para eliminar uno de los dos registros
> > teniendo en cuenta que los datos son identicos y que es el usuario admin
> > del sistema
> >
> > y porque paso esto
> >
> > Saludos
> >
> >
>
>
>
> ---(fin del mensaje)---
> TIP 2: puedes desuscribirte de todas las listas simultneamente
> (enve "unregister TuDireccinDeCorreo" a [EMAIL PROTECTED])
--
Daniel Ricardo Medina R.
Ing. de Desarrollo
Computec S.A. - Documentos
tel: 2607111 ext. 140
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [pgsql-es-ayuda] [ADMIN] tabla pg_user Ayuda......
Ok , pero si bajo y subo postgres sin el usuario admin no habra peligro
que no suba... y si sube con que usuario haria el cambio...
Gracias por tu colaboracion
El mié, 14-02-2007 a las 15:52 -0300, Alvaro Herrera escribió:
> Daniel Medina escribió:
> > borre accidentalmente de la tabla pg_shadow el usurio postgres ,
> > existian dos registros de este usuario la diferencia era el pass
> >
> > pero se borraron los dos
> >
> > al tratar de crearlo con create user
> > create user postgres with SYSID 1 password '';
> > ERROR: must be superuser to create users
> >
> >
> > o con el comando insert..
> >
> > insert into pg_shadow
> > (usename,usesysid,usecreatedb,usesuper,usecatupd,passwd) values
> > ('postgres',1,true,true,true,md5('*'));
> > ERROR: user with ID 1 does not exist
>
> ja ja ja ...
>
>
> Baja postmaster y levanta una sesion en modo standalone. Creo que es
>
> postgres -P -O
>
> (Mira postgres --help; depende de la version).
>
> Ahi dentro puedes hacer el insert into pg_shadow, pero primero trata con
> CREATE USER.
>
--
Daniel Ricardo Medina R.
Ing. de Desarrollo
Computec S.A. - Documentos
tel: 2607111 ext. 140
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [pgsql-es-ayuda] [ADMIN] tabla pg_user Ayuda......
esto tratando de hacer los que dices abajo borrar el usuario , los cree pero nuevamente me crea dos registros select * from pg_user; 1: usename (typeid = 19, len = 64, typmod = -1, byval = f) 2: usesysid(typeid = 23, len = 4, typmod = -1, byval = t) 3: usecreatedb (typeid = 16, len = 1, typmod = -1, byval = t) 4: usesuper(typeid = 16, len = 1, typmod = -1, byval = t) 5: usecatupd (typeid = 16, len = 1, typmod = -1, byval = t) 6: passwd (typeid = 25, len = -1, typmod = -1, byval = f) 7: valuntil(typeid = 702, len = 4, typmod = -1, byval = t) 8: useconfig (typeid = 1009, len = -1, typmod = -1, byval = f) 1: usename = "postgres"(typeid = 19, len = 64, typmod = -1, byval = f) 2: usesysid = "1" (typeid = 23, len = 4, typmod = -1, byval = t) 3: usecreatedb = "t" (typeid = 16, len = 1, typmod = -1, byval = t) 4: usesuper = "t" (typeid = 16, len = 1, typmod = -1, byval = t) 5: usecatupd = "t" (typeid = 16, len = 1, typmod = -1, byval = t) 6: passwd = "" (typeid = 25, len = -1, typmod = -1, byval = f) cree un usuario como usesyid = 1 y trate de borrar el usuario postgres pero no hace nada.. que comandos debo utilizar Gracias ... El jue, 15-02-2007 a las 10:49 -0300, Alvaro Herrera escribió: > Jaime Casanova escribió: > > > Alvaro, un vacuum como superusuario podria haber arreglado algo? o > > hubiera tenido que borrar la tupla que pareciera mas vieja? o ninguna > > de ambas podria haber funcionado? estoy asumiendo que el problema es > > el que mencione arriba, no se me ocurre que mas pudo ser... > > La verdad es que no estoy 100% seguro porque no sabemos en que estado > estaban esas tuplas ... pero hace un tiempo le hice una consultoria a > una empresa que tuvo un problema muy similar, y cuando les dije que > hicieran un VACUUM sobre pg_shadow, no tuvo el resultado esperado. > > Lo malo fue que hicimos un DELETE del usuario repetido, con el mismo > resultado: se borraron las dos tuplas :-) y no habia forma de volver a > crearlo. > > Asi que lo que hicimos fue bajar el postmaster, abrir un proceso > standalone, crear el usuario nuevamente, volver a levantar postmaster. > -- Daniel Ricardo Medina R. Ing. de Desarrollo Computec S.A. - Documentos tel: 2607111 ext. 140 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-es-ayuda] [ADMIN] tabla pg_user Ayuda......
Trato de borrar el usuario postgres y me dice que no existe , luego vuelvo a consultar la tabla pg_shadow y sale esto 1: usename = "postgres" typeid = 19, len = 64, typmod = -1, byval = f) 2: usesysid = "1" (typeid = 23, len = 4, typmod = -1, byval = t) 3: usecreatedb = "t" (typeid = 16, len = 1, typmod = -1, byval = t) 4: usesuper = "t" (typeid = 16, len = 1, typmod = -1, byval = t) 5: usecatupd = "t" (typeid = 16, len = 1, typmod = -1, byval = t) 1: usename = "daniel" (typeid = 19, len = 64, typmod = -1, byval = f) 2: usesysid = "1" (typeid = 23, len = 4, typmod = -1, byval = t) 3: usecreatedb = "f" (typeid = 16, len = 1, typmod = -1, byval = t) 4: usesuper = "f" (typeid = 16, len = 1, typmod = -1, byval = t) 5: usecatupd = "f" (typeid = 16, len = 1, typmod = -1, byval = t) 6: passwd = "md5xxx" (typeid = 25, len = -1, typmod = -1, byval = f) El jue, 15-02-2007 a las 10:49 -0300, Alvaro Herrera escribió: > Jaime Casanova escribió: > > > Alvaro, un vacuum como superusuario podria haber arreglado algo? o > > hubiera tenido que borrar la tupla que pareciera mas vieja? o ninguna > > de ambas podria haber funcionado? estoy asumiendo que el problema es > > el que mencione arriba, no se me ocurre que mas pudo ser... > > La verdad es que no estoy 100% seguro porque no sabemos en que estado > estaban esas tuplas ... pero hace un tiempo le hice una consultoria a > una empresa que tuvo un problema muy similar, y cuando les dije que > hicieran un VACUUM sobre pg_shadow, no tuvo el resultado esperado. > > Lo malo fue que hicimos un DELETE del usuario repetido, con el mismo > resultado: se borraron las dos tuplas :-) y no habia forma de volver a > crearlo. > > Asi que lo que hicimos fue bajar el postmaster, abrir un proceso > standalone, crear el usuario nuevamente, volver a levantar postmaster. > -- Daniel Ricardo Medina R. Ing. de Desarrollo Computec S.A. - Documentos tel: 2607111 ext. 140 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] [pgsql-es-ayuda] INSTALACION ADICIONAL POSTGRES
Pregunta > > tengo instalado postgres en el la carpeta /usr/local/pgsql/ , tengo > errores en las tablas de postgres y no me deja hacer backup, he > intentado modificar las tablas por medio de una conexion stand alone y > nada... > > puedo instalar postgres en otra carpeta , por ejempo /opt/postgres e > iniciar el servicio direcionando la carpeta de la base ya creada por > la otra instalación ??? > > al instalar postgres en otra carpeta puedo dañar las instalacion > anterior ??? > > > Gracias por su ayuda. > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] [pgsql-es-ayuda] INSTALACION ADICIONAL POSTGRES
Si señor, tiene toda la razon, el problema esta en las tablas de la base, asi que me da lo mismo. gracias.. Alvaro Herrera wrote: Daniel Medina wrote: Pregunta tengo instalado postgres en el la carpeta /usr/local/pgsql/ , tengo errores en las tablas de postgres y no me deja hacer backup, he intentado modificar las tablas por medio de una conexion stand alone y nada... puedo instalar postgres en otra carpeta , por ejempo /opt/postgres e iniciar el servicio direcionando la carpeta de la base ya creada por la otra instalación ??? al instalar postgres en otra carpeta puedo dañar las instalacion anterior ??? No, si lo haces con cuidado no hay por que dañar la otra instalacion. Pero ojo, no necesitas instalar Postgres en otra ubicacion; tu problema probablemente no tiene nada que ver con la instalacion de Postgres sino con los archivos de las tablas. Asi, puedes perfectamente instalar en /opt/postgres, pero lo mas probable es que te arroje el mismo error. ---(end of broadcast)--- TIP 1: 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
Re: [ADMIN] [pgsql-es-ayuda] INSTALACION ADICIONAL POSTGRES
Tengo instalado postgres en otra maquina exactamente igual a la de produccion , cree los mismos usuarios y base, pero si data,, alguien sabe cual es el archivo interno de postgres donde esta la tabla pg_shadow para copiarla al postgres que tiene la tabla corrupta he copiado la carpeta global, de produccion al de prueba y funciona bien ,, pero no puedo replicar el problema Gracias... Daniel Medina wrote: Si señor, tiene toda la razon, el problema esta en las tablas de la base, asi que me da lo mismo. gracias.. Alvaro Herrera wrote: Daniel Medina wrote: Pregunta tengo instalado postgres en el la carpeta /usr/local/pgsql/ , tengo errores en las tablas de postgres y no me deja hacer backup, he intentado modificar las tablas por medio de una conexion stand alone y nada... puedo instalar postgres en otra carpeta , por ejempo /opt/postgres e iniciar el servicio direcionando la carpeta de la base ya creada por la otra instalación ??? al instalar postgres en otra carpeta puedo dañar las instalacion anterior ??? No, si lo haces con cuidado no hay por que dañar la otra instalacion. Pero ojo, no necesitas instalar Postgres en otra ubicacion; tu problema probablemente no tiene nada que ver con la instalacion de Postgres sino con los archivos de las tablas. Asi, puedes perfectamente instalar en /opt/postgres, pero lo mas probable es que te arroje el mismo error. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] [pgsql-es-ayuda] INSTALACION ADICIONAL POSTGRES
Estaba tratando de crear una replica del servido y copiar los archivos de postgres para corregir el error... ahora en la maquina de prueba genere un dump de pg_shadow con lo mismos datos del de produccion, si entro como usuario standalone sobre la maquina de produccion borro la tabla pg_shadow y la creo con el dump que tengo del otro servidor funcionara ?? Daniel Medina wrote: Tengo instalado postgres en otra maquina exactamente igual a la de produccion , cree los mismos usuarios y base, pero si data,, alguien sabe cual es el archivo interno de postgres donde esta la tabla pg_shadow para copiarla al postgres que tiene la tabla corrupta he copiado la carpeta global, de produccion al de prueba y funciona bien ,, pero no puedo replicar el problema Gracias... Daniel Medina wrote: Si señor, tiene toda la razon, el problema esta en las tablas de la base, asi que me da lo mismo. gracias.. Alvaro Herrera wrote: Daniel Medina wrote: Pregunta tengo instalado postgres en el la carpeta /usr/local/pgsql/ , tengo errores en las tablas de postgres y no me deja hacer backup, he intentado modificar las tablas por medio de una conexion stand alone y nada... puedo instalar postgres en otra carpeta , por ejempo /opt/postgres e iniciar el servicio direcionando la carpeta de la base ya creada por la otra instalación ??? al instalar postgres en otra carpeta puedo dañar las instalacion anterior ??? No, si lo haces con cuidado no hay por que dañar la otra instalacion. Pero ojo, no necesitas instalar Postgres en otra ubicacion; tu problema probablemente no tiene nada que ver con la instalacion de Postgres sino con los archivos de las tablas. Asi, puedes perfectamente instalar en /opt/postgres, pero lo mas probable es que te arroje el mismo error. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] Problems connecting to postgres using JDBC Driver.
Hello. I need your help. I have a box running Fedora Core 6. I installed the Postgres Server using yum, so i have the distributing software. Ever since i installed it im having problems connecting my java applications to the database. But first thing first: This is the error message i get when connecting to the database: Checking if Driver is registered with DriverManager. Registered the driver ok, so let's make a connection. Couldn't connect: print out a stack trace and exit. org.postgresql.util.PSQLException: FATAL: missing or erroneous pg_hba.conf file at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication( ConnectionFactoryImpl.java:275) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl( ConnectionFactoryImpl.java:94) at org.postgresql.core.ConnectionFactory.openConnection( ConnectionFactory.java:65) at org.postgresql.jdbc2.AbstractJdbc2Connection.( AbstractJdbc2Connection.java:116) at org.postgresql.jdbc3.AbstractJdbc3Connection.( AbstractJdbc3Connection.java:30) at org.postgresql.jdbc3.Jdbc3Connection.(Jdbc3Connection.java :24) at org.postgresql.Driver.makeConnection(Driver.java:369) at org.postgresql.Driver.connect(Driver.java:245) at java.sql.DriverManager.getConnection(DriverManager.java:525) at java.sql.DriverManager.getConnection(DriverManager.java:171) at Connect.main(Connect.java:26) the pg_hba.conf file is located in: /var/lib/pgsql/dataand it looks like this: # TYPE DATABASEUSERCIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only #local all all ident sameuser local all all md5 # IPv4 local connections: #hostall all 127.0.0.1/32 ident sameuser hostall all ubuntu md5 hostall all 127.0.0.1/32md5 # IPv6 local connections: #hostall all ::1/128 ident sameuser hostall all ::1/128 md5 somewhere I read that i should live a blank line at the very bottom of the file, so i have... Also, i have checked that each value is separated by a tab. Also, i read that the PATH and the LD_LIBRARY_PATH should include the path to the postgres configuration. So on /etc/bashrc i have the following: export LD_LIBRARY_PATH=$JMFHOME/lib:/var/lib/pgsql/data/:$LD_LIBRARY_PATH export PATH=/var/lib/pgsql/data/:$PATH Now, this is the weird thing... I can connect using the psql client. psql -t template1 -U damunoz and then entering the password So I have no idea what is going on... I tested with different drivers with the same results. My guess is that maybe this has something to do with the Fedora's distribution. I will post this same message to them as well. Thanks in advance for your help! daniel -- ==== Daniel Muñoz Honiball [EMAIL PROTECTED] www.karinasoft.com Software para la Gloria de Dios. Si se humillare mi pueblo, sobre los cuales ni nombre es invocado, y oraren, y buscaren mi rostro, y se convirtieren de sus malos caminos; entonces yo oiré desde los cielos, y perdonaré sus pecados, y sanaré su tierra. 2 Crónicas 7-14
Re: [ADMIN] Problems connecting to postgres using JDBC Driver.
Hi Neil. On postgresql.conf I changed the listen_address to *. Then commented the first host entry on pg_hba.conf then restarted the server. I wasnt able to log to the client on the command line (psql) because or missing or erroneos pg_hba.conf. So I added again the first host entry and restarted the server again. This time I was able to log using the client. So I tried again the java application and I have the same error message!... BUT I JUST RECEIVED THE ANSWERED FROM A FEDORA FORUM!!! I wouldn't have guessed it never!! It turns out that the pg_hba.conf does not accept names to identify a host, only IPs and i have the name of one of my computers. I removed that host line and it works!!! The person who posted refers to this URL: http://www.postgresql.org/docs/8.0/interactive/client-authentication.html#AUTH-PG-HBA-CONF Thanks alot for your help!! daniel On 8/27/07, Neil Peter Braggio <[EMAIL PROTECTED]> wrote: > > Check the parameter listen_addresses in the postgresql.conf file to set > the correct value for you (may be '*' to test). > > Remove or comment the first host entry. I guess it is not a valid > CIDR-ADDRESS value ;-) > > Restart your server and try again your java application. > > > Neil Peter Braggio > [EMAIL PROTECTED] > > > On 8/27/07, Daniel Muñoz <[EMAIL PROTECTED]> wrote: > > > > Hello. I need your help. > > > > I have a box running Fedora Core 6. I installed the Postgres Server > > using yum, so i have the distributing software. > > > > Ever since i installed it im having problems connecting my java > > applications to the database. > > > > But first thing first: > > > > This is the error message i get when connecting to the database: > > Checking if Driver is registered with DriverManager. > > Registered the driver ok, so let's make a connection. > > Couldn't connect: print out a stack trace and exit. > > org.postgresql.util.PSQLException: FATAL: missing or erroneous > > pg_hba.conf file > > at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication > > (ConnectionFactoryImpl.java :275) > > at > > org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl( > > ConnectionFactoryImpl.java:94) > > at org.postgresql.core.ConnectionFactory.openConnection( > > ConnectionFactory.java:65) > > at org.postgresql.jdbc2.AbstractJdbc2Connection.( > > AbstractJdbc2Connection.java:116) > > at org.postgresql.jdbc3.AbstractJdbc3Connection.( > > AbstractJdbc3Connection.java:30) > > at org.postgresql.jdbc3.Jdbc3Connection .( > > Jdbc3Connection.java:24) > > at org.postgresql.Driver.makeConnection(Driver.java:369) > > at org.postgresql.Driver.connect(Driver.java:245) > > at java.sql.DriverManager.getConnection(DriverManager.java :525) > > at java.sql.DriverManager.getConnection(DriverManager.java:171) > > at Connect.main(Connect.java:26) > > > > > > the pg_hba.conf file is located in: /var/lib/pgsql/dataand it looks > > like this: > > > > # TYPE DATABASEUSERCIDR-ADDRESS METHOD > > > > # "local" is for Unix domain socket connections only > > #local all all ident sameuser > > local all all md5 > > # IPv4 local connections: > > #hostall all 127.0.0.1/32 ident sameuser > > hostall all ubuntu md5 > > hostall all 127.0.0.1/32md5 > > # IPv6 local connections: > > #hostall all ::1/128 ident sameuser > > hostall all ::1/128 md5 > > > > > > somewhere I read that i should live a blank line at the very bottom of > > the file, so i have... Also, i have checked that each value is separated by > > a tab. Also, i read that the PATH and the LD_LIBRARY_PATH should include > > the path to the postgres configuration. So on /etc/bashrc i have the > > following: > > > > export > > LD_LIBRARY_PATH=$JMFHOME/lib:/var/lib/pgsql/data/:$LD_LIBRARY_PATH > > export PATH=/var/lib/pgsql/data/:$PATH > > > > > > Now, this is the weird thing... I can connect using the psql client. > > > > psql -t template1 -U damunoz > > and then entering the password > > > > So I have no idea what is going on... I tested with different drivers > > with the same results. My guess is that maybe this has something to do with > > the Fedora's distribution. I will post this same message to them as well. >
[ADMIN] Can't login to 8.2.4 if not superuser...
Hi all I'm experiencing a weird situation with my test installation for 8.2.4. I installed with no problems on Solaris 10, and I've imported my databases and users from a 7.4.14 using the 8.2.4 pg_dumpall without excessive problems (some things about permissions on some tables, but I think it's not related). If I log via psql using the postgres user via psql there's no problem, but If I try to log as a different user, the postmaster process hangs and I can't connect. On the log file (debug1, using syslog) I see: Sep 5 09:24:10 pluto2 pg[7698]: [ID 748848 local4.info] [14-1] LOG: connection received: host=[local] Sep 5 09:24:10 pluto2 pg[7698]: [ID 748848 local4.info] [15-1] LOG: connection authorized: user=xxx database=xxx Sep 5 09:24:21 pluto2 pg[833]: [ID 748848 local4.info] [14-1] LOG: server process (PID 7698) was terminated by signal 11 Sep 5 09:24:21 pluto2 pg[833]: [ID 748848 local4.info] [15-1] LOG: terminating any other active server processes Sep 5 09:24:21 pluto2 pg[833]: [ID 748848 local4.info] [16-1] LOG: all server processes terminated; reinitializing Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [17-1] LOG: database system was interrupted at 2007-09-05 09:08:10 CEST Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [18-1] LOG: checkpoint record is at 0/2CFCB380 Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [19-1] LOG: redo record is at 0/2CFCB380; undo record is at 0/0; shutdown TRUE Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [20-1] LOG: next transaction ID: 0/2662; next OID: 917504 Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [21-1] LOG: next MultiXactId: 1; next MultiXactOffset: 0 Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [22-1] LOG: database system was not properly shut down; automatic recovery in progress Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [23-1] LOG: record with zero length at 0/2CFCB3D0 Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [24-1] LOG: redo is not required Sep 5 09:24:21 pluto2 pg[7700]: [ID 748848 local4.info] [25-1] LOG: database system is ready And in the command line: bash-3.00# ../bin/psql -d xxx -U xxx psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. If I login with the postgres user there's no problem, and if I set the user xxx as superuser (using pgAdmin) I can login with it too ... Actually, for test purposes, my pg_hba.conf settings are: # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1 255.255.255.255 password hostall all xxx.xxx.xxx.xxx 255.255.255.255 password hostall all yyy.yyy.yyy.yyy 255.255.255.255 password Where xxx.xxx.xxx.xxx is my host public ip address and yyy.yyy.yyy.yyy my laptop IP to access with pgAdmin. Some idea about what it's happening? -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[ADMIN] No pg_dumplo on 8.2.4
Hi again, a different question ... On a previous upgrade to 7.4 (I think) I used the pg_dumplo contrib utility to add the large objects to my restored via pg_dumpall databases (not all using large objects). I liked it because it was easy and I hadn't to remove the databases with large objects to reimport them with the dumped via pg_dump versions which seemed more work (and more possibilities of problems). Now I see that theres is not pg_dumplo on contrib directory for 8.2.4 (or at least i did'nt found it)... Which is the best method to import my large objects in this case? 1.- Import all the stuff via pg_dumpall+psql, drop databases with LO, import LO databases with pg_dump+psql 2.-Import all the stuff via pg_dumpall+psql, import LO databases with pg_dump+psql (without delete them) 3.- ?? Thanks in advance -- ******** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Can't login to 8.2.4 if not superuser...
En/na Tom Lane ha escrit: That's not a hang, it's a core dump. Can you get a stack trace with gdb or local equivalent? Here's the stack trace, I'm not sure this is all you need (it's the first time I use gdb) so, tell me if you need more information Core was generated by `/aplicacions/postgres/bin/postmaster -D /aplicacions/postgres/data'. Program terminated with signal 11, Segmentation fault. #0 0x001a66b4 in HaveNFreeProcs () (gdb) backtrace #0 0x001a66b4 in HaveNFreeProcs () #1 0x0024deb4 in InitPostgres () #2 0x001b1464 in PostgresMain () #3 0x001837f0 in ServerLoop () #4 0x00184cd8 in PostmasterMain () #5 0x001373f8 in main () regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- ******** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] Can't login to 8.2.4 if not superuser...
Our new Server is a sun T2000 with the "CoolThreads" technology. Wi installed the recomended gcc version to take advantage of the new features of this technology: Using built-in specs. Target: sparc-sun-solaris2.10 Configured with: /net/clpt-v490-1/export/data/bldmstr/20070425_mars_gcc/src/configure --prefix=/usr/sfw --enable -shared --with-system-zlib --enable-checking=release --disable-libmudflap --enable-languages=c,c++ --enable-vers ion-specific-runtime-libs --with-cpu=v9 --with-ld=/usr/ccs/bin/ld --without-gnu-ld Thread model: posix gcc version 4.0.4 (gccfss) Here is the pg_config output: BINDIR = /usr/bin DOCDIR = /usr/share/doc/pgsql/8.1.4 INCLUDEDIR = /usr/include/pgsql PKGINCLUDEDIR = /usr/include/pgsql INCLUDEDIR-SERVER = /usr/include/pgsql/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib LOCALEDIR = /usr/share/locale MANDIR = /usr/share/man SHAREDIR = /usr/share/pgsql SYSCONFDIR = /etc/pgsql PGXS = /usr/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/usr' '--bindir=/usr/bin' '--libexecdir=/usr/bin' '--sbindir=/usr/bin' '--datadir=/usr/share/pgsql' '--sysconfdir=/etc/pgsql' '--mandir=/usr/share/man' '--libdir=/usr/lib' '--includedir=/usr/include/pgsql' '--sharedstatedir=/var/lib/pgsql' '--localstatedir=/var/lib/pgsql' '--enable-nls' '--with-localedir=/usr/share/locale' '--with-docdir=/usr/share/doc/pgsql/8.1.4' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--enable-thread-safety' '--with-includes=/sfw10/builds/build/sfw10-patch/usr/src/cmd/postgres/rltmp/include:/usr/sfw/include' '--with-tclconfig=/usr/sfw/lib' '--with-libs=/sfw10/builds/build/sfw10-patch/usr/src/cmd/postgres/rltmp/lib:/usr/sfw/lib:/usr/lib' 'CC=/ws/on10-tools/SUNWspro/SOS8/bin/cc' 'CFLAGS=-xc99=none -xCC' CC = /ws/on10-tools/SUNWspro/SOS8/bin/cc CPPFLAGS = -I/sfw10/builds/build/sfw10-patch/usr/src/cmd/postgres/rltmp/include -I/usr/sfw/include CFLAGS = -xO3 -xarch=v8 -xspace -W0,-Lt -W2,-Rcond_elim -Xa -xildoff -xc99=none -xCC CFLAGS_SL = -KPIC LDFLAGS = -L/sfw10/builds/build/sfw10-patch/usr/src/cmd/postgres/rltmp/lib -L/usr/sfw/lib -L/usr/lib -Wl,-R/usr/lib LDFLAGS_SL = LIBS = -lpgport -lpam -lssl -lcrypto -lz -lreadline -ltermcap -lrt -lresolv -lgen -lsocket -lnsl -ldl -lm VERSION = PostgreSQL 8.1.4 Now I'm going to recompile using a 3.4.3 version to see what happens... En/na Tom Lane ha escrit: Daniel Rubio <[EMAIL PROTECTED]> writes: Here's the stack trace, I'm not sure this is all you need (it's the first time I use gdb) so, tell me if you need more information Core was generated by `/aplicacions/postgres/bin/postmaster -D /aplicacions/postgres/data'. Program terminated with signal 11, Segmentation fault. #0 0x001a66b4 in HaveNFreeProcs () (gdb) backtrace #0 0x001a66b4 in HaveNFreeProcs () #1 0x0024deb4 in InitPostgres () #2 0x001b1464 in PostgresMain () #3 0x001837f0 in ServerLoop () #4 0x00184cd8 in PostmasterMain () #5 0x001373f8 in main () Well, that's pretty darn odd. It looks like you could work around the crash by setting superuser_reserved_connections to 0, but that doesn't tell us *why* it's crashing. HaveNFreeProcs, and the data structure it looks at, are simple enough that it's hard to credit a bug there. I'm worried that what you've got is really a compiler bug. What compiler did you build with, and did you use any nondefault CFLAGS? (pg_config output would be helpful here.) regards, tom lane -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org
Re: [ADMIN] Can't login to 8.2.4 if not superuser...
Using the older gcc version to compile, the problem disappears ... En/na Daniel Rubio ha escrit: Our new Server is a sun T2000 with the "CoolThreads" technology. Wi installed the recomended gcc version to take advantage of the new features of this technology: Using built-in specs. Target: sparc-sun-solaris2.10 Configured with: /net/clpt-v490-1/export/data/bldmstr/20070425_mars_gcc/src/configure --prefix=/usr/sfw --enable -shared --with-system-zlib --enable-checking=release --disable-libmudflap --enable-languages=c,c++ --enable-vers ion-specific-runtime-libs --with-cpu=v9 --with-ld=/usr/ccs/bin/ld --without-gnu-ld Thread model: posix gcc version 4.0.4 (gccfss) Here is the pg_config output: BINDIR = /usr/bin DOCDIR = /usr/share/doc/pgsql/8.1.4 INCLUDEDIR = /usr/include/pgsql PKGINCLUDEDIR = /usr/include/pgsql INCLUDEDIR-SERVER = /usr/include/pgsql/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib LOCALEDIR = /usr/share/locale MANDIR = /usr/share/man SHAREDIR = /usr/share/pgsql SYSCONFDIR = /etc/pgsql PGXS = /usr/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/usr' '--bindir=/usr/bin' '--libexecdir=/usr/bin' '--sbindir=/usr/bin' '--datadir=/usr/share/pgsql' '--sysconfdir=/etc/pgsql' '--mandir=/usr/share/man' '--libdir=/usr/lib' '--includedir=/usr/include/pgsql' '--sharedstatedir=/var/lib/pgsql' '--localstatedir=/var/lib/pgsql' '--enable-nls' '--with-localedir=/usr/share/locale' '--with-docdir=/usr/share/doc/pgsql/8.1.4' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--enable-thread-safety' '--with-includes=/sfw10/builds/build/sfw10-patch/usr/src/cmd/postgres/rltmp/include:/usr/sfw/include' '--with-tclconfig=/usr/sfw/lib' '--with-libs=/sfw10/builds/build/sfw10-patch/usr/src/cmd/postgres/rltmp/lib:/usr/sfw/lib:/usr/lib' 'CC=/ws/on10-tools/SUNWspro/SOS8/bin/cc' 'CFLAGS=-xc99=none -xCC' CC = /ws/on10-tools/SUNWspro/SOS8/bin/cc CPPFLAGS = -I/sfw10/builds/build/sfw10-patch/usr/src/cmd/postgres/rltmp/include -I/usr/sfw/include CFLAGS = -xO3 -xarch=v8 -xspace -W0,-Lt -W2,-Rcond_elim -Xa -xildoff -xc99=none -xCC CFLAGS_SL = -KPIC LDFLAGS = -L/sfw10/builds/build/sfw10-patch/usr/src/cmd/postgres/rltmp/lib -L/usr/sfw/lib -L/usr/lib -Wl,-R/usr/lib LDFLAGS_SL = LIBS = -lpgport -lpam -lssl -lcrypto -lz -lreadline -ltermcap -lrt -lresolv -lgen -lsocket -lnsl -ldl -lm VERSION = PostgreSQL 8.1.4 Now I'm going to recompile using a 3.4.3 version to see what happens... En/na Tom Lane ha escrit: > Daniel Rubio <[EMAIL PROTECTED]> writes: > >> Here's the stack trace, I'm not sure this is all you need (it's the >> first time I use gdb) so, tell me if you need more information >> > > >> Core was generated by `/aplicacions/postgres/bin/postmaster -D >> /aplicacions/postgres/data'. >> Program terminated with signal 11, Segmentation fault. >> #0 0x001a66b4 in HaveNFreeProcs () >> (gdb) backtrace >> #0 0x001a66b4 in HaveNFreeProcs () >> #1 0x0024deb4 in InitPostgres () >> #2 0x001b1464 in PostgresMain () >> #3 0x001837f0 in ServerLoop () >> #4 0x00184cd8 in PostmasterMain () >> #5 0x001373f8 in main () >> > > Well, that's pretty darn odd. It looks like you could work around the > crash by setting superuser_reserved_connections to 0, but that doesn't > tell us *why* it's crashing. HaveNFreeProcs, and the data structure > it looks at, are simple enough that it's hard to credit a bug there. > I'm worried that what you've got is really a compiler bug. What > compiler did you build with, and did you use any nondefault CFLAGS? > (pg_config output would be helpful here.) > >regards, tom lane > > > > -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: drubio a oasi.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[ADMIN] Postgres port bindings changed after box restart
Our redhat server was restarted and postgres is showing different databases on different ports? Could someone explain how this might happen and where this information comes from (what conf for port bindings) as is there a way of guarenteeing this will not happen on restarts. Cheers Dan
[ADMIN] psql data restore not working
I would be glad on advice what might be the issue (or how I could narrow it down) with the non restoration of data from a postgres 7.1 database to a postgres 8 database. The schema was dumped with pg_dump -O -s databasename > database_schema_file_name The data was dumped with pg_dump -O -a databasename > database_data_file_name the datafile was gzipped and transferred to another server and gunzipped the database was successfully created the schema was successfully generated psql -d databasename -f database_schema_file_name the datafile was cleaned for the carriage return/line feed issue (between pgsql 7-> 8) sed 's/^M/\\r/' datafilename > datafilename I attempted to insert the data psql -d databasename -f database_data_file_name no errors but when I query the data - there is none .. Any ideas what I might be missing or how to see any errors or warnings? Cheers Daniel Punton Senior Analyst/Programmer Transact/EForms Team CSC
Re: [ADMIN] unable to backup database -- psql not up to date
What OS are you running on? How did you perform this upgrade? In a packaged form like rpm? or did you compile from source? I am running FreeBSD 6.1 I compiled from source, and followed the instructions in the documentation about upgrading to a new major release (pg_dumpall, shutdown, mv old version, install new version, restore) -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] pg_dump's table is empty
I am dumping postgres 7 tables under the postgres account mydb# pg_dump -O -a -t mytable > mytable.out and am getting either empty dumps ( actual db tables are populated) or no dump at all. I have file write permissions and this process has worked in the past. Any suggestions what might be wrong? Cheers Dan
[ADMIN] Change user password
Hi, Is is possible to modify a user password using a command line script? I know about the ALTER USER command from within the client, but this looks like it's quite insecure, storing the password in the history. I know it's possible to insert the encrypted password with ALTER USER but that means generating the hash elsewhere. A simple 'alteruser -P username' which then prompts for the user password would be nice. Does this exist? Thanks, Dan Scott -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Unaccent contrib module for PostgreSQL 8.4
Hi guys, I read in Unaccent contrib module that's compatible with PostgresSQL 8.4 and 9.x I know it is by default on PostgreSQL 9.x contrib folder, but how I can install it on a PostgreSQL 8.4 production running Server??. Unaccent module is not in postgresql 8.4 contrib folder by default. Thanks!
[ADMIN] Unaccent contrib module for PostgreSQL 8
Hi guys, I read in Unaccent contrib module that's compatible with PostgresSQL 8.4 and 9.x I know it is by default on PostgreSQL 9.x contrib folder, but how I can install it on a PostgreSQL 8.4 production running Server??. Unaccent module is not in postgresql 8.4 contrib folder by default. Thanks!
[ADMIN] listing installed modules
How to get a list of contrib module installed extensions on Postgresql 8.4?? I see \dx command for 9.1 but not works on 8.4 thx!
[ADMIN] Data corruption / recover
Hi, We're dealing with the following situation (PG version is 7.4.30): somehow, a DB has gone missing from pg_database (psql \l listing doesn't show it anymore). More, while you can still \c to it, a \d table listing only shows a fraction of the tables. A \d on the existing tables shows missing columns as well.. However, all the tables (still visible or not) may be queried by selects via psql and the actual data and columns in the result sets seem just fine. We backed up the data folder and for the last 24h tried to recover the situation to a point where pg_dump works. Unfortunately we haven't been fully successful yet although we tried tons of suggestions in previous similar posts. Reindex didn't help, but a "VACUUM *FULL* FREEZE [ANALYZE]" did free 70% (14GB to 4) of the used disk space. Of course, the database wasn't vacuumed regularly as it should have been so: /WARNING: some databases have not been vacuumed in over 2 billion transactions// //DETAIL: You may have already suffered transaction-wraparound data loss.// / A full vacuum _without_ the freeze option does make all databases, tables and users visible, but duplicated, i.e. \l then shows the database twice and a \d on the database lists all tables twice. The postgres user appears twice in pg_user, etc. pg_dump won't work if the database is not listed and won't either if the postgres user is duplicated.. Does this sound familiar to anyone? It's a pity that all the data seems to be there, but we're not able to recover and restore it to a clean DB. We'd appreciate any suggestions. Thank you!
Re: [ADMIN] Data corruption / recover
On 28.02.2013 18:48, Scott Marlowe wrote: On Thu, Feb 28, 2013 at 7:21 AM, Tom Lane wrote: Daniel Paval writes: We're dealing with the following situation (PG version is 7.4.30): 7.4? I sure hope you're going to migrate onto something less obsolete once you get out of this problem. In any still-in-support release series, autovacuum would have saved you from this. Also don't later versions refuse to start in anything other than single user mode once they get close to wrap around? Yes, I found out that newer versions play on the safe side and won't allow wrap-around at all. -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Problem with data migration from 9.1 to 9.2
Hi, I'm using Arch Linux i686, and postgresql-old-upgrade package which provides /opt/pgsql-9.1/ Here is exactly what I did (this procedure has always worked for me) su - postgres cd /var/lib/postgres mv data data-9.1 mkdir data initdb --locale en_US.UTF-8 -D /var/lib/postgres/data pg_upgrade -b /opt/pgsql-9.1/bin/ -B /usr/bin/ -d /var/lib/postgres/data-9.1 -D /var/lib/postgres/data' Every step is marked "ok". This is my first migration from 9.1 to 9.2. The only difference from what I usually do, is that this time I needed to set --locale en_US.UTF-8, because of pg_upgrade error: lc_collate cluster values do not match: old "en_US.UTF-8", new "C" Now I can connect, but database looks empty, like Postgre couldn't see my schemas and users. pg_upgrade did "something" because: du -s data* 34876 data 34068 data-9.1 Moving back and run 9.1 to use pg_dumpall is quite impossible, or at least vary difficult with Arch Linux, so I would really appreciate some other suggestions. -- Daniel Krysiak -- Sent via pgsql-admin mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] postgres user with automate rsync and private/public key pairs
Hi here! Centos 6.4 Postgres 9.1 I want to automate rsync backups from server1 to server2 via ssh using the postgres(linux deamon user) and a pair of private/public keys generated by ssh-keygen I generated the keys in Server1 and copy and added id_rsa.pub to /var/lib/pgsql/.ssh/authorized_keys in Server2. all owned by postgres:postgres and chmod 700 to .ssh/ folder and chmod 600 to authorized_key file. But when try to test it and try to access via ssh always ask for postgres password. Same procedure works properly for others user, but not for postgres user. postgres user is installed without password, I believed that this is the problem and I try to set a password, but the problem persists although postgres user has a password, can connect without avoid the ssh password required prompt. At first it seems that everything is properly configured, I forget something? Can postgres (linux user) login via ssh with private/public pair keys? Last think ... I try to reset the postgres user as initial setup (without password) using passwd -d command but shadow file shows different blank password is not as double exclamation I don't know if it's indiferent for correct postgres work or there are some way to reset original postgres user values. postgres::15849:0:9:7::: instead of: postgres:!!:15646:: Thanks for your comments
[ADMIN] Partial dump backup -T parameter and sequence incorrect inclusion
Hi here! Postgres 9.1 pg_backup with -T parameter excluding dump explicit table. Then when pg_restore any object of excluded table are restored EXCEPT the sequence. That's a problem because if use -c to clean try to drop existent sequence object and then raises dependency error. Thanks!
RE: [ADMIN] Postgres Startup trouble
Thanks a lot. Your solution fixed the problem. Glad to be a part of such a knowledgeable mailing list. > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: Monday, August 27, 2001 5:43 PM > To: root > Cc: [EMAIL PROTECTED] > Subject: Re: [ADMIN] Postgres Startup trouble > > > root <[EMAIL PROTECTED]> writes: > >> action "Starting PostgreSQL: " su -l $PGUSER -c "$DAEMON start > -D '$PGDATA' -l $PGLOG" > > I dunno what "action" is, but I'll bet that the problem with this script > is that the arguments of su end up getting scanned twice in the shell > script, once when action is called and again somewhere inside it, > so that the stuff after -c ends up looking like multiple arguments to > su, not just one argument. > > If there are only two levels of scanning, it might work to say > > action "Starting PostgreSQL: " su -l $PGUSER -c "'$DAEMON start > -D $PGDATA -l $PGLOG'" > > The idea here is to substitute for the $-variables the first time > through, then have a single-quoted string seen by the second rescan. > > A complaint to the author of the "action" subroutine might be in order > too, since his sloppiness is forcing you into some ugly and rather > error-prone pushups. > > regards, tom lane > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Help request
I think it will run on the cygwin package (a Unix-like environment for windows), but not on native windows OS. I'm not sure where you can get Cygwin (maybe www.cygwin.org), but you can get the latest Postgres at www.postgresql.org. Daniel Bakken, Systems Administrator [EMAIL PROTECTED] Encore Trading West | www.encoretrading.com 254.771.3508 phone 254.771.3786 fax -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Yogesh ShahirSent: Monday, August 27, 2001 12:45 PMTo: [EMAIL PROTECTED]Subject: [ADMIN] Help request Hello Sir, Is postgreSQL is available for windows ? If yes, from where I can sownload it ? Please help me. Regards, Yogesh
[ADMIN] BLOBs
Is there a way to easily store images or other large files into the DB over a TCP/IP network. I would prefer Access/ODBC, but I could also use pgaccess if necessary. It would really be nice if there was a GUI way to do it. Basically, I want to copy a file from my hard drive directly to a PostgreSQL field. Any chance it will work? If so, where do I find out how to do it? Daniel Bakken, Systems Administrator [EMAIL PROTECTED] Encore Trading West | www.encoretrading.com 254.771.3508 phone 254.771.3786 fax ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[ADMIN] Load problems...
Hi, Does postgresql have any problems managing load? I'm running a medium sized (30k customers) ISP off a postgresql database but it can't seem to manage very well with the dozen or so requests per second it receives. The machine we are handling is plenty powerful enough for a database like this (a dual 1ghz p3 machine with 1gb of ram) but for some reason it never runs quite perfectly. 7.1.3 would overload the system rapidly and slow to a crawl, so we installed 7.2b4 only to find it runs like a dream (less that 10% cpu usage most of the time) but eventually fails to fork and crashes and burns, at which point i have to restart it. Could anyone shed any light on a possible solution to these problems? Thanks, Daniel Andersen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Load problems...
> > Does postgresql have any problems managing load? I'm running a medium > > sized (30k customers) ISP off a postgresql database but it can't seem to > > manage very well with the dozen or so requests per second it receives. > > The machine we are handling is plenty powerful enough for a database like > > this (a dual 1ghz p3 machine with 1gb of ram) but for some reason it > > never runs quite perfectly. 7.1.3 would overload the system rapidly and > > slow to a crawl, so we installed 7.2b4 only to find it runs like a dream > > (less that 10% cpu usage most of the time) but eventually fails to fork > > and crashes and burns, at which point i have to restart it. Could anyone > > shed any light on a possible solution to these problems? > > We're probably going to want to look for some more information. What does > the log before failure look like? Are there any warning signs there > (messages about running out of file descriptors, etc)? What do you have > the memory settings (shared buffers, sort memory, etc) and max connections > set to? sort_mem = 1024 shared_buffers = 2048 (tried higher, but didnt' seem to improve anything) max_connections = 300 error message just comes out of the blue, the database is chugging along happily and then suddenly[4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: could not launch checkpoint process (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable etc etc etc the database is still responsive up till this point, so i'm assuming there are limitations somewhere that i'm not aware of... ---(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
Re: [ADMIN] Load problems...
> Hello Daniel, > > I can't answer your specific question, but I _can_ > tell you that the gurus will probably need more information > that you presented. > > 1) What operating system and version? Slackware linux 8.0, with the kernel upgraded to 2.4.16, stripped to the bare bones for efficiency > 2) Have you tuned your OS, or is it a stock installation? see above > 3) "running a medium sized (30k customers) ISP off a postgresql > database" ?? Ie, you're authenticating users via PostgreSQL? > You're serving them webmail via PostgreSQL? You're sniffing > their network traffic and stuffing it into PostgreSQL? ;) > You get the idea. Radius authentication, email authentication, customers checking their downloads, and a banner system (we're a free isp), so probably a bit heavier than the figures may have suggested... > 4) You mention that 7.2b4 runs better than before, but still > ends up with fork errors. Exact error message? How many > PostgreSQL processes are running when this happens? Does > the user that PostgreSQL is running under have resource > limits? Are they too low? Is this server also their STMP > host, along with HTTP, HTTPS, FTP .. etc? If you're > getting fork errors, you have too many processes running, > most likely. Are your processes closing the database > connection when they're done with it? [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: could not launch checkpoint process (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable [4059] DEBUG: connection startup failed (fork failure): Resource temporarily unavailable etc ad nauseum there are generally up to 200 copies of postmaster running at any given time, but there aren't any limits imposed upon the postgres user. The server is purely for the database and radius (which doesn't use up enough cpu to matter) The connections are mostly coming in via the teapop mail server, which closes the connection when done, and php scripts, which are (at least theoretically) using persistent connections. Radius just opens a couple of connections and uses those exclusively. > > You see what I'm getting at. :) There are simply too many > variables without more details. > > Benny ---(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
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
Brian, No data in the system is corrupted, just most of the dump and output routines in order PostgreSQL versions assume that oid == int (signed int), which is not true since few releases... or maybe never was true. Note that if you reference anything by oid, you should change your queries from SELECT whatever FROM wherever WHERE oid = value; to SELECT whatever FROM wherever WHERE oid = oid(value); This has bitten me many times already :) I guess all your dump troubles are related to oid overflow. Daniel >>>Brian Fujito said: > Thanks again for your input. I think you're right about the OID's. > > Watching the query logs, the pg_dump failed just after: > 021210.00:58:25.578 [25527] query: SELECT * FROM pg_language WHERE > lanispl ORDER BY oid > > > > MYDB=> select * from pg_language where lanispl order by oid; > lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler > -+-+--+---+- > plpgsql | t | t| -1983262688 | PL/pgSQL > (1 row) > > MYDB=> select oid from pg_proc where proname = 'plpgsql_call_handler'; > oid > - > -1983262688 > (1 row) > > lifeaudioV2=> select * from pg_language where lanplcallfoid = > -1983262688; > lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler > -+-+--+---+- > (0 rows) > > MYDB=> > > > So yes, it looks like the OID's got messed up. That negative number > looks suspiciously like an overflow on a max int. Is there *any* way to > fix this, or should I just deal with it and upgrade? > > BTW, does this mean that other data in the system could be corrupted? > > If an upgrade is the best way out, are there any particular versions > that would a) fix the problem and b) allow for a clean transition? > > I'm using Perl's DBI::Pg, PHP's pgsql extensions, and JDBC for > postgres... Just a bit weary of making transitions :-) I understand > I'll have to perform a pg_dump for the transition to work, which I can > do by turning off the triggers, for now. > > > Thank you, Tom, for all your help. > Brian > > > On Tue, 2002-12-10 at 00:02, Tom Lane wrote: > > Brian Fujito <[EMAIL PROTECTED]> writes: > > >> What exactly are you doing to drop and re-add the language? I should > > >> think CREATE LANGUAGE would fail if the handler proc isn't there. > > > > > I've tried both ways: > > > > > createlang/droplang from the command line as user postgres > > > > > and: > > > > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > > > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > > > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' > > > HANDLER plpgsql_call_handler > > > LANCOMPILER 'PL/pgSQL'; > > > > Hrmph. Looks perfectly standard from here; I don't see why pg_dump is > > failing to find the handler. It would help to see what the server-side > > view of the transaction is like. Would you run pg_dump after setting > > query logging on (from memory, I think export PGOPTIONS="-d2" will work > > in 7.0, but too tired to check it) and then show us the tail end of the > > postmaster log after pg_dump fails? > > > >regards, tom lane > > > > PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2 > > billion; is it possible your pg_language OID for plpgsql is over 2G? > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- > Brian T. Fujito > Chief Technical Officer > www.christianity.com > www.lightsource.com > www.online-giving.com > [EMAIL PROTECTED] (703) 548-8900 > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
I have had similar troubles, related to oid overflow. I had to modify pg_dump to properly cast queries that contain oids. This is against 7.1.3 source. The patch was hacked quickly, in order to get a corrupted database reloaded, and this while I was traveling in another country... so it is far from perfect but saved my database(s). It also fixes other oid-related troubles of pg_dump. See attached file. Daniel >>>Brian Fujito said: > Thanks for your input-- > > I've tried both ways: > > createlang/droplang from the command line as user postgres > > and: > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' > HANDLER plpgsql_call_handler > LANCOMPILER 'PL/pgSQL'; > > I'm using pg_dump (not pg_dumpall) on the specific database on which > I created the language. > > I realize 7.0.3 is ancient (same with 7.1)... I just don't have the > time to deal with an upgrade quite yet. Soon enough :) In the mean > time, a stop-gap solution would definitely be appreciated. > > Thank you, > Brian > > > On Mon, 2002-12-09 at 14:28, Tom Lane wrote: > > Brian Fujito <[EMAIL PROTECTED]> writes: > > > I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs. > > > I recently added plpgsql as a language to one of my databases, > > > and now when I try to do a pg_dump, I get: > > > > > "dumpProcLangs(): handler procedure for language plpgsql not found" > > > > > If I drop the language, pg_dump works fine, but if I add it back (and > > > even if I restart postgres), I get the same error. > > > > What exactly are you doing to drop and re-add the language? I should > > think CREATE LANGUAGE would fail if the handler proc isn't there. > > > > (Also, are you doing pg_dump or pg_dumpall? If the latter, maybe the > > failure is occurring in a different database than the one you are > > changing.) > > > > But having said that, 7.0.3 is ancient history ... you really are > > overdue for an upgrade. With my Red Hat fedora on, I'd say the same > > about your choice of OS version too. > > > >regards, tom lane > > > > ---(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 *** pg_dump.c.orig Mon Apr 15 09:45:58 2002 --- pg_dump.c Tue Jun 25 00:23:53 2002 *** *** 2006,2012 finfo[i].prorettype = strdup(PQgetvalue(res, i, i_prorettype)); finfo[i].retset = (strcmp(PQgetvalue(res, i, i_proretset), "t") == 0); finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs)); ! finfo[i].lang = atoi(PQgetvalue(res, i, i_prolang)); finfo[i].usename = strdup(PQgetvalue(res, i, i_usename)); finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable), "t") == 0); finfo[i].isstrict = (strcmp(PQgetvalue(res, i, i_isstrict), "t") == 0); --- 2006,2012 finfo[i].prorettype = strdup(PQgetvalue(res, i, i_prorettype)); finfo[i].retset = (strcmp(PQgetvalue(res, i, i_proretset), "t") == 0); finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs)); ! finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang)); finfo[i].usename = strdup(PQgetvalue(res, i, i_usename)); finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable), "t") == 0); finfo[i].isstrict = (strcmp(PQgetvalue(res, i, i_isstrict), "t") == 0); *** *** 2289,2295 resetPQExpBuffer(query); appendPQExpBuffer(query, ! "SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = %s ", tblinfo[i].oid); res2 = PQexec(g_conn, query->data); if (!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK) --- 2289,2295 resetPQExpBuffer(query); appendPQExpBuffer(query, ! "SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = oid(%s) ",
Re: [ADMIN] Large objects - SOS
Hi, in 7.3 this changed. type : CREATE DOMAIN lo AS oid; And everything works well. Daniel <[EMAIL PROTECTED]> schrieb im Newsbeitrag OF0B8A93EA.20F6C498-ON65256CC3.0043C73A@LocalDomain">news:OF0B8A93EA.20F6C498-ON65256CC3.0043C73A@LocalDomain... > Hi, > I need to create a large object - a photograph and work with it. I would be > using ODBC (application on Windows NT , db server on Linux) > The doc at > http://gborg.postgresql.org/project/psqlodbc/faq/faq.php?faq_id=52 > suggests creating a custom type like this. > create type lo ( >internallength=4, >externallength=10, >input=int4in, >output=int4out, >default='', >passedbyvalue > ); > > I need to define the input & output functions (int4in and int4out). > Can someone give me a clue as to how these functions should be defined? > Some sample code would really help me. > > Regards, > Manoj > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] system indices growing huge
|25785954 | 13 | 100 objekte_preis_cent_idx |25785958 | 13 | 100 objekte_nk_cent_idx |25785959 | 13 | 100 objekte_baujahr_idx |25785966 | 13 | 100 pg_description | 16416 | 12 |1 pg_toast_8283002| 8283004 | 12 | 100 objekte_typ_id_idx |25785952 | 12 | 100 objekte_n_global_idx|25785956 | 12 | 100 objekte_grundstueck_idx |25785960 | 12 | 100 objekte_nutzflaeche_idx |25785962 | 12 | 100 objekte_n_zimmer_idx|25785964 | 12 | 100 objekte_zeitpunkt_idx |25785953 | 11 | 100 objekte_wohnflaeche_idx |25785961 | 11 | 100 (40 rows) testdb=# select sum (relpages) from pg_class; sum --- 50605 (1 row) testdb=# select relname, relfilenode, relpages, relowner from pg_class order by relpages desc limit 40; relname | relfilenode | relpages | relowner -+-+--+-- pg_attribute_relid_attnam_index | 16426 |25196 |1 pg_attribute_relid_attnum_index | 16427 |10470 |1 pg_class_relname_index | 16429 | 7400 |1 pg_class_oid_index | 16428 | 3480 |1 pg_type_typname_index | 16458 | 947 |1 pg_type_oid_index | 16457 | 519 |1 pg_index_indrelid_index | 16435 | 505 |1 pg_index_indexrelid_index | 16436 | 505 |1 pg_attribute|1249 | 403 |1 pg_attrdef_adrelid_adnum_index | 16425 | 178 |1 pg_attrdef | 16384 | 124 |1 objekte |27150520 | 99 | 100 pg_index| 16390 | 40 |1 pg_type |1247 | 32 |1 pg_proc |1255 | 31 |1 pg_proc_proname_narg_type_index | 16446 | 29 |1 medien |27150488 | 16 | 100 pg_description | 16416 | 12 |1 objekte_kriterien_idx |27155393 | 11 | 100 pg_operator | 16392 | 10 |1 pg_toast_25983663 |25983665 | 10 | 100 artikel_tmp |25983663 | 10 | 100 pg_toast_27150432 |27150434 | 10 | 100 branchen|27150432 | 10 | 100 anbieter|27150440 | 10 | 100 pg_toast_27150444 |27150446 | 10 | 100 pg_toast_27150452 |27150454 | 10 | 100 pg_toast_27150458 |27150460 | 10 | 100 pg_toast_27150465 |27150467 | 10 | 100 pg_toast_27150471 |27150473 | 10 | 100 pg_toast_27150478 |27150480 | 10 | 100 pg_toast_27150488 |27150490 | 10 | 100 pg_toast_27150493 |27150495 | 10 | 100 bildvorschau|27150493 | 10 | 100 pg_toast_27150503 |27150505 | 10 | 100 pg_toast_27150511 |27150513 | 10 | 100 test|27150517 | 10 | 100 pg_toast_27150520 |27150522 | 10 | 100 plz |27150526 | 10 | 100 pg_toast_27150530 |27150532 | 10 | 100 (40 rows) What is the reason for this increase in size? How can I prevent it? I'm stuck with this. Any help is welcome. Regards, Daniel Alvarez <[EMAIL PROTECTED]> web41@julius64:~ > uname -a Linux julius64 2.4.14 #34 Wed Jan 23 17:41:57 MET 2002 i686 unknown web41@julius64:~ > psql -V psql (PostgreSQL) 7.2 Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996, Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! ---(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] compress the data sended via network???
Hi @ all, I have some trouble with speed of our online-server. The problem is that we have to much traffic in some cases. Particulary BLOB or Memo -Fields slow down everything. So i look for some possibilities to compress the data sended via network. I expect a large incresement of speed exspecially with large TEXT-Fields (RTF-Format). Is this is possible with postgres??? Or what do you think about this feature??? Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Still a bug in the VACUUM ??? !!!
> > > I have a problem with the "vacuum full". every time I run the vacuum > > > command I loose data from the parent tables. maybe also from the > > > subtables (haven't checked yet). I tried it a few times up to now an I > > > can reproduce the phenomena. > > > > That sounds ugly ... but are you sure you don't have a hardware problem? > > I don't think anyone's ever reported such behavior before. > > > > If it is a Postgres bug, we can't do much to help you without a lot more > > detail. > > > > regards, tom lane > I have a similiar problem with VACUUM FULL ANALYZE. I do not loose any data, but get hundreds of uninitialized pages and a segmentation fault. Processing is very slow (twenty minutes). The only thing unusual about my configuration is that system indices are bloated. I expect the hardwhere to be ok, but I can not verify it because the sever is hosted elsewhere. The last lines of output were: NOTICE: Rel pg_class: Uninitialized page 3344 - fixing NOTICE: Rel pg_class: Uninitialized page 3345 - fixing NOTICE: Rel pg_class: Uninitialized page 3346 - fixing NOTICE: Rel pg_class: Uninitialized page 3347 - fixing NOTICE: Rel pg_class: Uninitialized page 3348 - fixing NOTICE: Rel pg_class: Uninitialized page 3349 - fixing NOTICE: Rel pg_class: Uninitialized page 3350 - fixing NOTICE: Rel pg_class: Uninitialized page 3351 - fixing NOTICE: Rel pg_class: Uninitialized page 3352 - fixing NOTICE: Rel pg_class: Uninitialized page 3353 - fixing NOTICE: Rel pg_class: Uninitialized page 3354 - fixing NOTICE: Rel pg_class: Uninitialized page 3355 - fixing NOTICE: Rel pg_class: Uninitialized page 3356 - fixing batch/nachts.sh: line 3: 30855 Segmentation fault /usr/bin/php -q /usr/local/httpd/htdocs/kunden/web41/html/wcopy/batch/vacuum.php Running VACUUM FULL ANALYZE another time there were no errors. > hi, > > it does sound ugly. I checked the hardware. I can't see any problems with > i > t. > I know, somestimes you need a lot of luck to see a CPU problem under > solari > s. > But I think the hardware is ok. however, what kind of details do you need > t > o > qualify if it's a postgres problem or not ? > > regards > > -andreas schmitz -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Limiting database size ...
Hi! I'm working on an ISP, and we're testing Postgres to offer Database Services to our clients. We like to limit the maximum database size for each client (1 database for client), but as far as I know postgres hasn't this functionality. I've also tried to limit this size using system quotas but it doesn't work because to control this space I need that the files of the database (those under the data/base/xxx) owns to the user ant then postgres doesn't recognise them (I'm also afraid because i don't know what could happen to the database consistence if the operating system doesn't permit to complete an operation in course). Someone has the same problem? How can I make that? What would happen to the consistence if the quota is full while postgres is writing? Thank you, and sorry for my english :( -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Vacuum Question
Do a VACUUM FULL on your database. This should be the solution. Daniel ""Pascal PEYRE"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] > Hi, > > I use postgresql 7.2.x on Linux 2.4.18-6mdksmp #1 SMP i686 > > I have some big postgreSQL databases (4/5 GB at start) on this server. > Every night I erase data and I import a lot of new data. > > For optimize my database I operate a vacuum "all" every night. > > The problem is that the size of database is growing every day whereas > the volume of data stay remains stable. After six month my postgreSQL > databases size is approximately (40/50 Gb). Yesterday I suppress the > database and I restore it by pg_dump. By this way my database size is > gone down again to 4/5 Gb. > > Is there any solution to reduce the size of the database without restore > a SQL backup ? > > Thanks to your help > > Pascal PEYRE > > > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Limiting database size ...
Would it be feasible for you to install a copy of postgresql for each user, as that user? This might allow you to set up a quota per user. Should they run out of space, postgresql will just start throwing errors with references to no space left on the device (the specific errors will be dependent on what your trying to do). 200 Postgresql running? No, thanks :) -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: [EMAIL PROTECTED] ---(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
Re: [ADMIN] How to compile contribs ...
The README in corresponding contrib module must be of much help. If not, get back with the name of contrib module which you have tried. I know that README usually helps, but this is not the case for me : ( I need to compile dbsize ... regards, bhuvaneswaran ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- **** Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] database size
In the contrib directory exists the database_size function (dbsize directory, i think) ... And if you want to automate the process of retrieving all your databases size http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=15493 Anagha Joshi wrote: Hi, How can I know size of the specific database in Postgres? Thx. in advance. -Anagha -- Daniel Rubio Rodríguez OASI (Organisme Autònom Per la Societat de la Informació) c/ Assalt, 12 43003 - Tarragona Tef.: 977.244.007 - Fax: 977.224.517 e-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Run 4 postgresql session on ONE server?
Hello, I only know the traditional way of using postgreSQL: In serverprocess with an "unlimited" number of databases. But: Is is possible to have 4 processes, for each database one, running on one linux server? The reason is the following: I only have one server on which a productive database is working and a second database, which will be set upped at the moment. On my productive database I can't test with the parameters so I am searching for a way, that I can do the following: - let running for each database one serverprocess on the same machine - having for each database one pg_hba.conf and postgresql.conf (on testdb's, I want to log every SQL-statement, which is on a productive database impossible to log) I know, that you shouldn't test on a productive server, but at the moment this is the only way I can do but I hope to get an test server or a new productive server as soon as the second database is ready. Thank you in advance for your answers Daniel Seichter -- postgreSQL on Netware - the red elephant http://postgresql.dseichter.org Last update: 26th May 2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Run 4 postgresql session on ONE server?
Good morning, > Yes, you can do it. All you have to do is create four seperate accounts > for it to run under (pgsql1, pgsql2, pgsql3, pgsql4) and then in each of > those accounts, set up a different PGDATA value and initdb as that user. > Then edit each account's postgresql.conf to have a different port number > (I just incremented from 5432 to 5433 etc...) and start them up. Well, it looks easy *g* I will do this today and hope, it work. > Then when you connect just specify the port of the database instance you > need. Since each one is running as a different user with different > postgresql.conf and pg_hba.conf files you can lock the production instance > down tight to prevent brain farts (oops, I just dropped a table in the > production database) and you're gold. Ok, at the moment , I only can say: thank you, and I had to test it during this weekend to start at the next week with the configuration. Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Run 4 postgresql session on ONE server?
Hello, > BTW -- this is pretty easy with the RPMs (I think you said you were on > RedHat). Just make a symlink to the init script. Call it 'pg2' > > Then put the port and pgdata definitions in /etc/sysconfig/pgsql/pg2 No, I am on SuSE but don't using the RPMs (maybe on RedHat it will be better) because after ONE DAY of asking and configuring I was not able to make the initdb to the /db directory where the database should stored in, no, the SuSE RPMs are all linked to /var/.. and I don't want to use symlinks to a database. To an config-file it is possible, but not to a directory. So I sit on my server and wait 15 minuten until postgreSQL is compiled..*g* Daniel Seichter -- postgreSQL on Netware - the red elephant http://postgresql.dseichter.org Last update: 26th May 2003 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] Backup up Users and grants
Hello, is it possible, to backup all users with their grants to the tables or the database? Thank you Daniel Seichter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Run 4 postgresql session on ONE server?
Hello, > BTW -- this is pretty easy with the RPMs (I think you said you were on > RedHat). Just make a symlink to the init script. Call it 'pg2' > > Then put the port and pgdata definitions in /etc/sysconfig/pgsql/pg2 No, I am on SuSE but don't using the RPMs (maybe on RedHat it will be better) because after ONE DAY of asking and configuring I was not able to make the initdb to the /db directory where the database should stored in, no, the SuSE RPMs are all linked to /var/.. and I don't want to use symlinks to a database. To an config-file it is possible, but not to a directory. So I sit on my server and wait 15 minuten until postgreSQL is compiled..*g* Daniel Seichter -- postgreSQL on Netware - the red elephant http://postgresql.dseichter.org Last update: 26th May 2003 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Run 4 postgresql session on ONE server?
Hello, > BTW -- this is pretty easy with the RPMs (I think you said you were on > RedHat). Just make a symlink to the init script. Call it 'pg2' > > Then put the port and pgdata definitions in /etc/sysconfig/pgsql/pg2 No, I am on SuSE but don't using the RPMs (maybe on RedHat it will be better) because after ONE DAY of asking and configuring I was not able to make the initdb to the /db directory where the database should stored in, no, the SuSE RPMs are all linked to /var/.. and I don't want to use symlinks to a database. To an config-file it is possible, but not to a directory. So I sit on my server and wait 15 minuten until postgreSQL is compiled..*g* Daniel Seichter -- postgreSQL on Netware - the red elephant http://postgresql.dseichter.org Last update: 26th May 2003 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] postgreSQL users = LDAP users?
Hello, is it possible, to create all users of the databaseserver through LDAP? An schema extension is maybe required, but is it possible, yes or no?! If yes, where to find more information? Thank you Daniel Seichter -- postgreSQL on Netware - the red elephant http://postgresql.dseichter.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] postgreSQL users = LDAP users?
Hello, thank you for you links, I will take a look to it. Daniel -- postgreSQL on Netware - the red elephant http://postgresql.dseichter.org -- you wrote --- Look at this http://www.mandrakesecure.net/en/docs/samba-ldap-advanced.php http://www.mandrakesecure.net/en/docs/ldap-auth2.php if you only want to sync users, and not any information regarding them. If you would want more then: You'll have to use the ODBC based sql backend for ldap (VERY SLOW ), and connect using postgres, or whatever your database superuser is. You will need to create some tables and views, tables containing ldap specific things, see: http://www.samse.fr/GPL/ldap_pg/HOWTO/ and views which unify the former with pg_shadow information. I wish you the best luck in succeeding this ! Best Regards Geza Gemes ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Advantages and disadvantages of more than one dbserver on one server
Hello, I want to discuss a little bit about the following situation: I only have one server for my databases.I had to develope on this server and also had to manage several databases, so I can expect problems if I had to restart postgreSQL, if I changed the logging. What do you think about this solution: For each database I want to let run one server, and the different servers a located in /usr/local/pgsql-733-1data-dir:/db/pgsql-1 /usr/local/pgsql-733-2data... Is this a good idea? What problems can happen? I compile postgreSQL only with the --prefix flag. What things I should do? Each db got his own user, like postgres1, postgres2 or pgsql7331,... I sometimes need to log everything, but this is maybe once or twice a week, but I can't restart the server during the day. Daniel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Advantages and disadvantages of more than one dbserver
Hello Scott, > Note that even if you ARE going to run each cluster under a different user > (I recommend running each cluster under a different user so you can do > 'insane' things like let each person have C as an untrusted language and > be their own superuser in a mostly sane way) you still only need to > compile Postgresql only once. Unless you're going to run multiple > versions you shouldn't need multiple installs / seperate --prefix > switches. Yes, this is one problem I have: Another person has a database running on this server, but he is only the admin, not the 'engineer', but he also develop on another instance of his database and needs to restart his database instance on its own. I on myself using at the moment three databases for one instance, because two of this are only test-dbs, but during next week, we start working on it and then I can't restart the db without notifiy all users and even about this I want to try to let run all databases in its own instance (or thread). Hope I explained it right, some words only know in german, not in english. Daniel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Advantages and disadvantages of more than one dbserver on one server
The result of compiling, testing and let postgreSQL crash: Now I know, that I only have advantages if I will let run each database with its own session of pgsql. I can stop/start/restart(/kill) one session and the other works as long as the server is not shutting down or something like the worst case,... I can setting up different users with different rights to each postgresql and also can limit the possibility that users can have access to something they shoudl not get have access. Today memory isn't expensive so with 512MB I can be shure, every user on this server might have enough to work without any timeout or waiting times greater then 2 seconds. Thanks to all which helped. Daniel P.S.: I hope the new release of postgreSQL for netware will be available soon, so the same I can do without compiling. -- postgreSQL on Netware - the red elephant http://postgresql.dseichter.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Advantages and disadvantages of more than one dbserver
Hello Scott, > Note that running Postgresql on it's own server is a good idea if it's > currently sharing with an application server. While some systems (Zope, > PHPNuke stuff) can run in little or no memory, most app servers eat up a > lot of memory that postgresql could use for cacheing. We only use this server as postgreSQL server, and no apache, no mail no php or something like this works on it. And if the main database grows up to some GB of capacity, we had to migrate to an new server. BTW, Dual 750's will be great for me *g* Daniel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
