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 configurationpg_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 unpg_hba.conf
où on autorise le standby à se connecter. - créer un
postgresql.conf
avec les conf standby. La conf la plus importante estprimary_conninfo
pour se connect au primary - si
hot_standby=on
, créer un fichierstandby.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
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
oushow 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, leover
sera toujours traité apres lewhere
- l'expression doit correspondre à une window,