Aller au contenu

Postgresql

Psql

Action Command
Inline command psql -U user -c "my request"
List databases \l
List tables \d
List users \du
List default permissions \ddp
List permissions for database \dp
List permissions for table \z mytable
Switch to list mode \x
Connect to DB \c
Insert local file \i /tmp/file

Note: Psql commands can be inserted in sql scripts.

Objects

Privileges

Default privileges must be created before any objects. The objects then inherit from these defaults. * add Default privileges: alter default privileges in schema <name> grant all privileges on tables from <role> * Remove alter default privileges in schema <name> revoke all privileges on tables from <role>

Configuration & data

Data directory = PGDATA env var

  • Defaults to /var/lib/postgresql/data
  • Can be set in postgresql.conf > data_directory
  • Contains also logs
  • This is the directory to backup / docker volume

Main configuration files should be provided in another directory

  • Defaults to /etc/postgresql/
  • postgresql.conf: main configuration
  • pg_hba.conf: controls allowed connections. Pg_hba directory can be set in postgresql.conf > hba_file
  • All configuration files are processed and inserted in pg meta databases. Postgresql.conf is fully mapped to pg_setting database
  • Copies can also be found in the data dir.
Action Command
Show data directory show data_directory
Show log directory show log_directory
Verify any postgresql.conf setting select setting from pg_setting where name='<setting>'

Activer les logs

dans postgresql.conf

logging_collector = on
log_connections = on
log_disconnections = on
log_hostname = on
Action Command
Verify logging process show logging_collector

note: rien ne sera loggé tant que le logging_collector est off

Upgrading versions

Major version: internal data storage is subject to change. Either dump (pg_dumpall from the new version) -> reload the database, or pg_upgrade.

All versions: look at the migration section in the releases notes

  • administration: CHANGE how to monitor
  • sql: ADD new sql commands
  • system catalogs
  • c-language api

  • Test client applications

  • Setup multiple conccurent applications with different db versions

Internals

write-ahead log (WAL)

fonctionnement

  • Quand une transaction est commit, elle est loggée dans le WAL
  • Puis un flush est réalisé et la transaction passe sur le disque
  • Si on a synchronous_commit = on, commit se termine uniquement quand le WAL flush est terminé. Voir replication.

autres

  • un checkpoint = un threshold (configurable) à partir duquel le WAL flush
  • Le WAL sert aussi à reconstruire la BDD suite à un crash (quand le mode Archive est activé)
  • Stocké dans pg_xlog/

Replication

Action Commande
Process réplication (primary) select * from pg_stat_replication;
Wal reciever (standby) select * from pg_stat_wal_receiver;

Streaming Replication

Aussi appelée: binary, native, built-in, SR

  • Mode de réplication par défaut. Simple à mettre en place mais peu de configurabilité (tout est répliqué)
  • Asynchrone
  • Data partition possible (ex: le master crash et n'a pas eu le temps de faire le sender)

Le master/primary server prend le rôle de sending server. Il envoie les changements du WAL aux standby server avec le process interne "wal sender."

Le standby server utilise le process "wal receiver" pour récupérer les infos.

Howto configure

  • créer un postgresql.conf avec les conf spécifiques primary (nom du standby, wal_level replica etc). En plus de la conf postgresql.conf, le primary doit avoir un user avec les droits de replication, un replication slot et un pg_hba.conf où on autorise le standby à se connecter.
  • créer un postgresql.conf avec les conf standby. La conf la plus importante est primary_conninfo pour se connect au primary
  • si hot_standby=on, créer un fichier standby.signal dans le standby pour le faire reconnaître en tant que standby.
  • la data du standby doit être une copie parfaite du primary; Utiliser pg_basebackup pour copier les données du primary dans le standby (pg_basebackup utilise le process de replication, il faut ajouter une ligne dans le pg_hba.conf pour permettre le standby d'atteindre le primary avec l'user et base replication)

Cascading Replication

Comme la Streaming Replication, mais les standby peuvent aussi envoyer des infos WAL (et donc avoir le rôle sending server)

Logical Replication

décode le WAL et retransforme en SQL

Synchronous Replication

Configuration avancée de la Streaming Replication. On s'assure que le standby a bien récéptionné les données avant de valider la transaction!

  • écriture dans le WAL
  • écriture dans le standby

Le niveau

Query planner

Transactions

VACUUM: process qui récupère les tuples utilisés par aucune transaction et redonne de l'espace

Stats

citusdata: statistics

ANALYZE: calcule des stats sur la BDD et stocke dans la table pg_statistic et pg_class. ANALYZE est lancé par autocacuum. Mais sinon c'est conseillé de faire un ANALYZE après chaque grosse opération/changement sur la BDD. On peut faire analyze verbose pour avoir plus di'nfos

par table: select * from pg_stats where tablename = 'thing'

Optimisations & Performance

  • Les defaults de postgres sont orientés compatibilité, pas performance: tuning obligatoire! Tuning Postgresql server
  • Voir tous les settings : show all ou show max_connections pour voir un setting particulier

Connection Pooling

  • Postgres n'est pas multi-threadé. Il spawn un nouveau process pour chaque connection client (voir max_connections)
  • Autrement dit, quand il y a bcp de connections courtes, overhead d'ouverture/fermeture et multiplication des process
  • Avec la méthode du pooling, notion de connection virtuelle:

    • quand une connection est refermée pour le client, elle ne l'est pas pour Postgres et retourne dans un pool de connections disponibles à être réutilisées
      • de connections peuvent être gérées sans trop d'overhead pour Postgres

Pgpool

écosystème: pgpoolAdmin, pgpool_pgctl, pgpool_recovery

Config mémoire

  • shared_buffers: caching memoire Postgres. Règle officielle: 1/4 de la mémoire système
  • work_mem: mémoire allouée pour les sort. Attention, le réglage est par user.
  • maintenance_work_mem: mémoire allouée pour les opérations de maintenance comme VACUUM, CREATE INDEX etc. A augmenter temporairement pour les restaurations de dump par exemple. Sinon mettre 10%.
  • effective_cache_size: estimation taille du caching disque (ce n'est pas une allocation, utilisé par le query planner pour estimer s'il utilise la RAM ou le disque). Mettre 75% de la RAM.

Insertion de masse

COPY, pas INSERT

Materialized views

  • Une view normale est juste une query prédéfinie. Quand on appelle la vue, on appelle la query... (create view documentation)
  • Une materialized view (create materialized view masupervue as) est un snapshot du résultat de la query mis dans une table.
  • Il faut donc utiliser la commande refresh materialized view masupervue pour remettre à jour TOUTE la vue (pas de maj partielle). Possibilités: eager update avec des trigger (bof...), lazy update (expiration...)

Data types

JSON representation

  • Les opérateurs -> et ->> permettent de query l'intérieur du json

JSON:

  • Stocke l'input tel quel sans modification (duplicatas, ordre...).
  • Doit etre parsé ) chaque exécution

JSONB:

  • Stocke en format binaire, plus compact
  • La conversion initiale prend du temps mais ensuite, pas de re-parsing à chaque exécution.
  • Support des index sur le type jsonb
  • Attention: pas de statistiques récupérables donc query planning mauvais? when to avoid jsonb

HSTORE

HSTORE: type de donnée key-value (compatible avec les index). Attention, pas de nesting! Utiliser JSONB pour ça.

Exemple: la colonne 'data' est un HSTORE:

INSERT INTO mytable(data) VALUES ('foo=>1, bar=>abc, baz=>stuff');

Pour récupérer une valeur dedans, il faut bien sûr appeler la clé:

SELECT data->'bar' from mytable; retourne abc

Index

index B-Tree:

  • Les single column accélèrent les recherches sur >, <, = etc.
  • Multi-column (composite) marche en fonction de l'ordre, si l'index est sur A,B,C il faut au moins avoir A,B en clause
  • Expression index: c'est une expression qui est indexée !
  • Partial index: pas obligé d'indexer la totalité de la colonne, on utilise un WHEN pour limiter

select * from pg_stat_user_indexes; combien de fois un index est utilisé

index GIN/GIST

SQL specific commands

array_agg

renvoie un array formé de plusieurs valeurs récupérées en requête sql

postgres@localhost:postgres> select array_agg(x) from generate_series(3,10) as x
+---------------------------+
| array_agg                 |
|---------------------------|
| [3, 4, 5, 6, 7, 8, 9, 10] |
+---------------------------+

Attention: generate_series est proprietaire à Postgres...

### window function

  • operateur over dans le select. Attention, le over sera toujours traité apres le where
  • l'expression doit correspondre à une window,