Thread: Upgrading from 7.1
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? -Jonathan
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"> > 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 >
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"> > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
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
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