Postgres Pro
Downloads
Home   >   mailing lists

Thread: Re: [ADMIN] Restoring a Databases that features tserach2

Re: [ADMIN] Restoring a Databases that features tserach2

From
Fischer Ulrich
Date:
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

Re: [ADMIN] Restoring a Databases that features tserach2

From
Oleg Bartunov
Date:
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

Re: [ADMIN] Restoring a Databases that features tserach2

From
Tom Lane
Date:
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

Re: [ADMIN] Restoring a Databases that features tserach2

From
Fischer Ulrich
Date:
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

Re: [ADMIN] Restoring a Databases that features tserach2

From
Tom Lane
Date:
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

Re: [ADMIN] Restoring a Databases that features tserach2

From
Fischer Ulrich
Date:
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