Forums PostgreSQL.fr

lepinard

Membre

Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Bonjour à tous,

Je cherche un moyen de gérer des documents externes à la base (images, pdf...) stockés dans un répertoire du serveur, directement par des fonctions stockées sur le serveur.

Imaginons une table 'documents' contenant des chemins d'accès aux fichiers (stockés sur le serveur C:\documents\image.jpg).
Un poste client sur le réseau pourrait-il récupérer via une requête un document stocké sur le serveur ?
Ce même poste pourrait-il créer un document dans le répertoire du serveur ?

Ceci pour éviter de stocker les images dans la base SQL en conservant un fonctionnement en réseau sans partage de disque ou autre réglage externes.

Merci pour vos contributions.

gleu

Administrateur

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

PostgreSQL ne propose pas de fonctions de ce type. Cependant, il est tout à fait possible de créer des fonctions utilisateurs pour le faire. Évidemment, cela amène toutes sortes de problèmes de sécurité mais, techniquement, c'est possible.

---

Guillaume.

lepinard

Membre

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Merci Guillaume,
Mon problème est de savoir quelles commandes utiliser dans les fonctions, et je me demandais si, éventuellement, quelqu'un n'avait pas déjà écrit ce genre de fonction.
Pour les problèmes de sécurité, je suis d'accord mais j'avais prévu d'y réfléchir dans un second temps, en fonction de la faisabilité.

gleu

Administrateur

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

La sécurité doit être pensée dans un premier temps, sinon elle ne sert pas à grand-chose.

Je ne connais pas d'extension déjà écrite pour gérer cela.

---

Guillaume.

rjuju

Administrateur

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Pour la faisabilité, un langage "untrusted" comme plpythonu ou plperlu permet de créer facilement un fichier dans l'arborescence du serveur. Bien évidemment, ça permet aussi d'effacer n'importe quel fichier accessible avec l'utilisateur système postgres, comme par exemple le contenu entier de la base de données.

---

Julien.
https://rjuju.github.io/

abenhamdine

Membre

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Bonjour,

Par curiosité, pourquoi cette volonté de ne pas stocker les documents directement dans la base ?

Si l'on utilise une base de données, c'est pour y stocker des données, non ?

Cdlt, Arnaud.

Dernière modification par abenhamdine (11/12/2014 23:34:53)

gleu

Administrateur

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

C'est surtout pour pouvoir les rechercher. Autant chercher des informations sur les métadonnées d'un document est possible, autant chercher des informations dans le document lui-même est rare (sauf pour des fichiers textes... pensez à des images, que peut-on chercher là-dedans ? cette utilisation, quoique possible, est assez rare).

---

Guillaume.

lepinard

Membre

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Bonjour et merci de l'intérêt porté à ce sujet.

L'idée de stocker les images hors de la base est liée à la lecture d'autres discutions dans lesquelles il est déconseillé de stocker un grand nombre d'image dans une base SQL pour des raison de fiabilité.

Je vais néanmoins continuer à le faire, les autres possibilités étant assez complexes à priori.

Merci à tous.

gleu

Administrateur

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Je ne vois pas en quoi la fiabilité à quoi que ce soit à faire dans cette discussion. Quoi que vous stockiez dans une base PostgreSQL, la fiabilité n'est pas remise en cause.

---

Guillaume.

abenhamdine

Membre

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Bonjour et merci de l'intérêt porté à ce sujet.

L'idée de stocker les images hors de la base est liée à la lecture d'autres discutions dans lesquelles il est déconseillé de stocker un grand nombre d'image dans une base SQL pour des raison de fiabilité.

Je vais néanmoins continuer à le faire, les autres possibilités étant assez complexes à priori.

Merci à tous.

Si j'ai fait cette remarque, c'est que j'ai effectivement lu à plusieurs reprises des posts déconseillant de stocker dans la base (et ce concernant différents SGBD), mais j'y ai rarement vu associées des argumentations techniques pertinentes.

De mémoire, les principaux arguments avancés étaient :
- "ne pas alourdir la base" (qu'est ce que cela veut dire ?)
- "la base devient trop volumineuse et les sauvegardes aussi" (ben de toutes façons ça occupera de l'espace de stockage. On ne souhaite pas les sauvegarder ? hé bien donc les exclure dans pg_dump)

Arnaud.

Dernière modification par abenhamdine (13/12/2014 20:31:49)

rjuju

Administrateur

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Tout simplement, postgres (comme la plupart des autres moteurs de données) ne vont pas bien gérer le stockage d'objets volumineux en base, car c'est n'est pas prévu pour. Vous pouvez cependant le faire, vous n'aurez aucun problème de fiabilité, juste portentiellement des problèmes de performances différents.

Au passage, le standard SQL/MED (et plus particulièrement la partie datalink, voir https://wiki.postgresql.org/wiki/DATALINK) permet de s'affranchir du problème en utilisant le stockage disque standard pour cela, avec le même principe que pour la base(ACID), mais très peu de moteurs l'implémentent (ce n'est pas le cas de postgres).

---

Julien.
https://rjuju.github.io/

abenhamdine

Membre

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Tout simplement, postgres (comme la plupart des autres moteurs de données) ne vont pas bien gérer le stockage d'objets volumineux en base, car c'est n'est pas prévu pour. Vous pouvez cependant le faire, vous n'aurez aucun problème de fiabilité, juste portentiellement des problèmes de performances différents.

Au passage, le standard SQL/MED (et plus particulièrement la partie datalink, voir https://wiki.postgresql.org/wiki/DATALINK) permet de s'affranchir du problème en utilisant le stockage disque standard pour cela, avec le même principe que pour la base(ACID), mais très peu de moteurs l'implémentent (ce n'est pas le cas de postgres).

Merci de ces informations.

NB : il y une parenthèse indésirable en fin de votre lien, le bon lien est https://wiki.postgresql.org/wiki/DATALINK

Je précise que je n'affirme pas que cela ne pose pas de problèmes de stocker des fichiers volumineux en base, mais uniquement que je n'ai pas encore lu d'argumentaire convainquant étayant ce point.

Vous dites "potentiellement des problèmes de performance différents", mais pouvez vous préciser ? (ce n'est pas une critique, simplement avez vous des éléments factuels ou un lien mettant en évidence ces problèmes ?).

Concernant les fonctionnalités DATALINK, je n'arrive pas à trouver la partie correspondante dans la documentation officielle Posgtres. Est ce implémenté ?

Arnaud.

rjuju

Administrateur

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

J'ai corrigé le problème du lien, c'est un bug de fluxbb qui parse mal les URL.

Je ne connais personnellement pas de lien concernant les problèmes de performances et le stockage de fichiers volumineux. En vrac, vous pourrez avoir des soucis de shared_buffers, de work_mem, de compression par postgres de fichier déjà compressés, de fragmentation excessive etc etc.

Pour finir, comme je l'ai dit précédemment postgres ne gère pas les datalink. Il implémente par contre une bonne partie de la partie "foreign table" au fur et à mesure des nouvelles versions.

---

Julien.
https://rjuju.github.io/

abenhamdine

Membre

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Pour finir, comme je l'ai dit précédemment postgres ne gère pas les datalink.

Ah ok, j'avais mal lu.

Merci encore pour ces précisions.

Arnaud.

SQLpro

Membre

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Tout simplement, postgres (comme la plupart des autres moteurs de données) ne vont pas bien gérer le stockage d'objets volumineux en base, car c'est n'est pas prévu pour. Vous pouvez cependant le faire, vous n'aurez aucun problème de fiabilité, juste portentiellement des problèmes de performances différents.

Au passage, le standard SQL/MED (et plus particulièrement la partie datalink, voir https://wiki.postgresql.org/wiki/DATALINK) permet de s'affranchir du problème en utilisant le stockage disque standard pour cela, avec le même principe que pour la base(ACID), mais très peu de moteurs l'implémentent (ce n'est pas le cas de postgres).

Désolé de vous contredire, mais la plupart des SGBD Relationnels comme Oracle ou SQL Server proposent une méthode identique ou similaire pour ce faire. Oracle avec BFile et SQL Server avec FILESTREAM (depuis le version 2005, soit 10 ans)...

A +

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *

gilles

Membre

Re : Gérer par le serveur PostgreSQL des fichiers stockés sur disque

Bonjour,

Il est exact qu'Oracle offre ces fonctionnalités avec les BFILE, avec PostgreSQL on avait l'habitude de gérer cela avec les LO (Larges Object) qui offrent toutes les fonctionnalités permettant d'implémenter ce que vous voulez faire. Comme sous Oracle, leur manipulation nécessite beaucoup de code. Le jour où les DATALINK de la norme SQL/MED seront implémentés il y a des chances qu'on ai quelque chose de plus simple mais en attendant il n'y a que ça pour du stockage externe géré en base.

Reste que l'idée ici n'est pas d'orienter vers des solutions propriétaires mais d'essayer de proposer des solutions. Alors certes cela demande un peu de travail mais au moins on avance. En voyant votre demande, cela m'a rappelé le travail qu'un contributeur d'Ora2Pg m'avait soumis à commentaire il y a quelque temps, Dominique Legendre pour ne pas le citer. C'est donc justement ce genre de travail que je vous invite à tester pour implémenter votre solution.

Ce code est sous forme d'une extension PostgreSQL intitulée external_file (Allow access to "external files" from PostgreSQL server file systems). J'ai eu l'autorisation de publier ce code sous mon dépôt github, vous pouvez donc le télécharger ici : https://github.com/darold/external_file/ .

N'hésitez pas à faire des remarque et à remonter les dysfonctionnements éventuels, toute contribution est aussi la bienvenue. Le fonctionnement est assez simple et il y a des exemples dans le README. L'idée c'est de créer des "DIRECTORY" dans lequel seront stockées les données passées sous forme de bytea et sous le nom de fichier donné en paramètre. Pour reprendre les exemples :

Création d'un DIRECTORY:

    INSERT INTO directories(directory_name,directory_path) VALUES ('temporary','/tmp/');

ici on créé un emplacement de stockage nommé temporary avec comme emplacement physique /tmp/  (notez qu'il faut le / en fin de path).

Puis on donne des droits d'accès aux différent utilisateurs dans ce directory (ici lecture/écriture à user1) :

    INSERT INTO directory_roles(directory_name,directory_role,directory_read,directory_write) VALUES ('temporary', 'user1', true, true);

Cet utilisateur peut maintenant créer un fichier dans ce DIRECTORY :

    SELECT writeEfile('\x48656c6c6f2c0a0a596f75206172652072656164696e67206120746578742066696c652e0a0a526567617264732c0a', ('temporary', 'blahblah.txt'));

On peut vérifier que le fichier est bien créé sur disque :

 
    $ ls -la /tmp/blahblah.txt
    -rw-r--r-- 1 postgres postgres 47 janv. 22 19:16 /tmp/blahblah.txt
    $ cat /tmp/blahblah.txt
Hello,

You are reading a text file.

Création d'une table qui va utiliser ces fichiers externes :

    CREATE TABLE efile_test ( id smallint primary key, the_file efile);

Ajout du fichier que l'on vient d'enregistrer :

    INSERT INTO efile_test VALUES (1,('temporary','blahblah.txt'));

Pour lire ce fichier externe depuis la table :

    file=# SELECT id, readefile(the_file) FROM efile_test;
     id |                                            readefile
    ----+--------------------------------------------------------------------------------------------------
      1 | \x48656c6c6f2c0a0a596f75206172652072656164696e67206120746578742066696c652e0a0a526567617264732c0a
 

Voilà, vous avez à peu près les BFILE d'Oracle dans PostgreSQL. Reportez vous au README pour plus d'explications.

Ne pensez pas qu'avez des solutions propriétaires ce soit plus simple, voici comment on implémente la même fonction readEfile() sous Oracle :

CREATE OR REPLACE DIRECTORY DOCUMENT_DIR AS '/tmp';

CREATE OR REPLACE FUNCTION readEfile (src_file BFILE := bfilename('DOCUMENT_DIR', 'image.gif') )
 RETURN BLOB IS
    dst_file BLOB;
    lgh_file BINARY_INTEGER;
BEGIN
 
  -- open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

  -- determine length
  lgh_file := dbms_lob.getlength(src_file);

  -- read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

  -- close file
  dbms_lob.fileclose(src_file);

  RETURN dst_file;

EXCEPTION
  [...]
END readEfile;

Voilà, j'espère que cela aura aidé.

--
Gilles Darold
http://www.dalibo.com

Dernière modification par gilles (23/01/2015 12:37:22)