Thread: Restoring a db dump with tsearch fields fails
Bit of a problem here, am regularly backing up my database with pg_dumpall, which works fine. However when I attempt a restore on another box, things go rather pearshaped, and I'm pretty sure this is because I am using tsearch from the contrib directory. I've tried adding tsearch.sql to the template1 db, in case that helped, which it didn't. :/ I get lots of these during the restore: ERROR: parser: parse error at or near "ikebe" ERROR: parser: parse error at or near "'" ERROR: parser: parse error at or near "ne" ERROR: parser: parse error at or near "'" ERROR: parser: parse error at or near "ad" ERROR: parser: parse error at or near "phane" ERROR: parser: parse error at or near "rgaard" ERROR: parser: parse error at or near "gnes" ERROR: parser: parse error at or near "e" ERROR: parser: parse error at or near "otis" ERROR: parser: parse error at or near "gis" ERROR: parser: parse error at or near "rald" Query buffer reset (cleared). invalid command \n Query buffer reset (cleared). invalid command \n<p Query buffer reset (cleared). invalid command \n<p Query buffer reset (cleared). invalid command \n<p Query buffer reset (cleared). I'm presuming all of these relate to the way pg_dump creates scripts for the tsearch fields. This could make restoring my database rather tricky if I ever have to do this for real. :o Anyone have any suggestions? Yours Unwhettedly, Robert John Shepherd. Editor DVD REVIEWER The UK's BIGGEST Online DVD Magazine http://www.dvd.reviewer.co.uk For a copy of my Public PGP key, email: pgp@robertsworld.org.uk
Which version of pgsql? which version of tsearch? I had similar problems and emailed the creators of tsearch and they provided the fix. I believe it was later integrated back into the distro. If you check the lines where those errors occur, I bet it is missing a single or double quote around those word. On Fri, 8 Nov 2002, Robert John Shepherd wrote: > Bit of a problem here, am regularly backing up my database with > pg_dumpall, which works fine. > > However when I attempt a restore on another box, things go rather > pearshaped, and I'm pretty sure this is because I am using tsearch from > the contrib directory. > > I've tried adding tsearch.sql to the template1 db, in case that helped, > which it didn't. :/ > > I get lots of these during the restore: > ERROR: parser: parse error at or near "ikebe" > ERROR: parser: parse error at or near "'" > ERROR: parser: parse error at or near "ne" > ERROR: parser: parse error at or near "'" > ERROR: parser: parse error at or near "ad" > ERROR: parser: parse error at or near "phane" > ERROR: parser: parse error at or near "rgaard" > ERROR: parser: parse error at or near "gnes" > ERROR: parser: parse error at or near "e" > ERROR: parser: parse error at or near "otis" > ERROR: parser: parse error at or near "gis" > ERROR: parser: parse error at or near "rald" > Query buffer reset (cleared). > invalid command \n > Query buffer reset (cleared). > invalid command \n<p > Query buffer reset (cleared). > invalid command \n<p > Query buffer reset (cleared). > invalid command \n<p > Query buffer reset (cleared). > > > I'm presuming all of these relate to the way pg_dump creates scripts for > the tsearch fields. > > This could make restoring my database rather tricky if I ever have to do > this for real. :o > > Anyone have any suggestions? > > > > Yours Unwhettedly, > Robert John Shepherd. > > Editor > DVD REVIEWER > The UK's BIGGEST Online DVD Magazine > http://www.dvd.reviewer.co.uk > > For a copy of my Public PGP key, email: pgp@robertsworld.org.uk > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Thomas T. Thai Minnesota.com, Inc.
> Which version of pgsql? which version of tsearch? 7.2.3, and whatever ver of tsearch is included in the contrib dir for that. > I had similar problems and emailed the creators of tsearch and they > provided the fix. I believe it was later integrated back into > the distro. Don't suppose you still have that lying around? :) > If you check the lines where those errors occur, I bet it is missing a > single or double quote around those word. Not sure it is that, but it could be I guess. From looking at the source there are single quotes around all the tsearch words. I've also tried dumping the database with: pg_dump -Fc -b -c reviewer > reviewer.tar But when I restore using: pg_restore -Fc -C -d template1 reviewer.tar I get the following error: pg_restore: [archiver (db)] could not execute query: ERROR: Unable to locate type name 'txtidx' in catalog I've tried with and without including all the tsearch objects in template1, when I have included them I just get a load of other errors because the objects already exist. Yours Unwhettedly, Robert John Shepherd. Editor DVD REVIEWER The UK's BIGGEST Online DVD Magazine http://www.dvd.reviewer.co.uk For a copy of my Public PGP key, email: pgp@robertsworld.org.uk
Hi All, I know how to get column names from tables: select attname from pg_attribute where attrelid=(select oid from pg_class where relname='table1'); but it does not work for temporary tables, because when creating it gets a system name like 'pg_temp_6410_1' Is there any solution for it? Thanks, Joseph
"Robert John Shepherd" <robert@reviewer.co.uk> writes:
> I get the following error:
> pg_restore: [archiver (db)] could not execute query: ERROR: Unable
> to locate type name 'txtidx' in catalog
IIRC, there is some issue with tsearch that makes it likely to fool
pg_dump into outputting items in the wrong order (ie, you may have
tables with txtidx columns appearing before the type txtidx is defined).
The short-term workaround is to manually fix the load order. With a
text dump file you'd just hack away at it with a text editor. With a
tar dump file, you need to use pg_restore's options that control the
load order. I've never done this and can't offer any advice beyond
"read the man page" ...
regards, tom lane
"Kabai J�zsef" <kabai@audiobox.hu> writes:
> I know how to get column names from tables:
> select attname from pg_attribute where attrelid=(select oid from
> pg_class where relname='table1');
> but it does not work for temporary tables, because when creating it gets a system name like 'pg_temp_6410_1'
There is no good solution in pre-7.3 releases, because the mapping from
logical temp table name to actual table name is hidden inside the
backend.
In 7.3 temp tables actually have their user-given names. (They don't
conflict with regular tables because they're in a different schema.)
This moves the problem from "how do I find the temp table name" to "how
do I find the temp schema name" --- but there are several possible
answers to that. One nice way is to bypass the problem by using the
new regclass datatype:
select attname from pg_attribute where attrelid = 'table1'::regclass;
The regclass conversion produces essentially the same effect as your
subselect, ie, it gets the OID of table1 ... but the regclass input
converter uses your schema search path, so it will find the temp table
named 'table1' in preference to any other 'table1'.
So, come help beta-test 7.3 ... ;-)
regards, tom lane