Search code examples
postgresqlpsqlpg-dump

Upgrading from Postgres 7.4 to 9.4.1

I'm upgrading Postgres from ancient 7.4 to 9.4.1 and seeing some errors.

On the old machine, I did:

pg_dumpall | gzip > db_pg_bu.gz

On the new machine, I did:

 gunzip -c db_pg_bu.gz | psql

While restoring I got a number of errors which I don't understand, and don't know the importance of. I'm not a DBA, just a lowly developer, so if someone could help me understand what I need to do to get this migration done I would appreciate it.

Here are the errors:

 ERROR:  cannot delete from view "pg_shadow"
 DETAIL:  Views that do not select from a single table or view are not automatically updatable.
 HINT:  To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.

I also got about 15 of these:

 NOTICE:  SYSID can no longer be specified

And this, although this looks harmless since I saw plpgsql is installed by default stating in version 9.2:

 ERROR:  could not access file "/usr/lib/postgresql/lib/plpgsql.so": No such file or directory
 SET
 NOTICE:  using pg_pltemplate information instead of CREATE LANGUAGE parameters
 ERROR:  language "plpgsql" already exists

A big concern is that, as it restores the databases, for each ne I see something like this:

 COMMENT
 You are now connected to database "landrush" as user "postgres".
 SET
 ERROR:  could not access file "/usr/lib/postgresql/lib/plpgsql.so": No such file or directory
Solution

  • There are basically two ways. Both are difficult for the inexperienced. (and maybe even for the experienced)

    • do a stepwise migration, using a few intermediate versions (which will probably have to be compiled from source). Between versions you'd have to do a pg_dump --> pg_restore (or just the psql < dumpfile, like in the question). A possible path first hop could be 7.4 -> 8.3, but maybe an additional hop might be needed.
    • Edit the (uncompressed) dumpfile: remove (or comment out) anything that the new version does not like. This will be an iterative process, and it assumes your dump fits into your editor. (and that you know what you are doing). You might need to redump, separating schema and data (options --schema-only and --data-only, I don't even know if these were available in PG-7.4)

    BTW: it is advisable to use the pg_dump from the newer version(the one that you will import to). You'll need to specify the source host via the -h flag. The new (target) version knows about what the new version needs, and will try to adapt (upto a certain point, you still need to use more than one step) I will also refuse to work if it cannot produce a usable dump. (In which case you'll have to make smaller steps...)

    Extra:

    • if the result of your failed conversion is complete enough, and if you are only interested in the basic data, you could just stop here, and maybe polish a bit.
    • NOTICE: using pg_pltemplate information instead of CREATE LANGUAGE parameters I don't know what this is. Maybe the way that additional languages, such as plpgsql, were added to the core dbms.
    • ERROR: language "plpgsql" already exists : You can probably ignore this error. -->> comment out the offending lines.
    • DETAIL: Views that do not select from a single table or view are not automatically updatable. This implies that the postgres RULE rewrite system is used in the old DB. It will need serious work to get it working again.