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
