Forums PostgreSQL.fr

gom

Membre

pgdump sans index ?

Bonjour,

Question simple !

J'ai lu qu'il était possible de désactiver des index : http://stackoverflow.com/questions/6146 … n-postgres

Est-ce que du coup mon pg_dump sera sans index ?

pg_dump -c -C -O -b -o --format=c --compress=9 -f /chemin/de/monfichier.dmp mabase

Ma base (Datawarehouse) fait presque 700 Go, dont au moins 1/3 d'index. Sachant que je suis obligé de passer mon dump d'un environnement à l'autre via un disque dur externe ... évidemment sans index ce serait plus rapide !

A propos, si quelqu'un connait la requête pour calculer le nombre d'octets que représentent mes index ... je suis preneur ! Version PostgreSQL : 8.4.4 (Linux)

Gôm

Dernière modification par gom (27/11/2012 17:42:27)

gom

Membre

Re : pgdump sans index ?

Par défaut, --format=c correspond à --compress=5 ?

Combien de temps vais-je perdre avec un --compress=9 par rapport à la valeur par défaut ? Et combien de place je vais gagner entre un --compress=5 et un --compress=9 ?

Beaucoup de questions, mais malheureusement G**gle ne m'a pas aidé !

Gôm

gleu

Administrateur

Re : pgdump sans index ?

J'ai lu qu'il était possible de désactiver des index : http://stackoverflow.com/questions/6146 … n-postgres

C'est possible, oui. Pour l'instant. Il n'est pas garanti que cela fonctionne toujours. Tripoter les catalogues systèmes est souvent le meilleur moyen de faire des bêtises

Est-ce que du coup mon pg_dump sera sans index ?

Non, il sera sauvegardé.

évidemment sans index ce serait plus rapide !

Certainement pas la copie, vu que seul l'ordre de création d'index est présent dans le dump. Pas ses données. La restauration, par contre, là, oui

A propos, si quelqu'un connait la requête pour calculer le nombre d'octets que représentent mes index ... je suis preneur !

SELECT pg_size_pretty(sum(pg_total_relation_size(oid))::bigint) FROM pg_class WHERE relkind='i';

Par défaut, --format=c correspond à --compress=5 ?

Bonne question. Par défaut, ça utilise le niveau de compression par défaut de zlib. Mais je n'en sais pas plus.

Combien de temps vais-je perdre avec un --compress=9 par rapport à la valeur par défaut ?

Beaucoup ?

C'est évidemment impossible à dire comme ça. Un test que j'ai effectué avec un client montrait que passer de 1 à 2 doublait le temps de compression.

Et combien de place je vais gagner entre un --compress=5 et un --compress=9 ?

Peu ?

Là aussi, c'est évidemment impossible à dire.

Personnellement, je ne pense pas qu'il y ait un gros intérêt pour passer d'un niveau 5 à un niveau 9. J'aurais plutôt tendance à désactiver la compression et à envoyer le résultat de la sauvegarde à un outil de compression parallélisé. Ce sera beaucoup plus performant, et permettra du coup un meilleur niveau de compression.

---

Guillaume.

gom

Membre

Re : pgdump sans index ?

J'ai lu qu'il était possible de désactiver des index : http://stackoverflow.com/questions/6146 … n-postgres

C'est possible, oui. Pour l'instant. Il n'est pas garanti que cela fonctionne toujours. Tripoter les catalogues systèmes est souvent le meilleur moyen de faire des bêtises

OK, c'est noté. Merci.

Est-ce que du coup mon pg_dump sera sans index ?

Non, il sera sauvegardé.

évidemment sans index ce serait plus rapide !

Certainement pas la copie, vu que seul l'ordre de création d'index est présent dans le dump. Pas ses données. La restauration, par contre, là, oui

Génial, c'est exactement ce que je voulais ! Il ne me restera qu'à gérer correctement le pg_restore pour que les index ne soient pas créés à la restauration. Je le ferai manuellement après. Mon souhait est de restaurer uniquement les données, puis les index.

Par défaut, --format=c correspond à --compress=5 ?

Bonne question. Par défaut, ça utilise le niveau de compression par défaut de zlib. Mais je n'en sais pas plus.

OK.

Combien de temps vais-je perdre avec un --compress=9 par rapport à la valeur par défaut ?

Beaucoup ?

C'est évidemment impossible à dire comme ça. Un test que j'ai effectué avec un client montrait que passer de 1 à 2 doublait le temps de compression.

Et combien de place je vais gagner entre un --compress=5 et un --compress=9 ?

Peu ?

Là aussi, c'est évidemment impossible à dire.

Personnellement, je ne pense pas qu'il y ait un gros intérêt pour passer d'un niveau 5 à un niveau 9. J'aurais plutôt tendance à désactiver la compression et à envoyer le résultat de la sauvegarde à un outil de compression parallélisé. Ce sera beaucoup plus performant, et permettra du coup un meilleur niveau de compression.

Du coup je laisse "--format=c" (qui permet entre autre de compresser), mais j'enlève "--compress" ?

pg_dump -c -C -O -b -o --format=c -f /chemin/de/monfichier.dmp mabase

Si j'ai bien compris "--format=c" = "-Fc" or : http://www.postgresql.org/docs/8.4/stat … gdump.html, donc je suis obligé de laisser cette option si je veux pouvoir restaurer sans les index (car le dump sera ordonné, donc facile de supprimer toutes les commandes de création d'index), l'inconvénient est que je vais perdre du temps à cause de la compression par défaut. J'ai bon ?

Quel outil de compression parallélisé dois-je utiliser ? Je ne vois pas comment cela va fonctionner et comment le mettre en œuvre.

Gôm

Dernière modification par gom (28/11/2012 11:28:03)

gleu

Administrateur

Re : pgdump sans index ?

Si j'ai bien compris "--format=c" = "-Fc" or : http://www.postgresql.org/docs/8.4/stat … gdump.html, donc je suis obligé de laisser cette option si je veux pouvoir restaurer sans les index (car le dump sera ordonné, donc facile de supprimer toutes les commandes de création d'index), l'inconvénient est que je vais perdre du temps à cause de la compression par défaut. J'ai bon ?

Mettez le niveau de compression à 0 pour éviter la compression.

Pour être franc, je ne vois pas l'intérêt de toutes les options que vous indiquez. -c, -C, -o, -O peuvent être données directement à pg_restore. De toute façon, -o n'a normalement aucun intérêt (sauf si vous faites des choses pas catholiques ). -b ne sert à rien si vous n'utilisez ni -n ni -N. Bref, pour moi, votre commande devrait être :

pg_dump -F c -Z 0 -f /chemin/de/monfichier.dmp mabase

Quel outil de compression parallélisé dois-je utiliser ? Je ne vois pas comment cela va fonctionner et comment le mettre en œuvre.

pbzip2 et pigz sont les deux qui me viennent en tête.

---

Guillaume.

Marc Cousin

Membre

Re : pgdump sans index ?

Tu peux mettre --compress=0 (ce qui signifie ne pas compresser), et faire par exemple pg_dump -Fc --compress=0 ………… | pbzip2 -c > mon_dump.tar.bz2

pbzip2 est l'outil de compression parallélisé dans cet exemple.

---

Marc.

gom

Membre

Re : pgdump sans index ?

Merci messieurs !

Je vais donc faire :

pg_dump -c -C -O --format=c --compress=0 -f /chemin/de/monfichier.dmp mabase | pbzip2 -c > monfichier_dump.tar.bz2

OK ?

Dernière modification par gom (28/11/2012 13:08:04)

gom

Membre

Re : pgdump sans index ?

Sinon ... pigz ou pbzip2 => http://blog.bearstech.com/2011/10/compr … bzip2.html ?!

Mon objectif est certes de gagner de la place, mais aussi de gagner du temps ! Pour faire simple si je gagne 50% en compression, mais que je mets 50% de temps en plus (sachant que j'ai une base qui fait 660 Go dont 250 Go d'Index) alors je préfère perdre les 50% de compression.

J'espère que cet exemple vous aidera à me conseiller.

Dernière modification par gom (28/11/2012 13:15:20)

gleu

Administrateur

Re : pgdump sans index ?

Seuls des tests vous le diront. Tout dépend du nombre de cœurs sollicités, du niveau de compression... c'est impossible à dire sans tests.

---

Guillaume.

gom

Membre

Re : pgdump sans index ?

Je ne vais pas pouvoir tester sur une volumétrie équivalente. Mon environnement de tests contient très peu de données par rapport à celui où va être passé cette commande.

Mais bon, après avoir lu ça : http://nerdbynature.de/s9y/?251

Je vais au final demander à passer le pg_dump avec ces options là :

pg_dump -c -C -O --format=c --compress=0 -f /chemin/de/monfichier.dmp mabase | pigz -c > monfichier_dump.tar.pigz

Gôm

gom

Membre

Re : pgdump sans index ?

J'ai lancé exactement cette commande sur mon serveur de tests, mais du coup j'ai un fichier .dmp et un fichier .tar.gzip !

/app/mon_projet/pgsql/bin/pg_dump -p 5433 -c -C -O --format=c --compress=0 -f /home/postgres/moi/test_dump/dwh_20121128.dmp mabase | gzip -c > dwh_20121128.dmp.tar.gzip

Comment puis-je faire pour avoir uniquement le fichier .tar.gzip ?

Gôm

Dernière modification par gom (28/11/2012 17:25:36)

gom

Membre

Re : pgdump sans index ?

Comme ça !

/app/mon_projet/pgsql/bin/pg_dump -p 5433 -c -C -O --format=c --compress=0 mabase | gzip -c > dwh_20121128.dmp.tar.gzip

Merciiiii !

Gôm

Dernière modification par gom (28/11/2012 17:29:59)

gom

Membre

Re : pgdump sans index ?

Aïe ... j'ai le message d'erreur suivant.

[postgres@serveurtest test_dump]$ /app/mon_projet/pgsql/bin/pg_dump -p 5433 -c -C -O -Fc --compress=0 mabase | gzip -c > dwh_20121128.dmp.tar.gzip
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  cache lookup failed for index 84038
pg_dump: The command was: SELECT t.tableoid, t.oid, t.relname AS indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, t.relnatts AS indnkeys, i.indkey, i.indisclustered, c.contype, c.conname, c.tableoid AS contableoid, c.oid AS conoid, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, array_to_string(t.reloptions, ', ') AS options FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) LEFT JOIN pg_catalog.pg_depend d ON (d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i') LEFT JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) WHERE i.indrelid = '63797'::pg_catalog.oid ORDER BY indexname

C'est parce que j'ai enlevé l'option "-o" ?

Je viens de faire une relance avec cette option (pour voir) :

time /app/mon_projet/pgsql/bin/pg_dump -p 5433 -c -C -O -o --format=c --compress=0 mabase | gzip -c > dwh_20121128.dmp.tar.gzip

Gôm

gom

Membre

Re : pgdump sans index ?

C'est pas mieux ... mais là je sais pourquoi !

[postgres@serveurtest test_dump]$ time /app/mon_projet/pgsql/bin/pg_dump -p 5433 -c -C -O -o --format=c --compress=0 mabase | gzip -c > dwh_20121128.dmp.tar.gzip

gzip: stdout: No space left on device

real    5m19.118s
user    3m38.377s
sys     0m43.320s
[postgres@serveurtest test_dump]$ ll
total 470332
-rw-r--r-- 1 postgres postgres 481144832 nov 29 11:19 dwh_20121128.dmp.tar.gzip

Gôm

gom

Membre

Re : pgdump sans index ?

Mon fichier ne fait que 459 Ko (ou 459 Mo ?) ? Si c'est ça alors l'espace disque restant sur mon serveur est un peu inquiétant, non ?!!

Gôm

Dernière modification par gom (29/11/2012 13:03:34)

gleu

Administrateur

Re : pgdump sans index ?

Essayez sans les options -c -C -o et -O. Donnez-nous la commande exacte et le message d'erreu exact (si erreur il y a).

---

Guillaume.

gom

Membre

Re : pgdump sans index ?

La commande ne peut pas aller à son terme je n'ai pas assez d'espace disque sur mon serveur de tests. Tant pis je vais livrer la commande et le client va la tester sur son serveur de Recette.

Merci pour tout.

Gôm

gom

Membre

Re : pgdump sans index ?

Bonjour,

La commande a pu être passée chez le client sans problème particulier ... par contre la restauration a été une autre histoire ! Il m'a été impossible de déchiffrer le dump à partir du compte root. Je ne sais toujours pas pourquoi ... personne en interne n'a été capable de m'aider. J'ai donc copier le dump sur un serveur Windows où j'ai installé PGP et j'ai déchiffré sur Windows ... avant de tout renvoyer sur le serveur Linux !!

Au final la restauration a duré 6 jours ... oui oui 6 jours (sans compter le temps de déchiffrage et de décompression) ! Tout le contenu du dump s'affichait sur la sortie standard. Je ne sais pas si c'est normal.

J'ai restauré ainsi :

pg_restore mabase_infocentre_20121211.tar -U postgres -w

Ai-je fait une erreur n'y avait-il pas un moyen d'accélérer les choses ?

2ème et dernière question : comment se fait-il que je ne puisse pas accéder à la base que je viens de restaurer ? Le user qui a été restauré en même temps que la base n'est, apparemment, pas utilisable depuis PgAdmin sur mon PC sous Windows. Je ne peux utiliser que le user "postgres", le problème est que ce user n'a accès qu'à la base "postgres".

Que dois-je modifier pour que mon user "user_infocentre" soit utilisable depuis mon PC ?

Merci d'avance !

Gôm

Dernière modification par gom (18/01/2013 16:19:38)

gleu

Administrateur

Re : pgdump sans index ?

Tout le contenu du dump s'affichait sur la sortie standard. Je ne sais pas si c'est normal.

Vu la commande utilisée, oui, c'est normal. Il vous manque l'option -d pour spécifier la base de données où restaurer le dump.

Ai-je fait une erreur n'y avait-il pas un moyen d'accélérer les choses ?

Vu que la commande était erronée, difficile de se prononcer. Mais la restauration d'un dump peut nécessiter une configuration personnalisée (checkpoint, mémoire).

comment se fait-il que je ne puisse pas accéder à la base que je viens de restaurer ?

Vous n'avez rien restauré du tout (tout du moins si la commande que vous avez fourni est la bonne).

Oh et pour infos, la base doit être créé avant.

---

Guillaume.

gom

Membre

Re : pgdump sans index ?

Cette commande est bonne ?

pg_restore mabase_infocentre_20121211.tar -U postgres -w -d mabase_infocentre

J'ai retrouvé un paramétrage que vous m'aviez donné en 2010.

# - Specifique restauration -
fsync = off
checkpoint_segments = 200
checkpoint_timeout = 20min
maintenance_work_mem = 1GB

Est-il toujours valable ? Puis-je le mettre en place simplement en éditant "postgresql.conf" ?

Comment savoir quelles bases sont présentes sur mon serveur (en lignes de commandes Linux) ?

gom

Membre

Re : pgdump sans index ?

Sinon (plus simple), je peux faire ça, non ?

pg_restore mabase_infocentre_20121211.tar -C -U postgres -w

Marc Cousin

Membre

Re : pgdump sans index ?

Ces paramètres sont à chaud, donc il suffit d'éditer le postgresql.conf, puis de faire un reload de postgres (pas besoin d'un restart).

Le commande de restauration a l'air bonne. Sauf le -w qui ne sert à rien: s'il n'y a pas besoin de mot de passe, pg_restore n'en demandera de toutes façons pas.

Par contre, attention, fsync à off, c'est uniquement si l'instance est vide au départ. S'il y a d'autres bases déjà en place, il vaut mieux éviter: le moindre crash pendant la restauration, et c'est corruption assurée de l'instance.

Pour obtenir la liste des bases d'une instance, il suffit de taper «psql -l»

---

Marc.

gom

Membre

Re : pgdump sans index ?

Merci Marc !

J'ai donc lancé cette commande (après avoir édité mon "postgresql.conf" et fait un reload) :

pg_restore mabase_infocentre_20121211.tar -U postgres -C

Comment savoir si ma base a bien été créée au début de la restauration et que maintenant les données sont en train d'être restaurées ?

Gôm

Dernière modification par gom (18/01/2013 17:46:48)

gleu

Administrateur

Re : pgdump sans index ?

faire un "psql -l" comme le dit Marc ? ça permettrait de savoir si la base est là.

---

Guillaume.

gom

Membre

Re : pgdump sans index ?

Merci Guillaume.

Le problème est que j'ai eu à créer tous les users et les répertoires pour les tablespaces ... sinon le restore fonctionnait pas ... "forcément" vous allez dire, mais fallait-il encore savoir que pg_restore n'allait pas le faire. Encore que pour les tablespaces j'aurais pu m'en douter !

Bon par contre j'ai encore des erreurs, qu'en pensez-vous ?

[root@monserveur pgdata]# pg_restore mabase_infocentre_20121211.tar -U postgres -C -d postgres
pg_restore: [archiveur] n'a pas pu ouvrir le fichier en entrée « mabase_infocentre_20121211.tar » : Aucun fichier ou répertoire de ce type
[root@monserveur pgdata]# pg_restore backups_prod/mabase_infocentre_20121211.tar -U postgres -C -d postgres
pg_restore: [programme d'archivage (db)] Erreur pendant le traitement de la TOC (« PROCESSING TOC ») :
pg_restore: [programme d'archivage (db)] Erreur à partir de l'entrée TOC 32 ; 1255 17753 FUNCTION pg_stat_statements() postgres
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR:  n'a pas pu accéder au fichier « $libdir/pg_stat_statements » : Aucun fichier ou répertoire de ce type
    Command was: CREATE FUNCTION pg_stat_statements(OUT userid oid, OUT dbid oid, OUT query text, OUT calls bigint, OUT total_time double pre...
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR:  la fonction public.pg_stat_statements() n'existe pas
    Command was: ALTER FUNCTION public.pg_stat_statements(OUT userid oid, OUT dbid oid, OUT query text, OUT calls bigint, OUT total_time doub...
pg_restore: [programme d'archivage (db)] Erreur à partir de l'entrée TOC 33 ; 1255 17752 FUNCTION pg_stat_statements_reset() postgres
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR:  n'a pas pu accéder au fichier « $libdir/pg_stat_statements » : Aucun fichier ou répertoire de ce type
    Command was: CREATE FUNCTION pg_stat_statements_reset() RETURNS void
    LANGUAGE c
    AS '$libdir/pg_stat_statements', 'pg_stat_stateme...
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR:  la fonction public.pg_stat_statements_reset() n'existe pas
    Command was: ALTER FUNCTION public.pg_stat_statements_reset() OWNER TO postgres;

pg_restore: [programme d'archivage (db)] Erreur à partir de l'entrée TOC 2636 ; 1259 17754 VIEW pg_stat_statements postgres
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR:  la fonction pg_stat_statements() n'existe pas
LIGNE 2 : ...atements.total_time, pg_stat_statements.rows FROM pg_stat_st...
                                                               ^
ASTUCE : Aucune fonction ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
    Command was: CREATE VIEW pg_stat_statements AS
    SELECT pg_stat_statements.userid, pg_stat_statements.dbid, pg_stat_statements.query, p...
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR:  la relation « public.pg_stat_statements » n'existe pas
    Command was: ALTER TABLE public.pg_stat_statements OWNER TO postgres;