pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRestoring 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>
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 11:00 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
parent: Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
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 13:33 Oleg Bartunov <oleg@sai.msu.su>
parent: 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 13:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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 13:56 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
parent: Oleg Bartunov <oleg@sai.msu.su>
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 14:28 Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
parent: 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 15:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Fischer Ulrich <ulrich.fischer@aerodynamics.ch>
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