Upgrading postgresql ( CentOS/RHEL 5.3)
 

On Fri, 23 Jan 2009, John Summerfield wrote:

> And then there's postgresql. One has to backup one's data before
> upgrading major postgresql releases and then restore into the new.

I consider that a major upstream bug.

However, at the least a %pre script should create an SQL dump before
upgrading major releases, so user is not left with an unusable blob.

Better would be for postgresql to ship a standalone SQL dumper, which can
read old file formats.
_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

On Thu, 22 Jan 2009, Charlie Brady wrote:

> Better would be for postgresql to ship a standalone SQL dumper...

i.e. one which is self contained, and doesn't require a running
postmaster. openldap's slapcat is such a beast, for ldap backend to LDIF
dumping.
_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

Charlie Brady wrote:
> On Thu, 22 Jan 2009, Charlie Brady wrote:
>
>
>> Better would be for postgresql to ship a standalone SQL dumper...
>>
>
>
There is an ongoing effort to create an in-place-upgrade for PostgreSQL,
http://wiki.postgresql.org/images/1/17/Pg_upgrade.pdf


Regards,

Peter
> i.e. one which is self contained, and doesn't require a running
> postmaster. openldap's slapcat is such a beast, for ldap backend to LDIF
> dumping.
> _______________________________________________
> CentOS-devel mailing list
> CentOS-devel@centos.org
> http://lists.centos.org/mailman/listinfo/centos-devel
>
>


--

Dott. Peter Hopfgartner

R3 GIS Srl - GmbH
Via Johann Kravogl-Str. 2
I-39012 Meran/Merano (BZ)
Email: peter.hopfgartner@r3-gis.com
Tel. : +39 0473 494949
Fax : +39 0473 069902
www : http://www.r3-gis.com

_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

> I consider that a major upstream bug.
> Better would be for postgresql to ship a standalone SQL dumper, which can
> read old file formats.

Charlie,

Would you expect a "simple" upgrade of Oracle 10i to Oracle 11, for your
major enterprise application? Or, MS-SQL 2005 to MS-SQL 2008?

Any major database version upgrade requires the attention of a qualified
DBA who knows how to test data and applications against the new DB
version, and then dump/upgrade/restore.

For example, PostgreSQL introduced some minor syntactical differences with
8.3. If your application uses the features affected by these changes, it
would be impossible to simply 'dump/restore' without some massaging of the
data and the application.

PostgreSQL does ship with a dumper, pg_dump. If you have the current
version of postmaster, then you use pg_dump to connect to that and dump
your data in a version-agnostic format. IMHO, the effort of writing a
standalone dumper that can recognize all the old file formats is not worth
it, because it is a mistake to delete the old version of postmaster off
your system before you've done a dump of the database.

Cheers,
-Josh

--

-----
http://www.globalherald.net/jb01
GlobalHerald.NET, the Smarter Social Network! (tm)
_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

Joshua Kramer wrote:
> Any major database version upgrade requires the attention of a qualified
> DBA who knows how to test data and applications against the new DB
> version, and then dump/upgrade/restore.
>
> For example, PostgreSQL introduced some minor syntactical differences with
> 8.3. If your application uses the features affected by these changes, it
> would be impossible to simply 'dump/restore' without some massaging of the
> data and the application.
>
> PostgreSQL does ship with a dumper, pg_dump. If you have the current
> version of postmaster, then you use pg_dump to connect to that and dump
> your data in a version-agnostic format. IMHO, the effort of writing a
> standalone dumper that can recognize all the old file formats is not worth
> it, because it is a mistake to delete the old version of postmaster off
> your system before you've done a dump of the database.

So how do you package such a thing in RPM so it can permit both new and
old instances to run simultaneously while you do all of this required
testing? I suppose these days virtualbox is an almost-reasonable answer
but it just seems wrong to have a system that by design doesn't let you
test a new instance before replacing the old one.

--
Les Mikesell
lesmikesell@gmail.com
_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

> Charlie Brady wrote:
>On Fri, 23 Jan 2009, John Summerfield wrote:

>> And then there's postgresql. One has to backup one's data before
>> upgrading major postgresql releases and then restore into the new.

> I consider that a major upstream bug.

Upstream collectively disagrees with you. Even though a PostgreSQL core developer, Tom Lane, works for Red Hat and packages the RHEL PostgreSQL packages. Upgrading PostgreSQL is a HARD thing to do without using the documented 'dump-upgrade-initdb-restore' sequence (unless you want to get multiversion installs working, and use Slony to do the migration...good luck with the multiversion! Although Debian has that piece worked out, Debian can do things during install/upgrade in .deb packages that RPM's cannot do).

>However, at the least a %pre script should create an SQL dump before
>upgrading major releases, so user is not left with an unusable blob.

>Better would be for postgresql to ship a standalone SQL dumper, which can
>read old file formats.

I maintained the 'PGDG' or community upstream RPM's for PostgreSQL for five years, from 1999 to 2004. Personal reasons caused me to hand that over to Devrim, the current RPM maintainer lead. So I've fought with this issue a long time (as John knows).

A %pre scriptlet has no way of reliably detecting whether it is running under Anaconda during a media-fed upgrade or from a fully installed system. If the %pre scriptlet is running under Anaconda, it cannot do an SQL dump, which requires a major portion of the normal system to be present and running to complete. This is not the case in an Anaconda-mediated upgrade, during which many basic system services are simply not there.

Then there is the disk space issue (making sure you don't run out). Oh, and compiled C functions.

There was once a pg_upgrade program that could do some of this stuff; however, it can break in subtle ways. The PostgreSQL system catalogs that are part of the database contain things that are part of the core system, including functions, operators, and the like. And sometimes the actual tuple format changes from one release to another...

Now, it has been a while since I last looked at the upgrading situation; if you want to learn more about it, read the archives of the pgsql-hackers list and search for the various and many upgrade discussions.

As a user, I have a CentOS 4 system that at this point in time cannot be upgraded past its current PostgreSQL version due to the need to store photographs from Microsoft Access as large objects. The support for the method used is not in any subsequent version, and in fact doesn't work on anything but the version shipped with CentOS 4. We will have to recode the application to get it ported, unfortunately. Any automatic upgrade of this system would break, and break badly.

The moral is that, as a server administrator, you must be ever diligent to make sure that 'upgraded' software doesn't dramatically break things; and you cannot rely on the OS upgrade to do it. (I'm thinking BIND, Apache modules, java versions, amavisd and sendmail, among other things that tend to break in upgrades....)

--
Lamar Owen
Chief Information Officer
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC 28772
828-862-5554
www.pari.edu
_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

On Jan 22, 2009, at 12:25 PM, Les Mikesell wrote:


Joshua Kramer wrote:
Any major database version upgrade requires the attention of a
qualified

DBA who knows how to test data and applications against the new DB
version, and then dump/upgrade/restore.

For example, PostgreSQL introduced some minor syntactical
differences with
8.3. If your application uses the features affected by these
changes, it
would be impossible to simply 'dump/restore' without some massaging
of the

data and the application.

PostgreSQL does ship with a dumper, pg_dump. If you have the current
version of postmaster, then you use pg_dump to connect to that and
dump
your data in a version-agnostic format. IMHO, the effort of
writing a
standalone dumper that can recognize all the old file formats is
not worth
it, because it is a mistake to delete the old version of postmaster
off

your system before you've done a dump of the database.


So how do you package such a thing in RPM so it can permit both new
and

old instances to run simultaneously while you do all of this required
testing? I suppose these days virtualbox is an almost-reasonable
answer
but it just seems wrong to have a system that by design doesn't let
you

test a new instance before replacing the old one.



Historical note:
A long time ago (RHL 5.2 iirc) transparent upgrades
of postgres databases was attempted within *.rpm
packaging. The result was a total disaster.

Don't attempt the database conversion while upgrading is the moral.

Arrange paths in postgres packaging so that both old <-> new utilities
are available when needed. That can most easily be done by
including whatever old utilities are needed in the new package
so that the conversion can be done after the old -> new upgrade.

Alternatively, one can also attempt multiple installs of postgres
side-by-side kinda like kernel packages are done.

hth

73 de Jeff______________________________________________ _
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

Guys,

This is the CentOS-devel list. Will you please take this discussion to the general list.

Thanks.
Alan.

_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

On Thu, 22 Jan 2009, Joshua Kramer wrote:

> Would you expect a "simple" upgrade of Oracle 10i to Oracle 11, for your
> major enterprise application? Or, MS-SQL 2005 to MS-SQL 2008?

No, but I wouldn't choose to use those.

> PostgreSQL does ship with a dumper, pg_dump. If you have the current
> version of postmaster, then you use pg_dump to connect to that and dump
> your data in a version-agnostic format.

I know all that.

Thanks.
_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel

Upgrading postgresql ( CentOS/RHEL 5.3)
 

> So how do you package such a thing in RPM so it can permit both new and
> old instances to run simultaneously while you do all of this required
> testing? I suppose these days virtualbox is an almost-reasonable answer

I think this discussion is a reflection of our different environments. :)

On my websites... when 8.3 came out, I downloaded it to a test machine. I
then did a dump of the production data from 8.2, and did an import into my
8.3 test machine. After pointing an Apache dev instance at the test
database, I could verify that my applications still worked, and make any
code changes that were required.

After I had a test/dev environment that was stable under 8.3, I planned
the migration: 1. Dump 8.2; 2. Shutdown 8.2 and remove packages; 3. Move
8.2's data directory; 4. Install 8.3 packages, and initdb; 5. Import data
made during the dump and start db; 6. Migrate code changes to web server.
After things baked for a week and there were no errors, I deleted the old
8.2 data directories.

I realize that this is much more difficult if you're using a VM on a web
host that only allows one machine. Is this the type of environment that
is constraining you? As long as you can test your application under the
new database version to make sure it's OK, the migration can be done on
one machine. But let me ask: in what case would you not want to test your
application against a new database version?

--Josh

--

-----
http://www.globalherald.net/jb01
GlobalHerald.NET, the Smarter Social Network! (tm)
_______________________________________________
CentOS-devel mailing list
CentOS-devel@centos.org
http://lists.centos.org/mailman/listinfo/centos-devel