Restoring a Databases that features tserach2

Started by Fischer Ulrichalmost 22 years ago7 messagesgeneral

Jump to latest

Fischer Ulrich

ulrich.fischer@aerodynamics.ch

almost 22 years ago

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. +41 41 268 23 53 Fax. +41 41 268 38 97
ulrich.fischer@aerodynamics.ch www.ruag.com

Fischer Ulrich

ulrich.fischer@aerodynamics.ch

almost 22 years ago

In reply to: Fischer Ulrich (#1)

Re: Restoring a Databases that features tserach2

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

and rebuilt on the new system with:

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

Andreas Schmitz wrote:

How exactly is the dump created and the restore started ?

On Thursday 22 April 2004 10:55, Fischer Ulrich wrote:

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)

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. +41 41 268 23 53 Fax. +41 41 268 38 97
ulrich.fischer@aerodynamics.ch www.ruag.com

Import Notes

Reply to msg id not found: 200404221208.07902.a.schmitz@cityweb.de

Oleg Bartunov

oleg@sai.msu.su

almost 22 years ago

In reply to: Fischer Ulrich (#2)

Re: [GENERAL] Restoring a Databases that features tserach2

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

On Thu, 22 Apr 2004, Fischer Ulrich wrote:

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

and rebuilt on the new system with:

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

Andreas Schmitz wrote:

How exactly is the dump created and the restore started ?

On Thursday 22 April 2004 10:55, Fischer Ulrich wrote:

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)

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

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Fischer Ulrich (#2)

Re: [GENERAL] Restoring a Databases that features tserach2

Fischer Ulrich <ulrich.fischer@aerodynamics.ch> writes:

pg_dump (PostgreSQL) 7.3.2

(pg_dumpall -g GLOBALobjects.sql)
pg_dump -s DATABASE > DBschema.sql
pg_dump -Fc DATABASE > DBdata.tar

and rebuilt on the new system with:

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

Fischer Ulrich

ulrich.fischer@aerodynamics.ch

almost 22 years ago

In reply to: Oleg Bartunov (#3)

Re: [GENERAL] Restoring a Databases that features tserach2

Oleg,

this is exactly what I tryed to. But after cleaning all the new pg_ts_*
tables I wasn't able to restore my datas. My work arround is now to
rename all keys in the pg_ts_* tables (Ex: pg_ts_dict: dict_name: simple
to simple_old). With this modification the pg_restore worked!
Now I only have to clean the pg_ts_* tables.

regards,

Ulrich

Oleg Bartunov wrote:

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

On Thu, 22 Apr 2004, Fischer Ulrich wrote:

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

and rebuilt on the new system with:

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

Andreas Schmitz wrote:

How exactly is the dump created and the restore started ?

On Thursday 22 April 2004 10:55, Fischer Ulrich wrote:

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)

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

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

--
Ulrich G. Fischer Dipl. Natw. ETH
Center Aerodynamics Ruag Aerospace
P.O. Box 301 CH-6032 Emmen
Tel. +41 41 268 23 53 Fax. +41 41 268 38 97
ulrich.fischer@aerodynamics.ch www.ruag.com

Fischer Ulrich

ulrich.fischer@aerodynamics.ch

almost 22 years ago

In reply to: Tom Lane (#4)

Re: [GENERAL] Restoring a Databases that features tserach2

Tom Lane wrote:

Fischer Ulrich <ulrich.fischer@aerodynamics.ch> writes:

pg_dump (PostgreSQL) 7.3.2

(pg_dumpall -g GLOBALobjects.sql)
pg_dump -s DATABASE > DBschema.sql
pg_dump -Fc DATABASE > DBdata.tar

and rebuilt on the new system with:

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.

Get rid of Step 3 and 3a produces Errors in Step 4 like:

:
CREATE TABLE
REVOKE
GRANT
CREATE TABLE
REVOKE
GRANT
ERROR: type "tsvector" does not exist
ERROR: relation "mitarbeiter" does not exist
:

So I think this is not a good idea ;-) I've got now a Sollution which
I'll post after writing and testing my own HowTo!

regards

Ulrich

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

--
Ulrich G. Fischer Dipl. Natw. ETH
Center Aerodynamics Ruag Aerospace
P.O. Box 301 CH-6032 Emmen
Tel. +41 41 268 23 53 Fax. +41 41 268 38 97
ulrich.fischer@aerodynamics.ch www.ruag.com

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Fischer Ulrich (#6)

Re: [GENERAL] Restoring a Databases that features tserach2

Fischer Ulrich <ulrich.fischer@aerodynamics.ch> writes:

Tom Lane wrote:

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.

Get rid of Step 3 and 3a produces Errors in Step 4 like:
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