Thread: restore failure
I have a moderate size database under RH9 / pgsql 7.3 (I think... that's the PG_VERSION in the postgres user data directory.) Under 100 mb for the whole thing.
I'm trying to move the DB to postgresql 8.3 under the current ubuntu in a 4-core, 4gb machine. postgresql appears to be installed and working; I added a home directory and password so I could su - postgres and work as the postgres user as I did under RH9; other than that, no fiddling. A small test db acted perfectly normally as far as I coudl tell.
My database uses oids in a few places. I don't recall where or know how to figure out where (it's been years since I worried about the DB structure - it isn't broken); but I know it does use 'em.
I have tried pg_dump with -o and without. In both cases, under 7.3, the command produces output without complaint.
With -o, 8.3 chokes on the restore in many places, as if it didn't know what it was looking at. There are remarks about 'table "pgdump_oid" does not have oids' and there are pointers to \; at the end of a line... then it says 'syntax error at or near "94513916" (an oid?) after a create table
Without -o, things proceed a bit better; during the restore there are errors complaining about characters not in UTF-8 (but both 7.3 and 8.3 are using UTF-8... I have NO idea what its problem is but I surely know I want what was in the old table, in the new table too!), and in one case, an INDEX fails, indicating there is no OID column. Doesn't indicate what it's trying to index, though. After the restore, many tables are empty that should contain a great deal of data. Others appear to have been restored.
I don't know where to turn. The docs for pg_dump seem clear, use -o (because I use oids,) Use -c so that the database items are created. But restore doesn't work, and the error messages are opaque, to be generous. without -o, I'm not much better off. What now?
Thanks for any help. I'm beyond desperate. The HD is failing on the old server, and RH9 won't install on the newer machines. I have backups (but since I can't retrieve them.... !!!!!)
Is it possible to install the 7.3 postgresql version under ubuntu? If so, where would I find it?
I'm trying to move the DB to postgresql 8.3 under the current ubuntu in a 4-core, 4gb machine. postgresql appears to be installed and working; I added a home directory and password so I could su - postgres and work as the postgres user as I did under RH9; other than that, no fiddling. A small test db acted perfectly normally as far as I coudl tell.
My database uses oids in a few places. I don't recall where or know how to figure out where (it's been years since I worried about the DB structure - it isn't broken); but I know it does use 'em.
I have tried pg_dump with -o and without. In both cases, under 7.3, the command produces output without complaint.
With -o, 8.3 chokes on the restore in many places, as if it didn't know what it was looking at. There are remarks about 'table "pgdump_oid" does not have oids' and there are pointers to \; at the end of a line... then it says 'syntax error at or near "94513916" (an oid?) after a create table
Without -o, things proceed a bit better; during the restore there are errors complaining about characters not in UTF-8 (but both 7.3 and 8.3 are using UTF-8... I have NO idea what its problem is but I surely know I want what was in the old table, in the new table too!), and in one case, an INDEX fails, indicating there is no OID column. Doesn't indicate what it's trying to index, though. After the restore, many tables are empty that should contain a great deal of data. Others appear to have been restored.
I don't know where to turn. The docs for pg_dump seem clear, use -o (because I use oids,) Use -c so that the database items are created. But restore doesn't work, and the error messages are opaque, to be generous. without -o, I'm not much better off. What now?
Thanks for any help. I'm beyond desperate. The HD is failing on the old server, and RH9 won't install on the newer machines. I have backups (but since I can't retrieve them.... !!!!!)
Is it possible to install the 7.3 postgresql version under ubuntu? If so, where would I find it?
"Anony Mous" <fyngyrz@gmail.com> writes:
> I have a moderate size database under RH9 / pgsql 7.3 (I think... that's the
> PG_VERSION in the postgres user data directory.) Under 100 mb for the whole
> thing.
> I'm trying to move the DB to postgresql 8.3 under the current ubuntu in a
> 4-core, 4gb machine.
Try using the 8.3 pg_dump (or more likely pg_dumpall) to dump from the
7.3 server. (Just point it at that server over the net, you don't need
to install it on the old machine.)
regards, tom lane