What is the best plan to upgrade PostgreSQL from an ancient version?

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
------------------------------------------------------------------

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

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