pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Restoring a Databases that features tserach2
7+ messages / 3 participants
[nested] [flat]

* Restoring a Databases that features tserach2
@ 2004-04-22 08:55 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 11:00 ` Re: Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  0 siblings, 1 reply; 7+ messages in thread

From: Fischer Ulrich @ 2004-04-22 08:55 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Restoring a Databases that features tserach2
  2004-04-22 08:55 Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
@ 2004-04-22 11:00 ` Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 13:33   ` Re: [GENERAL] Restoring a Databases that features tserach2 Oleg Bartunov <oleg@sai.msu.su>
  2004-04-22 13:47   ` Re: [GENERAL] Restoring a Databases that features tserach2 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Fischer Ulrich @ 2004-04-22 11:00 UTC (permalink / raw)
  To: a.schmitz@cityweb.de; +Cc: pgsql-admin; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Restoring a Databases that features tserach2
  2004-04-22 08:55 Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 11:00 ` Re: Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
@ 2004-04-22 13:33   ` Oleg Bartunov <oleg@sai.msu.su>
  2004-04-22 13:56     ` Re: [GENERAL] Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  1 sibling, 1 reply; 7+ messages in thread

From: Oleg Bartunov @ 2004-04-22 13:33 UTC (permalink / raw)
  To: Fischer Ulrich <ulrich.fischer@aerodynamics.ch>; +Cc: a.schmitz@cityweb.de; pgsql-admin; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Restoring a Databases that features tserach2
  2004-04-22 08:55 Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 11:00 ` Re: Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 13:33   ` Re: [GENERAL] Restoring a Databases that features tserach2 Oleg Bartunov <oleg@sai.msu.su>
@ 2004-04-22 13:56     ` Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  0 siblings, 0 replies; 7+ messages in thread

From: Fischer Ulrich @ 2004-04-22 13:56 UTC (permalink / raw)
  To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: a.schmitz@cityweb.de; pgsql-admin; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Restoring a Databases that features tserach2
  2004-04-22 08:55 Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 11:00 ` Re: Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
@ 2004-04-22 13:47   ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-22 14:28     ` Re: [GENERAL] Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  1 sibling, 1 reply; 7+ messages in thread

From: Tom Lane @ 2004-04-22 13:47 UTC (permalink / raw)
  To: Fischer Ulrich <ulrich.fischer@aerodynamics.ch>; +Cc: a.schmitz@cityweb.de; pgsql-admin; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Restoring a Databases that features tserach2
  2004-04-22 08:55 Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 11:00 ` Re: Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 13:47   ` Re: [GENERAL] Restoring a Databases that features tserach2 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-22 14:28     ` Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 15:32       ` Re: [GENERAL] Restoring a Databases that features tserach2 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Fischer Ulrich @ 2004-04-22 14:28 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: a.schmitz@cityweb.de; pgsql-admin; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Restoring a Databases that features tserach2
  2004-04-22 08:55 Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 11:00 ` Re: Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
  2004-04-22 13:47   ` Re: [GENERAL] Restoring a Databases that features tserach2 Tom Lane <tgl@sss.pgh.pa.us>
  2004-04-22 14:28     ` Re: [GENERAL] Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
@ 2004-04-22 15:32       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2004-04-22 15:32 UTC (permalink / raw)
  To: Fischer Ulrich <ulrich.fischer@aerodynamics.ch>; +Cc: a.schmitz@cityweb.de; pgsql-admin; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2004-04-22 15:32 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-22 08:55 Restoring a Databases that features tserach2 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
2004-04-22 11:00 ` Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
2004-04-22 13:33   ` Oleg Bartunov <oleg@sai.msu.su>
2004-04-22 13:56     ` Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
2004-04-22 13:47   ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-22 14:28     ` Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
2004-04-22 15:32       ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox