Thread: Re: [ADMIN] postgres 6.2 vacuum

Re: [ADMIN] postgres 6.2 vacuum

From
Bruce Momjian
Date:
I wonder if we should have an auto-responder so when someone says they
are running 6.5, we can reply --- Yikes, upgrade.

In fact, we could go with a little chart:
7.3.4    great7.3.0-3 please upgrade, it is easy7.2    consider upgrading7.1    wow, that is old7.0    you need an
upgrade,pal<=6.5    run, don't walk, to the nearest PostgreSQL ftp server
 


---------------------------------------------------------------------------

Hornyak Laszlo wrote:
> 
> Yes, at the end we did the port at night, and in the morning the system
> started without any problem. Some of the dumps from pg 6.2 was not realy
> acceptable by 7.3, but it was easy to fix.
> 
> Thank you for your help!
> 
> Laszlo Hornyak
> 
> On Tue, 23 Sep 2003, scott.marlowe wrote:
> 
> > On Mon, 22 Sep 2003, Hornyak Laszlo wrote:
> > 
> > > Hi all!
> > > 
> > > We have a database on postgreSQL 6.2 and it is extremely slow, so we
> > > started vacuum on it. I know it locks the tables, so clients can not use
> > > it until the process is finished, but it is extremely slow on a 1.800.000
> > > record table and we don't know how to make it faster. Can anybody help me?
> > > 
> > > It seems it is writing an index file, but it grows very slowly.
> > > 
> > > I know we should use 7.3 at least, we are working on it, but we need to
> > > survive this day with 6.2 :(
> > 
> > In all honesty, it'd probably be faster to convert than to wait for that 
> > vacuum to finish.
> > 
> > seriously.  
> > 
> > 6.2 is like the model A of Postgresql versions.
> > 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: [ADMIN] postgres 6.2 vacuum

From
Hornyak Laszlo
Date:
I think it is not that simple. How should I explain the company leaders
why I must stop the system. It may risk their bussiness success too. I can
tell them that the new db is more stable, but until the old one does the
job, it is still acceptable for them (it served the system for 5-6 years
or so). Once it crashes, it is a good reason to do the move.

Laszlo

On Wed, 24 Sep 2003, Bruce Momjian wrote:

> 
> I wonder if we should have an auto-responder so when someone says they
> are running 6.5, we can reply --- Yikes, upgrade.
> 
> In fact, we could go with a little chart:
> 
>     7.3.4    great
>     7.3.0-3 please upgrade, it is easy
>     7.2    consider upgrading
>     7.1    wow, that is old
>     7.0    you need an upgrade, pal
>     <=6.5    run, don't walk, to the nearest PostgreSQL ftp server
> 
> 
> ---------------------------------------------------------------------------
> 
> Hornyak Laszlo wrote:
> > 
> > Yes, at the end we did the port at night, and in the morning the system
> > started without any problem. Some of the dumps from pg 6.2 was not realy
> > acceptable by 7.3, but it was easy to fix.
> > 
> > Thank you for your help!
> > 
> > Laszlo Hornyak
> > 
> > On Tue, 23 Sep 2003, scott.marlowe wrote:
> > 
> > > On Mon, 22 Sep 2003, Hornyak Laszlo wrote:
> > > 
> > > > Hi all!
> > > > 
> > > > We have a database on postgreSQL 6.2 and it is extremely slow, so we
> > > > started vacuum on it. I know it locks the tables, so clients can not use
> > > > it until the process is finished, but it is extremely slow on a 1.800.000
> > > > record table and we don't know how to make it faster. Can anybody help me?
> > > > 
> > > > It seems it is writing an index file, but it grows very slowly.
> > > > 
> > > > I know we should use 7.3 at least, we are working on it, but we need to
> > > > survive this day with 6.2 :(
> > > 
> > > In all honesty, it'd probably be faster to convert than to wait for that 
> > > vacuum to finish.
> > > 
> > > seriously.  
> > > 
> > > 6.2 is like the model A of Postgresql versions.
> > > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 



Re: [ADMIN] postgres 6.2 vacuum

From
Richard Huxton
Date:
On Thursday 25 September 2003 07:36, Hornyak Laszlo wrote:
> I think it is not that simple. How should I explain the company leaders
> why I must stop the system. It may risk their bussiness success too. I can
> tell them that the new db is more stable, but until the old one does the
> job, it is still acceptable for them (it served the system for 5-6 years
> or so).

Here, I agree with you Hornyak - you've got 5+ years real-world experience 
with this version and it does what you want (mostly). I'm half tempted to 
downgrade myself ;-)

By the way - are you saying the your system has been running *without 
interruption* for 5 years?

Having said that, when (and it's when, not if) you upgrade, you'll be looking 
at some major changes in PG, so it's probably a good idea to test 7.3.4 and 
see what changes are necessary now, before you need to. Short-term, could you 
stop the system long enough to pg_dump it and restore? Again, test the 
process first.

> Once it crashes, it is a good reason to do the move.

And just *before* it crashes is the best time.

--  Richard Huxton Archonet Ltd


Re: [ADMIN] postgres 6.2 vacuum

From
Shridhar Daithankar
Date:
Hornyak Laszlo wrote:

> I think it is not that simple. How should I explain the company leaders
> why I must stop the system. It may risk their bussiness success too. I can
> tell them that the new db is more stable, but until the old one does the
> job, it is still acceptable for them (it served the system for 5-6 years
> or so). Once it crashes, it is a good reason to do the move.

Well, I am sure there are data corruption bugs fixed between 6.2 and current CVS 
head which would count as large impact in terms of numbers and severity.

If your client business depends upon it, that is in fact a better reason to 
upgrade. If postgresql developers tells you to upgrade, that does count as 
recommendation.

Its not like oracle upgrade where you have to move the OS, hardware and spend a 
large amount of money. The impact of migration is restricted to downtime of 
servers and cleaning up any applications that depend upon any incorrect 
behaviour supported in past.

IMO you should move in all scenarios.
 Shridhar



Re: [ADMIN] postgres 6.2 vacuum

From
Lamar Owen
Date:
On Friday 26 September 2003 02:29, Shridhar Daithankar wrote:
> Well, I am sure there are data corruption bugs fixed between 6.2 and
> current CVS head which would count as large impact in terms of numbers and
> severity.

Indeed there are.

> Its not like oracle upgrade where you have to move the OS, hardware and
> spend a large amount of money. The impact of migration is restricted to
> downtime of servers and cleaning up any applications that depend upon any
> incorrect behaviour supported in past.

This isn't necessarily true.  That old of a version of PostgreSQL is probably 
running on a quite out-of-date OS -- for instance, if the OS was Red Hat 
Linux, then the point at which 6.2.1 was shipped was RHL 5.0.  Can you even 
compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?

I have had this problem, and still, at one client, have a box running 
PostgreSQL 6.5.3 because later PostgreSQL's haven't been well tested on RHL 
5.2.  There is a binary-only closed source app running on the box that won't 
run on even a Linux 2.2 kernel, much less a 2.4 kernel.  The 5.2 box is 
running the latest 2.0.x kernel.  That client depends upon behaviors of the 
older version of that application that the newer version of that application 
doesn't perform.  So they are quite literally stuck at 6.5.3.  I would love 
to get them up to something better, but, it's not at the moment worth enough 
to them to do it.  When the cost-benefit balance swings to the benefit side, 
things will change.

If I could even get the box up to RHL 6.2 I'd be better off, because 
PostgreSQL 7.3.x builds and runs well there.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu


Re: [ADMIN] postgres 6.2 vacuum

From
Hornyak Laszlo
Date:
No, I got this job 2 months ago, I don`t know who managed it before, and I
don`t know why didn`t he upgrade. Until this week I didn`t have the chance
to upgrade.
But it runs now on 7.3.4.


On Thu, 25 Sep 2003, Richard Huxton wrote:

> On Thursday 25 September 2003 07:36, Hornyak Laszlo wrote:
> > I think it is not that simple. How should I explain the company leaders
> > why I must stop the system. It may risk their bussiness success too. I can
> > tell them that the new db is more stable, but until the old one does the
> > job, it is still acceptable for them (it served the system for 5-6 years
> > or so).
> 
> Here, I agree with you Hornyak - you've got 5+ years real-world experience 
> with this version and it does what you want (mostly). I'm half tempted to 
> downgrade myself ;-)

<OFF>
If you think you can do it better, the job is yours.
</OFF>

Laszlo Hornyak

> 
> By the way - are you saying the your system has been running *without 
> interruption* for 5 years?
> 
> Having said that, when (and it's when, not if) you upgrade, you'll be looking 
> at some major changes in PG, so it's probably a good idea to test 7.3.4 and 
> see what changes are necessary now, before you need to. Short-term, could you 
> stop the system long enough to pg_dump it and restore? Again, test the 
> process first.
> 
> > Once it crashes, it is a good reason to do the move.
> 
> And just *before* it crashes is the best time.
> 
> -- 
>   Richard Huxton
>   Archonet Ltd
> 



Re: [ADMIN] postgres 6.2 vacuum

From
Tom Lane
Date:
Lamar Owen <lowen@pari.edu> writes:
> This isn't necessarily true.  That old of a version of PostgreSQL is probably
> running on a quite out-of-date OS -- for instance, if the OS was Red Hat 
> Linux, then the point at which 6.2.1 was shipped was RHL 5.0.  Can you even 
> compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?

Surely.  We still support other platforms that make RHL 5.0 look like
the new kid on the block.  There might not be RPMs available, but I
can't believe it wouldn't compile from source.

I do agree that people running that old a Linux distro need to think
about updating more than just Postgres, though.  They have kernel bugs
as well as PG bugs to fear :-(
        regards, tom lane


Re: [ADMIN] postgres 6.2 vacuum

From
Lamar Owen
Date:
On Friday 26 September 2003 10:52, Tom Lane wrote:
> Lamar Owen <lowen@pari.edu> writes:
> > This isn't necessarily true.  That old of a version of PostgreSQL is
> > probably running on a quite out-of-date OS -- for instance, if the OS was
> > Red Hat Linux, then the point at which 6.2.1 was shipped was RHL 5.0. 
> > Can you even compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?

> Surely.  We still support other platforms that make RHL 5.0 look like
> the new kid on the block.  There might not be RPMs available, but I
> can't believe it wouldn't compile from source.

I think I tried a 7.1.x on 5.2 a long time ago, and it didn't build for some 
reason.  But that has been some time ago.  I might just build up a 5.2 system 
(plus errata) to see.

> I do agree that people running that old a Linux distro need to think
> about updating more than just Postgres, though.  They have kernel bugs
> as well as PG bugs to fear :-(

2.0 happily doesn't have many new bugs, and it is being maintained, IIRC.  
Just not by Red Hat.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu


Re: [ADMIN] postgres 6.2 vacuum

From
Jan Wieck
Date:
Tom Lane wrote:

> Lamar Owen <lowen@pari.edu> writes:
>> This isn't necessarily true.  That old of a version of PostgreSQL is probably
>> running on a quite out-of-date OS -- for instance, if the OS was Red Hat 
>> Linux, then the point at which 6.2.1 was shipped was RHL 5.0.  Can you even 
>> compile PostgreSQL 7.3.x on RHL 5.0 or its contemporaries?
> 
> Surely.  We still support other platforms that make RHL 5.0 look like
> the new kid on the block.  There might not be RPMs available, but I
> can't believe it wouldn't compile from source.

It's not that simple. At some point we decide to support newer bison, 
flex, ant, jdk, tcl ... you go through some chain of upgrades.

> I do agree that people running that old a Linux distro need to think
> about updating more than just Postgres, though.  They have kernel bugs
> as well as PG bugs to fear :-(

Plus all the well known vulnerabilities used by worms and root kits ...


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Re: [ADMIN] postgres 6.2 vacuum

From
Lamar Owen
Date:
On Monday 29 September 2003 11:41 am, Jan Wieck wrote:
> Tom Lane wrote:
> > I do agree that people running that old a Linux distro need to think
> > about updating more than just Postgres, though.  They have kernel bugs
> > as well as PG bugs to fear :-(

> Plus all the well known vulnerabilities used by worms and root kits ...

Assuming the db server is exposed directly to the Internet.  I know of old, 
obscurity-secured systems with none of the development tools necessary to use 
a rootkit (and rootkits are extremely rare in precompiled form for things 
that old and uncommon), and running none of the traditionally exploited 
services.  A Red Hat 5.2 server running only PostgreSQL 6.3.2, for instance, 
can be made very secure without upgrades by disposing of vulnerable services 
and running the latest and greatest 2.0.x series kernel (2.0.40, IIRC).  And 
once such a server is running on, say, a dual PPro 200 and serving up queries 
at the design rate, what is the impetus and motivation to upgrade?  

Furthermore, if one were leery of the SCO business with Linux 2.4.x and later, 
then one would be running a 2.0.x or 2.2.x kernel based system anyway, where 
SCO has not made any claims.  This brings us back to a Red Hat 5.2 for 2.0.x 
or Red Hat 7.0 (not 7.1 or later) for 2.2.x.  Although Red Hat 6.2 is a safer 
bet for a 2.2.x based system.  Just make sure to update it before connecting 
it to the Internet, if it is to be connected to the Internet.  Or don't run 
the rootable services that 6.2 has out of the box.

7.3.4 is buildable on 6.2, which makes it a nice balance point for those who 
want to do this sort of thing. 
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu