Thread: What is the best plan to upgrade PostgreSQL from an ancient version?
My notion is to do a character mode database dump as SQL statements and then load into the new version by execution of psql against the sql STATEMENTS. What are the "gotchas" we can expect with this approach? When I say 'ancient' I mean v7.1.3 and the target is v8.3.5.
Re: What is the best plan to upgrade PostgreSQL from an ancient version?
From
Raymond O'Donnell
Date:
On 26/01/2009 21:37, Dann Corbit wrote: > My notion is to do a character mode database dump as SQL statements and > then load into the new version by execution of psql against the sql > STATEMENTS. > > What are the "gotchas" we can expect with this approach? > > When I say 'ancient' I mean v7.1.3 and the target is v8.3.5. Off the top of my head, I have a memory that constraints were once-upon-a-time implemented as triggers, whereas now they're "real" foreign keys.... I once loaded an old database dump into a new server, and then had to drop the triggers and create the foreign keys. I can't remember what version the old database came from, though. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Re: What is the best plan to upgrade PostgreSQL from an ancient version?
From
Raymond O'Donnell
Date:
On 26/01/2009 21:42, Raymond O'Donnell wrote: > Off the top of my head, I have a memory that constraints were Sorry, I meant to say "...foreign key constraints..." R. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Dann Corbit wrote:
> My notion is to do a character mode database dump as SQL statements and
> then load into the new version by execution of psql against the sql
> STATEMENTS.
>
> What are the "gotchas" we can expect with this approach?
>
> When I say 'ancient' I mean v7.1.3 and the target is v8.3.5.
>
Assuming you have a pretty good understanding of your schemas and
expected queries, I would:
1) Set aside a couple hours to carefully read all the release notes
between 7.1.3 and 8.3.5 - make notes of anything that might cause issues.
2) Create a test setup just for practicing 7.1.3 dump to 8.3.5 restore
including any additional changes you might make in the process (usual
recommendation, BTW, is to run the pg_dump from the target version of
the DB). Note/correct problems till you can pretty much script the
process. Problems I've experienced include handling the change from
SQL_ASCII on the old machine to UTF8 on the new one ("smart" quotes that
creep into the data are not your friend, here) as well as eliminating
OIDs on user tables.
3) Test your apps against the new version. (Remember, you may need new
libraries, recompilation of apps, etc. to the new version - the advice
I've been given is come time to pull the trigger, upgrade the clients
first. You might be able to move this step to the top if you don't have
problems with such a wide version mismatch.)
Obviously, there is a good chance of finding issues you will have to
correct at each stage. PostgreSQL has become stricter in many ways. You
may find, for example, that queries fail due to changes in the automatic
casting of variables. Overall this is a good thing but can involve some
extra work at upgrade time.
Cheers,
Steve
"Dann Corbit" <DCorbit@connx.com> writes:
> My notion is to do a character mode database dump as SQL statements and
> then load into the new version by execution of psql against the sql
> STATEMENTS.
> What are the "gotchas" we can expect with this approach?
> When I say 'ancient' I mean v7.1.3 and the target is v8.3.5.
Yoi, that is a long way. As already noted, you should use the 8.3
version of pg_dump to pull the data from the old server; this should
smooth some of the bumps, but there will be more.
Also, experiment with using the -d or -D options to pg_dump (ie
dump data via INSERT not COPY) if you have problems. I forget exactly
when we got rid of the last risk factors for COPY-style dumps, but it
might've been after 7.1. This'll be slower though.
I don't have too much else to add to what was already said, except
to reinforce the advice to test your applications before you do the live
migration. You're almost certain to hit some compatibility issues.
regards, tom lane
On Jan 26, 2009, at 6:33 PM, Tom Lane wrote: > "Dann Corbit" <DCorbit@connx.com> writes: >> My notion is to do a character mode database dump as SQL statements >> and >> then load into the new version by execution of psql against the sql >> STATEMENTS. >> What are the "gotchas" we can expect with this approach? >> When I say 'ancient' I mean v7.1.3 and the target is v8.3.5. > > Yoi, that is a long way. As already noted, you should use the 8.3 > version of pg_dump to pull the data from the old server; this should > smooth some of the bumps, but there will be more. ISTR there being some hard problems moving from something that old to 8.2, and that doing it via an intermediate 7.4 installation ( use pg_dump 7.4 against the 7.1 installation, load it into a 7.4 installation, then use the 8.3 pg_dump against that) avoided some problems. It's been a while, and I don't recall what the problems were, so ICBW. > > > Also, experiment with using the -d or -D options to pg_dump (ie > dump data via INSERT not COPY) if you have problems. I forget exactly > when we got rid of the last risk factors for COPY-style dumps, but it > might've been after 7.1. This'll be slower though. > > I don't have too much else to add to what was already said, except > to reinforce the advice to test your applications before you do the > live > migration. You're almost certain to hit some compatibility issues. +1 Cheers, Steve
Tom Lane wrote: > "Dann Corbit" <DCorbit@connx.com> writes: >> My notion is to do a character mode database dump as SQL statements and >> then load into the new version by execution of psql against the sql >> STATEMENTS. >> What are the "gotchas" we can expect with this approach? >> When I say 'ancient' I mean v7.1.3 and the target is v8.3.5. > > Yoi, that is a long way. As already noted, you should use the 8.3 > version of pg_dump to pull the data from the old server; this should > smooth some of the bumps, but there will be more. It's also worth thinking about doing a schema-only dump and get the schema loading into the new database. Once that's working fine, you can do a data-only dump and restore that into the already-loaded schema. If you dump the data with -Fc you can do table-by-table restores under your own control, which may be helpful in case of load ordering problems related to foreign key constraints. -- Craig Ringer
On Mon, Jan 26, 2009 at 10:37 PM, Dann Corbit <DCorbit@connx.com> wrote: > My notion is to do a character mode database dump as SQL statements and > then load into the new version by execution of psql against the sql > STATEMENTS. > > What are the "gotchas" we can expect with this approach? > > When I say 'ancient' I mean v7.1.3 and the target is v8.3.5. > One gotcha is that some ancient versions (and I think 7.1 is one of these) used to silently truncate varchar values that don`t fit into the declared field width, while recent versions throw errors. Check if Your apps don`t depend on this behaviour. good luck Marcin
On Mon, Jan 26, 2009 at 06:45:55PM -0800, Steve Atkins wrote: > On Jan 26, 2009, at 6:33 PM, Tom Lane wrote: >> "Dann Corbit" <DCorbit@connx.com> writes: >>> My notion is to do a character mode database dump as SQL >>> statements and then load into the new version by execution of >>> psql against the sql STATEMENTS. What are the "gotchas" we can >>> expect with this approach? When I say 'ancient' I mean v7.1.3 and >>> the target is v8.3.5. >> >> Yoi, that is a long way. As already noted, you should use the 8.3 >> version of pg_dump to pull the data from the old server; this >> should smooth some of the bumps, but there will be more. > > ISTR there being some hard problems moving from something that old > to 8.2, and that doing it via an intermediate 7.4 installation ( use > pg_dump 7.4 against the 7.1 installation, load it into a 7.4 > installation, then use the 8.3 pg_dump against that) avoided some > problems. It's been a while, and I don't recall what the problems > were, so ICBW. I think you may be thinking of the situation where foreign keys were implemented as visible triggers, and the contrib/adddepend script, which was removed (IIRC) in 8.2. You can still find that code here: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/adddepends/adddepends/ >> Also, experiment with using the -d or -D options to pg_dump (ie >> dump data via INSERT not COPY) if you have problems. I forget >> exactly when we got rid of the last risk factors for COPY-style >> dumps, but it might've been after 7.1. This'll be slower though. >> >> I don't have too much else to add to what was already said, except >> to reinforce the advice to test your applications before you do the >> live migration. You're almost certain to hit some compatibility >> issues. > > +1 +1 from here, too. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Re: What is the best plan to upgrade PostgreSQL from an ancient version?
From
"Joshua D. Drake"
Date:
On Tue, 2009-01-27 at 04:28 +0100, marcin mank wrote:
> On Mon, Jan 26, 2009 at 10:37 PM, Dann Corbit <DCorbit@connx.com> wrote:
> > My notion is to do a character mode database dump as SQL statements and
> > then load into the new version by execution of psql against the sql
> > STATEMENTS.
> >
> > What are the "gotchas" we can expect with this approach?
> >
> > When I say 'ancient' I mean v7.1.3 and the target is v8.3.5.
> >
>
> One gotcha is that some ancient versions (and I think 7.1 is one of
> these) used to silently truncate varchar values that don`t fit into
> the declared field width, while recent versions throw errors. Check if
> Your apps don`t depend on this behaviour.'
Wow that is reaching back. You can actually do this still:
postgres=# create table test_trunc(fname varchar(2)):
postgres-#
postgres=# create table test_trunc(fname varchar(2));
CREATE TABLE
postgres=# insert into test_trunc values ('fo');
INSERT 0 1
postgres=# insert into test_trunc values ('foo');
ERROR: value too long for type character varying(2)
postgres=# insert into test_trunc values ('foo'::varchar(2));
INSERT 0 1
postgres=# select * from test_trunc;
fname
-------
fo
fo
(2 rows)
Sincerely,
Joshua D. Drake
> good luck
> Marcin
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
David Fetter <david@fetter.org> writes:
> I think you may be thinking of the situation where foreign keys were
> implemented as visible triggers, and the contrib/adddepend script,
> which was removed (IIRC) in 8.2.
We replaced it with some hacks in CREATE TRIGGER, so theoretically that
conversion should still work. It's possible that adddepend did some
other useful things I'm forgetting, though.
regards, tom lane