Re: [ADMIN] HELP pleaseeee!!!
[EMAIL PROTECTED] ("andres barra") writes:
> hi, somebody can help me???
>
> I tried to create a new database but i can´t.
>
> psql: FATAL: Database "template1" does not exist in the system catalog.
> createdb: database creation failed
>
> I tried to make initdb in another directory and it shows me Segmentation
> Fault.
>
> how i do to restore the template1
You could restore template1 from template0...
$ createdb -T template0 template1
I would suggest figuring out first why template1 got trashed, as
that's a pretty severe problem. It sounds to me as though something's
severely broken with your PostgreSQL binaries; you may want to check
the provenance of the code.
If you compiled it yourself, then checking where the compile has
gotten to is clearly in your hands; if it was installed in "packaged"
form, you should look into whether someone has corrupted it, and
how/why...
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/finances.html
The human race will decree from time to time: "There is something at
which it is absolutely forbidden to laugh."
-- Nietzche on Common Lisp
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Odd query behavior [urgent, but long]
Hello all:
I'm seeing some very odd query behavior on postgres 7.1.3. I know that's way
out of date, and I do have a plan in place to upgrade, but any immediate
help will be, well, very helpful.
I have a server running two instances of postgres: 7.1.3 and 7.4.3. Each
supports a different web application -- the web apps are served from a
separate middleware server. Formerly both web apps ran on a single
all-in-one server, on postgres 7.1.3.
Since reconfiguring the servers (one web, one database) and deploying on the
two-postgres config, performance of the web app running on the older pg is
terrible, despite the new db-only machine being superficially "better" (in
processor and RAM) than the original.
I have enabled pretty extensive query logging on the 7.1.3 instance. What I
see is that certain queries take hideously long.
What is odd is that these are perfectly well optimized queries: they
represents updates to a table of web application sessions. Each user's
session is written once per web page view, so the query gets run frequently.
Now, this session update is not, I think, the only query that's taking long.
But there are so many of these queries that they are heavily represented in
the slow queries (which I'm determining somewhat laboriously by running the
huge logs through an awk script).
Here's an example EXPLAIN:
datapoint=# explain UPDATE nsse_session SET expiration = 1093541296, value =
'sessIdUser|s:5:\"31991\";sessPriv|s:1:\"u\";se\
datapoint'#
ssCustId|s:5:\"14688\";sessSchoolId|s:5:\"14781\";sessUserName|s:6:\"gcruse\
";sessImgVersion|i:2;sessAccessPrivs|N;sessAccessSchools|a:1:{i:14781;s:\
datapoint'#
5:\"14781\";}sessSort|a:0:{}!sessBack|sessDPVersion|s:4:\"full\";sessDataSou
rce|s:4:\"live\";sessUserMeasures|s:14:\"1948,1913,1703\";sessUserGroups\
datapoint'#
|s:84:\"3074,3113,3144,3179,3223,3263,5968,5997,6011,6027,6035,6046,6063,607
6,6087,6105,6116\";!sessGroupFilter|' WHERE id_session = '955af0272896ba\
datapoint'# a67956494dcb30a5fe' AND expiration > 1093441296;
NOTICE: QUERY PLAN:
Index Scan using nsse_session_pkey on nsse_session (cost=0.00..8.16 rows=3
width=50)
EXPLAIN
So, an index scan, as it should be.
When I check the postgres log for the query stats, I find this:
QUERY STATISTICS
! system usage stats:
! 0.012477 elapsed 0.00 user 0.00 system sec
! [0.07 user 0.00 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 3/1 [1044/309] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 0 read, 0 written, buffer hit rate
= 100.00%
! Local blocks: 0 read, 0 written, buffer hit rate
= 0.00%
! Direct blocks: 0 read, 0 written
QUERY STATISTICS
! system usage stats:
! 1878.708691 elapsed 194.00 user 1.18 system sec
! [194.02 user 1.19 sys total]
! 0/0 [0/0] filesystem blocks in/out
! 36441/3233 [37108/3496] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [0/0] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 16863 read,125 written, buffer hit rate
= 99.99%
! Local blocks: 0 read, 0 written, buffer hit rate
= 0.00%
! Direct blocks: 0 read, 0 written
Two sets of stats, of which the first looks OK, and the second is terrible.
Virtually all of the long queries I've seen so far display this behavior, a
double set of stats, with the second one looking like bad news. The second
set seems almost to show a very elevated level of page faults. (what are
these page faults against? They seem not to be against the pg shared buffers
-- are they against the OS disk cache?)
I did hear a suggestion that it's possible that a few very expensive queries
are blocking others (if they're writing) and that I see the effects mostly
on these session updates, because they're so frequent. I guess that's
possible, but would like to know more about what the above stats might mean.
Some of these session writes are fairly heavy -- we carry a lot of data in
the session, often multiple tens of K of text going into one column.
Is this a stats problem, symptom of insufficient vacuuming?
Relevant params: postgres 7.1.3 running on RedHat ES 2.1. SHMMAX set to
8. (Physical ram is 3gig). Shared buffers for this install = 2,
sort mem is 8 meg, max connections = 50. (I don't think it's pure concurrent
load, it happened during a user training session with only about 30 users).
The other pg install on the box is similarly configured. Just to complete
the data dump, here's the output of ipcs:
-- Shared Memory Segments
keyshmid owner perms bytes nattch status
0x0052e6a9 3538944postgres 600167649280 2
0x0052e2c
Re: [ADMIN] Odd query behavior [urgent, but long]
Steve Lane <[EMAIL PROTECTED]> writes: > I have enabled pretty extensive query logging on the 7.1.3 instance. What I > see is that certain queries take hideously long. Tell us about foreign keys associated with the table being updated? An UPDATE would fire triggers for both referencing and referenced keys ... I'm suspecting an unindexed or datatype-incompatible foreign key column. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Odd query behavior [urgent, but long]
> From: Tom Lane <[EMAIL PROTECTED]> > Date: Wed, 25 Aug 2004 15:16:32 -0400 > To: Steve Lane <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED], Jamie Thomas <[EMAIL PROTECTED]> > Subject: Re: [ADMIN] Odd query behavior [urgent, but long] > > Steve Lane <[EMAIL PROTECTED]> writes: >> I have enabled pretty extensive query logging on the 7.1.3 instance. What I >> see is that certain queries take hideously long. > > Tell us about foreign keys associated with the table being updated? > An UPDATE would fire triggers for both referencing and referenced keys ... > > I'm suspecting an unindexed or datatype-incompatible foreign key column. Hi Tom: Thanks. There are, unfortunately, no foreign keys or triggers anywhere in the db that I know of. And this query is not always slow, just occasionally. -- sgl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN]
Hola necesito ayuda, estoy configurando mi php con soporte para postgresql pero no logro que tenga soporte, mi strip de configuración es el siguiente ./configure --with-pgsql=/usr/local/pgsql --enable-dbase --enable-dbx Me pueden ayudar... Atte. Edgar Cante ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] Size of database
Hi, How can I know the size of the database on a server Postgresql 7.3.4 ? Regards. -- == | FREDERIC MASSOT | | http://www.juliana-multimedia.com | | mailto:[EMAIL PROTECTED] | ===Debian=GNU/Linux=== ---(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] 7.3.4 Table corruption
I'm running a 7.3.4 on a RedHat 7.2 i686 box. I take daily backups of my application's database, and last week it failed on one of the tables. After some checking I found that the rest of the data was ok, just a large number of tuples on one table were corrupt. I'm pretty sure it's table corruption, as a REINDEX does not solve the problem, and I can't COPY data from the table. I have backups I can use, but I only want to restore this one table, which would not normally be a big problem, as I can just pull the relevant commands out of the dump file. But there are a lot of connections through rules and references from the broken table to other tables, so I can't simply truncate this table and reload it, I would have to reload a large number of other tables as well. Something I don't really want to do, as I could potentially lose more data. My question is, if I load the good dump into a clean database, and then find the underlying file that represents the broken table and copy it over the top of the broken table, am I likely to face any big problems? Thanks Andrew ---(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] regression database
Hello, I inherited a production Postgres database server which has a "Regression" database. The problem is that the size of the objects with in the Regression database are increasing so much that the database has grown into several GB. (Ex: pg_attribute and its index have grown into 2GB each and so on with few other objects as well). I am not sure what the implications of completely deleting the 'Regression' database (I don't need the regression testing). I found the documentation on how to do the regression tests but can you tell me if there is a documentation of how to take care of these space issues for "Regression" database? Or is there a way to reduce the space used by 'Regression' database? I would appreciate your help in resolving this issue. thanks, Kris. Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses.
Re: [ADMIN] .pgpass and win32
Thomas Wegner wrote: > Hello, > > where i must place the .pgpass file under win32??? Uh, in 8.0 our .pgpass doesn't work yet. It will in beta2, but for now you have to define a %HOME% variable and point that to the directory with .pgpass. In beta2 it will use %USERPROFILE%. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] 7.3.4 Table corruption
Andrew Farmer <[EMAIL PROTECTED]> writes: > My question is, if I load the good dump into a clean database, and then > find the underlying file that represents the broken table and copy it > over the top of the broken table, am I likely to face any big problems? This strikes me as a real good way to shoot yourself in the foot ;-). Better take a backup and be prepared to restore from it. And I'd suggest experimenting in a scratch installation before you try it for real. Having said that, I think it would work, if your "clean database" is another DB in the same cluster (you could *not* copy from data prepared under a different postmaster). And you'll need to copy all the indexes on that table, and its toast table and toast table index if it has one. And shut down the postmaster while you do the copying. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] regression database
kris pal <[EMAIL PROTECTED]> writes: > I am not sure what the implications of completely deleting the > 'Regression' database (I don't need the regression testing). Is it named "Regression" with a capital R? If so, that's not the DB that is made by the standard regression tests --- that is called "regression". There is not any reason not to drop the standard regression database, if you're not using it. But there is no way that that DB would get that large unless something in your installation is using it. I'd suggest you find out what. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN]
Hola Edgar: Spanish is not my native language -- perhaps you can do about as well with my English as I did reading your post :-) What exactly happens? PHP doesn't build? Or doesn't even configure correctly? What's the output from configure? -- sgl > From: "Edgar Cante" <[EMAIL PROTECTED]> > Date: Wed, 25 Aug 2004 15:03:28 -0600 > To: <[EMAIL PROTECTED]> > Subject: [ADMIN] > > Hola necesito ayuda, estoy configurando mi php con soporte para postgresql > pero no logro que tenga soporte, mi strip de configuración es el siguiente > ./configure --with-pgsql=/usr/local/pgsql --enable-dbase --enable-dbx > > Me pueden ayudar... > > > > > Atte. > Edgar Cante > > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Odd query behavior [urgent, but long]
OK, well I may have some more useful information. The queries that blow up seem, by and large, to be updates to the session table. Again, some of these updates are big: I measured one at 50K today. I suspected that insufficient vacuuming might be involved. I did a full vacuum and got this for the session table: NOTICE: --Relation nsse_session-- NOTICE: Pages 3544: Changed 11, reaped 696, Empty 0, New 0; Tup 26121: Vac 37327, Keep/VTL 0/0, Crash 0, UnUsed 107, MinLen 132, MaxLen 2032; Re-using: Free/Avail. Space 5345328/5336916; EndEmpty/Avail. Pages 0/684. CPU 0.08s/0.03u sec. NOTICE: Index nsse_session_pkey: Pages 1435; Tuples 26121: Deleted 36871. CPU 0.07s/0.13u sec. NOTICE: Rel nsse_session: Pages: 3544 --> 2873; Tuple(s) moved: 13. CPU 0.02s/0.06u sec. NOTICE: Index nsse_session_pkey: Pages 1435; Tuples 26121: Deleted 13. CPU 0.00s/0.01u sec. NOTICE: --Relation pg_toast_3521195-- NOTICE: Pages 83872: Changed 6, reaped 81999, Empty 0, New 0; Tup 8100: Vac 327763, Keep/VTL 0/0, Crash 0, UnUsed 88, MinLen 45, MaxLen 2034; Re-using: Free/Avail. Space 668306776/668306512; EndEmpty/Avail. Pages 0/81954. CPU 3.22s/0.30u sec. NOTICE: Index pg_toast_3521195_idx: Pages 1668; Tuples 8100: Deleted 327763. CPU 0.33s/1.07u sec. NOTICE: Rel pg_toast_3521195: Pages: 83872 --> 1933; Tuple(s) moved: 15. CPU 10.16s/4.87u sec. NOTICE: Index pg_toast_3521195_idx: Pages 1668; Tuples 8100: Deleted 15. CPU 0.05s/0.00u sec. Now granted, this was after I had written scripts to perform that 50K update about 10-15K times -- I did this just to check the degradation in insert performance on the unvacuumed table, and as expected I saw a slow, steady degradation in insert time, but nothing catastrophic. Clearly the toast table for nsse_session is big, and the table is very frequently accessed. This seems like a recipe for page faults of some kind, but it seems this could lead to two opposite conclusions: 1) lower shared buffers in case shared buffers are starving the OS disk caching buffers 2) raise shared buffers so as to get the whole session/toast table in memory ?? -- sgl > From: Steve Lane <[EMAIL PROTECTED]> > Date: Wed, 25 Aug 2004 14:26:52 -0500 > To: Tom Lane <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]>, Jamie Thomas <[EMAIL PROTECTED]> > Subject: Re: [ADMIN] Odd query behavior [urgent, but long] > > > >> From: Tom Lane <[EMAIL PROTECTED]> >> Date: Wed, 25 Aug 2004 15:16:32 -0400 >> To: Steve Lane <[EMAIL PROTECTED]> >> Cc: [EMAIL PROTECTED], Jamie Thomas <[EMAIL PROTECTED]> >> Subject: Re: [ADMIN] Odd query behavior [urgent, but long] >> >> Steve Lane <[EMAIL PROTECTED]> writes: >>> I have enabled pretty extensive query logging on the 7.1.3 instance. What I >>> see is that certain queries take hideously long. >> >> Tell us about foreign keys associated with the table being updated? >> An UPDATE would fire triggers for both referencing and referenced keys ... >> >> I'm suspecting an unindexed or datatype-incompatible foreign key column. > > Hi Tom: > > Thanks. There are, unfortunately, no foreign keys or triggers anywhere in > the db that I know of. And this query is not always slow, just occasionally. > > -- sgl > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Size of database
Frederic Massot wrote: > Hi, > > How can I know the size of the database on a server Postgresql 7.3.4 ? There is a chapter in the 7.4 docs called "Managing disk space". -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
