problème de mémoire avec psql

Bonjour la liste,

Je vous soumets un problème que je n'ai pas résolu depuis longtemps,
mais que j'arrivais à contourner. J'aimerais avoir vos points de vue et
vos idées pour de nouvelles approches.

Le problème est simple, j'ai une table qui contient entre 30 et 50
millions de lignes. Je voudrais "simplement" écrire dans un fichier de
sortie csv le contenu de la table. Le fichier attendu est de l'ordre de
10Go. J'ai 25 tables similaires à copier dans des fichiers csv, pour
huit d'entre elles, la requête échoue.

Avec psql, la redirection de la sortie standard ou l'option de fichier
de sortie ne fonctionnent pas :

echo "SELECT * FROM temp_jrc_order_201206 ; " | psql -h noirdesir -A -U
postgres -F \; -d mabase > order_201206.csv

echo "SELECT * FROM temp_jrc_order_201206 ; " | psql -h noirdesir -A -U
postgres -F \; -d mabase -o order_201206.csv

Voici ce que contient /var/log/syslog du client :
Out of memory: Kill process 22870 (psql) score 861 or sacrifice child

Voici ce que contient /var/log/postgresql/postgresql-9.3-main.log serveur :
postgres 192.168.47.175 mabase 2015-01-27 14:18:57 CET 54c78f6f.344d
LOG: n'a pas pu envoyer les données au client : Connexion
ré-initialisée par le correspondant
postgres 192.168.47.175 mabase 2015-01-27 14:18:57 CET 54c78f6f.344d
INSTRUCTION : SELECT * FROM temp_jrc_order_201103 ;
postgres 192.168.47.175 mabase 2015-01-27 14:18:57 CET 54c78f6f.344d
FATAL: connexion au client perdue

Dans un premier temps, j'exécutais les requêtes sur le serveur, pour le
soulager, je les lance d'une autre machine.
Le serveur a 24Go de RAM, le client 10Go (avec shmmax à 5Go).
Ce sont des machines virtuelles, je pourrais leur ajouter de la mémoire,
mais cela ne résoudra pas éternellement mon problème.

Avez-vous des idées ? Y aurait-il une option masquée de psql pour "se
vider temporairement sur le disque" ? Y a t-il d'autres approches ?

Merci pour vos retours, ils seront les bienvenus.

Bien cordialement
François Bouhet
--
Administrateur des systèmes d'information
téléphone: +33 (0)4 76 63 53 89
Adresse postale:
CNRS / EUROFIDAI UPS 3390
150, rue de la Chimie BP 47
38040 GRENOBLE Cedex 9 France

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)

François Bouhet <francois(dot)bouhet(at)eurofidai(dot)org> writes:
> Le problème est simple, j'ai une table qui contient entre 30 et 50 millions
> de lignes. Je voudrais "simplement" écrire dans un fichier de sortie csv le
> contenu de la table. Le fichier attendu est de l'ordre de 10Go. J'ai 25
> tables similaires à copier dans des fichiers csv, pour huit d'entre elles,
> la requête échoue.

Il faut utiliser le protocole de streaming COPY de PostgreSQL pour faire
cela.

Essayer :

psql -h noirdesir -U postgres -d mabase \
-c 'COPY temp_jrc_order_201206 TO STDOUT WITH CSV DELIMITER ';'

--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)

François Bouhet wrote:

> Avec psql, la redirection de la sortie standard ou l'option de fichier
> de sortie ne fonctionnent pas :
>
> echo "SELECT * FROM temp_jrc_order_201206 ; " | psql -h noirdesir -A -U
> postgres -F \; -d mabase > order_201206.csv
>
> echo "SELECT * FROM temp_jrc_order_201206 ; " | psql -h noirdesir -A -U
> postgres -F \; -d mabase -o order_201206.csv

Il y a au moins 3 solutions, de la plus utilisée à la moins utilisée:

1) utiliser \copy au format CSV. C'est spécialement prévu pour ce genre
d'usage

2) utiliser la variable interne FETCH_COUNT de psql avec un nombre de lignes
raisonnable. Voilà ce que dit la doc:

http://docs.postgresqlfr.org/9.4/app-psql.html

FETCH_COUNT

Si cette variable est un entier positif, les résultats de la requête
SELECT sont récupérés et affichés en groupe de ce nombre de lignes, plutôt
que par le comportement par défaut (récupération de l'ensemble complet des
résultats avant l'affichage). Du coup, seule une petite quantité de mémoire
est utilisée, quelle que soit la taille de l'ensemble des résultats

3) pendant qu'on y est, dans les cas plus compliqués on peut utiliser un
curseur au niveau SQL avec DECLARE puis FETCH successifs, et CLOSE. Avec
cette technique on peut même combiner les résulats de plusieurs requêtes dans
le même flux et la mémoire allouée par psql n'est toujours celle que d'un
seul FETCH.

Cordialement,
--
Daniel

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)