Discussion :

[I folima Elda]

Bonjour à tous,

Alors avant toute chose, sachez que je suis novice en Postgres. J'ai même pas 1an de formation dans les doigts ^^. Mais je tente de me documenter et de tester ce que je trouve. On progresse petit à petit

Pour remettre dans le contexte. J'ai un projet de migration d'une base oracle sous postgres. Bon et comme c'est pas aussi simple, on la modifie entre temps (le nombre de table, relation etc...) donc les outils de migration = inutiles.
La création des tables, aucun soucis. Maintenant on me demande de partitionner l'une des tables pour la rendre plus performante. Ce partitionnement se fera sur une date avec une plage de 2ans. Donc j'aurai 24 tables filles mensuellement géré.

L'idée serai de supprimer le mois le plus ancien lorsque l'on arrive au mois en cours. Par exemple, supprimer la table fille de 2015-07 quand on arrive à 2017-07 et créer cette dernière.
A la main, pas de soucis: on CREATE TABLE 2017-07 et on DROP TABLE 2015-17 (et index associé)
Cependant, bon informaticien que je suis, je suis un fainéant et je désire automatiser ça: DROP/CREATE automatique avec l'horodatage dans le nom de la table fille.

En me perdant dans les méandre du net, j'ai pu voir cette proposition. Mais je ne la comprends pas totalement et ne suis pas certain qu'elle fasse exactement ce que je désire.
source: https://blog.hbis.fr/2014/01/13/zabb...ql_partitions/

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE OR REPLACE FUNCTION trg_monthly_partition()
RETURNS trigger AS $BODY$
DECLARE    tablename text;
        partname text;
        startdate text;
        enddate text;
        query text;
 
BEGIN
tablename := TG_ARGV[0];
partname := tablename || '_' || TO_CHAR(to_TIMESTAMP(NEW.clock), 'YYYY-MM');
 
EXECUTE 'INSERT INTO ' || 'partition.' || quote_ident(partname) || ' SELECT ($1).*' USING NEW;
 
RETURN NULL;
 
EXCEPTION
WHEN undefined_table THEN
 
startdate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock)));
enddate := EXTRACT(EPOCH FROM date_trunc('month', TO_TIMESTAMP(NEW.clock) + ('1 month')::interval));
 
EXECUTE 'CREATE TABLE IF NOT EXISTS ' || 'stat.' || quote_ident(partname) || ' (CHECK ((clock >= ' || quote_literal(startdate) || ' AND clock < ' || quote_literal(enddate) || '))) INHERITS ( ' || tablename || ' )';
EXECUTE 'CREATE INDEX ' || quote_ident(partname) || '_1 on ' || 'stat.' || quote_ident(partname) || '(itemid, clock)';
 
EXECUTE 'INSERT INTO ' || 'stat.' || quote_ident(partname) || ' SELECT($1).*' USING NEW;
 
RETURN NULL;
END;
$BODY$LANGUAGE plpgsql VOLATILE
COST 100;
 
ALTER FUNCTION trg_monthly_partition()
OWNER TO postgres;

Pourriez-vous m'indiquer un script (oui je fais tout en script.sql en ce moment ^^) pouvant résoudre mon soucis?
Merci beaucoup

[SQLpro]

Hé bien vous allez grandement souffrir et le résultat risque d'être particulièrement catastrophique. En effet le partitionnement est en pratique inexploitable... Même le staff de PG reconnait que la copie est à revoir ! C'est vous dire...

Lisez l'article que j'ai écrit à ce sujet :
https://blog.developpez.com/sqlpro/p...paraison_postg

Il est évident qu'il existe des différences notables entre des outils gratuits, généralement pauvres en fonctionnalité ou ayant des fonctionnalités bâclées et/ou buguées, et des outils payant faisant l'objet de plusieurs centaines d'années de R&D...

A +

[I folima Elda]

Bonjour,
Désolé de ma réponse (très) tardive, mais en effet ce projet demande pas mal de boulot (en plus du boulot quotidien ^^). De plus c'est la 1ere fois que je travaille sur un tel projet.

Merci pour votre réponse. Votre lien m'a bien aidé sur la réalisation du partitionnement. J'en ai fait part au chapoteur du projet des soucis pouvant être renconté. Mais au vu de la quantité de données journaliers qui vont arriver (~1,5Go après retraitement), le partitionnement est privilégié. Et la parole du chapoteur est toute puissante ^^
J'ai contourné mon problème d'horodatage des tables en faisant mes instructions dans un script bash, via la commande:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

psql -c "commandes" database user

Cela fonctionne très bien en ajoute des $variables ce qui me permet de jouer avec les dates. Pour les suivants, je mets un exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
if [ $# -eq 0 ] ; then
    var=$(date +%Y%m)
    annee=$(date +%Y)
    mois=$(date +%m)
elif [[ $1 =~ [0-9]{4}( 0[1-9] || 1[0-2] ) ]] ; then
    var=$1
    annee=`echo $1 | cut -c -4`
    mois=`echo $1 | cut -c 5-`
else
    echo -e "usage : format de date AAAAMM\n"
    exit 1
fi
 
# * variable pour les dossiers de destination
# ******************************************
dossierDat=/u03/pgsql/stat/dat
dossierScript=/home/postgres/script
 
if [[ -e $dossierScript/erreur_autoPartitionTable.log ]]
then
    rm -f $dossierScript/erreur_autoPartitionTable.log
fi
 
 
# *=====================================================*
# * PARTITIONNEMENT DE LA TABLE appel_serv                *
# *=====================================================*
# * partitionnement de APPEL_SERV et index associés
# * suppression de la table n-1 et creation de la table n
# *******************************************************
psql -c "
    DROP TABLE IF EXISTS stat.part_appel_serv_$(($annee-1))$mois CASCADE;    
    DROP INDEX IF EXISTS indx_part_appel_serv_$(($annee-1))$mois_nom_serveur CASCADE;
    DROP INDEX IF EXISTS indx_part_appel_serv_$(($annee-1))$mois_adr_ip CASCADE;
    DROP INDEX IF EXISTS indx_part_appel_serv_$(($annee-1))$mois_id_service CASCADE;
    DROP INDEX IF EXISTS indx_part_appel_serv_$(($annee-1))$mois_date_req CASCADE;
    DROP INDEX IF EXISTS indx_part_appel_serv_$(($annee-1))$mois_status CASCADE;
 
    DROP TABLE IF EXISTS stat.part_appel_serv_$var CASCADE;
    CREATE TABLE stat.part_appel_serv_$var(
        CHECK ( date_req >= DATE '$annee-$mois-01'
            AND    date_req < DATE '$annee-$(($mois+1))-01' )
   &nbsp;) INHERITS (appel_serv) TABLESPACE tbs_data;
 
    DROP INDEX IF EXISTS indx_part_appel_serv_$var_nom_serveur CASCADE;
    DROP INDEX IF EXISTS indx_part_appel_serv_$var_adr_ip CASCADE;
    DROP INDEX IF EXISTS indx_part_appel_serv_$var_id_service CASCADE;
    DROP INDEX IF EXISTS indx_part_appel_serv_$var_date_req CASCADE;
    DROP INDEX IF EXISTS indx_part_appel_serv_$var_status CASCADE;
    CREATE INDEX indx_part_appel_serv_$var_nom_serveur    ON part_appel_serv_$var (nom_serveur)    TABLESPACE tbs_index;
    CREATE INDEX indx_part_appel_serv_$var_adr_ip        ON part_appel_serv_$var (adr_ip)        TABLESPACE tbs_index;
    CREATE INDEX indx_part_appel_serv_$var_id_service    ON part_appel_serv_$var (id_service)    TABLESPACE tbs_index;
    CREATE INDEX indx_part_appel_serv_$var_date_req        ON part_appel_serv_$var (date_req)        TABLESPACE tbs_index;
    CREATE INDEX indx_part_appel_serv_$var_status        ON part_appel_serv_$var (status)        TABLESPACE tbs_index;
" statserv stat 1>/dev/null 2>>$dossierScript/erreur_autoPartitionTable.log

J'ai même ajouter une fonction pour la date (via regexp) comme ça je peux soit le mettre dans un crontab en auto soit le faire à la main si nécessaire. Je rajoute que j'ai testé et ça marche plutôt bien.
Bon maintenant, mon prochain défi c'est la gestion des règles horodatées et ça c'est une autre paire de manche

Merci encore pour la réponse et cette aide. Je clos le sujet.
Cordialement