Linux:Administration PostgreSQL

Un article de Djjnet.

Sommaire

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.

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


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;

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

Opérations de bases

  • Connexion à une base : 
psql nom_base

Pour quitter : 

nom_base=#  \q
  • Lister les bases : 
nom_base=#  \l

ou 

nom_base=# SELECT datname FROM pg_database;

ou par la commande 

psql -l
  • Version de postgresql :

Une fois connecté à une base avec psql : 

nom_base=# SELECT version();
  • Lancer des commandes d'un fichier source : 
nom_base=# \i fichier.sql

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;
  • 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.

Paramétrage/Options pour une base de données

Ajouter le langage plpgsql à une base : 

createlang -U postgres -W plpgsql nom_base

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.

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)

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);

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 la table virtuelle pg_settings : 

select * from pg_settings;

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

  • 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).


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.

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.

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

Sauvegardes et restauration de données

#!/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
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
Récupérée de « http://www.dj-j.net/waka/Linux:Administration_PostgreSQL »