:PROPERTIES:
:ID:       171ce2f7-4028-47b0-b4e0-5a4a6ccb74ac
:mtime:    20230729082927
:ctime:    20220104155310
:END:
#+title: Postgres
#+filetags: :database:

* Principales caractéristiques
 * Licence de type BSD,
 * Utilise des types de données modernes, dits composés ou enrichis,
 * Fonctionne sur Solaris, SunOS, Mac OS X, HP-UX, AIX, Linux, IRIX, Digital Unix, BSD, NetBSD, FreeBSD, OpenBSD, SCO unix, NeXTSTEP, UnixWare et toutes sortes d'Unix. Depuis la version 8.0, PostgreSQL fonctionne également nativement sur Windows.

* Interfaces utilisateur
** CLI
 * [[id:0455921f-3ac0-437e-ba76-1afb3f6f85ea][Psql]]
** Graphique
 * [[https://arctype.com/][ArcType]]
 * [[https://github.com/dbeaver/dbeaver][DBeaver]]

* Howto
** Pour lister les utilisateurs
#+BEGIN_SRC sql
\du
#+END_SRC

** Pour supprimer un utilisateur
#+BEGIN_SRC shell
sudo -u postgres dropuser -e <user>
#+END_SRC
** Pour supprimer une base de données
#+BEGIN_SRC shell
sudo -u postgres dropdb <database>
#+END_SRC
** Pour modifier le password d'un utilisateur (dans psql)
#+BEGIN_SRC sql
\password <user>
#+END_SRC

* Profiling de requêtes (query plan)
** Depuis [[id:0455921f-3ac0-437e-ba76-1afb3f6f85ea][Psql]]
#+BEGIN_SRC sql
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT _stops.kind, _stops.id, _stops.name, _stops.town_name, _stops.postal_region, _stops.xepsg2154, _stops.yepsg2154, _stops.version, _stops.created_ts, _stops.changed_ts, stops.id AS id_1, stops.latitude, stops.longitude, stops.transport_mode, stops.accessibility, stops.visual_signs_available, stops.audible_signs_available, stops.record_id, stops.record_ts, stop_areas.id AS id_2, stop_areas.type
FROM _stops LEFT OUTER JOIN stops ON _stops.id = stops.id LEFT OUTER JOIN stop_areas ON _stops.id = stop_areas.id
WHERE _stops.name ILIKE '%Chaville rive droite%'
#+END_SRC

#+BEGIN_SRC
 Nested Loop Left Join  (cost=148.62..250.06 rows=5 width=194) (actual time=1.213..1.346 rows=12 loops=1)
   Output: _stops.kind, _stops.id, _stops.name, _stops.town_name, _stops.postal_region, _stops.xepsg2154, _stops.yepsg2154, _stops.version, _stops.created_ts, _stops.changed_ts, stops.id, stops.latitude, stops.longitude, stops.transport_mode, stops.accessibility, stops.visual_signs_available, stops.audible_signs_available, stops.record_id, stops.record_ts, stop_areas.id, stop_areas.type
   Inner Unique: true
   Buffers: shared hit=123
   ->  Nested Loop Left Join  (cost=148.33..208.53 rows=5 width=182) (actual time=1.196..1.296 rows=12 loops=1)
         Output: _stops.kind, _stops.id, _stops.name, _stops.town_name, _stops.postal_region, _stops.xepsg2154, _stops.yepsg2154, _stops.version, _stops.created_ts, _stops.changed_ts, stops.id, stops.latitude, stops.longitude, stops.transport_mode, stops.accessibility, stops.visual_signs_available, stops.audible_signs_available, stops.record_id, stops.record_ts
         Inner Unique: true
         Buffers: shared hit=97
         ->  Bitmap Heap Scan on public._stops  (cost=148.04..166.99 rows=5 width=93) (actual time=1.172..1.219 rows=12 loops=1)
               Output: _stops.kind, _stops.id, _stops.name, _stops.town_name, _stops.postal_region, _stops.xepsg2154, _stops.yepsg2154, _stops.version, _stops.created_ts, _stops.changed_ts
               Recheck Cond: ((_stops.name)::text ~~* '%Chaville rive droite%'::text)
               Heap Blocks: exact=11
               Buffers: shared hit=63
               ->  Bitmap Index Scan on name_idx_gin  (cost=0.00..148.04 rows=5 width=0) (actual time=1.155..1.155 rows=12 loops=1)
                     Index Cond: ((_stops.name)::text ~~* '%Chaville rive droite%'::text)
                     Buffers: shared hit=52
         ->  Index Scan using stops_pkey on public.stops  (cost=0.29..8.31 rows=1 width=89) (actual time=0.005..0.005 rows=1 loops=12)
               Output: stops.id, stops.latitude, stops.longitude, stops.transport_mode, stops.accessibility, stops.visual_signs_available, stops.audible_signs_available, stops.record_id, stops.record_ts
               Index Cond: (stops.id = _stops.id)
               Buffers: shared hit=34
   ->  Index Scan using stop_areas_pkey on public.stop_areas  (cost=0.29..8.30 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=12)
         Output: stop_areas.id, stop_areas.type
         Index Cond: (stop_areas.id = _stops.id)
         Buffers: shared hit=26
 Planning:
   Buffers: shared hit=22
 Planning Time: 1.015 ms
 Execution Time: 1.521 ms
(28 rows)

Time: 3.503 ms
#+END_SRC

** Visualisation de Query plan
Différents sites permettent de visualiser graphiquement les query plans:
 * Gratuits:
   * [[https://explain.dalibo.com/][PEV2 - Dalibo]]
 * Payants:
   * [[https://app.pgmustard.com/login][PgMustard]]

** Audit
Possibilité de logger ou de stocker en base les ajout/modifications ou suppressions de donnéées:
 * logs: [[https://www.pgaudit.org/]]
 * En base: [[https://wiki.postgresql.org/wiki/Audit_trigger_91plus]] ([[https://github.com/2ndQuadrant/audit-trigger]])

* Références
 * [[https://stackoverflow.com/questions/45395538/postgres-md5-password-plain-password][Postgres: MD5 Password / Plain password - Github]]
 * [[https://www.postgresql.org/docs/current/sql-explain.html][Sql-explain - PostgreSQL]]