Linux:Administration PostgreSQL

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

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;

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)

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;

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

connexions

• 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 mémoire et disques

Il faut d'abord vérifier au niveau système :

sysctl -a|grep kernel.shm

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

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

Gestion des processus

Voir les process en cours :

select * from pg_stat_activity;

Taille des bases et tables

Tables :

SELECT pg_relation_size('table');

Bases :

SELECT pg_database_size('base');

Pour un affichage converti :

SELECT pg_size_pretty(pg_database_size('base'));

Liste complète des bases :

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

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

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)

select * from pg_settings where name like '%temp%';

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

SET default_tablespace = espace1;
SELECT spcname FROM pg_tablespace;

Logs :

log_destination = 'stderr'
logging_collector = on
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_temp_files = 1MB
log_line_prefix = '%t - %u - %d - %r'
log_statement='ddl'
log_min_duration_statement = 1000

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

Connaître le taux d'occupation du shared_buffer

SELECT count(*) * 100/4096 AS "% du cache utilise" 

FROM pg_buffercache 

WHERE relfilenode IS NOT NULL;

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;

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;

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)

wal_buffers = 16MB
checkpoint_segments=32
checkpoint_completion_target = 0.9

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

exemple /tmp/local_backup_script.sh

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

forcer le changement de fichier wal:

select pg_switch_xlog();