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 (AT) gson (DOT) org

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

gson (AT) gson (DOT) org (Andreas Gustafsson) writes:
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

Tom Lane wrote:
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 (AT) gson (DOT) org

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

-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)
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 (AT) postgresql (DOT) org
[mailtogsql-bugs-owner (AT) postgresql (DOT) org] On Behalf Of Tom Lane
Sent: Friday, August 04, 2006 8:08 AM
To: Andreas Gustafsson
Cc: pgsql-bugs (AT) postgresql (DOT) org
Subject: Re: [BUGS] Restoring 7.4 "pg_dumpall -o" output in 8.1 fails=20


gson (AT) gson (DOT) org (Andreas Gustafsson) writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match