Vous n'êtes pas identifié(e).
Bonjour,
Je teste, pour le moment en local, la mise en place d'une réplication par streaming.
Je suis sous PostgreSQL 9.5, sous Linux Mint.
Je simule le fonctionnement d'un maître et d'un esclave par deux instances différentes de postgresql sur deux ports différents (5432 pour le maître, 5433 pour l'esclave).
J'ai créé un répertoire destiné à l'archivage : /var/lib/postgresql/9.5/archives
Pour le serveur maître, j'ai placé les paramètres de réplication dans un fichier postgresql.replication.conf, appelé par le fichier postgresql.conf (comme ça, je peux contrôler à part ce qui concerne la réplication). Les paramètres de ce fichier sont les suivants :
wal_level = 'hot_standby'
max_wal_senders = 5
archive_mode = on
archive_command = 'rsync %p /var/lib/postgresql/9.5/archives/%f'
J'ai créé un utilisateur dédié à la réplication, que j'ai appelé "replitest". Il se connecte sans problème, via psql, à l'instance maître. J'ai ajouté une ligne dans le pg_hba.conf du serveur maître comme suit :
host replication replitest 127.0.0.1/32 md5
Le maître a redémarré sans souci et l'archivage des journaux fonctionne.
J'ai ensuite créé un serveur esclave à l'aide de l'outil pg_basebackup, comme suit :
sudo -u postgres pg_basebackup -D /home/CDuprez/PostgreSQL/9.5/esclave -P -h 127.0.0.1 -U replitest
J'ai recopié les fichiers de configuration du maître vers l'esclave, en prenant soin d'enlever le pointage vers le fichier postgresql.replication.conf...
J'ai mis à jour le postgresql.conf de l'esclave comme suit :
data_directory = '/home/CDuprez/PostgreSQL/9.5/esclave'
hba_file = '/etc/postgresql/9.5/esclave/pg_hba.conf'
ident_file = '/etc/postgresql/9.5/esclave/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.5-esclave.pid'
port = 5433
Donc, normalement, il devrait être configuré comme il faut.
Ce serveur est bien détecté, en vérifiant avec pg_lsclusters.
J'ai configuré, sur le serveur esclave, un fichier replication.conf avec les paramètres suivants :
restore_command = 'rsync /var/lib/postgresql/9.5/archives/%f %p'
standby_mode = on
primary_conninfo = 'host=127.0.0.1 port=5432 user=replitest password=mon_passe'
trigger_file = '/tmp/esclave_autonome'
Mais le démarrage du serveur esclave pose problème. J'ai le message d'erreur suivant :
Warning: connection to the database failed, disabling startup checks:
psql: FATAL: le système de bases de données se lance
Dans les logs de l'esclave, voici ce qu'on observe :
rsync: link_stat "/var/lib/postgresql/9.5/archives/000000010000000400000030" failed: No such file or directory (2)
rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1183) [sender=3.1.0]
2016-04-25 20:16:36 CEST [1486-1] LOG: Commence le flux des journaux depuis le principal à 4/30000000 sur la timeline 1
2016-04-25 20:37:02 CEST [1288-1] LOG: a reçu une demande d'arrêt intelligent
2016-04-25 20:37:02 CEST [1486-2] FATAL: arrêt du processus walreceiver suite à la demande de l'administrateur
2016-04-25 20:37:02 CEST [1329-1] LOG: arrêt en cours
2016-04-25 20:37:02 CEST [1329-2] LOG: le système de base de données est arrêté
2016-04-25 20:37:03 CEST [8548-1] LOG: le système de bases de données a été arrêté pendant la restauration à 2016-04-25 20:37:02 CEST
2016-04-25 20:37:03 CEST [8548-2] LOG: entre en mode standby
rsync: link_stat "/var/lib/postgresql/9.5/archives/000000010000000400000030" failed: No such file or directory (2)
rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1183) [sender=3.1.0]
2016-04-25 20:37:03 CEST [8548-3] LOG: état de restauration cohérent atteint à 4/30000370
2016-04-25 20:37:03 CEST [8548-4] LOG: la ré-exécution commence à 4/30000370
2016-04-25 20:37:03 CEST [8548-5] LOG: longueur invalide de l'enregistrement à 4/30000450
2016-04-25 20:37:03 CEST [8555-1] LOG: Commence le flux des journaux depuis le principal à 4/30000000 sur la timeline 1
2016-04-25 20:37:04 CEST [8557-1] [inconnu]@[inconnu] LOG: paquet de démarrage incomplet
2016-04-25 20:37:04 CEST [8560-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:05 CEST [8563-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:05 CEST [8566-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:06 CEST [8570-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:06 CEST [8573-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:07 CEST [8576-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:07 CEST [8579-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:08 CEST [8582-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:08 CEST [8585-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:09 CEST [8598-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:09 CEST [8604-1] postgres@postgres FATAL: le système de bases de données se lance
2016-04-25 20:37:09 CEST [8605-1] [inconnu]@[inconnu] LOG: paquet de démarrage incomplet
Effectivement, dans le répertoire d'archivage, il n'y a pas de fichier 000000010000000400000030...
D'où peut venir le problème ? Comment l'esclave récupère-t-il le nom des journaux à rejouer ?
Merci pour votre aide,
Cédric
Alors ceci explique cela.
Je sais à présent que dès qu'on utilise des tables temporaires, il est bon de procéder manuellement à un VACUUM ANALYZE pour ne pas perdre l'optimiseur de requêtes.
Merci pour toute votre aide.
La version de PostgreSQL sur ce serveur est la 9.4.4.
L'autovacuum est activé.
Mais les deux tables en question sont des tables temporaires. Ce qui explique peut-être une partie du problème, non ?
Précision : ces tables ont tout de même une clé primaire et sont indexées.
Il va vraiment falloir que j'apprenne à lire un explain...
Voici le plan de requête à l'issue d'un ANALYZE sur chacune des tables (la requête s'exécute très rapidement) :
Merge Join (cost=9149.08..9706.91 rows=1164 width=84) (actual time=92.203..110.693 rows=17816 loops=1)
Merge Cond: ((p5.absc = n.absc) AND (p5.ord = n.ord))
-> Sort (cost=4244.33..4306.88 rows=25023 width=46) (actual time=33.797..35.399 rows=21788 loops=1)
Sort Key: p5.absc, p5.ord
Sort Method: quicksort Memory: 2471kB
-> Seq Scan on pts5 p5 (cost=0.00..2416.28 rows=25023 width=46) (actual time=2.037..19.254 rows=21788 loops=1)
Filter: ((tirmax > 1) AND ("poi$" = '1'::bpchar) AND (nincref = 7))
Rows Removed by Filter: 59142
-> Sort (cost=4904.66..5022.27 rows=47043 width=42) (actual time=58.393..63.883 rows=46758 loops=1)
Sort Key: n.absc, n.ord
Sort Method: external sort Disk: 2664kB
-> Seq Scan on noeuds n (cost=0.00..1253.72 rows=47043 width=42) (actual time=0.020..11.331 rows=47009 loops=1)
Filter: (zpop = '1'::text)
Rows Removed by Filter: 7929
Planning time: 0.693 ms
Execution time: 111.634 ms
Bonjour à tous les deux et merci pour l'aide que vous m'apportez.
Voici le résultat d'un EXPLAIN ANALYZE sans avoir désactivé les NestedLoop :
QUERY PLAN
Nested Loop (cost=6.42..2185.25 rows=1 width=302) (actual time=7.269..196975.150 rows=17816 loops=1)
Join Filter: ((p5.absc = n.absc) AND (p5.ord = n.ord))
Rows Removed by Join Filter: 1024214276
-> Seq Scan on pts5 p5 (cost=0.00..1700.00 rows=1 width=148) (actual time=0.033..51.698 rows=21788 loops=1)
Filter: ((tirmax > 1) AND ("poi$" = '1'::bpchar) AND (nincref = 7))
Rows Removed by Filter: 59142
-> Bitmap Heap Scan on noeuds n (cost=6.42..481.12 rows=275 width=158) (actual time=2.517..5.569 rows=47009 loops=21788)
Recheck Cond: (zpop = '1'::text)
Heap Blocks: exact=12353796
-> Bitmap Index Scan on nd_zpop_idx (cost=0.00..6.35 rows=275 width=0) (actual time=2.469..2.469 rows=47009 loops=21788)
Index Cond: (zpop = '1'::text)
Planning time: 0.490 ms
Execution time: 196976.834 ms
Et voici le résultat de l'EXPLAIN ANALYZE après désactivation des NestedLoop :
QUERY PLAN
Hash Join (cost=485.25..2185.34 rows=1 width=302) (actual time=25.282..62.898 rows=17816 loops=1)
Hash Cond: ((p5.absc = n.absc) AND (p5.ord = n.ord))
-> Seq Scan on pts5 p5 (cost=0.00..1700.00 rows=1 width=148) (actual time=0.040..15.750 rows=21788 loops=1)
Filter: ((tirmax > 1) AND ("poi$" = '1'::bpchar) AND (nincref = 7))
Rows Removed by Filter: 59142
-> Hash (cost=481.12..481.12 rows=275 width=158) (actual time=25.226..25.226 rows=47009 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 3489kB
-> Bitmap Heap Scan on noeuds n (cost=6.42..481.12 rows=275 width=158) (actual time=5.866..15.316 rows=47009 loops=1)
Recheck Cond: (zpop = '1'::text)
Heap Blocks: exact=567
-> Bitmap Index Scan on nd_zpop_idx (cost=0.00..6.35 rows=275 width=0) (actual time=5.745..5.745 rows=47009 loops=1)
Index Cond: (zpop = '1'::text)
Planning time: 0.187 ms
Execution time: 63.409 ms
N'étant pas DBA et n'ayant pas ces connaissances, je ne suis pas trop au fait de la lecture de ces résultats.
Bonjour,
Je me pose une question sur l'utilité du "nested loop" dans la stratégie choisie par l'optimiseur de requête de PostgreSQL.
Je m'explique : je constate très souvent des requêtes drastiquement longues (plus de 30 secondes pour 20 000 lignes) pour lesquelles le temps devient inférieur à 1 seconde dès lors qu'on passe le paramètre enable_nestloop à FALSE. La plupart du temps, quand j'ai des requêtes longues, c'est comme ça que je résout le problème... et l'écart est énorme avant et après le changement de ce paramètre.
Quelle est l'utilité de cette stratégie ?
Pourquoi l'optimiseur y a-t-il facilement recours ?
Quels peuvent être les risques si, au niveau de la base de donnée, je passe ce paramètre à FALSE par défaut ?
Merci d'avance pour vos éclaircissements.
Cédric
Merci pour toute ces réponses.
Je vais me contenter de révoquer les droits de création pour le moment, ça suffira.
Cordialement.
Cédric
Bonjour,
Un utilisateur qui n'a que des droits de lecture sur une base peut visiblement créer toutes les vues qu'il souhaite : celles-ci se retrouvent dans le schéma "public" (le CREATE VIEW y fonctionne).
Y a-t-il un moyen d'empêcher la création de ces vues dans ce schéma public ou pas ?
Si oui, comment faire ?
Merci d'avance pour votre aide.
Cedric
Bonjour Guillaume,
Merci pour cette aide, qui m'a mise sur la bonne voie.
J'avais bien re-configuré ma variable PATH...
J'ai donc fait un locate pg_config, et il y en avait un qui trainait dans /usr/bin (qui est aussi dans le PATH).
C'est très étrange, parce qu'il n'y a pas eu d'installation de PostgreSQL 9.3 via des binaires sur cette machine. D'ailleurs, un locate psql ne pointe que sur le répertoire d'installation de la 9.4.
Quoi qu'il en soit, après suppression de ce dernier pg_config, tout est rentré dans l'ordre.
Encore merci,
Cédric
Bonjour,
Sur un serveur Linux, je disposais jusque-là de la version 9.3 de PostgreSQL, compilée à partir des sources.
Pour la mise à jour en 9.4, j'ai donc compilé également les sources, avec pour seul paramètre de configuation ./configure --prefix=/opt/postgreSQL/9.4
La version précédente 9.3 était également installée sous /opt/postgreSQL/9.3.
La migration s'est très bien passée, tout fonctionne parfaitement.
Sauf que, quand j'exécute pg_config, voici ce que je récupère :
BINDIR = /usr/lib/postgresql/9.3/bin
DOCDIR = /usr/share/doc/postgresql-doc-9.3
HTMLDIR = /usr/share/doc/postgresql-doc-9.3
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/9.3/server
LIBDIR = /usr/lib
PKGLIBDIR = /usr/lib/postgresql/9.3/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/9.3/man
SHAREDIR = /usr/share/postgresql/9.3
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/9.3/lib/pgxs/src/makefiles/pgxs.mk
...
Bref, la version renvoyée n'est pas à jour (9.3), et quoi qu'il en soit, les variables ne pointent pas sur les bons répertoires (y compris concernant la version 9.3).
Qu'est-ce que j'aurais raté dans ma configuration ?
Comment mettre à jour les infos renvoyées par pg_config ?
Merci d'avance pour votre aide,
ced
Je regretterais presque d'avoir compris... Ça va m'obliger à créer un mapping utilisateur par utilisateur réellement connecté, mais qu'à cela ne tienne.
Merci pour les confirmations.
Bonjour,
Je commence à tester postgres_fdw pour connecter deux serveurs distants, dont les bases de données sont complémentaires.
Sur mon serveur1 (celui sur lequel je veux installer les tables distantes contenues physiquement dans le serveur2), j'ai un utilisateur user1 qui est membre d'un rôle serveur1_admin. Ce rôle possède tous les droits sur serveur1 (quasi super-user).
Voici ce que ça donne :
-------------------------------------------------------------------------------------
CREATE SERVER serv2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'serveur2', dbname 'base2', port '5432');
ALTER SERVER serv2 OWNER TO serveur1_admin;
-------------------------------------------------------------------------------------
Je crée un mapping utilisateur pour serveur1_admin sur mon serveur2 (en utilisant un utilisateur user2 qui a les droits de SELECT sur serveur2) :
-------------------------------------------------------------------------------------
CREATE USER MAPPING FOR serveur1_admin SERVER serv2 OPTIONS (user 'user2', password 'pwd2');
-------------------------------------------------------------------------------------
Je crée un nouveau schéma sur serveur1 pour recevoir les tables étrangères de serveur2 :
-------------------------------------------------------------------------------------
CREATE SCHEMA base2
AUTHORIZATION production_admin;
GRANT ALL ON SCHEMA base2 TO serveur1_admin;
GRANT USAGE ON SCHEMA base2 TO public;
-------------------------------------------------------------------------------------
Je crée une table étrangère sur serveur1 :
-------------------------------------------------------------------------------------
CREATE FOREIGN TABLE base2.matable
(
col1 INT NOT NULL,
col2 character varying(12),
col3 character varying(12),
col4 character varying(50)
)
SERVER serv2
OPTIONS (schema_name 'schema2', table_name 'matable_sur_serveur2');
ALTER FOREIGN TABLE base2.matable
OWNER TO serveur1_admin;
-------------------------------------------------------------------------------------
Maintenant, si je suis connecté comme user1, la requête sur la table distante échoue avec le message suivant :
ERROR: user mapping not found for "user1"
État SQL :42704
Cela signifie-t-il que si user1 est membre de serveur1_admin, qui a les droits pour interroger la table distante, je suis obligé de créer un mapping user pour chaque utilisateur membre de serveur1_admin ?
Ou bien est-ce que j'ai raté une étape ?
Ce qui est étonnant (quoi que), c'est qu'un user mapping avec PUBLIC fonctionne bien (mais c'est sans doute dû au caractère particulier du rôle PUBLIC) :
-------------------------------------------------------------------------------------
CREATE USER MAPPING FOR public SERVER serv2 OPTIONS (user 'user2', password 'pwd2');
-------------------------------------------------------------------------------------
Là, la requête sur la table étrangère renvoie un résultat conforme.
Merci d'avance pour votre aide,
ced
Bon, il va donc falloir que j'essaie de contourner le problème.
Merci pour l'info.
J'ai jeté un œil sur les spécifications de xpath 1.0 et les fonctions name() et local-name() en font pourtant bien partie...
Bonjour,
J'essaie une interrogation XPATH d'un champ XML pour récupérer le nom d'un noeud et je récupère systématiquement une chaîne vide.
Prenons un exemple simple qui reproduit le problème :
Créons un élément XML :
SELECT XMLPARSE(DOCUMENT '<unite>toto</unite>')Si je veux récupérer le nom du noeud (ici "unite"), normalement, la requête suivante devrait fonctionner :
SELECT XPATH('name(/*)', XMLPARSE(DOCUMENT '<unite>toto</unite>'))Or, ça renvoie un tableau vide ({}).
Comment faire pour récupérer le nom "unite" ?
Merci d'avance de votre aide,
ced
Merci pour ces éléments de réponse.
La première requête me laisse tout de même perplexe : pourquoi une somme sur un nombre stocké en real entraîne un tel écart de total au final (plusieurs unités) ?
Quand on regarde la documentation de PostgreSQL, il est précisé que le type real a une précision d'au moins 6 chiffres décimaux.
Sachant que ma table contient 250000 lignes, je devrais avoir un erreur de l'ordre de 0,25 et pas de plusieurs unités ?
ced
Bonjour,
Je suis en PostgreSQL 8.4.3 sous RedHat.
J'observe un comportement curieux quand je fais la somme sur une colonne de type REAL (nombre à virgule flottante sur 4 octets).
En effet, si je fais la requête suivante :
SELECT SUM(macolonne1) AS total_colonne1 FROM matableJ'obtiens le total suivant : 275375
Si je décompose ensuite selon une seconde colonne, par la requête suivante :
SELECT macolonne2, SUM(macolonne1) AS total_colonne1 FROM matable GROUP BY macolonne2J'obtiens le résultat suivant :
macolonne2 total_colonne1
0 54754,8
1 54601,1
2 55598,3
3 55248
4 55178
dont le total fait 275380,2.
Enfin, si j'exécute la requête suivante, en typant ma colonne explicitement :
SELECT SUM(macolonne1::float) AS total_colonne1 FROM matableJ'obtiens un total de 275381,66...
Quand j'exporte toutes mes lignes dans un outil permettant de faire la somme sur la colonne 1 (logiciel R), le total que m'affiche cet outil est 275381,66.
Bref, je suis surpris d'observer de tels écarts. A quoi cela peut-il être dû ?
Et surtout, que dois-je faire dans mes différentes requêtes qui utilisent des sommes (ou autres fonctions d'agrégations) pour être certain que mes totaux soient le plus juste possible ? Faut-il systématiquement caster explicitement ?
Merci de votre aide,
ced
C'est une excellente idée. J'ai finalement opté pour la solution avec table_to_xml en passant par une table temporaire.
Voici ce que ça donne :
CREATE OR REPLACE FUNCTION test.audit_update_offline () RETURNS TRIGGER AS
$body$
BEGIN
CREATE TEMP TABLE tblchg AS SELECT OLD.*;
INSERT INTO audit.data_change (tbl_id, chg_user, chg_datetime, chg_delete, chg_xml_data)
SELECT lt.tbl_id, CURRENT_USER, CURRENT_TIMESTAMP, 0, (SELECT * FROM TABLE_TO_XML('tblchg', false, false, ''))
FROM audit.liste_tables lt
WHERE lt.tbl_schema = TG_TABLE_SCHEMA
AND lt.tbl_name = TG_TABLE_NAME;
DROP TABLE tblchg;
RETURN NULL;
END;
$body$
LANGUAGE plpgsql;Pour info, j'ai choisi de ne pas utiliser les solutions déjà existantes parce qu'elles ont le défaut de créer une table d'audit par table auditée. Or, dans mon cas, je préfère une seule table d'audit.
Encore merci de ton aide,
ced
Bonjour,
Je tente de mettre en place un système d'audit des tables d'une base de données, via un système de triggers en PL/pgSQL sous PostgreSQL 8.4.
J'en suis à tenter d'insérer les anciennes données (avant mise à jour ou suppression) dans une table d'audit.
Pour m'en sortir, j'ai choisi une solution qui consiste à transformer en XML tout le contenu de la ligne avant changement, et stocker ça dans un champ de type xml.
Seulement, impossible de trouver comment transformer le contenu du record OLD en XML.
Voici ce que j'essaie :
CREATE OR REPLACE FUNCTION test.audit_delete_offline () RETURNS TRIGGER AS
$body$
BEGIN
INSERT INTO audit.data_change (tbl_id, chg_user, chg_datetime, chg_delete, chg_xml_data)
SELECT lt.tbl_id, CURRENT_USER, CURRENT_TIMESTAMP, 1, XMLFOREST(OLD.*)
FROM audit.liste_tables lt
WHERE lt.tbl_schema = TG_TABLE_SCHEMA
AND lt.tbl_name = TG_TABLE_NAME;
RETURN NULL;
END;
$body$
LANGUAGE plpgsql;J'ai stocké dans une table (audit.liste_tables) toutes les tables sur lesquelles l'enregistrement des modifications doit s'enregistrer.
Je crée ensuite mon trigger sur ma table à auditer.
Quand je supprime un enregistrement, j'ai l'erreur suivante :
ERROR: row expansion via "*" is not supported here
LINE 1: ...d, CURRENT_USER, CURRENT_TIMESTAMP, 0, XMLFOREST( $1 .*) FRO...
J'ai tenté plusieurs choses, notamment en essayant d'utiliser la fonction QUERY_TO_XML, mais rien n'y fait... Je ne trouve pas de solution.
D'où ma question : comment est-ce que je peux transformer en XML tout le contenu de la variable OLD dans un trigger ?
Merci d'avance à ceux qui voudront bien m'aider...
ced