PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 08/03/2023 17:00:23

leandre
Membre

Migration pg 9.6 vers pg13

Bonjour,

Nous engageons des tests de migration de Pg 9.6 vers Pg 13.
Dans un premier temps, nous devons identifier toutes les tables dont la clause WITH OID est true afin de générer un attribut physique dans ces tables nommé "oid" contenant la valeur de l'oid de la clause pour ensutie associer ce nouvel attribut à une séquence. (Pour des raisons de transparence avec nos outils SIG, nous devons appeler ce nouvel attribut"oid").

Nous avons donc utilisé la requête suivante, censée lister toutes les tables ayant cette clause en TRUE :

SELECT DISTINCT
    pt.schemaname,
    pt.tablename
FROM
    pg_catalog.pg_class pc
    JOIN pg_catalog.pg_tables pt on pc.relname=pt.tablename
WHERE
    pc.relhasoids
    AND pt.schemaname != 'pg_catalog'
ORDER BY schemaname;

Ensuite, j'applique la création d'une séquence, je crée un attribut new_oid sur chacune de ces tables, que je lie à la séquence, je passe l'option WITH OID à False et je renomme mon nouvel attribut en "oid".

create or replace function alter_oids_table(boolean) returns integer as $$
declare
    v_count integer;
    v_table record;
    v_preserve_oids boolean;
    oid_max_value bigint;
begin
    v_count := 0;
    v_preserve_oids := $1;

    FOR v_table IN
        select
            pt.schemaname ,
            pt.tablename
        from
            pg_catalog.pg_class pc
            join pg_catalog.pg_tables pt on pc.relname=pt.tablename
        where
            pc.relhasoids
            and pt.schemaname != 'pg_catalog'
    LOOP
        IF v_preserve_oids THEN
            EXECUTE 'SELECT MAX(oid) FROM ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) INTO oid_max_value;
            if oid_max_value is null then
                oid_max_value := 0;
            end if;
            oid_max_value := oid_max_value + 1;
            EXECUTE 'CREATE SEQUENCE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename || '_oid_seq') || ' START ' || oid_max_value || ' CYCLE';
            EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' ADD COLUMN new_oid bigint';
            EXECUTE 'UPDATE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' SET new_oid = oid';
            EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' SET WITHOUT OIDS';
            EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' ALTER COLUMN new_oid SET DEFAULT nextval(''' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename || '_oid_seq') || ''')';
            EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' RENAME COLUMN new_oid TO oid';
        ELSE
            EXECUTE 'ALTER TABLE ' || v_table.schemaname || '.' || v_table.tablename || ' SET WITHOUT OIDS';
            EXECUTE 'ALTER TABLE ' || quote_ident(v_table.schemaname) || '.' || quote_ident(v_table.tablename) || ' ADD COLUMN oid bigserial';
        END IF;
        v_count := v_count + 1;
    END LOOP;
   
    RETURN v_count;
end
$$ language 'plpgsql';

-- select alter_oids_table(true);

Cela ne fonctionne toutefois pas.
Je récupère, dans la première partie de requête, dans le catalogue système pg_table des tables identifiées comme ayant l'option WITH OID à TRUE, alors que ce n'est pas le cas.
Je peux vérifier via un SELECT oid FROM ma_table; et une erreur m'indique bien que je n'ai pas d'oid...

Les informations dans pg_tables semblent donc obsolètes.
Historique de migration qui aurait pu intervenir sur ces lignes non correctes dans pg_tables, restauration de version différente de pg, que sais-je ...

J'ai tenté de checker dans pg_attributes et je vois aussi des attname correspondant à "oid" sur des tables qui n'en ont pas...
Avez-vous déjà été confronté à ce genre de situation ?

Existe-t-il une manière de regénérer/mettre à jour les tables du catalogue system ?

Bien Cordialement,
Léandre BERON

Hors ligne

#2 09/03/2023 02:11:01

rjuju
Administrateur

Re : Migration pg 9.6 vers pg13

Pouvez-vous fournir un exemple concret (enregistrement dans pg_class + pg_attribute et un aperçu de SELECT oid, ... FROM latable LIMIT 1) de tables pour lesquels vous avez ce problème?

Hors ligne

#3 09/03/2023 13:22:12

leandre
Membre

Re : Migration pg 9.6 vers pg13

Bonjour,

Voici deux tables exemple :

Ici, le SQL obtenu via PgAdmin pour la création de la table administratif_arrondissement, dont l'option WITH OIDS est à True :

CREATE TABLE IF NOT EXISTS bdcarto.administratif_arrondissement
(
    "insee_région" character(2) COLLATE pg_catalog."default",
    "insee_département" character(2) COLLATE pg_catalog."default",
    id_bdcarto integer,
    insee_arrondissement character(1) COLLATE pg_catalog."default",
    geom geometry,
    CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 3950),
    CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL),
    CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2)
)
WITH (
    OIDS = TRUE
)
TABLESPACE pg_default;

Ici, une autre table, dont l'option WITH OIDS est à False :

CREATE TABLE IF NOT EXISTS m_education_jeunesse.centre_loisir
(
    type_eq_cl character varying(45) COLLATE pg_catalog."default",
    nom_cl character varying(50) COLLATE pg_catalog."default",
    num_voie double precision,
    prefixe character varying(20) COLLATE pg_catalog."default",
    denom character varying(30) COLLATE pg_catalog."default",
    commune character varying(20) COLLATE pg_catalog."default",
    code_insee character varying(5) COLLATE pg_catalog."default",
    code_cl character varying(5) COLLATE pg_catalog."default",
    compteur double precision,
    comite character varying(60) COLLATE pg_catalog."default",
    code_voie character varying(5) COLLATE pg_catalog."default",
    communaute character varying(20) COLLATE pg_catalog."default",
    secteur character varying(10) COLLATE pg_catalog."default",
    geom geometry,
    id_voie integer,
    id_adresse bigint,
    fid integer NOT NULL DEFAULT nextval('m_education_jeunesse.centre_loisir_fid_seq'::regclass),
    CONSTRAINT centre_loisir_pkey PRIMARY KEY (fid),
    CONSTRAINT id_adresse_fkey FOREIGN KEY (id_adresse)
        REFERENCES r_voie_adresse.geo_point_adresse (id_adresse) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT id_voie_fkey FOREIGN KEY (id_voie)
        REFERENCES r_voie_adresse.an_voie (id_voie) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 3950),
    CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOINT'::text OR geom IS NULL),
    CONSTRAINT enforce_dims_geom CHECK (st_ndims(geom) = 2)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Dans pg_class, via la requête indiquée précédement je vois bien ces deux lignes :

schemaname | tablename
"bdcarto"    | "administratif_arrondissement"
"m_education_jeunesse" |    "centre_loisir"

En passant par pg_attribute, j'obtiens :

SELECT (pa.attrelid::regclass)::varchar as schema_table, TRUE AS oid_true
        FROM   pg_attribute pa
        WHERE  attname  = 'oid'
            AND    NOT attisdropped  -- no dropped (dead) columns
            AND (pa.attrelid::regclass)::varchar NOT LIKE 'pg_%'
            AND ((pa.attrelid::regclass)::varchar LIKE '%bdcarto.administratif_arrondissement%'
            OR (pa.attrelid::regclass)::varchar LIKE '%m_education_jeunesse.centre_loisir%')
            AND    attnum < 0     -- system columns <0 or ordinary column >=1
        ORDER BY attrelid::regclass

Résultat :

schema_table | oid_true |
"bdcarto.administratif_arrondissement" |    true

Je n'obtiens pas la même chose en passant par pg_class ou par pg_attribute.
--> Via pg_attribute cela semble clean, mais pourquoi ne verrais-je pas la même chose en passant par pg_class ? Une erreur dans la requête ? Mauvaise jointure ?

Cordialement,
Léandre BERON

Hors ligne

#4 09/03/2023 14:38:37

dverite
Membre

Re : Migration pg 9.6 vers pg13

La première requête n'est pas correcte car elle joint  avec la seule condition pc.relname=pt.tablename, en ignorant le schéma.
Plutôt utiliser cette version:

select n.nspname, c.relname from 
pg_class c join pg_namespace n on c.relnamespace=n.oid 
and nspname!='pg_catalog' 
and relhasoids;

Hors ligne

#5 10/03/2023 17:27:46

leandre
Membre

Re : Migration pg 9.6 vers pg13

Bonjour,

Je comprends mieux en effet !
Je tombe sur le même nombre de résultat qu'en passant pas pg_attribute.

Je vais poursuivre mes tests de mise en compatibilité.

Un grand merci pour la réactivité de réponse !!

Léandre BERON

Hors ligne

#6 19/03/2023 17:56:22

leandre
Membre

Re : Migration pg 9.6 vers pg13

Bonjour,

Cette requête a bien fonctionné et je peux maintenant engager des tests de migration avec pg_upgrade.
Après avoir installé pg13 sur le serveur, et avoir stoppé les clusters main et maindev créés sur la nouvelle instance pg13, je lance la commande suivante :

/usr/lib/postgresql/13/bin/pg_upgrade -b /usr/lib/  postgresql/9.6/bin -B /usr/lib/postgresql/13/bin -d /var/lib/postgresql/9.6/main -D /var/lib/postgresql/13/main -c

La vérification commence mais je tombe sur une erreur que je ne comprends pas.

c1n3.png

hk2j.png

N'étant pas un habitué de linux, qu'est-ce que cela signifie ? Un problème de répertoire introuvable à priori, mais je ne comprends pas.
En vous remerciant pour votre aide.

Cordialement,
Léandre BERON

Hors ligne

#7 20/03/2023 07:55:59

rjuju
Administrateur

Re : Migration pg 9.6 vers pg13

Que dit le fichier pg_upgrade_server.log ?


L'erreur indique simplement que pg_upgrade n'a pas pu se connecter sur la socket unix définie dans les arguments de pg_upgrade.

Hors ligne

#8 20/03/2023 16:33:51

leandre
Membre

Re : Migration pg 9.6 vers pg13

Bonjour,

Merci pour votre retour.


En checkant le .log, j'ai pu corriger le problème : mauvais répertoire indiqué pour -d et -D.
La vérification a montré qu'il manquait Postgis 2.5 sur la version 13.
J'ai ajouté la version 3 de postgis (plus récente) et ça n'a pas marché.
En installant exactement la version 2.5, la vérification a fonctionné et tout est sur ok.


J'ai donc tenté une migration.
Problème, des vues (au moins 1) m'a retourné une erreur :
elle utilise l'attribut consrc de pg_constraint de la 9.6 qui n'existe plus à partir de pg12.


Est-ce que pg_upgrade de pg11 prend ce changement en considération et effectuera les changements ? Cela signifierait migrer de 9.6 vers 11, puis 11 vers 12 et enfin 12 vers 13 ?
Sinon, dois-je supprimer les vues en question avant et les recréer ensuite (post migration) ?

Cordialement,
Léandre BERON

Hors ligne

#9 20/03/2023 17:14:08

dverite
Membre

Re : Migration pg 9.6 vers pg13

leandre a écrit :

Problème, des vues (au moins 1) m'a retourné une erreur :
elle utilise l'attribut consrc de pg_constraint de la 9.6 qui n'existe plus à partir de pg12.


Est-ce que pg_upgrade de pg11 prend ce changement en considération et effectuera les changements ? Cela signifierait migrer de 9.6 vers 11, puis 11 vers 12 et enfin 12 vers 13 ?
Sinon, dois-je supprimer les vues en question avant et les recréer ensuite (post migration) ?

Non pg_upgrade ne peut pas gérer ça. La doc recommande d'utiliser la fonction  pg_get_constraintdef() pour retrouver la définition d'une contrainte au lieu de la colonne consrc qui n'existe plus à partir de la version 12.

Le faire avant ou après la migration, c'est à vous de voir ce qui est le plus pratique pour vous.

Hors ligne

#10 20/03/2023 17:41:50

leandre
Membre

Re : Migration pg 9.6 vers pg13

Bonjour,

Un grand merci pour votre aide.

Je vais regarder ça.

Cordialement,
Léandre BERON

Hors ligne

#11 23/03/2023 10:32:58

genamiga
Membre

Re : Migration pg 9.6 vers pg13

Bonjour,

Je suis curieux...

Pourquoi migrer vers PG13 et pas PG15 directement ?

Hors ligne

#12 23/03/2023 12:06:58

leandre
Membre

Re : Migration pg 9.6 vers pg13

Bonjour,

La migration a bien fonctionné je vous remercie.


genamiga a écrit :

Pourquoi migrer vers PG13 et pas PG15 directement ?

Tout simplement parce que notre logiciel applicatif SIG n'est compatible uniquement jusqu'à la 13 pour le moment.

Léandre

Hors ligne

Pied de page des forums