Обсуждение: pg_dump/pg_Restore 8.1.5 db -> 8.2 tsearch2 problem
Hi list,
I've downloaded and compiled the new 8.2 to a new debian box.
I also compile and installed tsearch2 support.
Now I have a db on a 8.1.5 box with tsearch2 support.
How do a dump and restore my database to the new 8.2 box?
I get all kinds of errors when trying to restore the db.
Should I uninstall tsearch2 before dumping or?
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 67; 1255 26918
FUNCTION snb_ru_init(internal) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: could
not find function "snb_ru_init" in file "/usr/local/pgsql/lib/
tsearch2.so"
Command was: CREATE FUNCTION snb_ru_init(internal) RETURNS internal
AS '$libdir/tsearch2', 'snb_ru_init'
LANGUAGE c;
pg_restore: [archiver (db)] could not execute query: ERROR: function
public.snb_ru_init(internal) does not exist
Command was: ALTER FUNCTION public.snb_ru_init(internal) OWNER
TO postgres;
WARNING: errors ignored on restore: 2
Regards,
Henrik
Henrik --
I have only dabbled in tsearch2 so I can'toffer direct advise, but this looks like the problem when upgrading the
postGISstuff ... tsearch2 might have an upgrade functionality (postGIS does), but you can also do a more lbaorious
methodthat strips out the unwanted tsearch2 definitions from the old version; leave the newly compiled tsearch2 in
place.
a) dump the DDL for your database
b) dump the data as its own file
c) edit the ddl sql file and break it up into three parts:
1) All definitions *except* tsearch2 related ones; postGIS stuff is always clumped together and easy to identify;
don'tknow about tsearch2 though.
2) the tsearch2 related stuff
3) the indexes, constraints and other stuff best applied after data is loaded
d) run the ddl with just the table, type, etc definitions
c) load the data
e) run the ddl sql that definex indexes etc.
f) run vacuum analyze, tweak config settings and start testing!
HTH a little,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Henrik Zagerholm
Sent: Thu 12/7/2006 1:35 AM
To: pgsql-general@postgresql.org
Cc:
Subject: [GENERAL] pg_dump/pg_Restore 8.1.5 db -> 8.2 tsearch2 problem
Hi list,
I've downloaded and compiled the new 8.2 to a new debian box.
I also compile and installed tsearch2 support.
Now I have a db on a 8.1.5 box with tsearch2 support.
How do a dump and restore my database to the new 8.2 box?
I get all kinds of errors when trying to restore the db.
Should I uninstall tsearch2 before dumping or?
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 67; 1255 26918
FUNCTION snb_ru_init(internal) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: could
not find function "snb_ru_init" in file "/usr/local/pgsql/lib/
tsearch2.so"
Command was: CREATE FUNCTION snb_ru_init(internal) RETURNS internal
AS '$libdir/tsearch2', 'snb_ru_init'
LANGUAGE c;
pg_restore: [archiver (db)] could not execute query: ERROR: function
public.snb_ru_init(internal) does not exist
Command was: ALTER FUNCTION public.snb_ru_init(internal) OWNER
TO postgres;
WARNING: errors ignored on restore: 2
Regards,
Henrik
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4577e027268986467114494&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:4577e027268986467114494!
-------------------------------------------------------
Thanks for the info! So its that easy, huh? =) I found a solution that worked but I don't know the effects yet. =) I noticed that it complained a lot about snb_ru_init function which apparently is not present in the new tsearch2.so file. So I removed the SP snb_ru_init() from my old database. Made a new dump and voilá it worked. I don't have that much russian texts so I hope the removal of the SP won't impact on the functionality in the future.. =P Cheers, henrik 7 dec 2006 kl. 11:14 skrev Gregory S. Williamson: > Henrik -- > > I have only dabbled in tsearch2 so I can'toffer direct advise, but > this looks like the problem when upgrading the postGIS stuff ... > tsearch2 might have an upgrade functionality (postGIS does), but > you can also do a more lbaorious method that strips out the > unwanted tsearch2 definitions from the old version; leave the newly > compiled tsearch2 in place. > > a) dump the DDL for your database > b) dump the data as its own file > c) edit the ddl sql file and break it up into three parts: > 1) All definitions *except* tsearch2 related ones; postGIS > stuff is always clumped together and easy to identify; don't know > about tsearch2 though. > 2) the tsearch2 related stuff > 3) the indexes, constraints and other stuff best applied after > data is loaded > d) run the ddl with just the table, type, etc definitions > c) load the data > e) run the ddl sql that definex indexes etc. > f) run vacuum analyze, tweak config settings and start testing! > > HTH a little, > > Greg Williamson > DBA > GlobeXplorer LLC > > -----Original Message----- > From: pgsql-general-owner@postgresql.org on behalf of Henrik Zagerholm > Sent: Thu 12/7/2006 1:35 AM > To: pgsql-general@postgresql.org > Cc: > Subject: [GENERAL] pg_dump/pg_Restore 8.1.5 db -> 8.2 tsearch2 problem > > Hi list, > > I've downloaded and compiled the new 8.2 to a new debian box. > I also compile and installed tsearch2 support. > > Now I have a db on a 8.1.5 box with tsearch2 support. > > How do a dump and restore my database to the new 8.2 box? > > I get all kinds of errors when trying to restore the db. > Should I uninstall tsearch2 before dumping or? > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 67; 1255 26918 > FUNCTION snb_ru_init(internal) postgres > pg_restore: [archiver (db)] could not execute query: ERROR: could > not find function "snb_ru_init" in file "/usr/local/pgsql/lib/ > tsearch2.so" > Command was: CREATE FUNCTION snb_ru_init(internal) RETURNS > internal > AS '$libdir/tsearch2', 'snb_ru_init' > LANGUAGE c; > pg_restore: [archiver (db)] could not execute query: ERROR: function > public.snb_ru_init(internal) does not exist > Command was: ALTER FUNCTION public.snb_ru_init(internal) OWNER > TO postgres; > WARNING: errors ignored on restore: 2 > > Regards, > Henrik > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > > ------------------------------------------------------- > Click link below if it is SPAM gsw@globexplorer.com > "https://mailscanner.globexplorer.com/dspam/dspam.cgi? > signatureID=4577e027268986467114494&user=gsw@globexplorer.com&retrain= > spam&template=history&history_page=1" > !DSPAM:4577e027268986467114494! > ------------------------------------------------------- > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings