Linux:Administration PostgreSQL

De Djjnet
Aller à : Navigation, rechercher

Sommaire

Généralités

Sous postgresql, nous parlons d'instance, une instance est composée de :

  • Une zone mémoire
  • Un processus père postmaster
  • Des processus d'arrières plan
  • Une arborescence (racine de l'instance, des tablesspaces spécifiques)
  • Des fichiers journaux
  • Un port d'écoute

Les bases de données sont identifiées par un OID et disposent d'une arborescence de dossiers et fichiers de données.

Les blocks sont de tailles de 8K et est définie par instance. Dans le cas de base décisionnelle, il faut augmenter la taille du block.

Les process :

/usr/local/pgsql/bin/postgres -D /var/lib/postgres
\_ postgres: logger process                          
\_ postgres: checkpointer process                    
\_ postgres: writer process                          
\_ postgres: wal writer process                      
\_ postgres: autovacuum launcher process             
\_ postgres: stats collector process

A la différence de MySQL qui utilise le multithreads, Postgresql fonctionne en mode processus. En cas de problème, chaque processus est isolé.

Les relations sont les tables, indexes etc... Les espaces de noms sont les schémas et les catalogues. Pour une base il y a au moins le schéma public et les catalogues information_schema et pg_catalog.

Initialisation

Le groupe de base de données ou l'espace de stockage des bases de données est initialisée par initdb. Le dossier doit appartenir à l'utilisateur de postgresql.

root# mkdir /var/lib/pgsql/data
root# chown postgres /var/lib/pgsql/data
root# su - postgres
postgres$ initdb -d /var/lib/pgsql/data

Les bases postgres et template1 sont alors créées.

Il est possible de spécifier :

  • --encoding=UTF8
  • --locale=C
  • --xlogdir=/chemin_vers_les_fichiers_wal

Exemple :

initdb -d /var/lib/pgsql/data --encoding=UTF8 --locale=fr_FR --xlogdir=/var/log/pgsql/xlog --username=postgres

Par défaut, les droits sont initialisés à "trust" dans le fichier pg_hba.conf.

Définir le dossier par défaut :

PGDATA=/var/lib/pgsql/data
export PGDATA

(dans ce cas, on peut faire initdb sans le -d)

Lancement manuel :

pg_ctl -D $PGDATA start

ou avec la valeur par défaut :

pg_ctl start

Rôles et droits d'accès

Sous postgres >=8.1, il n'y a pas de notion réelle d'utilisateur ou groupe, on parle de rôles.

CREATE ROLE nom_utilisateur;
DROP ROLE nom_utilisateur;

Attention au droit de connexion : CREATE ROLE nom LOGIN; <=> CREATE USER nom;

Depuis le shell :

createuser nom_utilisateur
dropuser nom_utilisateur

Voir les rôtes :

SELECT rolname FROM pg_roles;
SELECT * FROM pg_roles;

ou

\du

Les attributs de rôles

  • SUPERUSER (souvent donné à l'utilisateur postgres) : à tous les droits
  • LOGIN : permettre la connexion
  • CREATEDB : autoriser la création de base de données
  • CREATEROLE : autoriser la création d'autres rôles
  • PASSWORD : mot de passe pour le rôle (non lié au mot de passe système)
ALTER ROLE nom WITH PASSWORD 'pass';
ALTER ROLE nom CREATEROLE CREATEDB;

Désactiver un attribut :

ALTER ROLE test NOCREATEROLE;

Supprimer un rôle :

DROP ROLE nom;

Changer le rôle propriétaire d'un élément :

ALTER SCHEMA stock OWNER TO nouveau_role;

Création d'un rôle avec tous les attributs spécifiés

CREATE ROLE test WITH PASSWORD 'password' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

Les droits

Par défaut, un objet est accessible à celui qui l'a créé, il est possible de donner des droits à d'autres rôles, par exemple :

GRANT SELECT ON nom_table TO nom_role;

Il est possible d'utiliser PUBLIC désignant tous les rôles et ALL pour tous les droits :

GRANT ALL ON nom_table TO PUBLIC;

Pour enlever un droit :

REVOKE UPDATE ON nom_table FROM nom_role;

Il est possible de créer des pseudo-groupes permettant de donner des droits à un rôle ou plusieurs rôles identiques :

CREATE ROLE nom_groupe;

Ajouter ou enlever des rôles de ce groupe :

GRANT nom_groupe TO nom_role1, ... ;
REVOKE nom_groupe FROM nom_role1, ... ;

Ainsi si nom_groupe a des droits d'écriture sur un objet que role1 n'a pas, avec l'appartenance au groupe nom_groupe, cela sera le cas.

Attention, un rôle n'hérite pas directement des droits CREATEROLE, CREATEDB, LOGIN ou SUPERUSER. Il est possible d'utiliser la commande suivante pour changer de role :

SET ROLE nom_role

Exemple d'ajout de droit à un rôle puis vérification des droits :

test=# grant select , insert, update, delete on matable to titi;
GRANT
test=# \dp matable
                                   Droits d'accès
 Schéma |   Nom   | Type  |      Droits d'accès       | Droits d'accès à la colonne 
--------+---------+-------+---------------------------+-----------------------------
 public | matable | table | postgres=arwdDxt/postgres+| 
        |         |       | titi=arwd/postgres        | 
(1 ligne)

Opérations de bases

Le client psql

  • Connexion à une base :
psql nom_base

Pour quitter :

nom_base=#  \q
  • Connexion vers un serveur distant :
psql -h 172.16.45.7 -U user -W
  • Utilisateur actuel :
pmx_quarantine=# SELECT CURRENT_USER AS utilisateur_courant;
 utilisateur_courant
---------------------
 pmx6
(1 row)
  • Version de postgresql :
nom_base=# SELECT version();
  • Encodage courant :
test=# \encoding
UTF8
  • Quitter :
test=# \q

Choix de la base

  • Lister les bases :
nom_base=#  \l

ou

nom_base=# SELECT datname FROM pg_database;

ou par la commande

psql -l
  • une fois connecter, changer de base :
postgres=# \c test
  • Base actuelle :
pmx_quarantine=#  SELECT CURRENT_DATABASE() AS base_courante;
 base_courante
----------------
 pmx_quarantine

Entrée et sortie depuis/vers un fichier

  • Lancer des commandes d'un fichier source :
nom_base=# \i fichier.sql

ou

psql -U user -f fichier.sql base
  • Envoyer la sortie vers un fichier :
nom_base=# \o sortie.txt

Information sur les tables, utilisateurs etc...

  • Lister les tables :
test=# \dt
          Liste des relations
 Schéma |  Nom   | Type  | Propriétaire 
--------+--------+-------+--------------
 public | entier | table | postgres
  • Lister les indexes ou vues : \di et \dv
  • Lister les utilisateurs :
test=# \du
                                    Liste des rôles
 Nom du rôle |                          Attributs                           | Membre de 
-------------+--------------------------------------------------------------+-----------
 postgres    | Superutilisateur, Créer un rôle, Créer une base, Réplication | {}
  • Décrire une table, indexe, vue ou séquence :
\d nom
test=# \d entier
      Table « public.entier »
 Colonne |  Type   | Modificateurs 
---------+---------+---------------
 i       | integer | 
  • Liste des schémas :
test=# \dn
   Liste des schémas
  Nom   | Propriétaire 
--------+--------------
 public | postgres
(1 ligne)

Création et suppression de bases de données

  • Créer un base de données :
createdb nom_base

Créer une base du même nom que l'utilisateur courant :

createdb

Créer une base ayant pour propriétaire un autre rôle :

 createdb -O nom_role nom_base

En utilisant la syntaxe SQL, une fois connecté :

nom_base=# CREATE DATABASE nom_base OWNER nom_role;

Toutes les options :

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ CONNECTION LIMIT [=] connlimit ] ]
  • Supprimer une base :
dropdb nom_base

Utilisation des templates :

CREATE DATABASE réalise une copie d'une base template, par défaut template1. Il est possible d'en spécifier une autre :

CREATE DATABASE nom_base TEMPLATE template0;

Il est possible ainsi de modifier template1 pour que la modification soit réalisée à toute nouvelle base. Il ne faut pas modifier template0 pour toujours avoir une base vierge.

Utilisation des tablespaces

Les tablespaces sont des espaces de stockages pour les données et indexes.

Vous pouvez lister les tablespaces existants :

SELECT spcname FROM pg_tablespace;

Il y a par défaut pg_default utilisé par les bases de données template1 et template0. Il est donc celui des bases de données créées avec ces templates. Le deuxième tablespace est pg_global utilisé pour les catalogues système partagés.

Pour créer un tablespace :

CREATE TABLESPACE espace_bases LOCATION '/data/postgresql/data2';

A la création d'un tablespace, le dossier doit exister et avoir les droits pour l'utilisateur postgres :

postgres=# CREATE TABLESPACE espace_bases LOCATION '/tmp/datapostgres';
ERREUR:  n'a pas pu configurer les droits du répertoire « /tmp/datapostgres » : Aucun fichier ou répertoire de ce type
postgres@beaufix:~/8.1/main/pg_tblspc$ mkdir /tmp/datapostgres
postgres@beaufix:~/8.1/main/pg_tblspc$ ls -ld /tmp/datapostgres/
drwxr-xr-x 2 postgres postgres 4096 2009-12-28 22:24 /tmp/datapostgres/
postgres=# CREATE TABLESPACE espace_bases LOCATION '/tmp/datapostgres';
CREATE TABLESPACE
postgres=# SELECT * FROM pg_tablespace;
  spcname    | spcowner |    spclocation    | spcacl
--------------+----------+-------------------+--------
pg_default   |       10 |                   |
pg_global    |       10 |                   |
espace_bases |       10 | /tmp/datapostgres |
(3 lignes)

Autre façon de les lister :

\db
SELECT * FROM pg_catalog.pg_tablespace;


Les tablespaces sont gérés par postgresql avec des liens symboliques dans le dossier pg_tblspc du dossier de data principal. Par exemple sous Debian Etch, il se situe dans /var/lib/postgresql/8.1/main/pg_tblspc :

postgres@beaufix:~/8.1/main/pg_tblspc$ ls -l
total 0
lrwxrwxrwx 1 postgres postgres 17 2009-12-28 22:24 77632 -> /tmp/datapostgres

Pour spécifier un tablespace lors de la création d'un objet comme une table :

CREATE TABLE nom_table(i int) TABLESPACE espace_bases;

ou

SET default_tablespace = espace_bases;
CREATE TABLE nom_table(i int);

Renommer un tablespace :

ALTER TABLESPACE tbs_index RENAME TO tbs_indexes;

Suppression :

DROP TABLESPACE IF EXISTS tbs_data;

Changement de tablespace:

alter table clients set tablespace tbs_data2;

Les schémas

Les schémas sont des espaces de noms dans une base de données où sont placés les objets (les tables notamment). Par défaut, le schéma est "public".

Pour les lister :

test=# \dt
          Liste des relations
 Schéma |  Nom   | Type  | Propriétaire 
--------+--------+-------+--------------
 public | entier | table | postgres

Il peut y avoir plusieurs objets d'un même nom s'ils sont dans des schémas différents.

Il faut référencer le schéma devant l'objet, par exemple, :

select * from public.entier;

est équivalent à :

select * from entier;

La variable search_path définis les schémas recherchés si aucun schéma n'est précisé, elle vaut par défaut :

"$user",public

Pour la modifier dans la session courante :

set search_path=nom_schema;

Pour créer un schéma :

CREATE SCHEMA nom;

Pour le supprimer :

DROP SCHEMA nom;

Pour supprimer le contenu :

DROP SCHEMA nom CASCADE;

Langages procéduraux

Postgresl permet d'utiliser plusieurs langages pour les procédures stockées.

Pour voir la liste des langages créés pour une base :

test=# select lanname from pg_language where lanispl = true;
 lanname 
---------
 plpgsql

Pour créer un langage sur une base :

test=# CREATE LANGUAGE plperl;
ERROR:  could not access file "$libdir/plperl": No such file or directory
STATEMENT:  CREATE LANGUAGE plperl;
ERROR:  could not access file "$libdir/plperl": No such file or directory

Celui-ci doit être disponible. Dans le cas d'une installation par compilation, il faut par exemple ajouter :

./configure --with-perl

plperl.so est alors présent dans le dossier des lib et il n'y a plus d'erreur :

test=# CREATE LANGUAGE plperl;
CREATE LANGUAGE

Une autre manière de l'ajouter à une base :

createlang -U postgres -W plpgsql nom_base

Configuration de postgresql

Le fichier de configuration principal est postgresql.conf et se situe par défaut dans le dossier de stockage des bases de données. Il peut se situer dans un autre dossier s'il est précisé au démarrage avec par exemple :

/usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -config_file=/etc/postgresql/8.3/main/postgresql.conf

Sauf exceptions, les paramètres peuvent être pris en compte grâce à la commande :

pg_ctl reload

Sous psql, il est possible de voir les paramètres courants :

show all;
show autovacuum; #uniquement le paramètre autovacuum

Il existe aussi le catalogue pg_settings :

select * from pg_settings;
select * from pg_settings where name like '%temp%';
select name,setting,unit,category,context from pg_settings where name='shared_buffers';
postgres=# select distinct context from pg_settings ;
  context   
------------
 backend
 user
 internal
 postmaster
 superuser
 sighup

Les différents niveaux de variables sont :

  • internal : nécessite une recompilation
  • postmaster : nécessite un redémarrage pour modification
  • sighup : nécessite l'envoi du signal SIGHUP pour recharger les paramètres de ce niveau dans le fichier de configuration
  • backend : ne nécessite pas de relance de postgresql comme sighup mais sont conservés jusqu'à la fin d'une session
  • superuser : modifiable avec la commande SET mais pour les superutilisateurs
  • user : modifiable avec la commande SET

Il est possible de modifier un paramètre pour une session particulière de deux façons :

env PGOPTIONS='-c parametre=valeur' psql
SET parametre TO valeur;

Paramètres de postgresql.conf

connexions et requêtes
  • listen_addresses

Sur quelles interfaces postgresql écoute (localhost par défaut si le paramètre est en commentaire) :

listen_addresses = 'localhost'

-> exemple pour une connexion distante autorisée :

listen_addresses = '*'

Il est aussi possible de passer l'argument -i au démarrage pour autoriser les connexions distantes (tcp).

  • max_connections

Nombre maximum de connexions à 100 par défaut :

max_connections = 100
  • superuser_reserved_connections

Il s'agit du nombre de connexions réservée pour les superutilisateurs sur le nombre totales de connexions autorisées (max_connections).

  • statement_timeout

Par défaut désactivé, il est possible de limiter le temps maximal d'exécution d'une requête.

  • default_transaction_isolation

Le niveau d'isolation des transactions est par défaut à read committed

Gestion de la mémoire

Postgresql utilise deux types de buffers, globaux ou de session, on peut aussi parler de buffers partagés et de buffers locaux.

Les principaux sont :

  • Partagés :
    • shared_buffers (32 Mo par défaut) : mémoire partagée par tous les process pour l'accès aux blocks de données (la valeur mise ne peut dépasser shmmax). Il ne faut généralement pas dépasser 25% de la RAM disponible. (Attention, pour postgresql <8.2, la taille est indiqué en nombre de blocs de 8K)
    • effective_cache_size : paramètre utilisé par le plan d'exécution pour estimer combien de donnés sont en cache OS, la valeur peut être déterminé grace aux graphes munin qui montrent le cache pour la partie mémoire. Ce paramètre est important et trop bas par défaut, il peut avoir des conséquences sur le temps d'exécution d'une requête. Article intéressant : http://www.cybertec.at/2013/11/effective_cache_size-better-set-it-right/
    • wal_buffers (64 Ko par défaut) : buffer d'accès aux segments WAL, quelques Mo est généralement suffisant mais pour simplifier, doit être paramétré à 16Mo comme la taille des segments WAL.
    • Le cache FSM n'est plus utilisé depuis la 8.3, nous n'en parlerons pas.
    • Les verrous : Correspond à max_connections*max_lock_per_transaction : Par défaut, max_connections * 64. Cela est négligeable de nos jours et suffisant.
  • Locaux (ou de sessions) :
    • temp_buffers (8 Mo par défaut) : buffer pour les tables temporaires
    • work_mem (1Mo par défaut) : utilisée pour les tris et les hashs (order by, group by)
    • maintenance_work_mem (16M par défaut) : utilisé pour les VACCUM FULL, CREATE INDEX ..., mettre 100Mo par Go disponible.

Un processus peut allouer plusieurs fois work_mem s'il a plusieurs tris à réaliser. La mémoire de sessions maximale (max_connecions *(temp_buffers+work_mem+maintenance_work_mem) n'est donc qu'approximative. L'idéal pour maintenance_work_mem est d'allouer une plus forte valeure pour un ROLE dédié à la maintenance.

Pour détailler shared_buffer : shared_buffers est consulté à chaque lecture de données, si celle-ci n'est pas présente, elle y est copiée. Il faut vérifier au niveau système les paramètres de la mémoire partagée :

sysctl -a|grep kernel.shm
kernel.shmmax = 33554432
kernel.shmall = 2097152

Pour connaître la taille de chaque PAGE de mémoire, vous pouvez utiliser :

getconf PAGESIZE
4096

Comme dans la plupart des configurations de kernel, la valeur est de 4Ko. shmall est en nombre de pages, on a donc dans l'exemple 8Go maximum de mémoire partagée.

Pour shmmax, en octet, il y a dans l'exemple 32Mo maximum pour un segment de mémoire partagée. Ce qui convient uniquement pour la valeur par défaut de shared_buffers. Il faudra l'augmenter pour correspondre à shared_buffers.

La lecture dans ce cache provoque des locks consultables par pg_locks :

SELECT * FROM pg_locks;
Logs

Par défaut, les logs seront mis dans le sous-dossier pg_log du dossier de données de l'instance. Les logs sont activés par logging_collector à on.

logging_collector = on
log_destination = 'stderr'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y%m%d.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
#log_connections = on
#log_disconnections = on
log_checkpoints = on
log_lock_waits = on # log si attente de plus de deadlock_timeout pour avoir un verrou.
log_temp_files = 1MB
log_line_prefix = '%t - %u - %d - %r'
log_statement='ddl'
log_min_duration_statement = 1000
#checkpoint_warning = 30s #Envoie un message si le temps entre les checkpoints sont supérieurs à checkpoint_warning


Pour log_line_prefix :

%u  utilisateur 
%d  base de données  
%r  nom ou adresse IP de l'hôte distant et port distant
%h  nom d'hôte distant ou adresse IP

Paramètres des pg_hda.conf

Ce fichier détermine les autorisations d'accès des clients vers le serveur de base de données. Il se situe par défaut dans le dossier des données, par exemple /var/lib/postgresql/8.3/main/pg_hda.conf.

Attention, les lignes sont lues dans l'ordre d'apparission dans le fichier.

Un ligne correspond à :

TYPE  DATABASE        USER            ADDRESS                 METHOD

Les TYPE sont :

  • local : connexion par une socket unix (la colonne ADDRESS est alors vide)
  • host : connexion chiffrée ou non par TCP/IP
  • hostssl : connexion chiffrée par TCP/IP
  • hostnossl : connexion non chiffrée par TCP/IP

La colonne BASE peut recevoir :

  • all : n'importe quelle base
  • samerole, sameuser : la base a le même nom que le ROLE ou l'UTILISATEUR
  • un nom de base
  • une liste séparée de virgule

La colonne USER peut recevoir :

  • all : n'importe quel role
  • +ROLE : un role représentant un groupe
  • ROLE : un role précis
  • une liste séparée de virgule

La colonne ADDRESS est au format CIDR-address, par exemple : 172.20.143.89/32 pour une adresse précise ou 172.20.143.0/24 pour un masque.

La colonne METHOD peut recevoir :

  • trust : autoriser sans mot de passe
  • reject : ne pas autoriser la connexion
  • md5 : vérifier le mot de passe fourni
  • crypt ou password : ne pas utiliser
  • d'autres méthodes sont disponibles, voir la documentation officielle

VACUUM et ANALYSE

VACUUM permet de libérer l'espace disque suite à des DELETE ou UPDATE.

VACUUM ne libère pas réellement l'espace disque mais marque l'espace comme réutilisable. Il faut lancer un VACUUM FULL pour libérer de l'espace mais cela n'est pas forcément efficace en terme de performance si les tables sont souvent modifiées.

ANALYSE lui maintient à jour les données de statistiques pour améliorer le plan d'exécution des requêtes.

VACUUM et ANALYSE sont exécutés automatiquement depuis la version 8.1 avec l'auto-vacuum.

Attention, track_counts doit être à On pour que l'auto-vacuum soit actif. Il est actif par défaut depuis la version 8.3.

L'autovacuum ne libère que l'espace à la fin du fichier.

Pour les anciennes versions de postgresql, vous pouvez mettre une tâche de maintenance ainsi :

Créer le fichier /etc/cron.daily/postgresmaintenance

#!/bin/bash
/usr/local/pgsql/bin/psql  -U postgres -c "VACUUM;" > /dev/null
/usr/local/pgsql/bin/psql  -U postgres -c "ANALYSE;" > /dev/null

REINDEX

REINDEX { INDEX | TABLE | DATABASE | SYSTEM } nom [ FORCE ]

Administration

Arborescence des dossiers/fichiers

Considérons l'initialisation suivante : /usr/local/pgsql/bin/initdb -D /var/lib/postgres. Ainsi que CREATE DATABASE test;

L'OID de la base indique le répertoire sur le système de fichiers :

postgres=# SELECT oid,datname FROM pg_database;
  oid  |  datname  
-------+-----------
     1 | template1
 12015 | template0
 12020 | postgres
 16384 | test
ls -l /var/lib/postgres/base/
total 24
drwx------ 2 postgres users 12288 Oct  9 00:05 1
drwx------ 2 postgres users  4096 Oct  9 00:04 12015
drwx------ 2 postgres users  4096 Oct  9 00:05 12020
drwx------ 2 postgres users  4096 Oct  9 00:06 16384

Pour les tables, le fichier est identifié par son relfilenode :

test=# CREATE TABLE entier (i int);
test=# SELECT relname,relfilenode FROM pg_class WHERE relname='entier';
 relname | relfilenode 
---------+-------------
 entier  |       16385

Sur le FS :

ls -l /var/lib/postgres/base/16384/16385 
-rw------- 1 postgres users 0 Oct  9 00:11 /var/lib/postgres/base/16384/16385

Ajoutons une donnée et vérifions qu'un bloc de 8k est créé :

test=# INSERT into entier VALUES (1);
ls -l /var/lib/postgres/base/16384/16385 
-rw------- 1 postgres users 8192 Oct  9 00:14 /var/lib/postgres/base/16384/16385

Les journaux binaires WAL

Ils sont dans le dossier pg_xlog et sont gérés par le wal writer process (depuis la version 8.3).

Ces journaux conservent les transactions modifiant les données. Par un système de checkpoint, le writer process écrit régulièrement dans les fichiers de données les modifications. Un point de contrôle est écrit à chaque fois pour connaître les transactions qui ne sont plus utiles dans les fichiers WAL et seront réutilisés ultérieurement. Ils sont réutilisés de façon cyclique, un peu comme une FIFO.

Il est possible de gérer des archivages des fichiers WAL afin de rejouer ensuite ceux-ci après une restauration.

Les fichiers WAL sont appelés segments, un point de contrôle est lancé à chaque fois que :

  • checkpoint_segments segments sont actifs
  • checkpoint_timeout secondes se sont écoulées depuis le dernier point de contrôle
  • la commande checkpoint est lancée.

Pour forcer le changement de fichier wal:

select pg_switch_xlog();

Chaque fichier a une taille de 16Mo et des pages de 8Ko, pour le changer il faut recompiler avec les options :

  • --with-wal-segsize
  • --with-wal-blocksize

Le paramétrage pour les WAL pourra être dans la plupart des cas :

wal_buffers = 16MB
checkpoint_segments=10 #Jusque 32 si le contrôleur raid possède une batterie
checkpoint_completion_target = 0.9

Catalogues systèmes

Postgres dispose d'un ensemble de catalogues permettant d'obtenir un ensemble d'informations sur les bases, schémas, tables etc...

Voir : http://docs.postgresql.fr/current/catalogs.html

Généralités
  • Liste des bases
postgres=# select * from pg_database;
  • Liste des tablespaces
postgres=# select * from pg_tablespace;
  • Liste des schémas
postgres=# select * from pg_namespace;
  • Liste des tables d'une base
postgres=# select * from pg_class;
  • Liste des rôles
postgres=# select * from pg_roles;
  • Les types supportés par postgresql
postgres=# select * from pg_type;
  • Connaître l'OID d'une base de données
postgres=# select oid, datname from pg_catalog.pg_database;
  • Connaître le référence sur le FS d'une table

postgres=# select relfilenode, relname from pg_catalog.pg_class;

Taille des bases, tables et tablespace

Tables :

SELECT pg_relation_size('table');

Bases :

SELECT pg_database_size('base');

Tablespaces :

SELECT pg_ablespace_size('tablespace');

Pour un affichage converti :

SELECT pg_size_pretty(pg_database_size('base'));

Liste complète des bases avec leur taille :

SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database order by pg_database_size(pg_database.datname) desc;
  datname  |  size   
-----------+---------
 test      | 5990 kB
 postgres  | 5982 kB
 template1 | 5889 kB
 template0 | 5881 kB

Collecteur de statistiques

Généralités

Correspond au processus :

postgres: stats collector process

Les données collectées sont utilisées par l'optimiseur de requête.

Il est activé par la variable :

postgres=# show track_counts;
 track_counts 
--------------
 on

Réinitialiser les statistiques de la base courante :

test=# select pg_stat_reset();
 pg_stat_reset 
---------------
 
(1 ligne)
  • Statistiques sur les tables système :
select * from pg_stat_sys_tables;
select * from pg_statio_sys_tables;
  • Statistiques sur les tables utilisateurs :
select * from pg_stat_user_tables;
select * from pg_statio_user_tables;
  • Statistiques sur les indexes utilisateurs :
select * from pg_stat_user_indexes;
select * from pg_statio_user_indexes;
  • Statistiques sur une base :
select * from pg_stat_database;
Requêtes utiles

Statistiques sur les tables et indexes :

  • Pourcentage d'accès par index comparé aux seq_scan :
SELECT schemaname, relname, seq_scan, idx_scan,
cast(idx_scan AS numeric) / (idx_scan + seq_scan) AS pourcent_idx_scan
FROM pg_stat_user_tables
WHERE (idx_scan + seq_scan) > 0
ORDER BY pourcent_idx_scan;
  • Pourcentage de lignes traitées par index comparé aux seq_scan :
SELECT relname, seq_tup_read, idx_tup_fetch,
cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS pourcent_idx_tup_scan
FROM pg_stat_user_tables
WHERE (idx_tup_fetch+seq_tup_read) > 0
ORDER BY pourcent_idx_tup_scan;
  • Pourcentage sur les types de modifications : INSERT, UPDATE et DELETE:
SELECT relname, cast(n_tup_ins AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS insert_pct,
cast(n_tup_upd AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS update_pct,
cast(n_tup_del AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS delete_pct
FROM pg_stat_user_tables
WHERE (n_tup_ins + n_tup_upd + n_tup_del)> 0
ORDER BY relname;
  • Pourcentage d'accès en cache des pages par tables :
SELECT relname,
cast(heap_blks_hit AS numeric) / (heap_blks_hit + heap_blks_read) AS pourcent_hit,
heap_blks_hit,heap_blks_read
FROM pg_statio_user_tables
WHERE (heap_blks_hit + heap_blks_read)>0
ORDER BY pourcent_hit;
  • Pourcentage d'accès en cache des indexes par tables :
SELECT relname,
cast(idx_blks_hit AS numeric) / (idx_blks_hit + idx_blks_read) AS pourcent_hit,
idx_blks_hit,idx_blks_read
FROM pg_statio_user_tables
WHERE (idx_blks_hit + idx_blks_read)>0
ORDER BY pourcent_hit;
  • Pourcentage d'accès en cache des indexes :
SELECT indexrelname,
cast(idx_blks_hit AS numeric) / (idx_blks_hit + idx_blks_read) AS pourcent_hit,
idx_blks_hit,idx_blks_read
FROM pg_statio_user_indexes
WHERE (idx_blks_hit + idx_blks_read)>0
ORDER BY pourcent_hit;

Checkpoints :

  • Ecriture des pages modifiés sur disque :
SELECT pg_size_pretty((buffers_checkpoint*block_size)/(checkpoints_timed + checkpoints_req)) as avg_checkpoint_write,
100 * buffers_checkpoint / (buffers_checkpoint + buffers_clean + buffers_backend) as checkpoint_write_pct,
100 * buffers_backend / (buffers_checkpoint + buffers_clean + buffers_backend) as backend_writen_pct
FROM pg_stat_bgwriter, (SELECT cast(current_setting('block_size') as integer) as block_size) as taille_bloc;

Gestion des processus et locks

  • Voir les process en cours :
select * from pg_stat_activity;
  • Voir les locks en cours :
select * from pg_locks;
  • Nombre de clients connectés :
SELECT count(*) FROM pg_stat_activity WHERE NOT procpid=pg_backend_pid();
  • Liste des processus actuels avec leur durée d'exécution et leur éventuel attente de verrou :
SELECT pid,waiting,state,current_timestamp - least(query_start,xact_start) AS runtime,
substr(query,1,25) AS current_query
FROM pg_stat_activity
WHERE NOT pid=pg_backend_pid();
SELECT 
locked.pid AS locked_pid,
locker.pid AS locker_pid,
locked_act.usename AS locked_user,
locker_act.usename AS locker_user,
locked.virtualtransaction,
locked.transactionid,
locked. locktype
FROM
pg_locks locked,
pg_locks locker,
pg_stat_activity locked_act,
pg_stat_activity locker_act
WHERE
locker.granted=true AND
locked.granted=false AND
locked.pid=locked_act.procpid AND
locker.pid=locker_act.procpid AND
(locked.virtualtransaction=locker.virtualtransaction OR
locked.transactionid=locker.transactionid);

pg_buffercache

  • Installation

pg_buffercache est une contribution à ajouter à l'installation de base. Pour chaque base, il faut activer la procédure et la vue associée.

D'abord, il faut compiler l'extension si vous avez installé postgres depuis les sources :

cd postgresql-9.2.2/contrib/pg_buffercache/
make
make install

make install reprendra la configuration de compilation de postgres.

Pour l'utiliser, il faut lancer :

postgres=# CREATE EXTENSION pg_buffercache;
CREATE EXTENSION


  • Connaître le taux d'occupation du shared_buffer, il faut d'abord connaître le nombre de blocs dans le cache :
postgres=# select count(*) from pg_buffercache;
 count  
--------
 131072

puis remplacer dans :

SELECT count(*) * 100/131072 AS "% du cache utilise" 
FROM pg_buffercache 
WHERE relfilenode IS NOT NULL;
djj=# select * from entier;
 id 
----
  1
  2
(2 rows)

djj=#  SELECT count(*) * 100/131072 AS "% du cache utilise" 
 FROM pg_buffercache 
 WHERE relfilenode IS NOT NULL;
 % du cache utilise 
--------------------
                  0

Il y a peu de données par rapport à la taille du buffer.

  • Visualisation des relations qui sont dans le buffer_cache :
SELECT rel.relname AS "Relation",
count(*) AS "Nb de tampons dans le cache"
FROM pg_buffercache AS buf, pg_class AS rel
WHERE buf.relfilenode=rel.relfilenode
GROUP BY rel.relname
ORDER BY count(*) DESC;

Il y a surtout des catalogues systèmes et aussi la table entier :

             Relation              | Nb de tampons dans le cache 
-----------------------------------+-----------------------------
 pg_operator                       |                          15
 pg_amproc_fam_proc_index          |                          12
 pg_index                          |                          12
 pg_depend_reference_index         |                          11
 ...
 pg_default_acl_role_nsp_obj_index |                           1
 pg_largeobject_metadata_oid_index |                           1
 pg_language                       |                           1
 entier                            |                           1
(40 rows)
  • Voir le cache d'une table :
djj=#  SELECT buf.* FROM pg_buffercache AS buf, pg_class AS rel
 WHERE buf.relfilenode=rel.relfilenode and rel.relname='entier';
-[ RECORD 1 ]--+------
bufferid       | 491
relfilenode    | 16388
reltablespace  | 1663
reldatabase    | 16384
relforknumber  | 0
relblocknumber | 0
isdirty        | f
usagecount     | 3

Insérons une nouvelle donnée :

djj=# insert into entier values (3);
INSERT 0 1

Puis revisualison le cache :

djj=#  SELECT buf.* FROM pg_buffercache AS buf, pg_class AS rel
 WHERE buf.relfilenode=rel.relfilenode and rel.relname='entier';
-[ RECORD 1 ]--+------
bufferid       | 491
relfilenode    | 16388
reltablespace  | 1663
reldatabase    | 16384
relforknumber  | 0
relblocknumber | 0
isdirty        | t
usagecount     | 4

isdirty est à t car le bloc a été modifié en mémoire et que le CHECKPOINT n'a pas encore été réalisé. Il repassera à f ensuite.



Résumé du contenu du cache, avec leur pourcentages (très utile)

Requête permettant de visualiser la quantité de données de chaque table présente en cache, mais

également le pourcentage que cette quantité représente par rapport à sa taille totale.

SELECT c.relname, pg_size_pretty(count(*)*8192) as BUFFERED,
round(100.0*count(*) / (SELECT setting FROM pg_settings 
WHERE name='shared_buffers')::integer,1) as BUFFERS_POURCENT,
round(100.0*count(*)*8192 / pg_relation_size(c.oid),1) as POURCENT_DE_LA_RELATION
FROM pg_class c INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database())
AND c.relname like '%'
GROUP BY c.oid, c.relname
ORDER BY 3 DESC
LIMIT 10;

Distribution du cache par utilisation

Requête permettant de comptabiliser les blocs de chaque relation par leur compteur

d'utilisation, de manière à voir précisément comment les scores d'utilisation

sont distribués pour chaque table

SELECT c.relname, count(*) as BUFFERS, usagecount
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database())
AND c.relname like '%account%'
GROUP BY c.oid, c.relname, usagecount
ORDER BY 3 DESC
LIMIT 10;

cache, bgwriter et pg_stat_bgwriter

Comme vu avant, postgres met toute donnée lue et/ou modifiée en cache. Il faut invalider ce cache en cas de besoin de place. Il y a pour cela un compteur usagecount qui vaudra au maximum 5. Il est incrémenté à chaque utilisation et décrémenté à chaque fois qu'un espace est recherché. Les blocs à 0 sont réutilisables.

-[ RECORD 1 ]--+------
bufferid       | 491
relfilenode    | 16388
reltablespace  | 1663
reldatabase    | 16384
relforknumber  | 0
relblocknumber | 0
isdirty        | f
usagecount     | 3

Si le bloc sélectionné pour réutilisation est modifié (isdirty), postgres va devoir l'écrire à la place de bgwriter. Ce n'est alors pas bon pour les performances.

djj=# SELECT buffers_checkpoint, buffers_backend, buffers_alloc FROM pg_stat_bgwriter;
-[ RECORD 1 ]------+----
buffers_checkpoint | 126
buffers_backend    | 1
buffers_alloc      | 870

Dans le cas de l'exemple ci-dessus, 1 bloc a été écrit pas postgres et 126 par bgwriter, il n'y a pas de problème.

Si le rapport est trop important pour buffers_backend, il faut alors adapter bgwriter_delay et peut être aussi bgwriter_lru_maxpages.

Sauvegardes et restauration de données

Sauvegarde logique

#!/bin/bash
save_dir=/var/backup/postgres
madate=`date +%Y%m%d`
mkdir -p $save_dir
chown postgres $save_dir
/usr/bin/find $save_dir -type f -mtime +7 -exec rm -f {} ';' > /dev/null
su - postgres -c "pg_dumpall --globals-only > $save_dir/globals-${madate}.sql"
for bdd in $(su - postgres -c "psql -l -t" |awk '{print $1}'|grep -v template)
do
              $(su - postgres -c "pg_dump -F c -f $save_dir/${bdd}-${madate}.dump ${bdd}")
done

Au format SQL :

su - postgres -c "pg_dump ${bdd} > $save_dir/${bdd}-${madate}.sql"

Quelques options :

  • -C : ajouter la création de la base
  • -a : dumper les données
  • -s : dumper la structure de la base
  • -h et -p : pour une sauvegarde distante (host et port)

Exemple de sauvegarde d'une table au format SQL avec des INSERT au lieu de COPY :

pg_dump -U postgres -F p --inserts -t schema.table test --file=table.sql

Sauvegarde complète d'une instance

$ psql
postgres=# select pg_start_backup('LABEL');
postgres=# \q

Réaliser le tar du dossier de données.

$ psql
postgres=# select pg_stop_backup();
postgres=# \q

Le nom indiqué à la fonction pg_start_backup sera concervé dans le fichier $PGDATA/backup_label.

Un fichier $PGDATA/pg_xlog/***.backup est créé pour indiquer le dernier journal utilisé avant sauvegarde.

Analyse des requêtes

EXPLAIN [ ANALYZE ] [ VERBOSE ] requête
  • ANALYZE : exécute la requête (attention au insert,update,delete...), permet d'avoir les temps réels d'exécution
  • VERBOSE :


divers

http://pgfoundry.org/

  • verrouillage au niveau enregistrement
  • pas de limitation du nombre de coeur
  • pas possible de faire des hints comme oracle pour préciser le plan d'exécution
  • test cpu :
create database myperf; 
\c myperf
\timing
SELECT sum(generate_series) FROM generate_series(1,1000000);
  • test cpu+io disques/ram
CREATE TABLE test (id INTEGER PRIMARY KEY);
INSERT INTO test VALUES (generate_series(1,1000000));
EXPLAIN ANALYSE SELECT COUNT(*) FROM test;


\h CREATE TABLE;


postgres=# \timing
Chronométrage activé.
postgres=# \timing 
Chronométrage désactivé.


postgres=# checkpoint;
CHECKPOINT
postgres=# show shared_buffers;
 shared_buffers 
----------------
 32MB
(1 ligne)


SET default_tablespace = espace1;
SELECT spcname FROM pg_tablespace;



Cache disque :

hdparm -I /dev/sda|grep -i "write cache"
	   *	Write cache
hdparm -W 0 /dev/sda

/dev/sda:
  setting drive write-caching to 0 (off)
  write-caching =  0 (off)

hdparm -I /dev/sda|grep -i "write cache"
	    	Write cache


 myperf=# select count(*) from test;
  count  
 ---------
  1000000
 (1 ligne)

Temps : 120,269 ms

myperf=# select count(*) from test;


myperf=# select pg_size_pretty(CAST(pg_relation_size('test')/120.269 * 1000 AS int8)) AS BYTES_PAR_SECOND;
 bytes_par_second 
------------------
 255 MB
(1 ligne)


Test sans cache, stop postgres puis cache os :

sync
echo 3 > /proc/sys/vm/drop_caches 

relance postgres

On recommence \timing + select count(*) from test; on obtient par exemple 805,495 ms

Affichage étendu activé :

\x



checkpoint_segments : 10 mini et jusque 32 si le cache controleur dispose d'une batterie.

fsync = off
synchronous_commit = off
wal_sync_method = fsync_writethrough (windows)
wal_sync_method = open_sync (linux)


archive_mode = on              
archive_command = '/tmp/local_backup_script.sh "%p" "%f"'

exemple /tmp/local_backup_script.sh

#!/bin/bash
cp $1 /tmp/$2