Upgrading from 7.1

On Wed, 2005-07-27 at 13:41, Jonathan Villa wrote:

I've been googling a little bit and appears that 7.1 pretty old. What steps are
advised to upgrade from 7.1 to 7.4?

The best way, in my humble opinion, is to build a new machine, and
install 7.4 or 8.0 on it. Backup the old 7.1 machine using the pg_dump
or pg_dumpall program on the 7.4/8.0 box, and restore it to the 7.4/8.0
machine. Then test the heck out of it with your applications. If it
all works, then over a weekend, repeat the process, replacing the 7.1
machine with the 7.4/8.0 machine. That way, you have a backup plan,
should things go wrong at some point, you can revert to 7.1.

and yes, 7.1 is pretty old.

Depending on hold old your 7.1 version is (like 7.1.0 or something) you
might want to take a backup of it and upgrade pgsql on it to the latest
7.1.xxx version, where xxx was the largest version released.

On Wednesday 27 July 2005 11:41 am, Jonathan Villa wrote:

I've been googling a little bit and appears that 7.1 pretty old.

Very.

What steps are advised to upgrade from 7.1 to 7.4?

Unless there is some reason you really need 7.4, just go straight to
8.0.3.

Instructions start on page 230 of the PG8 documentation. Basically you
do a pg_dumpall to backup your old database (make sure it is not
being updated), stop the old server, install the new server, restore
your data:
http://www.postgresql.org/docs/8.0/interactive/install-upgrading.html

Cheers,
Steve

Jonathan Villa wrote:

I've been googling a little bit and appears that 7.1 pretty old.
What steps are advised to upgrade from 7.1 to 7.4?

1. Dump the old db using 7.4's pg_dump.
2. Read the release notes for the in-between versions to make sure
nothing will impact your behaviour. Keep a close eye for tightening-up
error checking, or changing typecasting rules etc.

If I was you I'd go straight to 8.0 - it's not going to be noticably
more work and brings you bang up to date.

--
Richard Huxton
Archonet Ltd

My approach will be/has been as follows:

I've used pg_dump of 7.4 to do

pgsql-7.4 $>pg_dump --schema-only dbName > schema.sql

Aside from some tweaking, the import seemed to work fine.

Now, I'm attempting the following

pgsql-7.4 $> pg_dump --data-only --inserts dbName > data.sql

and when I attempt an import, I get

ERROR: insert or update on table "doc_data" violates foreign key constraint
"docdata_languageid_fk"
DETAIL: Key (language)=(1) is not present in table "supported_languages".

Regarding the violations of the foreign key contraints, I've been able to
export/import from 7.1 to 7.1 ok.

When I was doing the schema.sql import, I did receive a lot of messages regarding
implicit indexes being created? Is this something I should be worried about?

Reason I'm not moving to 8.0 is because the application I'm trying to get upgraded
does not give it it's seal of approval.

-Jonathan

<quote who="Richard Huxton">

On Jul 27, 2005, at 3:43 PM, Jonathan Villa wrote:

My approach will be/has been as follows:

I've used pg_dump of 7.4 to do

pgsql-7.4 $>pg_dump --schema-only dbName > schema.sql

Aside from some tweaking, the import seemed to work fine.

Now, I'm attempting the following

pgsql-7.4 $> pg_dump --data-only --inserts dbName > data.sql

and when I attempt an import, I get

ERROR: insert or update on table "doc_data" violates foreign key
constraint
"docdata_languageid_fk"
DETAIL: Key (language)=(1) is not present in table
"supported_languages".

Regarding the violations of the foreign key contraints, I've been
able to
export/import from 7.1 to 7.1 ok.

When I was doing the schema.sql import, I did receive a lot of
messages regarding
implicit indexes being created? Is this something I should be
worried about?

Reason I'm not moving to 8.0 is because the application I'm trying
to get upgraded
does not give it it's seal of approval.

-Jonathan

Jonathan,

The implicit indexes are no big deal; they're just a sign of indexes
getting created by PRIMARY KEYs on your tables.

I'm not sure why you're getting errors. Is there a reason you did the
schema dump separately from the data dump rather than a monolithic
dump/restore?

Once you get your data import working, you might want to check out
contrib/adddepend, though, since you're coming from a pre-7.3 database.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

Thomas F. O'Connell wrote:

Jonathan,

The implicit indexes are no big deal; they're just a sign of indexes
getting created by PRIMARY KEYs on your tables.

I'm not sure why you're getting errors. Is there a reason you did the
schema dump separately from the data dump rather than a monolithic
dump/restore?

I seem to remember encountering an issue some time ago with pg_dump
dumping tables in an order that prevented them from being reloaded.

The solution was to specify the order of the tables in the commandline.

Hope this helps.
Chris Travers
Metatron Technology Consulting

On Wed, Jul 27, 2005 at 05:13:01PM -0700, Chris Travers wrote:

Thomas F. O'Connell wrote:

The implicit indexes are no big deal; they're just a sign of indexes
getting created by PRIMARY KEYs on your tables.

I'm not sure why you're getting errors. Is there a reason you did the
schema dump separately from the data dump rather than a monolithic
dump/restore?

I seem to remember encountering an issue some time ago with pg_dump
dumping tables in an order that prevented them from being reloaded.

This problem is solved in 8.0's pg_dump. Not sure if 7.1 has enough
information in catalogs to make the algorithm run correctly -- I wonder
if pg_depend is needed, because AFAIR there was no pg_depend in 7.1.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"The Gord often wonders why people threaten never to come back after they've
been told never to return" (www.actsofgord.com)

Interesting, How would I specify the order of the tables on the commandline

To Thomas: I decided to separate the schema/data export/import to make sure the
schema was at least being created correctly...

How can I export one table by itself? Not just table, but a view/trigger, etc... Is
it even possible to export them separately?

-Jonathan

<quote who="Chris Travers">

Hi All;

Actually, as I am remembering how I worked through this problem...

I ended up withh a simple shell script something like:

#!/bin/bash
dumpfile="dump.pgsql"
pg_dump -s dbname > $dumpfile
for a in table1 table2 table3 table4 table5 table5 table6
do
pg_dump -a -t $a >> $dumpfile
done

Hope this helps,
Chris Travers
Metatron Technology Consulting

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

On Wed, Jul 27, 2005 at 05:13:01PM -0700, Chris Travers wrote:

Thomas F. O'Connell wrote:

I'm not sure why you're getting errors. Is there a reason you did the
schema dump separately from the data dump rather than a monolithic
dump/restore?

I seem to remember encountering an issue some time ago with pg_dump
dumping tables in an order that prevented them from being reloaded.

This problem is solved in 8.0's pg_dump. Not sure if 7.1 has enough
information in catalogs to make the algorithm run correctly -- I wonder
if pg_depend is needed, because AFAIR there was no pg_depend in 7.1.

There was not, and current pg_dump can't promise a safe dump order when
dumping from a server too old to have correct dependency info.

My advice is to use a recent pg_dump with -Fc option, so that you can
twiddle the load order using pg_restore's options to control the order.

7.1 to 8.0 is definitely a big jump --- it'd be worth your time to work
on schema-level incompatibilities (that is, try to load and work with
a "pg_dump -s" dump) before you even think of moving any data.

regards, tom lane