Re: [GENERAL] Retoring non-administrative user databases
Keith C. Perry writes: What am I missing? A reproduceable test case. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Retoring non-administrative user databases
Quoting Peter Eisentraut [EMAIL PROTECTED]: Keith C. Perry writes: What am I missing? A reproduceable test case. -- Peter Eisentraut [EMAIL PROTECTED] ??? Ok, lets try the question this way... What is a method of dumping and restoring a complete database cluster when that cluster contains users that are NOT allowed to create databases. -- Keith C. Perry, MS E.E. Director of Networks Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Retoring non-administrative user databases
I was going a test run through of of moving my 7.1.3 databases to 7.4RC1 and I have a problem with creating databases for my users that do not have administrative accounts. By that I mean, these users are NOT allow to create databases. So the process was this: On the 7.1.3 server: pg_dumpall -c dump.db On the 7.4RC1 server: psql -f dump.db template1 or psql dump.db Either style has the same result. Also, I usually don't use pg_retore but in this case I tried: bin/pg_restore -d template1 --ignore-version --use-set-session-authorization dump.db the error I got was: pg_restore: [archiver] input file does not appear to be a valid archive I'm also tried pg_restore with a 7.3.4 database file and the result was the same on the 7.4 server. What am I missing? -- Keith C. Perry, MS E.E. Director of Networks Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] vacuum from ecpg
On Sat, Nov 15, 2003 at 11:31:08PM -0500, Bruce Momjian wrote: I might be wrong on this but I think that ecpg using transactions by default for each query. Perhaps turning on autocommit? Yep, use: EXEC SQL SET AUTOCOMMIT = ON; Or use 'ecpg -t'. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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
Re: [GENERAL] Retoring non-administrative user databases
Keith C. Perry [EMAIL PROTECTED] writes: On the 7.1.3 server: pg_dumpall -c dump.db You would probably have better luck using the 7.4 installation's pg_dump and pg_dumpall to extract data from the 7.1 server; there are three releases worth of bug-fixes in those that are not in the 7.1 dump tools. Given the lack of detail about the actual problem in your posting, it's hard to make any other specific recommendations. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Retoring non-administrative user databases
Keith C. Perry writes: What is a method of dumping and restoring a complete database cluster when that cluster contains users that are NOT allowed to create databases. There is nothing special you need to do, except of course not actually restoring the dump as one of those unprivileged users. A pg_dumpall dump must be restored as a superuser. -- Peter Eisentraut [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: [GENERAL] how to find version?
If the database is running it will tell you when you log into it with psql. bpalmer wrote: I'm trying to figure out what version of a source code I have. I know it's a 7.2 release, but how can I find out of it's 7.2, 7.2.3, 7.2.4, etc. FROM THE SOURCE CODE, not from compiling (it doesn't compile, it's testing code). Thanks c: 917-697-8665h: 201-435-6226 b. palmer, [EMAIL PROTECTED] pgp:crimelabs.net/bpalmer.pgp5 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Error on initdb with 7.4RC2
Martin Marques [EMAIL PROTECTED] writes: El Vie 14 Nov 2003 12:37, Tom Lane escribió: Hmm. If it got that far and no farther, I'd guess you have SEMVMX set too small. Is there a way of changing this value on Linux without recompiling? See sysctl. But what is the current value? I've never heard of a Linux installation with small SEMVMX. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Error on initdb with 7.4RC2
El Dom 16 Nov 2003 15:23, Tom Lane escribió: Martin Marques [EMAIL PROTECTED] writes: El Vie 14 Nov 2003 12:37, Tom Lane escribió: Hmm. If it got that far and no farther, I'd guess you have SEMVMX set too small. Is there a way of changing this value on Linux without recompiling? See sysctl. But what is the current value? I've never heard of a Linux installation with small SEMVMX. [EMAIL PROTECTED]:~/latex$ cat /proc/sys/kernel/sem 250 32000 32 128 This is a Linux Kernel for SPARC (Debian) that came with the distribution (no recompile done yet here). [EMAIL PROTECTED]:~/latex$ uname -a Linux bugs 2.4.21 #1 Thu Aug 7 20:30:12 EDT 2003 sparc64 GNU/Linux -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - ---(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: [GENERAL] Conservation of OIDs
On Sun, Nov 16, 2003 at 10:47:22AM -0800, Joshua D. Drake wrote: Whoa! You mean these aren't already separate database clusters or even separate systems? I am very shocked, you can't do a proper Dev -- QAT -- Prod environment if all three systems are run by the same postmaster, or on the same host imo. But maybe I'm just over cautious, or worked on systems where access to production systems is controlled. I second this. Use different databases for each. You can run them on the same machine (there are some real advantages to this) but create a separate initdb for each... What's the point? You can keep them separate through pg_hba.conf if it's really needed. I don't see how having several clusters, one database each, buys you any security. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Cuando mañana llegue pelearemos segun lo que mañana exija (Mowgli) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Conservation of OIDs
Whoa! You mean these aren't already separate database clusters or even separate systems? I am very shocked, you can't do a proper Dev -- QAT -- Prod environment if all three systems are run by the same postmaster, or on the same host imo. But maybe I'm just over cautious, or worked on systems where access to production systems is controlled. I second this. Use different databases for each. You can run them on the same machine (there are some real advantages to this) but create a separate initdb for each... Then run PostgreSQL on its own port for each. If you really want to make it structured create virtual IP addresses for each so that you never think about it... dev.database.com qat.database.com prod.database.com I can see the advantages in that Dev and QAT environments are automatically the same as Prod but in general Dev can be a law unto itself almost and QAT reflects the environment of Prod, e.g. Prod is Solaris 5.9 so QAT is Solaris 5.9, with the only differences being changes applied to QAT that have not yet been applied to Prod, and Dev could be Windows if that can provide everything needed to develop for the end product. At the very least I think your three database should be run as separate clusters, indeed reading the section I edited out from your email about the usage pattern on QAT and Dev my first thought was Well if you think oid wrap around would be a problem just throw an initdb into your rebuild cycle. I've seen some useful replies on how to run these separately but am I the only one shocked that the whole process is happening on a production system? -- Nigel Andrews ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Retoring non-administrative user databases
What am I missing? A reproduceable test case. It is reproduceable for him Peter. Keith could you provide a little more information? Who is the user doing the dump? Who is the user doing the restore? Are these users superusers? Either way, my suggestion would be to dump the schema only, restore the schema only. Then dump the data only, and restore the data only. 7.1.3 has some oddities that don't always make a clean restore to a newere version (at leat not 7.3 series) Sincerely, Joshua Drake -- Peter Eisentraut [EMAIL PROTECTED] ??? Ok, lets try the question this way... What is a method of dumping and restoring a complete database cluster when that cluster contains users that are NOT allowed to create databases. -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Error on initdb with 7.4RC2
El Dom 16 Nov 2003 16:56, escribió: Martin Marques [EMAIL PROTECTED] writes: El Dom 16 Nov 2003 15:23, Tom Lane escribió: See sysctl. But what is the current value? I've never heard of a Linux installation with small SEMVMX. [EMAIL PROTECTED]:~/latex$ cat /proc/sys/kernel/sem 250 32000 32 128 This is a Linux Kernel for SPARC (Debian) that came with the distribution (no recompile done yet here). As far as I can tell, SEMVMX isn't readable or writable via /proc or sysctl in Linux at all; at least it is not visible in my Red Hat 8.0 installation. So it's really hard to see why it'd be different in your installation than everywhere else. Just to make sure we're barking up the right tree, would you see whether it helps to change this line in src/backend/port/sysv_sema.c: #define PGSemaMagic 537 /* must be less than SEMVMX */ Try making it 100 or so instead of 537. Same problem. Changed it and I got this: bugs:/home/martin/basura/postgresql-7.4RC2# su - postgres [EMAIL PROTECTED]:~$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -E latin1 The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. creating directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok selecting default max_connections... 10 selecting default shared_buffers... 50 creating configuration files... ok creating template1 database in /usr/local/pgsql/data/base/1... FATAL: semctl(7766033, 16, SETVAL, 99) failed: Invalid argument initdb: failed initdb: removing data directory /usr/local/pgsql/data Now the interesting part of all this is that, with all the initdb that failed, lots of semaphores where been used, so when I tried this last initdb at first I got this, which looks like I'm out of semaphores, not as in the message from above: [EMAIL PROTECTED]:~$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -E latin1 The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. creating directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok selecting default max_connections... 10 selecting default shared_buffers... 50 creating configuration files... ok creating template1 database in /usr/local/pgsql/data/base/1... FATAL: could not create semaphores: No space left on device DETAIL: Failed system call was semget(129, 17, 03600). HINT: This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about configuring your system for PostgreSQL. initdb: failed initdb: removing data directory /usr/local/pgsql/data -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Error on initdb with 7.4RC2
Martin Marques [EMAIL PROTECTED] writes: El Dom 16 Nov 2003 15:23, Tom Lane escribió: See sysctl. But what is the current value? I've never heard of a Linux installation with small SEMVMX. [EMAIL PROTECTED]:~/latex$ cat /proc/sys/kernel/sem 250 32000 32 128 This is a Linux Kernel for SPARC (Debian) that came with the distribution (no recompile done yet here). As far as I can tell, SEMVMX isn't readable or writable via /proc or sysctl in Linux at all; at least it is not visible in my Red Hat 8.0 installation. So it's really hard to see why it'd be different in your installation than everywhere else. Just to make sure we're barking up the right tree, would you see whether it helps to change this line in src/backend/port/sysv_sema.c: #define PGSemaMagic 537 /* must be less than SEMVMX */ Try making it 100 or so instead of 537. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
