Обсуждение: pg_restore error
hello everybody, i am pascal, i am from paris, france, and i am a newbie here. well, i have a problem with the pg_restore command and i don't find any informations on the web about it. when trying to restaure a database, i have the above error : [postgres@preprod2 pgsql]$ bin/pg_restore -d interlig -Fc /home/postgres/dump_20021010new.out pg_restore: [archiver (db)] could not execute query: ERROR: Relation 'cable_id_cable_seq' already exists and i try this : [postgres@preprod2 pgsql]$ bin/pg_restore -d interlig -Fc /home/postgres/dump_20021010new.out -t synd_article pg_restore: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'synd_article_pkey' for table 'synd_article' pg_restore: [archiver (db)] could not execute query: ERROR: Relation 'synd_article' already exists I don't understand what can be this error, i just want to restore my database as the same it was a few hours ago. thank you for your help, pascal. -- http://www.magnetophone.com
Are you sure the synd_article table and cable_id_cable_seq sequence aren't already in the database? Presumably your dump file contains the create statements for these two objects and so the pg_restore is trying to create them. How did you create the dump file? The -a switch will allow you to dump only the data if that¹s what you need. Looks like you may be dumping the schema as well HTH adam > hello everybody, > > i am pascal, i am from paris, france, and i am a newbie here. > well, i have a problem with the pg_restore command and i don't find > any informations on the web about it. when trying to restaure a > database, i have the above error : > > [postgres@preprod2 pgsql]$ bin/pg_restore -d interlig -Fc > /home/postgres/dump_20021010new.out > pg_restore: [archiver (db)] could not execute query: ERROR: Relation > 'cable_id_cable_seq' already exists > > and i try this : > > [postgres@preprod2 pgsql]$ bin/pg_restore -d interlig -Fc > /home/postgres/dump_20021010new.out -t synd_article > pg_restore: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit > index 'synd_article_pkey' for table 'synd_article' > pg_restore: [archiver (db)] could not execute query: ERROR: Relation > 'synd_article' already exists > > > I don't understand what can be this error, i just want to restore my > database as the same it was a few hours ago. > > thank you for your help, > > pascal.
>Are you sure the synd_article table and cable_id_cable_seq sequence aren't >already in the database? yes, there are existing. >Presumably your dump file contains the create statements for these two >objects and so the pg_restore is trying to create them. > >How did you create the dump file? The -a switch will allow you to dump only >the data if that's what you need. Looks like you may be dumping the schema >as well I tried with the -a option, and it was quite the same. I just tried to delete the synd_article_pkey then restore the table synd_article, but it didn't recreate the keys as they were. and i don't know how to do that (as i am using mysql for my other projects and that it seams to be more simple). thanks, pascal. >HTH > >adam > >> hello everybody, >> >> i am pascal, i am from paris, france, and i am a newbie here. >> well, i have a problem with the pg_restore command and i don't find >> any informations on the web about it. when trying to restaure a >> database, i have the above error : >> >> [postgres@preprod2 pgsql]$ bin/pg_restore -d interlig -Fc >> /home/postgres/dump_20021010new.out >> pg_restore: [archiver (db)] could not execute query: ERROR: Relation >> 'cable_id_cable_seq' already exists >> >> and i try this : >> >> [postgres@preprod2 pgsql]$ bin/pg_restore -d interlig -Fc >> /home/postgres/dump_20021010new.out -t synd_article >> pg_restore: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit > > index '' for table 'synd_article' >> pg_restore: [archiver (db)] could not execute query: ERROR: Relation >> 'synd_article' already exists >> >> >> I don't understand what can be this error, i just want to restore my > > database as the same it was a few hours ago. > > > > thank you for your help, > > > > pascal. -- http://www.magnetophone.com
On 10/10/02 1:25 pm, "pascal bérest" <lists@magnetophone.com> wrote: >> Are you sure the synd_article table and cable_id_cable_seq sequence aren't >> already in the database? > > yes, there are existing. > >> Presumably your dump file contains the create statements for these two >> objects and so the pg_restore is trying to create them. >> >> How did you create the dump file? The -a switch will allow you to dump only >> the data if that's what you need. Looks like you may be dumping the schema >> as well > > I tried with the -a option, and it was quite the same. > I just tried to delete the synd_article_pkey then restore the table > synd_article, but it didn't recreate the keys as they were. and i > don't know how to do that (as i am using mysql for my other projects > and that it seams to be more simple). > What happened when you tried the -a option? You can create a table with a primary key like so CREATE TABLE test ( id int, PRIMARY KEY (id) );
At 13:38 +0100 10/10/2002, Adam Witney wrote: >On 10/10/02 1:25 pm, "pascal bérest" <lists@magnetophone.com> wrote: > >>> Are you sure the synd_article table and cable_id_cable_seq sequence aren't >>> already in the database? >> >> yes, there are existing. >> >>> Presumably your dump file contains the create statements for these two >>> objects and so the pg_restore is trying to create them. >>> >>> How did you create the dump file? The -a switch will allow you to dump only >>> the data if that's what you need. Looks like you may be dumping the schema >>> as well >> >> I tried with the -a option, and it was quite the same. >> I just tried to delete the synd_article_pkey then restore the table >> synd_article, but it didn't recreate the keys as they were. and i >> don't know how to do that (as i am using mysql for my other projects >> and that it seams to be more simple). >> > >What happened when you tried the -a option? i have quite the same error. i have a message telling that the key can't be duplicate. i suppose that as the id is already existing, it can't duplicate it, as in mysql. >You can create a table with a primary key like so > >CREATE TABLE test ( > id int, > PRIMARY KEY (id) >); yep, but is a way to create a primary key when the table already exists ? thanx, pascal. -- http://www.magnetophone.com
>>> I tried with the -a option, and it was quite the same. >>> I just tried to delete the synd_article_pkey then restore the table >>> synd_article, but it didn't recreate the keys as they were. and i >>> don't know how to do that (as i am using mysql for my other projects >>> and that it seams to be more simple). >>> >> >> What happened when you tried the -a option? > > i have quite the same error. i have a message telling that the key > can't be duplicate. i suppose that as the id is already existing, it > can't duplicate it, as in mysql. > >> You can create a table with a primary key like so >> >> CREATE TABLE test ( >> id int, >> PRIMARY KEY (id) >> ); > > yep, but is a way to create a primary key when the table already exists ? It seems that your dump file contains data that is already in the database and so it is complaining when you try to duplicate a primary key What exactly are you trying to do with your dump and restore? pg_dump will archive all the data from a table and pg_restore will restore all the data back into an empty table. Take a look here for more details http://www.postgresql.org/idocs/index.php?backup.html I think the ability to add a primary key after table creation is only available as of version 7.3 (currently in beta) HTH adam