Vous n'êtes pas identifié(e).
Pages : 1
Hi,
I got these errors message when i have made a restore for my date base from our production server to new one
1) I'm wondering if it is a major errors ?
2) Is there a way to fix these errors ?
Please note that when I test the restored db all seems ok I get only one error with one table
please advice
here is the errors that i got when i made the restore
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1865; 2620 3212527 TRIGGER RI_ConstraintTrigger_3212527 rd
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "infodht_invhwtype_fk" for relation "infodht" already exists
Command was: CREATE CONSTRAINT TRIGGER infodht_invhwtype_fk
AFTER UPDATE ON invhwtype
FROM infodht
NOT DEFERRABLE INITIALLY I...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212528
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212529
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212530
pg_restore: [archiver (db)] Error from TOC entry 1871; 2620 3212530 TRIGGER RI_ConstraintTrigger_3212530 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "infodht_invhwrev_fk" for relation "infodht" already exists
Command was: CREATE CONSTRAINT TRIGGER infodht_invhwrev_fk
AFTER UPDATE ON invhwrev
FROM infodht
NOT DEFERRABLE INITIALLY IMM...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212531
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212532
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212533
pg_restore: [archiver (db)] Error from TOC entry 1877; 2620 3212533 TRIGGER RI_ConstraintTrigger_3212533 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "mac_infodht_fk" for relation "infodht" already exists
Command was: CREATE CONSTRAINT TRIGGER mac_infodht_fk
AFTER UPDATE ON mac
FROM infodht
NOT DEFERRABLE INITIALLY IMMEDIATE
...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212534
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212535
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212536
pg_restore: [archiver (db)] Error from TOC entry 1899; 2620 3212536 TRIGGER RI_ConstraintTrigger_3212536 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "infotest_infodht_fk" for relation "infotest" already exists
Command was: CREATE CONSTRAINT TRIGGER infotest_infodht_fk
AFTER UPDATE ON infodht
FROM infotest
NOT DEFERRABLE INITIALLY IMM...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212537
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212538
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212539
pg_restore: [archiver (db)] Error from TOC entry 1901; 2620 3212539 TRIGGER RI_ConstraintTrigger_3212539 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscaloffsets_infodht_fk" for relation "dscaloffsets" already exists
Command was: CREATE CONSTRAINT TRIGGER dscaloffsets_infodht_fk
AFTER UPDATE ON infodht
FROM dscaloffsets
NOT DEFERRABLE INITI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212540
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212541
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212542
pg_restore: [archiver (db)] Error from TOC entry 1938; 2620 3212542 TRIGGER RI_ConstraintTrigger_3212542 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscaloffsets_infotest_fk" for relation "dscaloffsets" already exists
Command was: CREATE CONSTRAINT TRIGGER dscaloffsets_infotest_fk
AFTER UPDATE ON infotest
FROM dscaloffsets
NOT DEFERRABLE INI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212543
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212544
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212545
pg_restore: [archiver (db)] Error from TOC entry 1903; 2620 3212545 TRIGGER RI_ConstraintTrigger_3212545 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscalpower_infodht_fk" for relation "dscalpower" already exists
Command was: CREATE CONSTRAINT TRIGGER dscalpower_infodht_fk
AFTER UPDATE ON infodht
FROM dscalpower
NOT DEFERRABLE INITIALLY...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212546
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212547
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212548
pg_restore: [archiver (db)] Error from TOC entry 1940; 2620 3212548 TRIGGER RI_ConstraintTrigger_3212548 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscalpower_infotest_fk" for relation "dscalpower" already exists
Command was: CREATE CONSTRAINT TRIGGER dscalpower_infotest_fk
AFTER UPDATE ON infotest
FROM dscalpower
NOT DEFERRABLE INITIAL...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212549
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212550
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212551
pg_restore: [archiver (db)] Error from TOC entry 1905; 2620 3212551 TRIGGER RI_ConstraintTrigger_3212551 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "uscaloffsets_infodht_fk" for relation "uscaloffsets" already exists
Command was: CREATE CONSTRAINT TRIGGER uscaloffsets_infodht_fk
AFTER UPDATE ON infodht
FROM uscaloffsets
NOT DEFERRABLE INITI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212552
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212553
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212554
pg_restore: [archiver (db)] Error from TOC entry 1942; 2620 3212554 TRIGGER RI_ConstraintTrigger_3212554 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "uscaloffsets_infotest_fk" for relation "uscaloffsets" already exists
Command was: CREATE CONSTRAINT TRIGGER uscaloffsets_infotest_fk
AFTER UPDATE ON infotest
FROM uscaloffsets
NOT DEFERRABLE INI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212555
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212556
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212557
pg_restore: [archiver (db)] Error from TOC entry 1907; 2620 3212557 TRIGGER RI_ConstraintTrigger_3212557 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "uscalpower_infodht_fk" for relation "uscalpower" already exists
Command was: CREATE CONSTRAINT TRIGGER uscalpower_infodht_fk
AFTER UPDATE ON infodht
FROM uscalpower
NOT DEFERRABLE INITIALLY...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212558
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212559
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212560
pg_restore: [archiver (db)] Error from TOC entry 1944; 2620 3212560 TRIGGER RI_ConstraintTrigger_3212560 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "uscalpower_infotest_fk" for relation "uscalpower" already exists
Command was: CREATE CONSTRAINT TRIGGER uscalpower_infotest_fk
AFTER UPDATE ON infotest
FROM uscalpower
NOT DEFERRABLE INITIAL...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212561
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212562
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212563
pg_restore: [archiver (db)] Error from TOC entry 1909; 2620 3212563 TRIGGER RI_ConstraintTrigger_3212563 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscalpower_02_infodht_fk" for relation "dscalpower_02" already exists
Command was: CREATE CONSTRAINT TRIGGER dscalpower_02_infodht_fk
AFTER UPDATE ON infodht
FROM dscalpower_02
NOT DEFERRABLE INI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212564
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212565
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212566
pg_restore: [archiver (db)] Error from TOC entry 1946; 2620 3212566 TRIGGER RI_ConstraintTrigger_3212566 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscalpower_02_infotest_fk" for relation "dscalpower_02" already exists
Command was: CREATE CONSTRAINT TRIGGER dscalpower_02_infotest_fk
AFTER UPDATE ON infotest
FROM dscalpower_02
NOT DEFERRABLE I...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212567
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212568
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212569
pg_restore: [archiver (db)] Error from TOC entry 1867; 2620 3212569 TRIGGER RI_ConstraintTrigger_3212569 rd
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "infodht_invhwtype_fk" for relation "infodht" already exists
Command was: CREATE CONSTRAINT TRIGGER infodht_invhwtype_fk
AFTER UPDATE ON invhwtype
FROM infodht
NOT DEFERRABLE INITIALLY I...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212570
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212571
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212572
pg_restore: [archiver (db)] Error from TOC entry 1873; 2620 3212572 TRIGGER RI_ConstraintTrigger_3212572 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "infodht_invhwrev_fk" for relation "infodht" already exists
Command was: CREATE CONSTRAINT TRIGGER infodht_invhwrev_fk
AFTER UPDATE ON invhwrev
FROM infodht
NOT DEFERRABLE INITIALLY IMM...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212573
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212574
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212575
pg_restore: [archiver (db)] Error from TOC entry 1879; 2620 3212575 TRIGGER RI_ConstraintTrigger_3212575 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "mac_infodht_fk" for relation "infodht" already exists
Command was: CREATE CONSTRAINT TRIGGER mac_infodht_fk
AFTER UPDATE ON mac
FROM infodht
NOT DEFERRABLE INITIALLY IMMEDIATE
...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212576
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212577
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212578
pg_restore: [archiver (db)] Error from TOC entry 1914; 2620 3212578 TRIGGER RI_ConstraintTrigger_3212578 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "infotest_infodht_fk" for relation "infotest" already exists
Command was: CREATE CONSTRAINT TRIGGER infotest_infodht_fk
AFTER UPDATE ON infodht
FROM infotest
NOT DEFERRABLE INITIALLY IMM...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212579
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212580
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212581
pg_restore: [archiver (db)] Error from TOC entry 1916; 2620 3212581 TRIGGER RI_ConstraintTrigger_3212581 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscaloffsets_infodht_fk" for relation "dscaloffsets" already exists
Command was: CREATE CONSTRAINT TRIGGER dscaloffsets_infodht_fk
AFTER UPDATE ON infodht
FROM dscaloffsets
NOT DEFERRABLE INITI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212582
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212583
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212584
pg_restore: [archiver (db)] Error from TOC entry 1949; 2620 3212584 TRIGGER RI_ConstraintTrigger_3212584 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscaloffsets_infotest_fk" for relation "dscaloffsets" already exists
Command was: CREATE CONSTRAINT TRIGGER dscaloffsets_infotest_fk
AFTER UPDATE ON infotest
FROM dscaloffsets
NOT DEFERRABLE INI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212585
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212586
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212587
pg_restore: [archiver (db)] Error from TOC entry 1918; 2620 3212587 TRIGGER RI_ConstraintTrigger_3212587 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscalpower_infodht_fk" for relation "dscalpower" already exists
Command was: CREATE CONSTRAINT TRIGGER dscalpower_infodht_fk
AFTER UPDATE ON infodht
FROM dscalpower
NOT DEFERRABLE INITIALLY...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212588
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212589
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212590
pg_restore: [archiver (db)] Error from TOC entry 1951; 2620 3212590 TRIGGER RI_ConstraintTrigger_3212590 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscalpower_infotest_fk" for relation "dscalpower" already exists
Command was: CREATE CONSTRAINT TRIGGER dscalpower_infotest_fk
AFTER UPDATE ON infotest
FROM dscalpower
NOT DEFERRABLE INITIAL...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212591
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212592
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212593
pg_restore: [archiver (db)] Error from TOC entry 1920; 2620 3212593 TRIGGER RI_ConstraintTrigger_3212593 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "uscaloffsets_infodht_fk" for relation "uscaloffsets" already exists
Command was: CREATE CONSTRAINT TRIGGER uscaloffsets_infodht_fk
AFTER UPDATE ON infodht
FROM uscaloffsets
NOT DEFERRABLE INITI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212594
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212595
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212596
pg_restore: [archiver (db)] Error from TOC entry 1953; 2620 3212596 TRIGGER RI_ConstraintTrigger_3212596 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "uscaloffsets_infotest_fk" for relation "uscaloffsets" already exists
Command was: CREATE CONSTRAINT TRIGGER uscaloffsets_infotest_fk
AFTER UPDATE ON infotest
FROM uscaloffsets
NOT DEFERRABLE INI...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212597
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212598
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212599
pg_restore: [archiver (db)] Error from TOC entry 1922; 2620 3212599 TRIGGER RI_ConstraintTrigger_3212599 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "uscalpower_infodht_fk" for relation "uscalpower" already exists
Command was: CREATE CONSTRAINT TRIGGER uscalpower_infodht_fk
AFTER UPDATE ON infodht
FROM uscalpower
NOT DEFERRABLE INITIALLY...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212600
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212601
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212602
pg_restore: [archiver (db)] Error from TOC entry 1955; 2620 3212602 TRIGGER RI_ConstraintTrigger_3212602 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "uscalpower_infotest_fk" for relation "uscalpower" already exists
Command was: CREATE CONSTRAINT TRIGGER uscalpower_infotest_fk
AFTER UPDATE ON infotest
FROM uscalpower
NOT DEFERRABLE INITIAL...
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212603
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212604
pg_restore: creating TRIGGER RI_ConstraintTrigger_3212605
pg_restore: [archiver (db)] Error from TOC entry 1924; 2620 3212605 TRIGGER RI_ConstraintTrigger_3212605 alainle
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "dscalpower_02_infodht_fk" for relation "dscalpower_02" already exists
Command was: CREATE CONSTRAINT TRIGGER dscalpower_02_infodht_fk
AFTER UPDATE ON infodht
FROM dscalpower_02
Hors ligne
This is a french web forums about PostgreSQL. There are better options for english help, for example PostgreSQL Mailing lists.
Anyway, on your issue, it seems you're trying to restore your dump in the old database. The first error message, and a lot of others are like this : ERROR: constraint "infodht_invhwtype_fk" for relation "infodht" already exists ... it cannot restore your object because it already exists. You have to make sure that the database is empty (or at the very least, doesn't already contain objets of the same name).
Guillaume.
Hors ligne
Merci pour votre réponse désolée pour l'anglais j'ai pensé qu'on pourrait écrire dans les deux langues sur ce forum
Concernant mon problème je ne comprends pas comment ces objets pourrait exister car lorsque le restore était fait; je l'ai fais sur un nouveau serveur qui ne contenait pas aucune base de données
Donc je suis sure que ma base de données était vide avant que je fasse le restore
L’autre point que je ne comprends pas on dirait que ces erreurs ne sont pas majeures ou du moins ils n’ont pas d'impact car en testant ma db elle semble bien fonctionner
Hors ligne
L'erreur ne semble pas majeure car par défaut la restauration n'est pas faite dans une unique transaction. Vous êtes donc dans un état un peu bancal où certaines données sont restaurées et d'autres non.
Pour les erreurs de restauration, pour ma part pas trop d'idée. Vous avez une erreurs toutes les 3 contraintes ?
A tout hasard, , vous pouvez vérifier sur la base originale quelles sont les contraintes avant et après une des contraintes en erreur, peut-être y a-t-il un problème à ce niveau là, par exemple :
SELECT * FROM pg_constraint WHERE oid IN (3212528,3212529,3212530)
pour voir les 3 premières contraintes restaurées
ou
SELECT * FROM pg_constraint WHERE conname = 'infodht_invhwtype_fk'
S'il y a des doublons cela expliquerait l'erreur (et indiquerait un problème sur la base aussi).
Julien.
https://rjuju.github.io/
Hors ligne
Merci pour votre réponse
1) En faisant SELECT * FROM pg_trigger WHERE oid IN (3212528,3212529,3212530) sur la base original
J’ai eu ceci comme réponse (désolé pour le format pas trop lisible j'aurai aimé avoir la possibilité de joindre un fichier):
50266;"RI_ConstraintTrigger_3212528";1644;21;t;t;"infodht_invhwrev_fk";50260;f;f;6;"";"infodht_invhwrev_fk\000infodht\000invhwrev\000UNSPECIFIED\000invhwrev_revpcb\000revpcb\000"
50260;"RI_ConstraintTrigger_3212529";1654;9;t;t;"infodht_invhwrev_fk";50266;f;f;6;"";"infodht_invhwrev_fk\000infodht\000invhwrev\000UNSPECIFIED\000invhwrev_revpcb\000revpcb\000"
50260;"RI_ConstraintTrigger_3212530";1655;17;t;t;"infodht_invhwrev_fk";50266;f;f;6;"";"infodht_invhwrev_fk\000infodht\000invhwrev\000UNSPECIFIED\000invhwrev_revpcb\000revpcb\000"
et en faisant :SELECT * FROM pg_constraint WHERE conname = 'infodht_invhwtype_fk' toujour sur la base original je n'ai rien eu comme data output
2) par contre sur la base restaurer lorsque j'ai fais SELECT * FROM pg_trigger WHERE oid IN (3212528,3212529,3212530)
Je n'ai rien eu en sortie
et en faisant :SELECT * FROM pg_constraint WHERE conname = 'infodht_invhwtype_fk' j'ai eu ceci comme output
"infodht_invhwtype_fk";2200;"f";f;f;17195894;0;20774602;17195916;"a";"a";"u";t;0;"{2}";"{1}";"{98}";"{98}";"{98}";"";"";""
3) Aussi en comparant les deux table pg_constreaint et pg_trigger sur mon ancien serveur dont la PG_VERSION=8.1 et le nouveau dont la PG_VERSION=9.0 j'ai noté qu'ils n'ont pas le même nombre de colonnes et même le data type peux déférer pour certain colonnes de même tableau d'un serveur à autre
Ainsi je me demande s'il y a un moyen de remédier à cela ?
Hors ligne
Quelle version de pg_dump et pg_restore utilisez-vous ? 8.1 ou 9.0
Lors d'une montée de version, il faut utiliser les pg_dump et pg_restore de la version la plus récente il me semble. Le problème pourrait venir de là.
Julien.
https://rjuju.github.io/
Hors ligne
pour faire le pg_dump et pg_restore j'utilise l'utilitaire de pgAdminIII version 1.12.3
backup pour faire le dump du serveur 8.1
et restore pour restorer sur le serveur 9.0
Hors ligne
pgAdmin stocke le répertoire des exécutables qu'il utilise dans sa configuration. Vous devez vérifier dans les préférences, onglet "Général" le répertoire indiqué par "Chemin des binaires PG", et vérifier la version de ces exécutables (ex: pg_dump --version) pour voir de quelle version il s'agit.
Julien.
https://rjuju.github.io/
Hors ligne
Les OID d'un serveur à un autre sont différents, donc ce n'est pas très étonnant que vous les trouviez sur un serveur et pas sur l'autre. Recommencez la restauration sur une base vierge.
Guillaume.
Hors ligne
@rjuju
version pg_dump=9.0.3
version pg_dumpall=9.0.3
version pg_restore=9.0.3
étant donnée que j’utilise l'utilitaire pg admin pour faire le backup et le restore je ne sais pas si il fait appel à pg_dump ou pgdumpall lors de l'opération backup
Hors ligne
@gleu
Oui je l'ai recommencé en créant une base de donne vierge mais je continue à avoir les mêmes erreurs
Hors ligne
Si c'est une sauvegarde de la base, c'est forcément pg_dump.
Guillaume.
Hors ligne
Pages : 1
