Re: pg_dumpall restore fails

Thanks Tom.

I've now successfully restored my database. I manually modified the SQL to
include the missing statements. What prompted me to email the list was that
I expected the dump tool to handle the incompatibilities between the
versions automatically. I thought it was a bug that it didn't.

I wasn't expecting the database to necessarily work 100% after the upgrade
but I was expecting the dump tool to have recreated my schema and data
accurately, ready for me to sort out my in-app SQL.

As you said, this problem was flagged in the release notes and I should have
read them!

-Shaun

2008/11/26 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> "Shaun Crampton" <shaun(at)cantab(dot)net> writes:
> > I tried to dump the 7.4.13 database using pg_dumpall from 8.3.1 and
> restore
> > into 8.3.5 on my local test machine. I get the following errors at
> restore
> > time:
>
> > ERROR: missing FROM-clause entry for table "icc_countries"
> > LINE 2: ...de, icc_members.county, icc_states.name AS state,
> icc_countr...
> > ^
>
> Per the 8.1 release notes:
>
> It might be necessary to set add_missing_from to true
> in order to load an existing dump file, if the dump contains any
> views or rules created using the implicit-FROM syntax.
> This should be a one-time annoyance, because
> PostgreSQL 8.1 will convert
> such views and rules to standard explicit-FROM syntax.
> Subsequent dumps will therefore not have the problem.
>
> Going from 7.4 to 8.3 in one jump is a bit ambitious; you should expect
> to learn about a few compatibility issues the hard way. Start by
> reading the release notes for each version in between, *particularly*
> the compatibility warnings.
>
> regards, tom lane
>