Thread: pg_dumpall restore fails

Hi,

I'm trying to upgrade my server from Ubuntu Feisty to a later version and I think I've found a bug in pg_dumpall.  The version of Postgres from Feisty is not supported in later versions so I want to dump the DB and restore after upgrade.

On the server machine I have

• Postgres 7.4.13 running with my data in it.
• Postgres 8.3.1 (latest version available on Feisty).

On my test machine I have Postgres 8.3.5.

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...
                                                             ^
ERROR:  relation "public.icc_members_test" does not exist
ERROR:  missing FROM-clause entry for table "icc_countries"
LINE 2: ...name AS state, icc_states.abbrev AS state_abbrev, icc_countr...
                                                             ^
ERROR:  relation "public.icc_members_with_admin" does not exist
ERROR:  missing FROM-clause entry for table "icc_countries"
LINE 2: ...de, icc_members.county, icc_states.name AS state, icc_countr...
                                                             ^
ERROR:  relation "public.icc_members_with_country" does not exist

It looks like pg_dumpall is generating malformed SQL for the dumped views.  I've attached the output from "pg_dumpall -c -s" as schema_dump.sql and the full output of that running psql 8.3.5 with that as input as psql_output.log.

Regards,

-Shaun

"Shaun Crampton" <shaun@cantab.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

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@sss.pgh.pa.us>

> "Shaun Crampton" <shaun@cantab.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
>