Обсуждение: Re: problems with pg_restore to 7.3.3 db
hi, sorry for posting this again, but if anyone has any ideas, they would be greatly appreciated, i've searched through the FAQ's and other newsgroups etc but cant seem to find any answers that will help me wit this problem. thanks andy > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of andy morrow > Sent: 02 July 2003 15:17 > To: pgsql-general@postgresql.org > Subject: [GENERAL] problems with pg_restore to 7.3.3 db > > > hi, > > fairly new to postgres admin stuff...... > > i have a production machine that is running postgresql 7.1.3 > also, there's a test machine which already had 7.0.3, and which i newly > installed 7.3.3 > > so, i dumped all the databases from the production db and reloaded on the > test, > there was a few problems but only small ones and i sorted them out ok, but > one of the databases has large objects in it, so the dumpall wont dump the > large objects, > so, i made a dump of the db with large objects > > > pg_dump -b -Fc -f /var/lib/pgsql/backups/mydblo_dump mydblo > > > this went fine, > then i created the db using > > > createdb -T template0 mydblo > > then, i try to restore on the test machine using > > > pg_restore -Fc -d mydblo mydblo_dump > > to which i get the error > > pg_restore: [archiver (db)] could not execute query: ERROR: Load of file > /usr/lib/pgsql//plpgsql.so failed: /usr/lib/pgsql//plpgsql.so: undefined > symbol: fmgr_pl_finfo > > > is there something wrong with my pg_restore command or is there > some mix up > that its looking at variabels from the old version of the db? > > i've tried > > pg_restore -Fc -f mydblo_dump -d mydblo > > but that just starts and does nothing for ages, and i have to kill the job > then. > > thanks for any ideas, > > andy > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"andy morrow" <andy.morrow@jinny.ie> writes:
>> pg_restore -Fc -d mydblo mydblo_dump
>> pg_restore: [archiver (db)] could not execute query: ERROR: Load of file
>> /usr/lib/pgsql//plpgsql.so failed: /usr/lib/pgsql//plpgsql.so: undefined
>> symbol: fmgr_pl_finfo
It looks like it's trying to use the 7.1 version of plpgsql.so, which of
course will not work in 7.3. What you need is to get rid of the
absolute path to plpgsql.so that's in the pg_proc entry, and use a
$libdir-relative path instead, so that you won't have a version-dependent
definition of plpgsql anymore.
-Fc dumps are unfortunately not easy to edit. The best way to do this
might be to temporarily do (in the old database)
UPDATE pg_proc SET probin = '$libdir/plpgsql' WHERE proname =
'plpgsql_call_handler';
then make another dump file, and then revert the probin setting to what
it was (if you still care about the 7.1 database working; the $libdir
notation was added in 7.2 IIRC).
regards, tom lane
Was this an upgrade from an RPM to a source file install? Are parts of the older database in a different directory and maybe the database is trying to load the wrong versions of the /usr/lib/pgsql//plpgsql.so file? Just guessing. On Thu, 3 Jul 2003, andy morrow wrote: > hi, > > sorry for posting this again, but if anyone has any ideas, they would be > greatly appreciated, > > i've searched through the FAQ's and other newsgroups etc but cant seem to > find any answers that will help me wit this problem. > > thanks > > andy > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of andy morrow > > Sent: 02 July 2003 15:17 > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] problems with pg_restore to 7.3.3 db > > > > > > hi, > > > > fairly new to postgres admin stuff...... > > > > i have a production machine that is running postgresql 7.1.3 > > also, there's a test machine which already had 7.0.3, and which i newly > > installed 7.3.3 > > > > so, i dumped all the databases from the production db and reloaded on the > > test, > > there was a few problems but only small ones and i sorted them out ok, but > > one of the databases has large objects in it, so the dumpall wont dump the > > large objects, > > so, i made a dump of the db with large objects > > > > > > pg_dump -b -Fc -f /var/lib/pgsql/backups/mydblo_dump mydblo > > > > > > this went fine, > > then i created the db using > > > > > > createdb -T template0 mydblo > > > > then, i try to restore on the test machine using > > > > > > pg_restore -Fc -d mydblo mydblo_dump > > > > to which i get the error > > > > pg_restore: [archiver (db)] could not execute query: ERROR: Load of file > > /usr/lib/pgsql//plpgsql.so failed: /usr/lib/pgsql//plpgsql.so: undefined > > symbol: fmgr_pl_finfo > > > > > > is there something wrong with my pg_restore command or is there > > some mix up > > that its looking at variabels from the old version of the db? > > > > i've tried > > > > pg_restore -Fc -f mydblo_dump -d mydblo > > > > but that just starts and does nothing for ages, and i have to kill the job > > then. > > > > thanks for any ideas, > > > > andy > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >