Vous n'êtes pas identifié(e).
Pour poursuivre l'optimisation du serveur que j'utilise, je me pose également une autre question :
La requête "type" sur laquelle nous avons réalisé des tests a démontré une nette amélioration des temps de calcul.
Les tâches que je réalise en ce moment m'amènent à lancer des enchaînements de ce même genre de requêtes. (vous me direz si c'est une bonne ou une mauvaise idée de procéder de la sorte ? Je ne suis pas administrateur de données de formation :-) )
Requête 1
;
Requête 2
;
Requête ...
Là aussi je me demande si, au bout de plusieurs heures calcul, il n'y a pas des opérations de maintenance réalisées en automatique par le serveur (des vacuum ou je ne sais quoi d'autre) qui viennent interférer dans la réalisation de la requête ?
L'idée étant, dans mon cas précis ou je suis le seul accédant, et ayant de nombreuses tâches de mises à jour à réaliser en ce moment, de désactiver temporairement celles-ci et me laisser les lancer à la fin de chaque traitement chaîné ?
Concernant les 0 lignes écrites, voici le dernier test réalisé de ma requête (suppression des champs, recréation de ceux-ci pour partir sur des champs vides). J'ai vérifié les champs mis à jour et ils y a bien de la donnée écrite. Le query plan dit le contraire ?
QUERY PLAN
Update on edi_parc_uf (cost=835946.26..1332168.33 rows=977854 width=1517) (actual time=1095770.487..1095770.487 rows=0 loops=1)
Buffers: shared hit=32783420 read=746508 dirtied=917352, temp read=157351 written=157337
CTE ident_surf_bati_uf
-> HashAggregate (cost=339152.96..348931.50 rows=977854 width=76) (actual time=82749.792..84263.735 rows=2064209 loops=1)
Buffers: shared hit=63567 read=266475
-> Seq Scan on edi_parc (cost=0.00..298628.54 rows=3241954 width=76) (actual time=9.489..71975.019 rows=3241824 loops=1)
Buffers: shared read=266209
-> Hash Join (cost=487014.76..983236.83 rows=977854 width=1517) (actual time=117747.933..187374.810 rows=2064208 loops=1)
Hash Cond: (ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr)
Buffers: shared hit=164512 read=400398, temp read=157351 written=157337
-> CTE Scan on ident_surf_bati_uf (cost=0.00..19557.08 rows=977854 width=576) (actual time=82749.807..85493.955 rows=2064209 loops=1)
Buffers: shared hit=63567 read=266475
-> Hash (cost=154669.56..154669.56 rows=2065056 width=1185) (actual time=34997.763..34997.763 rows=2065094 loops=1)
Buckets: 65536 Batches: 8 Memory Usage: 131834kB
Buffers: shared hit=36628 read=133923, temp written=112380
-> Seq Scan on edi_parc_uf (cost=0.00..154669.56 rows=2065056 width=1185) (actual time=12.052..30452.669 rows=2065094 loops=1)
Buffers: shared hit=96 read=133923
Total runtime: 1096110.130 ms
Alors là c'est impressionnant... le checkpoint_segments monté à 60 me fait passer les requête de 2h à ... 10 min !!!
Si je comprends bien, cette valeur augmente sensiblement la rapidité des requêtes en réduisant la fréquence des accès disque, mais en prenant le risque de pertes plus importantes de données en cas de crash de la bdd et donc de plus de temps pour son redémarrage/reprise de son état avant crash ?
Dans mon cas (seul utilisateur de la bdd) s'il y a "crash", et données à rejouer, elles ne devraient concerner que la requête en cours d’exécution... Vu les gains de performance !!!
Voici donc le résultat des dernières modifications (checkpoint_segments et checkpoint_timeout).
Celles-ci ont donc encore amélioré les choses. il faut désormais moins de 2h pour que la requête s'exécute.
"Update on edi_parc_uf (cost=835983.54..1332141.61 rows=977854 width=1522) (actual time=6838898.978..6838898.978 rows=0 loops=1)"
" Buffers: shared hit=33420101 read=780370 dirtied=4330486, temp read=157346 written=157332"
" CTE ident_surf_bati_uf"
" -> HashAggregate (cost=339152.96..348931.50 rows=977854 width=76) (actual time=60984.287..62757.712 rows=2064209 loops=1)"
" Buffers: shared hit=63581 read=266476"
" -> Seq Scan on edi_parc (cost=0.00..298628.54 rows=3241954 width=76) (actual time=6.372..52798.937 rows=3241824 loops=1)"
" Buffers: shared read=266209"
" -> Hash Join (cost=487052.04..983210.10 rows=977854 width=1522) (actual time=100525.498..242831.533 rows=2064208 loops=1)"
" Hash Cond: (ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr)"
" Buffers: shared hit=167710 read=401011, temp read=157346 written=157332"
" -> CTE Scan on ident_surf_bati_uf (cost=0.00..19557.08 rows=977854 width=576) (actual time=60984.305..64211.110 rows=2064209 loops=1)"
" Buffers: shared hit=63581 read=266476"
" -> Hash (cost=154776.24..154776.24 rows=2064624 width=1190) (actual time=39541.053..39541.053 rows=2065094 loops=1)"
" Buckets: 65536 Batches: 8 Memory Usage: 131831kB"
" Buffers: shared hit=37971 read=134535, temp written=112375"
" -> Seq Scan on edi_parc_uf (cost=0.00..154776.24 rows=2064624 width=1190) (actual time=0.013..33909.582 rows=2065094 loops=1)"
" Buffers: shared read=134130"
"Total runtime: 6863807.552 ms"
J'imagine, vu le nombre de paramètres disponibles dans la configuration de postgres, qu'il est encore possible d'améliorer les choses, mais vous m'avez tous permis de sérieuses améliorations dans mes temps de calcul !!! 3h30 à moins de 2h...
Un grand merci à tous.
Après exploration des logs pour voir si celui-ci remonte des erreurs de type "checkpoints are occurring too frequently", celles-ci remontent en effet. Je vais tester une valeur à 60.
En effet.
Dans le cas présent il s'agit d'une base de données cadastrale qui est intégrée annuellement, d'un bloc. La création de nouveaux champs, et leur mise à jour se fait donc d'un bloc aussi.
En tous cas merci pour la proposition :-)
Oups, je n'avais pas vu passer votre réponse ruizsebastien...
checkpoint_segments et checkpoint_timeout ... modifications et tests en cours.
Je travaille sur ma machine windows car c'est ma machine de tous les jours et qu'il est plus pratique pour moi de faire ainsi.
Je pourrais effectivement ajouter un Linux sur ma machine :
- install d'un double boot : oui, mais je ne peux plus travailler sur mes applications windows (je suis géomaticien)
- machnie virtuelle : oui, mais avec ma configuration mémoire actuelle (8GB) WINDOWS + LINUX + POSTGRES j'ai peur que ça commence à faire beaucoup... à moins bien entendu d'augmenter la mémoire. Le travail qui génère mes questions actuelles n'est que temporaire et je n'ai pas toujours à solliciter mon pc pour de telles tâches.
J'ai un serveur Linux avec postgres dessus, mais c'est un serveur qui est en charge. Je ne voudrais pas le paralyser pour mes traitements "persos" et bloquer tout le monde derrière, en sachant que j'ai plusieurs dizaines de requêtes du même type que celle qui nous occupe à réaliser. :-)
Bonjour et merci pour ce retour très complet !!!
J'ai donc appliqué quelques modifications dans ma configuration :
shared_buffers => passé à 3GB (pour test et même si cela doit attribuer une grosse partie de mes ressources machines à postgres, au moins pour le temps de mes mises à jour, ce n'est pas un pb)
checkpoint_segments : 20
checkpoint_timeout : 15MIN
Dans les faits, mes tests se sont faits de multiples fois sur une table qui a donc déjà été mise à jour au fil des tests, je n'avais pas effacé le champ, ceci explique peut-être les fameuse 0 lignes mises à jour au final ? Mea culpa.
Aucune des tables n'a été alimentée avant cette mise à jour.
Je vais relancer des tests pour voir ce que mes dernières modifications donnent.
Pour ce qui est de la config de postgres sur un windows, je cherche juste à faire au mieux. A l'impossible nul n'est tenu ;-)
Le modifications de configuration proposées par ruizsebastien ont donc sensiblement amélioré les résultats (gain de 30%).
Maintenant, et à la vue de l'EXPLAIN ANALYSE, considérez-vous que l'on soit dans un fonctionnement "normal" pour ce type de requête ? En sachant, bien entendu, que nous sommes sur une station de travail (processeur XEON) et pas un serveur et donc des disque à 7200 rpm.
La requête s'est finalement terminée au bout de 2h30.
Voici le résultat de l'EXPLAIN ANALYSE :
"Update on edi_parc_uf (cost=837865.78..1330604.12 rows=959944 width=1518) (actual time=8613371.967..8613371.967 rows=0 loops=1)"
" Buffers: shared hit=32794495 read=771955 dirtied=4816855, temp read=157346 written=157332"
" CTE ident_surf_bati_uf"
" -> HashAggregate (cost=339150.04..348749.48 rows=959944 width=76) (actual time=24802.636..26038.480 rows=2064209 loops=1)"
" Buffers: shared hit=63581 read=266476"
" -> Seq Scan on edi_parc (cost=0.00..298627.24 rows=3241824 width=76) (actual time=13.321..18158.419 rows=3241824 loops=1)"
" Buffers: shared read=266209"
" -> Hash Join (cost=489116.30..981854.64 rows=959944 width=1518) (actual time=60348.342..248611.858 rows=2064208 loops=1)"
" Hash Cond: (ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr)"
" Buffers: shared hit=167910 read=402743, temp read=157346 written=157332"
" -> CTE Scan on ident_surf_bati_uf (cost=0.00..19198.88 rows=959944 width=576) (actual time=24802.654..26909.001 rows=2064209 loops=1)"
" Buffers: shared hit=63581 read=266476"
" -> Hash (cost=156780.91..156780.91 rows=2064991 width=1186) (actual time=35545.419..35545.419 rows=2065094 loops=1)"
" Buckets: 65536 Batches: 8 Memory Usage: 131831kB"
" Buffers: shared hit=38502 read=135971, temp written=112375"
" -> Seq Scan on edi_parc_uf (cost=0.00..156780.91 rows=2064991 width=1186) (actual time=0.062..30886.513 rows=2065094 loops=1)"
" Buffers: shared hit=160 read=135971"
"Total runtime: 8613547.955 ms"
Je viens donc d'appliquer les recommandations de ruizsebastien, et pour la question ma station de travail est dotée de 8 giga de mémoire vive :
effective_cache size : 5GB
maintenance_work_mem : 400MB
shared_buffers : 2GB
work_mem : 500MB (n'ayant pas eu le retour de la rqte d'analyze je l'ai augmentée "au doigt mouillé" pour l'instant)
J'ai relancé la requête en EXPLAIN(ANALYZE,BUFFERS), j'attends les résultats...
Rjuju : ma machine est bien sous windows, j'attends de voir les résultats précédents pour me faire un avis sur les améliorations potentielles qu'elles pourront m'apporter.
Merci à tous, je vous tiens informé de l'évolution des choses dès que j'ai des résultats.
Bonjour,
Le voici :
"allow_system_table_mods";"off"
"application_name";"pgAdmin III - ??diteur de requ??tes"
"archive_command";"(disabled)"
"archive_mode";"off"
"archive_timeout";"0"
"array_nulls";"on"
"authentication_timeout";"60"
"autovacuum";"on"
"autovacuum_analyze_scale_factor";"0.1"
"autovacuum_analyze_threshold";"50"
"autovacuum_freeze_max_age";"200000000"
"autovacuum_max_workers";"3"
"autovacuum_naptime";"60"
"autovacuum_vacuum_cost_delay";"20"
"autovacuum_vacuum_cost_limit";"-1"
"autovacuum_vacuum_scale_factor";"0.2"
"autovacuum_vacuum_threshold";"50"
"backslash_quote";"safe_encoding"
"bgwriter_delay";"200"
"bgwriter_lru_maxpages";"100"
"bgwriter_lru_multiplier";"2"
"block_size";"8192"
"bonjour";"off"
"bonjour_name";""
"bytea_output";"escape"
"check_function_bodies";"on"
"checkpoint_completion_target";"0.5"
"checkpoint_segments";"3"
"checkpoint_timeout";"300"
"checkpoint_warning";"30"
"client_encoding";"UNICODE"
"client_min_messages";"notice"
"commit_delay";"0"
"commit_siblings";"5"
"config_file";"D:/PostgreSQL/9.3/data/postgresql.conf"
"constraint_exclusion";"partition"
"cpu_index_tuple_cost";"0.005"
"cpu_operator_cost";"0.0025"
"cpu_tuple_cost";"0.01"
"cursor_tuple_fraction";"0.1"
"data_directory";"D:/PostgreSQL/9.3/data"
"DateStyle";"ISO, DMY"
"db_user_namespace";"off"
"deadlock_timeout";"1000"
"debug_assertions";"off"
"debug_pretty_print";"on"
"debug_print_parse";"off"
"debug_print_plan";"off"
"debug_print_rewritten";"off"
"default_statistics_target";"100"
"default_tablespace";""
"default_text_search_config";"pg_catalog.french"
"default_transaction_deferrable";"off"
"default_transaction_isolation";"read committed"
"default_transaction_read_only";"off"
"default_with_oids";"off"
"dynamic_library_path";"$libdir"
"effective_cache_size";"16384"
"effective_io_concurrency";"0"
"enable_bitmapscan";"on"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexonlyscan";"on"
"enable_indexscan";"on"
"enable_material";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"on"
"enable_sort";"on"
"enable_tidscan";"on"
"escape_string_warning";"on"
"event_source";"PostgreSQL"
"exit_on_error";"off"
"external_pid_file";""
"extra_float_digits";"0"
"from_collapse_limit";"8"
"fsync";"on"
"full_page_writes";"on"
"geqo";"on"
"geqo_effort";"5"
"geqo_generations";"0"
"geqo_pool_size";"0"
"geqo_seed";"0"
"geqo_selection_bias";"2"
"geqo_threshold";"12"
"gin_fuzzy_search_limit";"0"
"hba_file";"D:/PostgreSQL/9.3/data/pg_hba.conf"
"hot_standby";"off"
"hot_standby_feedback";"off"
"ident_file";"D:/PostgreSQL/9.3/data/pg_ident.conf"
"ignore_checksum_failure";"off"
"ignore_system_indexes";"off"
"integer_datetimes";"on"
"IntervalStyle";"postgres"
"join_collapse_limit";"8"
"krb_caseins_users";"off"
"krb_server_keyfile";""
"krb_srvname";"postgres"
"lc_collate";"French_France.1252"
"lc_ctype";"French_France.1252"
"lc_messages";"French_France.1252"
"lc_monetary";"French_France.1252"
"lc_numeric";"French_France.1252"
"lc_time";"French_France.1252"
"listen_addresses";"*"
"lo_compat_privileges";"off"
"local_preload_libraries";""
"lock_timeout";"0"
"log_autovacuum_min_duration";"-1"
"log_checkpoints";"off"
"log_connections";"off"
"log_destination";"stderr"
"log_directory";"pg_log"
"log_disconnections";"off"
"log_duration";"off"
"log_error_verbosity";"default"
"log_executor_stats";"off"
"log_file_mode";"0600"
"log_filename";"postgresql-%Y-%m-%d_%H%M%S.log"
"log_hostname";"off"
"log_line_prefix";"%t "
"log_lock_waits";"off"
"log_min_duration_statement";"-1"
"log_min_error_statement";"error"
"log_min_messages";"warning"
"log_parser_stats";"off"
"log_planner_stats";"off"
"log_rotation_age";"1440"
"log_rotation_size";"10240"
"log_statement";"none"
"log_statement_stats";"off"
"log_temp_files";"-1"
"log_timezone";"Europe/Brussels"
"log_truncate_on_rotation";"off"
"logging_collector";"on"
"maintenance_work_mem";"16384"
"max_connections";"100"
"max_files_per_process";"1000"
"max_function_args";"100"
"max_identifier_length";"63"
"max_index_keys";"32"
"max_locks_per_transaction";"64"
"max_pred_locks_per_transaction";"64"
"max_prepared_transactions";"0"
"max_stack_depth";"2048"
"max_standby_archive_delay";"30000"
"max_standby_streaming_delay";"30000"
"max_wal_senders";"0"
"password_encryption";"on"
"port";"5432"
"post_auth_delay";"0"
"pre_auth_delay";"0"
"quote_all_identifiers";"off"
"random_page_cost";"4"
"restart_after_crash";"on"
"search_path";""$user", public, sde"
"segment_size";"131072"
"seq_page_cost";"1"
"server_encoding";"UTF8"
"server_version";"9.3.2"
"server_version_num";"90302"
"session_replication_role";"origin"
"shared_buffers";"131072"
"shared_preload_libraries";""
"sql_inheritance";"on"
"ssl";"off"
"ssl_ca_file";""
"ssl_cert_file";"server.crt"
"ssl_ciphers";"DEFAULT:!LOW:!EXP:!MD5:@STRENGTH"
"ssl_crl_file";""
"ssl_key_file";"server.key"
"ssl_renegotiation_limit";"524288"
"standard_conforming_strings";"on"
"statement_timeout";"0"
"stats_temp_directory";"pg_stat_tmp"
"superuser_reserved_connections";"3"
"synchronize_seqscans";"on"
"synchronous_commit";"on"
"synchronous_standby_names";""
"syslog_facility";"none"
"syslog_ident";"postgres"
"tcp_keepalives_count";"0"
"tcp_keepalives_idle";"-1"
"tcp_keepalives_interval";"-1"
"temp_buffers";"1024"
"temp_file_limit";"-1"
"temp_tablespaces";""
"TimeZone";"Europe/Brussels"
"timezone_abbreviations";"Default"
"trace_notify";"off"
"trace_recovery_messages";"log"
"trace_sort";"off"
"track_activities";"on"
"track_activity_query_size";"1024"
"track_counts";"on"
"track_functions";"none"
"track_io_timing";"off"
"transaction_deferrable";"off"
"transaction_isolation";"read committed"
"transaction_read_only";"off"
"transform_null_equals";"off"
"unix_socket_directories";""
"unix_socket_group";""
"unix_socket_permissions";"0777"
"update_process_title";"on"
"vacuum_cost_delay";"0"
"vacuum_cost_limit";"200"
"vacuum_cost_page_dirty";"20"
"vacuum_cost_page_hit";"1"
"vacuum_cost_page_miss";"10"
"vacuum_defer_cleanup_age";"0"
"vacuum_freeze_min_age";"50000000"
"vacuum_freeze_table_age";"150000000"
"wal_block_size";"8192"
"wal_buffers";"2048"
"wal_keep_segments";"0"
"wal_level";"minimal"
"wal_receiver_status_interval";"10"
"wal_receiver_timeout";"60000"
"wal_segment_size";"2048"
"wal_sender_timeout";"60000"
"wal_sync_method";"open_datasync"
"wal_writer_delay";"200"
"work_mem";"204800"
"xmlbinary";"base64"
"xmloption";"content"
"zero_damaged_pages";"off"Bonjour et merci à tous pour vos conseils. J'ai mis un peu de temps à répondre, car pris par d'autres missions.
Je fais une réponse groupée.
J'ai donc apporté des modifications à la configuration de ma base de données :
Shared_buffers => 1GB
Work_mem => 200MB
J'ai redémarré la machine pour repartir sur des bases "saines".
Je viens de lancer un EXPLAIN (ANALYZE,BUFFERS) sur la requête citée précédemment (le disque repasse immédiatement à 100% de son temps d'activité maximal). Comme elle était longue à l'origine (plus de 3 heures), et malgré mes modifications de configuration, elle tourne encore. Je ferai remonter les résultats dès que disponibles.
Concernant le CTE, je suppose que vous faites référence au "WITH" que j'utilise. Je pensais, mais c'est apparemment une mauvaise méthode, que cela pourrait améliorer les types de requêtes que je réalise en prés-calculant en mémoire les données à injecter dans ma table à mettre à jour. Vous évoquez la possibilité de l'éviter, par quel moyen ?
Bonjour et merci pour votre retour rapide,
En fonctionnement ma machine possède encore plusieurs Gigas de mémoire vive disponible, je peux donc l'exploiter.
J'avais déjà modifié certains paramètres en suivant des conseils de paramétrage et notamment pour :
shared_buffers : 500MB => est-ce suffisant ?
Concernant work_mem je constate que l'option n'est même pas cochée dans l'éditeur de configuration de pgAdmin. Je vais donc l'appliquer. Etant le seul utilisateur de la bdd, quelle valeur me conseilleriez-vous ? J'ai trouvé des conseils autour de 16MB, ce qui me semble peu...
Merci par avance.
Bonjour,
Je réalise de nombreuses requêtes de mise en jour sur une base postgres. Les tables mises à jour possèdent plusieurs millions de lignes (entre 3 et 4 millions). Le temps de mise à jour me semble particulièrement long (plusieurs heures même pour mettre un seul champ à jour). Mais, est ce que je fais bien les choses ???
Pour exemple, voici une requête type :
WITH ident_surf_bati_uf AS (
SELECT edi_parc.id_uf,
SUM(edi_parc.surf_bati_dur) AS surf_bati_dur,
SUM(edi_parc.surf_bati_leger) AS surf_bati_leger,
SUM(edi_parc.surf_bati_total) AS surf_bati_tot,
SUM(edi_parc.surf_resultante_tt_bati) AS surf_resultante_tt_bati
FROM cad_2015.edi_parc
GROUP BY edi_parc.id_uf)
UPDATE cad_2015.edi_parc_uf
SET surf_bati_leger = ident_surf_bati_uf.surf_bati_leger,
surf_bati_dur = ident_surf_bati_uf.surf_bati_dur,
surf_bati_tot = ident_surf_bati_uf.surf_bati_tot,
surf_resultante_tt_bati = ident_surf_bati_uf.surf_resultante_tt_bati
FROM
ident_surf_bati_uf
WHERE
ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr
;Un explain me renvoi ce résultat :
"Update on edi_parc_uf (cost=4198990.84..6334634.17 rows=850593 width=1510)"
" CTE ident_surf_bati_uf"
" -> GroupAggregate (cost=1073593.37..1130730.27 rows=850593 width=76)"
" -> Sort (cost=1073593.37..1081698.53 rows=3242065 width=76)"
" Sort Key: edi_parc.id_uf"
" -> Seq Scan on edi_parc (cost=0.00..290808.65 rows=3242065 width=76)"
" -> Hash Join (cost=3068260.56..5203903.90 rows=850593 width=1510)"
" Hash Cond: (ident_surf_bati_uf.id_uf = edi_parc_uf.id_centr)"
" -> CTE Scan on ident_surf_bati_uf (cost=0.00..17011.86 rows=850593 width=576)"
" -> Hash (cost=940953.14..940953.14 rows=13298914 width=1178)"
" -> Seq Scan on edi_parc_uf (cost=0.00..940953.14 rows=13298914 width=1178)"Suis-je fautif de mauvaises requêtes, de mauvais paramétrages de postgrès, ou bien ces résultats sont-ils "acceptables" ?
Ayant le serveur postgres en question d'installé sur ma station de travail, j'utilise aussi le moniteur de ressources pour observer le fonctionnement général de ma machine. Je constate que mon disque est constamment à 100% de son temps d'activité maximal... et donc qu'il semble être, notamment, un goulot d'étranglement.
Peut-être y a-t-il des paramètres de configuration de postgres à même d'optimiser son fonctionnement, notamment en s’appuyant plus sur la mémoire vive de la machine ?
Merci d'avance pour vos conseils.
Bon j'ai fini par résoudre mon problème d'import.
Etant dans la dernière version du couple PostGrès/PostGIS, le tout sur une machine en 64bits... et après de nombreuses tentatives sur des versions précédentes de PostGrès (identiques à celles utilisées par le fournisseur de mes données) qui se sont révélées être des échecs également... je suis revenu sur un bon vieux XP 32bits avec les anciennes versions de Postgrès, résultat : ça fonctionne parfaitement.
Des fois faut pas chercher à comprendre !!!
Maintenant faut que j'arrive à faire migrer tout ce petit monde dans ma nouvelle base de données.
Bonsoir,
Voici quelques exemples des premiers messages d'erreur :
1er exemple
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 40328 ; 1259 100337184 TABLE d63_2011_pb0010_local postgres
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR: le type « public.geometry » n'existe pas
LIGNE 79 : geomloc public.geometry,
^
La commande était : CREATE TABLE d63_2011_pb0010_local (
idlocal character varying(12),
idbat character varying(16),
idpar character...
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR: la relation « ff_d63_2011.d63_2011_pb0010_local » n'existe pas
La commande était : ALTER TABLE ff_d63_2011.d63_2011_pb0010_local OWNER TO postgres;
pg_restore: création de COMMENT TABLE d63_2011_pb0010_local
pg_restore: [programme d'archivage (db)] Erreur à partir de l'entrée TOC 41598 ; 0 0 COMMENT TABLE d63_2011_pb0010_local postgres
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR: la relation « d63_2011_pb0010_local » n'existe pas
La commande était : COMMENT ON TABLE d63_2011_pb0010_local IS 'Table des locaux';2eme exemple
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 40328 ; 1259 100337184 TABLE d63_2011_pb0010_local postgres
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR: la fonction public.ndims(public.geometry) n'existe pas
ASTUCE : Aucune fonction ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
La commande était : CREATE TABLE d63_2011_pb0010_local (
idlocal character varying(12),
idbat character varying(16),
idpar character...
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR: la relation « ff_d63_2011.d63_2011_pb0010_local » n'existe pas
La commande était : ALTER TABLE ff_d63_2011.d63_2011_pb0010_local OWNER TO postgres;Bonjour,
Une base de donnée vient de m'être livrée par le biais d'un fichier de sauvegarde d'une base de données PostGrès/PostGIS : un fichier ".dump" en l’occurrence.
Cette base contient des données purement attributaires et pour le reste des données graphiques (tables SIG gérées par PostGIS)
Lorsque je tente de restaurer ladite base de donnée :
- Toutes les tables purement attributaires semblent se restaurer "correctement" même si bon nombre de messages d'erreurs font référence à des "relations inexistantes" avec des tables qui ne se sont, elles, pas restaurées... cf. point suivant
- Par contre 2 tables faisant référence à des données graphiques ne sont pas restaurées avec, notamment, le message suivant dans le rapport de restauration : "ERREUR: la fonction public.ndims(public.geometry) n'existe pas"
L'erreur portant sur des tables graphiques je présuppose qu'il doit y avoir un problème du côté PostGIS ?
Je fais cette restauration dans la dernière version de PostGrès/PostGIS. Je précise que la cartouche spatiale PostGIS est bien installée.
Je suppose que je dois faire une erreur à un moment ou à un autre :
- Erreur dans lors de la création de la base de donnée dans laquelle je tente ensuite de restaurer les données ? (j'ai testé les modèles "postgis20" et "template_postgis_20" sans résultats)
- Erreur lors de la restauration de la base ? (je laisse toutes les options par défaut)
- Incompatibilité entre la version de postgrès qui a servi à créer le fichier de restauration et la version de postgrès (9.2) dans laquelle je tente de faire ma restauration ? (j'ai testé l'import dans une base en 9.1 sans plus de succès)
Si quelqu'un a une piste à me donner je suis preneur...
D'avance merci.
Bonsoir,
Ça y est je suis parvenu à utiliser le code que tu m'avais transmis.
Le problème venait bien de moi et de mon peu d'expérience avec postgres. J'avais notamment des noms de champs avec des majuscules. Une fois corrigé la requête s'exécute parfaitement.
Ton code m'a même permis de rajouter d'autres conditions de sous sélection et de tri.
SELECT
communes_fr_rgpt.code_au,
communes_fr_rgpt2.code_au,
Sum(fd_mobpro_2006.ipondi)
FROM
fd_mobpro_2006
INNER JOIN communes_fr_rgpt ON communes_fr_rgpt.code_insee = fd_mobpro_2006.commune
INNER JOIN communes_fr_rgpt2 ON communes_fr_rgpt2.code_insee = fd_mobpro_2006.dclt
WHERE communes_fr_rgpt2.code_au not in ('999','000')
AND communes_fr_rgpt.code_au not in ('999','000')
AND communes_fr_rgpt.code_au <> communes_fr_rgpt2.code_au
GROUP BY communes_fr_rgpt2.code_au, communes_fr_rgpt.code_au
ORDER BY communes_fr_rgpt.code_au;.Merci beaucoup pour ton aide Meles
Bonjour,
Je réponds avec du retard, d'autres impératifs m'ont imposé une "pause" dans mes travaux sur postgres.
Tu as bien saisi l'objet de ma requête.
Une fois que j'ai rattaché une aire urbaine à chacune de mes communes je réalise ensuite une agrégation par aire urbaine.
Mon champ IPONDI se rapporte au poids de chacun de mes individus dans la population française (chacun de mes individus représentant un échantillon de la population, et également un déplacement domicile-travail), poids qu'il me faut sommer pour obtenir la nombre total de déplacements entre aires urbaines.
Voici se que sa donne :
Individu l Aire urbaine <-> Commune de domicile l Commune de travail <-> Aire urbaine l IPONDI
N°1 AU1 Commune A Commune Z AU2 1,5
N°2 AU1 Commune B Commune Y AU8 2
N°3 AU2 Commune Z Commune X AU3 3
N°4 AU1 Commune C Commune P AU2 2,5
N°5 AU2 Commune E Commune V AU3 4
Résultat de la requête :
Aire urbaine (domicile) l Aire urbaine (travail) l Somme de IPONDI
AU1 AU2 4
AU1 AU8 2
AU2 AU3 7
Le tout se réalisant sur une base à l'individu... chaque individu ayant un poids... et il y a 8 000 000 d'individus. Ça commence donc à faire des requêtes complexes et lourdes.
J'arrive donc à obtenir des réponses à mes requêtes en 2-3 minutes. Ce qui ne m'apparaît pas problématique pour l'usage que j'ai à en faire, à savoir extraire des données de ma table de départ pour les exploiter/analyser par la suite.
Bonsoir,
Je viens d'essayer le code proposé mais ça ne fonctionne pas. Il est aussi fort probable que je fasse des erreurs. Je vais retenter tout de même.
Concernant la particularité de ma requête, c'est qu'elle concerne une table d'origine/destination :
Chaque ligne représente un déplacement d'une commune d'habitation (champ commune) vers une commune de travail (champ DCLT)
Comme je souhaite agréger mes communes à l'aide d'une seconde table contenant le code des aires urbaines auxquelles celles-ci appartiennent je joins donc ma table des aires urbaine :
- une première fois sur mon champ commune
- une seconde fois sur mon champ DCLT
...afin de demander une agrégation sur les aires urbaine d'origine et également sur les aires urbaine de destination.
L'objectif étant d'obtenir les flux de migration entre les aires urbaines (et non pas entre les communes).
En tous cas merci pour l'aide, je continue à tester vos propositions.
Cordialement
Voici la requête telle que saisie sous ACCESS :
SELECT
public_communes_fr_rgpt.Code_AU,
public_communes_fr_rgpt_1.Code_AU,
Sum(public_fd_mobpro_2006.IPONDI)
FROM
public_communes_fr_rgpt AS public_communes_fr_rgpt_1 INNER JOIN (public_communes_fr_rgpt INNER JOIN public_fd_mobpro_2006 ON public_communes_fr_rgpt.code_insee = public_fd_mobpro_2006.commune) ON public_communes_fr_rgpt_1.code_insee = public_fd_mobpro_2006.DCLT
WHERE
(((public_communes_fr_rgpt.Code_AU)<>"999" And (public_communes_fr_rgpt.Code_AU)<>"000") AND ((public_communes_fr_rgpt_1.Code_AU)<>"999" And (public_communes_fr_rgpt_1.Code_AU)<>"000"))
GROUP BY
public_communes_fr_rgpt.Code_AU, public_communes_fr_rgpt_1.Code_AU;
Merci pour toutes ces infos.
De mon côté j'ai bien avancé sur le fait que le problème puisse venir d'ACCESS, et notamment à la lecture du log qui laisse entendre que postgres "n'a pas pu recevoir les données du client".
Pour réaliser ma requête je faisais, entre autres, une jointure entre une table sous ACCESS et une autre dans postgres... ce qui est très certainement une erreur de débutant. Résultat j'ai importé ladite table directement dans postgres et recommencé ma requête depuis ACCESS... et ça fonctionne !!!
Le temps de réponse est cependant très long, de l'ordre de 5 à 10 minutes, ce qui m'étonne un peu pour un SGBD aussi performant postgres. Ça me laisse entendre qu'il y a certainement encore des choses que je ne fais pas correctement.
Mes deux tables ont une clé primaire et son indexées sur les champs principaux que j'utilise pour requêter, en l'occurrence les codes insee de mes communes.
N'est-il pas nécessaire de faire d'autres choses pour que les performance soient meilleurs ?
Merci
En effet, ACCESS peut très bien être la cause du problème. J'ai aussi cherché dans cette direction et celle du pilote odbc mais je n'ai, pour l'instant, pas trouvé de réponses à mon problème de ce côté non plus. Je suis ouvert à toutes les pistes.
Comme je débute, voici ce que j'ai trouvé comme fichier log, en espérant que ce soit ce que vous évoquez :
2011-01-20 09:00:46 CET FATAL: le système de bases de données se lance
2011-01-20 09:00:46 CET LOG: le système de bases de données a été arrêté à 2011-01-19 18:11:11 CET
2011-01-20 09:00:47 CET FATAL: le système de bases de données se lance
2011-01-20 09:00:48 CET LOG: le système de bases de données est prêt pour accepter les connexions
2011-01-20 09:00:48 CET LOG: lancement du processus autovacuum
2011-01-20 09:20:53 CET ERREUR: la relation « msysconf » n'existe pas au caractère 28
2011-01-20 09:20:53 CET INSTRUCTION : SELECT Config, nValue FROM MSysConf
2011-01-20 09:26:55 CET LOG: n'a pas pu envoyer les données au client : Unknown winsock error 10061
2011-01-20 09:26:55 CET INSTRUCTION : SELECT "commune" ,"DCLT" ,"IPONDI" FROM "public"."fd_mobpro_2006"
2011-01-20 09:27:04 CET LOG: n'a pas pu recevoir les données du client : Unknown winsock error 10061
2011-01-20 09:27:04 CET LOG: fin de fichier (EOF) inattendue de la connexion du client
2011-01-20 09:27:06 CET ERREUR: la relation « msysconf » n'existe pas au caractère 28
2011-01-20 09:27:06 CET INSTRUCTION : SELECT Config, nValue FROM MSysConf
2011-01-20 09:28:18 CET LOG: n'a pas pu envoyer les données au client : Unknown winsock error 10061
2011-01-20 09:28:18 CET INSTRUCTION : SELECT "commune" ,"DCLT" ,"IPONDI" FROM "public"."fd_mobpro_2006"
2011-01-20 09:28:29 CET LOG: n'a pas pu recevoir les données du client : Unknown winsock error 10061
2011-01-20 09:28:29 CET LOG: fin de fichier (EOF) inattendue de la connexion du client
2011-01-20 09:28:29 CET ERREUR: la relation « msysconf » n'existe pas au caractère 28
2011-01-20 09:28:29 CET INSTRUCTION : SELECT Config, nValue FROM MSysConf
Pour ce qui est de récupérer la requête exécutée depuis le pilote... je ne sais pas faire. Quelle en est la méthode ?
Merci
Bonjour,
J'utilise depuis peu postgres.
J'ai besoin de réaliser des requêtes sur ma base postgres à partir d'ACCESS et ce par le biais d'une connexion ODBC.
La table sur laquelle je réalise des requêtes contient 8 000 000 de lignes.
Dans la plupart des requêtes simples que j'exécute, j'obtiens un résultat quasi instantané, par exemple :
- agrégation de ma table sur un champ X...
- avec somme des enregistrements du champ Y...
- sélection d'un seul de mes individus du champ X
Malheureusement, dès que je souhaite réaliser une requête un peu plus "lourde" à savoir la même requête que précédemment mais sans sous-sélectionner un de mes individus la requête plante systématiquement.
Requête type :
- agrégation de ma table sur un champ X...
- avec somme des enregistrements du champ Y... BUG !!!
Message d'erreur renvoyé par ACCESS :
ODBC -- l'appel à échoué
Bindingd were not allocated properly. (#15)
Pour informations :
- quand la requête "simple" retourne une réponse quasi instantanée, la requête plus "lourde" demande 10 à 20 secondes.
- dans le cas de la requête plus "lourde" la charge mémoire du PC client grimpe en flèche.
Quelqu'un saurait-il d'où peu venir le problème ? Limitation du pilote ODBC, limitation côté serveur postgres (donc mauvaise configuration de ma part)...
D'avance merci,
Fabrice.