Concerned about previous version still in place

[Guy Smith]

Hi,

Some time ago I upgraded to 7.3.4 from 7.2.2 and am now looking to upgrade again to 7.4.1.

At the time I upgraded to 7.3.4 I took a very cautious approach and ran make install after a configure --prefix /home/postgres/root (and created a new database similarly) so that I could easily run up 7.2.2 installed under /usr/bin (originally installed there by rpm) if I needed to fail back. I'm now looking to install 7.3.4 "officially" as a precursor to beta installing 7.4.1 and have rerun configure without the --prefix and performed a make install. This has installed to /usr/local/pgsql/bin et al as would be expected from reading INSTALL (which, of course, I've done after the event!). My concern is that I now have 7.2.2 executables installed at /usr/bin and libraries in /usr/lib and so on, which may be used by my site code inadvertently.

I believe I could configure --prefix /usr and make install again to overwrite these but I've also noticed that there are additional files in /usr/include/pgsql that don't exist in /usr/local/pgsql/include so am concerned I could create a mess. I would also like to move towards a consistent approach where my team don't need to consider --prefix for the final install, we go with the default.

I'm running SuSE Linux 8.2 - can I just use rpm to remove postgresql-7.2.2-90.i586.rpm without breaking anything?

Thanks,
Guy

----------------------------------------------------------------------------
Project Manager - SourceForge
Deutsche Bank [/]
----------------------------------------------------------------------------




--

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Marco Gaiani]

Hi all,

I have experienced a distressing problem lately with my hosting provider
(they run RedHat), they had to downgrade their version of Postgresql
from 7.3.4 to 7.3.3 due to 7.3.4 "crashing" constantly. Instead of doing
a pg_dump of my databases they copied the data directory somewhere else
as data_old and went on with the downgrade of Postgres. They have not
been able to restore my databases but sent me a copy of the data directory.

I have tried to mount it with Mandrake 9.2 and Postgres 7.3.3 but I get
this error:

DEBUG: FindExec: searching PATH ...
DEBUG: ValidateBinary: can't stat "/bin/postgres"
DEBUG: FindExec: found "/usr/bin/postgres" using PATH
DEBUG: invoking IpcMemoryCreate(size=983040)
PANIC: The database cluster was initialized with LC_COLLATE
'en_US.iso885915',
which is not recognized by setlocale().
It looks like you need to initdb.
Aborted

Any ideas on how to solve this, desesperatly yours
--
Marco Gaiani
Unidad de Promoción y Relaciones
FUNDACITE ARAGUA
mailto: ***@fundacite.arg.gov.ve


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Andrew Sullivan]

Post by Marco Gaiani
Hi all,
I have experienced a distressing problem lately with my hosting provider
(they run RedHat), they had to downgrade their version of Postgresql
from 7.3.4 to 7.3.3 due to 7.3.4 "crashing" constantly. Instead of doing

First, this sounds pretty dodgy to me. I have a feeling that they're
overlooking some other problem. But that won't help you.

Post by Marco Gaiani
a pg_dump of my databases they copied the data directory somewhere else
as data_old and went on with the downgrade of Postgres. They have not
been able to restore my databases but sent me a copy of the data directory.

This is ok -- the binaries are compatible across major versions, so a
7.3.3 tree should work with 7.3.x

Post by Marco Gaiani
'en_US.iso885915',
which is not recognized by setlocale().
It looks like you need to initdb.
Aborted

You need the locale support offered by the Red Hat system. Seems you
need ISO 8859-15. My bet is either that Mandrake's locale doesn't
support that, you don't have the right libs, or the binary you've
installed wasn't compiled with the right support. Can you actually
set your LANG to iso885915?

A
--
Andrew Sullivan | ***@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Robert Treat]

Post by Andrew Sullivan

Post by Marco Gaiani
Hi all,
I have experienced a distressing problem lately with my hosting provider
(they run RedHat), they had to downgrade their version of Postgresql
from 7.3.4 to 7.3.3 due to 7.3.4 "crashing" constantly. Instead of doing

First, this sounds pretty dodgy to me. I have a feeling that they're
overlooking some other problem. But that won't help you.

On top of this, there is a fairly ciritcal bug in 7.3.3 that can cause server
start up failure. It's a corner case, but still a pretty serious one. You
should talk to your webhost about getting on 7.3.5 asap.

Robert Treat

[Jeremy Smith]

I have newly installed PostgreSQL onto my server, the server's main function
is to serve up a fantasy football site that has a tremendous number of
queries per page. Right now with very low traffic I am seeing a server load
of 2.0+. That got me a little concerned, so I looked at "top" and noticed
that postgres is taking anywhere from 60 - 100 percent of my CPU at any
given time. There are also 116 sleeping processes out of 123. This all
seems very bad, do you guys have any idea what might be causing it or how it
can be addressed? How do I go about cleaning out the sleeping processes?

Thanks alot,
Jeremy


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Lamar Owen]

Post by Jeremy Smith
I have newly installed PostgreSQL onto my server, the server's main
function is to serve up a fantasy football site that has a tremendous
number of queries per page. Right now with very low traffic I am seeing a
server load of 2.0+.

Not too bad.

Post by Jeremy Smith
That got me a little concerned, so I looked at "top"
and noticed that postgres is taking anywhere from 60 - 100 percent of my
CPU at any given time.

That would be good.

Post by Jeremy Smith
There are also 116 sleeping processes out of 123.
This all seems very bad, do you guys have any idea what might be causing it
or how it can be addressed?
How do I go about cleaning out the sleeping
processes?

You don't. Sleeping processes is a normal thing with a multitasking OS.

How fast does the page load? That would be the big question. Run apache
bench (ab) against the page and see how many pages per second yu can get. A
load of 2.0, an average CPU of 60-100%, and 7 running processes is not bad at
all. It just means your server is working.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC 28772
[phone]
www.pari.edu


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[scott.marlowe]

Post by Lamar Owen
How fast does the page load? That would be the big question. Run apache
bench (ab) against the page and see how many pages per second yu can get. A
load of 2.0, an average CPU of 60-100%, and 7 running processes is not bad at
all. It just means your server is working.

That really depends on the server. If it's a PII-266 then it's about
right, if it's a dual AMD Athlon 2800 with 2 gigs of ram something's
horribly wrong. So, what kind of hardware is this jer?


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[scott.marlowe]

Post by Jeremy Smith
I have newly installed PostgreSQL onto my server, the server's main function
is to serve up a fantasy football site that has a tremendous number of
queries per page. Right now with very low traffic I am seeing a server load
of 2.0+. That got me a little concerned, so I looked at "top" and noticed
that postgres is taking anywhere from 60 - 100 percent of my CPU at any
given time. There are also 116 sleeping processes out of 123. This all
seems very bad, do you guys have any idea what might be causing it or how it
can be addressed? How do I go about cleaning out the sleeping processes?

Don't worry about sleeping processes, you should have a good hundred
sleeping on any unix box. My workstation has 152, my server has 173, and
the response time on both is way sub second.

Now, about postgresql, what is it doing when it's chewing up 100% cpu?


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mitch Pirtle]

Post by Jeremy Smith
I have newly installed PostgreSQL onto my server, the server's main function
is to serve up a fantasy football site that has a tremendous number of
queries per page. Right now with very low traffic I am seeing a server load
of 2.0+. That got me a little concerned, so I looked at "top" and noticed
that postgres is taking anywhere from 60 - 100 percent of my CPU at any
given time. There are also 116 sleeping processes out of 123. This all
seems very bad, do you guys have any idea what might be causing it or how it
can be addressed? How do I go about cleaning out the sleeping processes?

I agree with Lamar's comments, as well as wondering if it is really
needed to run a 'tremendous number of queries' for each page view...
Some quick solutions could be to determine if you could:

1) make changes to your design to require fewer hits to the database per
page,
2) make a view that provided the information without running so many
separate queries, and/or
3) consider using a caching library like ADOdb to limit the number of
trips to your database

Any combination of these three could significantly reduce the load on
your DB box, as well as provide some huge performance gains. How hard
is your webserver working? Are they running on the same box?

-- Mitch


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jeremy Smith]

I agree that my site is a bit bloated, it has more than 2500 total queries,
but it is a bit more complex of an application that might be readily
apparent. For the curious, this is my site: http://www.xpertleagues.com.
But the issue is that with mysql, at my peak levels last year I had a server
load of 30+ (I know this is horrendous, I am looking into either upgrading
my P4 2.4gig 1gig ram server this year, or distributing across more than one
server) but the site itself never performed as slowly as it is now. And
amazingly considering the server load last year, the server never crashed.
But now I am actually getting complaints on the lagtime, and I only have one
league actively drafting, last year I had 70+ at peak.

I will look into some of the suggestions you have made, the problem is that
I can't do large scale optimization at the moment because I am still adding
features to the site. I just wonder if the best mode of attack would be
switching back to mysql until I have added all of the necessary features,
optimizing the queries and code there, and then switching back to pg at a
later date.

Jeremy



-----Original Message-----
From: pgsql-admin-***@postgresql.org
[mailto:pgsql-admin-***@postgresql.org]On Behalf Of Mitch Pirtle
Sent: Friday, February 20, 2004 1:57 PM
To: ***@highboard.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] PosgreSQL hogging resources?

Post by Jeremy Smith
I have newly installed PostgreSQL onto my server, the server's main function
is to serve up a fantasy football site that has a tremendous number of
queries per page. Right now with very low traffic I am seeing a server load
of 2.0+. That got me a little concerned, so I looked at "top" and noticed
that postgres is taking anywhere from 60 - 100 percent of my CPU at any
given time. There are also 116 sleeping processes out of 123. This all
seems very bad, do you guys have any idea what might be causing it or how it
can be addressed? How do I go about cleaning out the sleeping processes?

I agree with Lamar's comments, as well as wondering if it is really
needed to run a 'tremendous number of queries' for each page view...
Some quick solutions could be to determine if you could:

1) make changes to your design to require fewer hits to the database per
page,
2) make a view that provided the information without running so many
separate queries, and/or
3) consider using a caching library like ADOdb to limit the number of
trips to your database

Any combination of these three could significantly reduce the load on
your DB box, as well as provide some huge performance gains. How hard
is your webserver working? Are they running on the same box?

-- Mitch


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Mitch Pirtle]

Post by Jeremy Smith
I will look into some of the suggestions you have made, the problem is that
I can't do large scale optimization at the moment because I am still adding
features to the site. I just wonder if the best mode of attack would be
switching back to mysql until I have added all of the necessary features,
optimizing the queries and code there, and then switching back to pg at a
later date.

When you switch to pg, you will be able to move some/lots? of your code
into the database as views, stored procedures, triggers etc... So keep
that in mind while working in MySQL, as you will definitely need a
different approach.

I inherited a site that had very database-hungry pages, and started
using stored procs and triggers to automate some of the jobs (like
updates and such) in the database, instead of making the webserver
manually send the instructions across the wire. If you plan on taking
this approach then you should get your app into pg sooner than later...

-- Mitch, wondering about 12 packs?


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Jeremy Smith]

Mitch,

One of the issues with my site is that many of the pages use iframes, so I
am actually loading up to 7 pages of varying database hungriness into the
browser at the same time. I agree that pg with the triggers and procedures
should be able to adequately compete with mysql in terms of speed. The
problem I am having is the complexity of the existing code combined with the
short amount of time that I have to get it to work. Hopefully next
"offseason" I won't have as many new features do add and so will be able to
concentrate on optimization..

Anyway, I'm going to keep tinkering with pg offsite and I very much
appreciate all of the help and attention that I have been given from this
list.

Thanks,
Jeremy



-----Original Message-----
From: pgsql-admin-***@postgresql.org
[mailto:pgsql-admin-***@postgresql.org]On Behalf Of Mitch Pirtle
Sent: Friday, February 20, 2004 3:24 PM
To: ***@highboard.com
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] PosgreSQL hogging resources?

Post by Jeremy Smith
I will look into some of the suggestions you have made, the problem is that
I can't do large scale optimization at the moment because I am still adding
features to the site. I just wonder if the best mode of attack would be
switching back to mysql until I have added all of the necessary features,
optimizing the queries and code there, and then switching back to pg at a
later date.

When you switch to pg, you will be able to move some/lots? of your code
into the database as views, stored procedures, triggers etc... So keep
that in mind while working in MySQL, as you will definitely need a
different approach.

I inherited a site that had very database-hungry pages, and started
using stored procs and triggers to automate some of the jobs (like
updates and such) in the database, instead of making the webserver
manually send the instructions across the wire. If you plan on taking
this approach then you should get your app into pg sooner than later...

-- Mitch, wondering about 12 packs?


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[scott.marlowe]

Are you getting problems with crashing backends in postgresql and such
showing up? I'm wondering if you have bad memory or something like that.

In my experience, Linux/apache/php/postgresql never crashes, it just goes
unresponsive when you get into severe overload.

Is your database vacuum / analyzed often?

Do you have indexes that are being used?

Post by Jeremy Smith
I agree that my site is a bit bloated, it has more than 2500 total queries,
but it is a bit more complex of an application that might be readily
apparent. For the curious, this is my site: http://www.xpertleagues.com.
But the issue is that with mysql, at my peak levels last year I had a server
load of 30+ (I know this is horrendous, I am looking into either upgrading
my P4 2.4gig 1gig ram server this year, or distributing across more than one
server) but the site itself never performed as slowly as it is now. And
amazingly considering the server load last year, the server never crashed.
But now I am actually getting complaints on the lagtime, and I only have one
league actively drafting, last year I had 70+ at peak.
I will look into some of the suggestions you have made, the problem is that
I can't do large scale optimization at the moment because I am still adding
features to the site. I just wonder if the best mode of attack would be
switching back to mysql until I have added all of the necessary features,
optimizing the queries and code there, and then switching back to pg at a
later date.
Jeremy
-----Original Message-----
Sent: Friday, February 20, 2004 1:57 PM
Subject: Re: [ADMIN] PosgreSQL hogging resources?

Post by Jeremy Smith
I have newly installed PostgreSQL onto my server, the server's main

function

Post by Jeremy Smith
is to serve up a fantasy football site that has a tremendous number of
queries per page. Right now with very low traffic I am seeing a server

load

Post by Jeremy Smith
of 2.0+. That got me a little concerned, so I looked at "top" and noticed
that postgres is taking anywhere from 60 - 100 percent of my CPU at any
given time. There are also 116 sleeping processes out of 123. This all
seems very bad, do you guys have any idea what might be causing it or how

it

Post by Jeremy Smith
can be addressed? How do I go about cleaning out the sleeping processes?

I agree with Lamar's comments, as well as wondering if it is really
needed to run a 'tremendous number of queries' for each page view...
1) make changes to your design to require fewer hits to the database per
page,
2) make a view that provided the information without running so many
separate queries, and/or
3) consider using a caching library like ADOdb to limit the number of
trips to your database
Any combination of these three could significantly reduce the load on
your DB box, as well as provide some huge performance gains. How hard
is your webserver working? Are they running on the same box?
-- Mitch
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(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

[Tom Lane]

Post by Marco Gaiani
I have experienced a distressing problem lately with my hosting provider
(they run RedHat), they had to downgrade their version of Postgresql
from 7.3.4 to 7.3.3 due to 7.3.4 "crashing" constantly.

You should ask them why they've not reported their problems. I've not
heard from anyone else that 7.3.4 is less stable than 7.3.3.

Post by Marco Gaiani
PANIC: The database cluster was initialized with LC_COLLATE
'en_US.iso885915',
which is not recognized by setlocale().
It looks like you need to initdb.

I think it should be possible to get the localedef source file for this
locale and build the necessary locale support on your Mandrake system.
I don't know exactly where to look for the locale source, though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings