PostgreSQL, un serveur de bases de données relationnelles.

Ci-dessous, quelques astuces indispensables à beaucoup d'utilisateurs de Posgresql.
Le vaste sujet du paramètrage

'Psql' est une interface d'administration de la base de données Postgresql, en mode texte. Elle permet de réaliser la plupart des tâches et opérations sur la base.

Exemple de connexion à la base de données :

psql -U postgres -d template1

Quelques commandes de 'psql' :

Commandes Commentaires
psql -U login -d base accéder à une base
\q pour sortir
\c autre_base pour se connecter à 'autre_base'
\l liste des bases de l'instance; # psql -l
\d liste des tables
\d nom_table description d'une table
\db liste des tablespaces
\du liste des rôles, des utilisateurs # select * from pg_user; pg_group;
\o fichier redirige le résultat vers 'fichier'; \o (équivalent du 'spool off')
\z liste des privilèges
\i fichier.sql execute un script sql, équivalent de 'psql -f fichier.sql'
\x auto affichage horrizontal ou vertical, optimisé en fonction de la longueur du résultat

Gestion des utilisateurs

Pour créer un administrateur. Attention, ici, il aura les mêmes droits que l'utilisateur 'postgres', c'est à dire tous :

CREATE ROLE nom LOGIN SUPERUSER;

On créé l'utilisateur 'nom' qui pourra créer des bases de données :

CREATE ROLE nom LOGIN CREATEDB CREATEROLE PASSWORD 'mdp'; 

Création de la base 'nom_base' :

CREATE DATABASE nom_base ; 

Ici, elle est explicitement encodée en UTF-8 :

CREATE DATABASE nom_base ENCODING 'UTF8' TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';

L'utilisateur 'user_2' fait partie du role “nom”. Il peut donc accéder à ses objets :

CREATE ROLE user_2 LOGIN IN ROLE nom ; 

Les droits de l'utilisateur 'nom' sont modifiés :

ALTER ROLE nom NOSUPERUSER CREATEDB LOGIN;
GRANT nom_role TO nom,nom2;                                     # attribuer un groupe à un role-user
GRANT ALL ON DATABASE nom_base TO nom_user;
REVOKE role_groupe TO nom,nom2;

Suppression d'un rôle :

REVOKE ALL ON DATABASE pylaasp04 from oyeasp01 cascade;
DROP ROLE nom;

Sauvegardes / Restauration

Commandes Commentaires
pg_dump mabase > secu_mabase.sql
pg_dumpall --clean > secu_serveur.sql ;toutes les bases, drop db before
pg_dump -F c mabase > nom_fichier fichier zippé binaire, permet de restaurer les tables sélectivement
pg_dump -F p -c mabase > nom_dump fichier Format texte, avec les drop et les create
pg_dump -F c -c -C mabase > nom_dump fichier bin, avec les drop et les create de la base
pg_dump -s mabase > nom_dump --schema-only ; uniquement le schema
pg_dump -a mabase > nom_dump --data-only ; uniquement les données.
pg_restore --list nom_dump voir le contenu du dump en format custom.
pg_restore -U user -F c -c -d mabase < nom_dump import de fichier bin, drop des objets (-c)
pg_restore -O -s -c -d mabase dump.dmp import du scheme seulement (-s) ; -O : no owner
pg_restore -O -a --disable-triggers -d mabase dump.dmp import des données seulement (-a)