Обсуждение: Restoring 7.4 "pg_dumpall -o" output in 8.1 fails
I recently attempted to upgrade a PostgreSQL 7.4 installation to 8.1 by following the instructions at <http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html>, having used "pg_dumpall -o" to dump the data in 7.4. When I tried to restore the data in 8.1, psql reported a large number of syntax errors. This happens even when restoring a trivial database cluster, such as the empty one that exists immediately after installing. Restoring works fine if the data are dumped without "-o". To reproduce (as a minimal test case): Install and start PostgreSQL 7.4.13 # su pgsql -c 'pg_dumpall -o' >backup.pgdump-o Stop 7.4.3 and install and start 8.1.4 # su pgsql -c 'psql -d postgres -f backup.pgdump-o' This produces the output: You are now connected to database "template1". psql:/tmp/backup.pgdump-o:11: ERROR: cannot delete from a view HINT: You need an unconditional ON DELETE DO INSTEAD rule. psql:/tmp/backup.pgdump-o:19: ERROR: cannot delete from a view HINT: You need an unconditional ON DELETE DO INSTEAD rule. You are now connected to database "template1". SET SET CREATE TABLE psql:/tmp/backup.pgdump-o:44: ERROR: table "pgdump_oid" does not have OIDs psql:/tmp/backup.pgdump-o:46: invalid command \. psql:/tmp/backup.pgdump-o:47: ERROR: syntax error at or near "17145" at character 1 psql:/tmp/backup.pgdump-o:47: LINE 1: 17145 0 psql:/tmp/backup.pgdump-o:47: ^ SET REVOKE GRANT SET COMMENT SET COMMENT When attempting to restore the actual production database there were numerous additional syntax errors. I can't show all of them (nor the full database contents) because they contain confidential information, but here are a few examples: psql:backup:741: ERROR: table "radacct" does not have OIDs psql:backup:742: invalid command \. psql:backup:750: ERROR: table "radcheck" does not have OIDs psql:backup:752: invalid command \. psql:backup:760: ERROR: syntax error at or near "17302" at character 1 psql:backup:760: LINE 1: 17302 1 gson Password == xxxxx psql:backup:845: ERROR: syntax error at or near "17306" at character 1 psql:backup:845: LINE 1: 17306 gson-frendit 0030bdfec250 psql:backup:845: ^ psql:backup:846: invalid command \N psql:backup:847: invalid command \N psql:backup:848: invalid command \N This is on NetBSD-current, i386. -- Andreas Gustafsson, gson@gson.org
gson@gson.org (Andreas Gustafsson) writes: > I recently attempted to upgrade a PostgreSQL 7.4 installation to 8.1 by > following the instructions at > <http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html>, > having used "pg_dumpall -o" to dump the data in 7.4. When I tried to > restore the data in 8.1, psql reported a large number of syntax > errors. Try making the dump from the 7.4 server using 8.1's pg_dump(all). If you've already wiped the 7.4 installation then you'll have to deal with the incompatibilities yourself. It looked like setting default_with_oids to true in the 8.1 server would help (although do you *really* need to preserve OIDs in your dump? That -o switch is pretty well deprecated these days). The "cannot delete from a view" is probably coming from an attempt to "DELETE FROM pg_shadow", which you can ignore. You didn't show us any other problems. regards, tom lane
Tom Lane wrote: > Try making the dump from the 7.4 server using 8.1's pg_dump(all). > > If you've already wiped the 7.4 installation then you'll have to deal > with the incompatibilities yourself. It looked like setting > default_with_oids to true in the 8.1 server would help (although do you > *really* need to preserve OIDs in your dump? That -o switch is pretty > well deprecated these days). The "cannot delete from a view" is > probably coming from an attempt to "DELETE FROM pg_shadow", which you > can ignore. You didn't show us any other problems. What I did was to reinstall 7.4, which successfully restored the dump. I don't think I actually need to preserve OIDs; I was using the -o option out of habit, having been recommended to use it at some point. In any case, that's not the point; I'm not looking for support or workarounds, but simply to have the bug fixed. Whether or not I need to preserve OIDs, the documented upgrade procedure for the case where OIDs do need to preserved is not working, and that clearly is a bug in either PostgreSQL itself or the documentation. -- Andreas Gustafsson, gson@gson.org
> ls -l total 282 -rwxr-xr-x 1 jliang wheel 210873 Aug 10 10:54 pg_dump -rwxr-xr-x 1 jliang wheel 57452 Aug 10 10:54 pg_dumpall These are pg_dump(all) executable from postgresql-8.1.4 I copied them onto my postgresql-7.4.2 server and chmod to executable. ---(postgres@iguard)--(11:05:06AM)-- (/db/pg_backup) > /usr/local/pg8.1.4/pg_dumpall -o |gzip > db.out.gz su: /usr/local/pg8.1.4/pg_dumpall: cannot execute binary file > /usr/local/pg8.1.4/pg_dump -t languages urldb > test su: /usr/local/pg8.1.4/pg_dump: cannot execute binary file User postgres have full permission under /db/pg_backup, so I don't know how you could use 8.1's pg_dump(all) on 7.4 server. Jie Liang -----Original Message----- From: pgsql-bugs-owner@postgresql.org [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Tom Lane Sent: Friday, August 04, 2006 8:08 AM To: Andreas Gustafsson Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] Restoring 7.4 "pg_dumpall -o" output in 8.1 fails=20 gson@gson.org (Andreas Gustafsson) writes: > I recently attempted to upgrade a PostgreSQL 7.4 installation to 8.1=20 > by following the instructions at=20 > <http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html > >, > having used "pg_dumpall -o" to dump the data in 7.4. When I tried to > restore the data in 8.1, psql reported a large number of syntax > errors. Try making the dump from the 7.4 server using 8.1's pg_dump(all). If you've already wiped the 7.4 installation then you'll have to deal with the incompatibilities yourself. It looked like setting default_with_oids to true in the 8.1 server would help (although do you *really* need to preserve OIDs in your dump? That -o switch is pretty well deprecated these days). The "cannot delete from a view" is probably coming from an attempt to "DELETE FROM pg_shadow", which you can ignore. You didn't show us any other problems. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend