Обсуждение: 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
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
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).
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 >