Forums PostgreSQL.fr

pvincent

Membre

migration de base postgres 8x vers 9.2.3

Bonjour à tous

Qui peux me dire pourquoi avec postgres sous linux, un
pg_dump -Fc -b ma_base_de_donnees > mon_fichier_dump
ou
pg_dump ma_base_de_donnees > mon_fichier_dump.sql

ne sauvegarde pas toute la base ? quand je regarde le mon_fichier_dump.sql sous un éditeur,il manque des tables, c'est bisarre ça quand même.

De plus quand je restore avec la derniere version de postgres 9.2.3
J'ai des tonnes d'erreurs.

pg_dump versiàon 8x et pg_restore version 9.2.3 ne sont pas compatibles ?

merci de répondre si quelqu'un a les mêmes soucis.

Marc Cousin

Membre

Re : migration de base postgres 8x vers 9.2.3

pg_dump ma_base_de_donnees > mon_fichier_dump.sql sauve toute la base dans un fichier texte. Si ce n'est pas le cas pour vous, c'est un bug, mais ça serait surprenant (on l'aurait vu depuis longtemps, sur une 8.x). Comment vérifiez-vous ?

---

Marc.

pvincent

Membre

Re : migration de base postgres 8x vers 9.2.3

Bonjour

En fait le problème ne vient pas de de pg_restore en lui même mais des (d'après ce que je crois comprendre)  des procédures stockées
j'ai ce message : Extension libraries are required to use the PG_MODULE_MAGIC macro
De quelle extensions s'agit-il et ou les trouver et comment les installer ?

Merci

Marc Cousin

Membre

Re : migration de base postgres 8x vers 9.2.3

Avec le message d'erreur complet, on devrait pouvoir vous aider (le nom de la fonction qui n'arrive pas à être restaurée…)

---

Marc.

pvincent

Membre

Re : migration de base postgres 8x vers 9.2.3

Bah des messages d'erreurs j'en ai des centaines à tel point que je vais laisser tomber la restore...

En voici quelques uns

Merci quand même de vos réponses.

pg_restore: [archiver (db)] Error from TOC entry 2066; 1259 12857608 TABLE table_10 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_10" already exists
    Command was: CREATE TABLE table_10 (
    notable_10 integer DEFAULT nextval(('seq_table_10'::text)::regclass) NOT NULL,
    cod_couv char...
pg_restore: [archiver (db)] Error from TOC entry 2067; 1259 12857611 TABLE table_11_2 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_11_2" already exists
    Command was: CREATE TABLE table_11_2 (
    notable_11_2 integer DEFAULT nextval(('seq_table_11_2'::text)::regclass) NOT NULL,
    cod_cou...
pg_restore: [archiver (db)] Error from TOC entry 2068; 1259 12857614 TABLE table_12_7 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_12_7" already exists
    Command was: CREATE TABLE table_12_7 (
    notable_12_7 integer DEFAULT nextval(('seq_table_12_7'::text)::regclass) NOT NULL,
    cod_cou...
pg_restore: [archiver (db)] Error from TOC entry 2069; 1259 12857617 TABLE table_13_3 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_13_3" already exists
    Command was: CREATE TABLE table_13_3 (
    notable_13_3 integer DEFAULT nextval(('seq_table_13_3'::text)::regclass) NOT NULL,
    cod_cou...
pg_restore: [archiver (db)] Error from TOC entry 2070; 1259 12857620 TABLE table_16_6 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_16_6" already exists
    Command was: CREATE TABLE table_16_6 (
    notable_16_6 integer DEFAULT nextval(('seq_table_16_6'::text)::regclass) NOT NULL,
    cod_cou...
pg_restore: [archiver (db)] Error from TOC entry 2071; 1259 12857623 TABLE table_17_1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_17_1" already exists
    Command was: CREATE TABLE table_17_1 (
    notable_17_1 integer DEFAULT nextval(('seq_table_17_1'::text)::regclass) NOT NULL,
    cod_cou...
pg_restore: [archiver (db)] Error from TOC entry 2072; 1259 12857626 TABLE table_18_1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_18_1" already exists
    Command was: CREATE TABLE table_18_1 (
    notable_18_1 integer DEFAULT nextval(('seq_table_18_1'::text)::regclass) NOT NULL,
    cod_cou...
pg_restore: [archiver (db)] Error from TOC entry 2073; 1259 12857629 TABLE table_8_1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_8_1" already exists
    Command was: CREATE TABLE table_8_1 (
    notable_8_1 integer DEFAULT nextval(('seq_table_8_1'::text)::regclass) NOT NULL,
    cod_couv c...
pg_restore: [archiver (db)] Error from TOC entry 2074; 1259 12857632 TABLE table_9_1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "table_9_1" already exists
    Command was: CREATE TABLE table_9_1 (
    notable_9_1 integer DEFAULT nextval(('seq_table_9_1'::text)::regclass) NOT NULL,
    cod_couv c...
pg_restore: [archiver (db)] Error from TOC entry 2075; 1259 12857635 TABLE tarif postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tarif" already exists
    Command was: CREATE TABLE tarif (
    notarif integer DEFAULT nextval(('seq_tarif'::text)::regclass) NOT NULL,
    code_tarif integer,
  ...
pg_restore: [archiver (db)] Error from TOC entry 2076; 1259 12857641 TABLE tarif_80d postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tarif_80d" already exists
    Command was: CREATE TABLE tarif_80d (
    notarif integer,
    code_tarif integer,
    regle_tarif integer[]
);

pg_restore: [archiver (db)] Error from TOC entry 2077; 1259 12857646 TABLE tdeltacheck postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tdeltacheck" already exists
    Command was: CREATE TABLE tdeltacheck (
    nodelta integer,
    ind_pref integer,
    seuil double precision,
    abas double precision,...
pg_restore: [archiver (db)] Error from TOC entry 2078; 1259 12857648 TABLE technique postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  type "dateheure" does not exist
LINE 5:     datdeb dateheure,
                   ^
    Command was: CREATE TABLE technique (
    notechnique integer DEFAULT nextval('seq_technique'::regclass) NOT NULL,
    noana integer,
   ...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "public.technique" does not exist
    Command was: ALTER TABLE public.technique OWNER TO postgres;

pg_restore: [archiver (db)] Error from TOC entry 2079; 1259 12857652 TABLE texte postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "texte" already exists
    Command was: CREATE TABLE texte (
    num_labo integer,
    num_ligne integer,
    texte character varying(61)
);

pg_restore: [archiver (db)] Error from TOC entry 2080; 1259 12857654 TABLE texteana postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "texteana" already exists
    Command was: CREATE TABLE texteana (
    sficdos integer,
    notexte integer,
    "type" integer,
    texte text
);

pg_restore: [archiver (db)] Error from TOC entry 2081; 1259 12857659 TABLE tnormz postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tnormz" already exists
    Command was: CREATE TABLE tnormz (
    tabrev character varying(8),
    tnoana integer,
    version integer DEFAULT nextval(('seqv_tnormz...
pg_restore: [archiver (db)] Error from TOC entry 2082; 1259 12857665 TABLE tnumlot postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tnumlot" already exists
    Command was: CREATE TABLE tnumlot (
    sficdos integer,
    noana integer,
    num_lot text,
    operateur character varying(8),
    dat...
pg_restore: [archiver (db)] Error from TOC entry 2083; 1259 12857670 TABLE tpcours postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "tpcours" already exists
    Command was: CREATE TABLE tpcours (
    bt_cle character varying(16),
    bt_labo integer,
    bt_fic integer,
    bt_rec integer,
    bt...
pg_restore: [archiver (db)] Error from TOC entry 2084; 1259 12857672 TABLE trans_hprim_dem postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "trans_hprim_dem" already exists
    Command was: CREATE TABLE trans_hprim_dem (
    nomlabo character varying(6),
    nolis integer,
    sficdos integer,
    noana integer,
...
pg_restore: [archiver (db)] Error from TOC entry 2116; 1259 27400739 SEQUENCE type_archi_id_seq postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "type_archi_id_seq" already exists
    Command was: CREATE SEQUENCE type_archi_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

pg_restore: [archiver (db)] Error from TOC entry 2085; 1259 12857674 TABLE utilisateur postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "utilisateur" already exists
    Command was: CREATE TABLE utilisateur (
    noutilisateur integer DEFAULT nextval('seq_noutilisateur'::regclass) NOT NULL,
    ucode char...
pg_restore: [archiver (db)] Error from TOC entry 2086; 1259 12857677 TABLE utilisateur_info postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "utilisateur_info" already exists
    Command was: CREATE TABLE utilisateur_info (
    noutilisateur integer,
    groupe integer
);

pg_restore: [archiver (db)] Error from TOC entry 2087; 1259 12857679 TABLE valid postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "valid" already exists
    Command was: CREATE TABLE "valid" (
    bt_cle character varying(16),
    bt_labo integer,
    bt_fic integer,
    bt_rec integer,
    sf...
pg_restore: [archiver (db)] Error from TOC entry 2088; 1259 12857681 TABLE vnormz postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "vnormz" already exists
    Command was: CREATE TABLE vnormz (
    enoana integer,
    version integer DEFAULT nextval(('seqv_vnormz'::text)::regclass),
    zip_vnor...
pg_restore: [archiver (db)] Error from TOC entry 3435; 0 36737751 TABLE DATA actioncorrectnc postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "actioncorrectnc" does not exist
    Command was: COPY actioncorrectnc (noac, datecreatac, libetatac, respac, delaimoac, causepac, causeeetsac, solmoac, verifmoac, verifesac)...
pg_restore: [archiver (db)] Error from TOC entry 3120; 0 12856846 TABLE DATA admission postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "admission" does not exist
    Command was: COPY admission (reference, ipp, sexe, nom, patnom, patprenom, njf, assure, assnom, assprenom, assdnaiss, adr1, adr2, adr3, t...
pg_restore: [archiver (db)] Error from TOC entry 3121; 0 12856848 TABLE DATA adr postgres
pg_restore: [archiver (db)] COPY failed for table "adr": ERROR:  duplicate key value violates unique constraint "adr_pkey"
DETAIL:  Key (noadr)=(105) already exists.
CONTEXT:  COPY adr, line 115: ""
pg_restore: [archiver (db)] Error from TOC entry 3124; 0 12856858 TABLE DATA analyse postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "analyse"
LINE 1: COPY analyse (noana, simpl, stext, blfss, transmis, abrev, n...
             ^
    Command was: COPY analyse (noana, simpl, stext, blfss, transmis, abrev, nchap, nno, lettr, stat, coeff, coeff2, cle, cfact, quest, cotmin...
pg_restore: [archiver (db)] Error from TOC entry 3126; 0 12856866 TABLE DATA appli_regle postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "appli_regle" does not exist
    Command was: COPY appli_regle (rec_appli_regle, datdeb, datfin, noana, no_regle_exec) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 3127; 0 12856869 TABLE DATA applifac postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "applifac" does not exist
    Command was: COPY applifac (no_applifac, no_analyse, datdeb, datfin, lettre, nomenc, coefficiant, code_fact) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 3440; 0 38411096 TABLE DATA article postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "article" does not exist
    Command was: COPY article (no_article, "type", code, libelle, deb_appli, cotation) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 3439; 0 38410834 TABLE DATA article_installe postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "article_installe" does not exist
    Command was: COPY article_installe ("type", code, libelle, deb_appli, cotation) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 3128; 0 12856879 TABLE DATA assosprelprint postgres
pg_restore: [archiver (db)] COPY failed for table "assosprelprint": ERROR:  duplicate key value violates unique constraint "assosprelprint_pkey"
DETAIL:  Key (idassosprelprint)=(4) already exists.
CONTEXT:  COPY assosprelprint, line 727: ""
pg_restore: [archiver (db)] Error from TOC entry 3129; 0 12856882 TABLE DATA assure postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "assure" does not exist
    Command was: COPY assure (sficdos, anom, nom, prenom, pseudo_nom, pseudo_prenom, aad1, aad2, aad3, anjfille, adnais) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 3132; 0 12856888 TABLE DATA calcul postgres
pg_restore: [archiver (db)] COPY failed for table "calcul": ERROR:  duplicate key value violates unique constraint "calcul_pkey"
DETAIL:  Key (nocalcul)=(1) already exists.
CONTEXT:  COPY calcul, line 259: ""
pg_restore: [archiver (db)] Error from TOC entry 3139; 0 12856906 TABLE DATA cetelic postgres
pg_restore: [archiver (db)] COPY failed for table "cetelic": ERROR:  duplicate key value violates unique constraint "cetelic_pkey"
DETAIL:  Key (no_cetelic)=(1) already exists.
CONTEXT:  COPY cetelic, line 2: ""
pg_restore: [archiver (db)] Error from TOC entry 3403; 0 27401688 TABLE DATA cfgfact postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "cfgfact" does not exist
    Command was: COPY cfgfact (no_cfgfact, debut_facturation, fin_facturation, labo_facture, labogroupe_facture, prochain_no_facture, no_fact...
pg_restore: [archiver (db)] Error from TOC entry 3140; 0 12856909 TABLE DATA cfgtrans postgres
pg_restore: [archiver (db)] COPY failed for table "cfgtrans": ERROR:  duplicate key value violates unique constraint "cfgtrans_pkey"
DETAIL:  Key (rec_cfgtrans)=(1) already exists.
CONTEXT:  COPY cfgtrans, line 8: ""
pg_restore: [archiver (db)] Error from TOC entry 3383; 0 27400539 TABLE DATA cfonb postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "cfonb" does not exist
    Command was: COPY cfonb (nocfonb, mtt, mon, dat, typeop, libelle, refcode, vuauto, nocompte, norappro) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 3438; 0 36737771 TABLE DATA chgetatnc postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "chgetatnc" does not exist
    Command was: COPY chgetatnc (nonc, libetatnc, datechgnc, heurenc, code_user) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 3142; 0 12856921 TABLE DATA cod_affine postgres
pg_restore: [archiver (db)] COPY failed for table "cod_affine": ERROR:  duplicate key value violates unique constraint "cod_affine_pkey"
DETAIL:  Key (no_cod_affine)=(1) already exists.
CONTEXT:  COPY cod_affine, line 64: ""
pg_restore: [archiver (db)] Error from TOC entry 3143; 0 12856924 TABLE DATA codefact postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "codefact" does not exist
    Command was: COPY codefact (codefact_no, numero_codefact, regle_codefact_norm, regle_codefact_spec, regle_analyses_spec, date_appli_spec)...
pg_restore: [archiver (db)] Error from TOC entry 3151; 0 12856950 TABLE DATA correspondant postgres
pg_restore: [archiver (db)] COPY failed for table "correspondant": ERROR:  duplicate key value violates unique constraint "correspondant_pkey"
DETAIL:  Key (nocor)=(1) already exists.
CONTEXT:  COPY correspondant, line 30: ""
$

Marc Cousin

Membre

Re : migration de base postgres 8x vers 9.2.3

Là c'est que vous essayez de restaurer sur une base déjà existante.

Deux solutions:
- vous détruisez puis recréez la base avant de restaurer
- vous rajoutez l'option -c (pour détruire les objets avant de les recréer) à pg_restore

---

Marc.

pvincent

Membre

Re : migration de base postgres 8x vers 9.2.3

Bonjour,

Il y a t-il une différence dans la contrib/userlocks version 8 et version 9.2.3 ?
J'ai compilé et installé Postgres 9.2.3 sur un environnement RedHat 64bits j'ai installé aussi les contrib dont j'ai besoin dont userlocks et là
j'ai toujours mes fichiers lockés quand je veux le consulter. Voici ce que je trace dans mon fichier erreurs. Si vous avez une idée, merci d'avance.

11/04/13 15:58:43 proserv/5529 6/PAV1 pg_open_res, status 7: select user_write_lock_oid(oid) from dosz where sficdos=708090184
11/04/13 15:58:43 proserv/5529 6/PAV1
11/04/13 15:58:43 proserv/5529 6/PAV1 ERROR:  unrecognized lock method: 0

11/04/13 15:58:43 proserv/5529 6/PAV1 Pb lock: requete echou{ (select user_write_lock_oid(oid) from dosz where sficdos=708090184)
11/04/13 15:58:51 gw2/5594 3/pts/1 ec_getc: read retourne 0, exit(1)
11/04/13 15:58:51 proserv/5595 5/pts/1 Erreur lecture pipe: nl 0