Обсуждение: Re: [ADMIN] 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
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
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
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> 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
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