Restoring a Databases that features tserach2

[Fischer Ulrich]

Hi

I'm trying to restore tsearch2 featuring database like discribed in the
'tsearch-V2-intro' document.

(http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html)

In point '5) Restore the data for the database' I get the following Error:

pg_restore: ERROR: duplicate key violates unique constraint
"pg_ts_dict_pkey"
CONTEXT: COPY pg_ts_dict, line 1: "simple 215247 \N 215248
Simple example of dictionary."
pg_restore: [archiver (db)] error returned by PQendcopy
pg_restore: *** aborted because of error


Does anybody have some experience in this task?

Thanks

Ulrich
--
Ulrich G. Fischer Dipl. Natw. ETH
Center Aerodynamics Ruag Aerospace
P.O. Box 301 CH-6032 Emmen
Tel. [phone] Fax. [phone]
***@aerodynamics.ch www.ruag.com


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Oleg Bartunov]

Fischer,

probable scenario:

after step 3. you have tables pg_ts_* and when you restore your database
you tried to insert duplicated data.
I recommend to see remove any entries related to pg_ts_* tables.

Oleg

Some additional infos to my problem
Old System: Postgresql 7.3.2
New System: Postgresql 7.4.1
pg_dump (PostgreSQL) 7.3.2
(pg_dumpall -g GLOBALobjects.sql)
pg_dump -s DATABASE > DBschema.sql
pg_dump -Fc DATABASE > DBdata.tar
psql (PostgreSQL) 7.4.1
1. createdb DATABASE
2. (psql DATABASE < GLOBALobjects.sql)
3. psql DATABASE < tsearch2.sql
3.a. psql DATABASE < setup_ge_ispell.sql
4. psql DATABASE < DBschema.sql
5. pg_restore -N -a -v -d DATABASE DBdata.tar
There is no difference when dumped with "pg_dump (PostgreSQL) 7.4.1". I
get the same error. When I do not update the dict-settings
(setup_ge_ispell.sql) I can not even restore the other datas.
Ulrich

How exactly is the dump created and the restore started ?

Post by Fischer Ulrich
Hi
I'm trying to restore tsearch2 featuring database like discribed in the
'tsearch-V2-intro' document.
(http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-int
ro.html)
pg_restore: ERROR: duplicate key violates unique constraint
"pg_ts_dict_pkey"
CONTEXT: COPY pg_ts_dict, line 1: "simple 215247 \N 215248
Simple example of dictionary."
pg_restore: [archiver (db)] error returned by PQendcopy
pg_restore: *** aborted because of error
Does anybody have some experience in this task?
Thanks
Ulrich

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: ***@sai.msu.su, http://www.sai.msu.su/~megera/
phone: [phone]

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

[Tom Lane]

pg_dump (PostgreSQL) 7.3.2
(pg_dumpall -g GLOBALobjects.sql)
pg_dump -s DATABASE > DBschema.sql
pg_dump -Fc DATABASE > DBdata.tar
psql (PostgreSQL) 7.4.1
1. createdb DATABASE
2. (psql DATABASE < GLOBALobjects.sql)
3. psql DATABASE < tsearch2.sql
3.a. psql DATABASE < setup_ge_ispell.sql
4. psql DATABASE < DBschema.sql
5. pg_restore -N -a -v -d DATABASE DBdata.tar

This is certainly not going to work because the dump from the old
database will already have all the tsearch2 objects, not to mention
whatever setup_ge_ispell.sql may create. You should get rid of steps
3 and 3a.

There is no difference when dumped with "pg_dump (PostgreSQL) 7.4.1". I
get the same error. When I do not update the dict-settings
(setup_ge_ispell.sql) I can not even restore the other datas.

If you're still having trouble, try dropping the -N option to pg_restore.
I don't know why that option even exists ... there is no practical use
to it AFAIK.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Post by Tom Lane
This is certainly not going to work because the dump from the old
database will already have all the tsearch2 objects, not to mention
whatever setup_ge_ispell.sql may create. You should get rid of steps
3 and 3a.

ERROR: type "tsvector" does not exist

Yeah, not surprising if you used ALTER TABLE to add a tsvector column
to a table created before you'd loaded tsearch2 into the database.
The dump will create the objects in the wrong order.

This whole class of problems should be gone in 7.5, since CVS-tip
pg_dump understands dependencies properly. As a short-term workaround
I'd suggest using "pg_dump -Fc db" as the source data and manually
adjusting the load order with pg_restore's -L option. The
separate-schema-and-data approach is not very good since it's a lot
slower to load large databases that way.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings