Re: problems with pg_restore to 7.3.3 db

Started by andy morrowalmost 23 years ago3 messagesgeneral

andy.morrow@jinny.ie

almost 23 years ago

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

Show quoted text

-----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

Import Notes

Reply to msg id not found: ONEGLGLFGGHIEDFGBPCMKEDACBAA.andy.morrow@jinny.ie

Tom Lane

tgl@sss.pgh.pa.us

almost 23 years ago

In reply to: andy morrow (#1)

"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

scott.marlowe

scott.marlowe@ihs.com

almost 23 years ago

In reply to: andy morrow (#1)

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:

Show quoted text

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