Quelques infos en vrac ..
Structure de Postgresql (Fait depuis Draw.io)
Le schéma par défaut s’appelle Public.
Il est possible de créer plusieurs schéma au sein même d’une bdd.
/!\ Infos utiles
. Lors de la première connexion a Postgres, la DB par défaut est celle de Postgres.
. Le prompt:
. Un utilisateur ne peux créer/renommer/supprimer une database.
Créer, modifier, supprimer une base de données
Création de la DB
CREATE DATABASE mabasededonnees;
Modification du nom de la DB
ALTER DATABASE mabasededonnees RENAME TO ma_base_de_donnees;
Supprimer la DB
DROP DATABASE ma_base_de_donnees;
Pour lister les db’s
\l
\l+
Se connecter a une autre db depuis posgresql
\c nom_de_la_db
Se connecter a une autre db avec un autre utilisateur depuis posgresql
\c nom_de_la_db nom_du_user
Créer, modifier, supprimer un schéma
Création d’un schéma
CREATE SCHEMA monschema;
Modification du nom d’un schéma
ALTER SCHEMA monschema RENAME TO mon_schema;
Pour lister les schémas
\dnS
\dnS+
Pour connaître le schéma en cours
show search_path;
ou
select current_schema();
Pour changer le schéma par défaut
set search_path to mon_schema;
Supprimer un schéma vide
DROP SCHEMA mon_schema;
Supprimer un schéma et toutes ces tables
/!\ Il faut re-séter un schéma ou quitter la session pour retrouver un schéma par défaut (Public).
DROP SCHEMA mon_schema CASCADE;
Créer, modifier, supprimer une table
Création de la table
/!\ La table est constitué de colonne et est un pré-requis a la création.
CREATE TABLE matable ( colonne_Nom VARCHAR(20) NOT NULL, colonne_Prenom VARCHAR(20) NOT NULL, colonne_Email VARCHAR(30) NOT NULL, Primary Key (colonne_Email));
Pour lister les tables
\dt
\dt+
Voir la description d’une table
\d nom_de_la_table
Modification du nom de la table
ALTER TABLE matable RENAME TO ma_table;
Supprimer une table
DROP TABLE ma_table;
/!\ Info pour la création d’une table
Insérer des données dans une table
Dans l’exemple, insérer des données en respectant le nombre de colonne.
/!\ Un champ ne peut etre vide, il faut ajouter null.
/!\ La Primary Key ayant été sété dans la colonne email, elle devient donc une valeur unique. Il n’est pas possible d’avoir un doublon.
(ERROR: duplicate key value violates unique constraint “ma_table_pkey”
DETAIL: Key (colonne_email)=(jean.dupont@monemail.fr) already exists.
INSERT INTO ma_table (colonne_Nom,colonne_Prenom,colonne_Email) VALUES ('Jean','Dupont','jean.dupont@monemail.fr');
Vider une table de toutes ces donnees
TRUNCATE ma_table;
Copie de table
Copier une table avec ces clés mais sans ces données
CREATE TABLE copie_de_ma_table (LIKE ma_table INCLUDING ALL);
Copier une table sans ces clés mais avec ces données
CREATE TABLE copie_2_de_ma_table AS SELECT * FROM ma_table;
Modifier une table
Ajouter une colonne (sans constraint)
ALTER TABLE ma_table ADD COLUMN colonne_Pays VARCHAR(20);
Ajouter la constraint d’une colonne
ALTER TABLE ma_table ALTER COLUMN colonne_Pays SET NOT NULL;
Supprimer la constraint d’une colonne
ALTER TABLE ma_table ALTER COLUMN colonne_Pays DROP NOT NULL;
Renommer une colonne
ALTER TABLE ma_table RENAME COLUMN colonne_Pays to colonne_Location;
Modifier le type de données d’une colonne
ALTER TABLE ma_table ALTER COLUMN colonne_Location TYPE CHAR(10);
Supprimer une colonne
ALTER TABLE ma_table DROP COLUMN colonne_Location;
Mise en place d’une tache en cron pour une requête
Sous le user postgres
$ crontab -l
01 23 11 02 02 /usr/bin/psql -f /tmp/tache.sql -a -d nom_de_la_db > '/tmp/psql.log' 2>&1
Contenu du fichier tache.sql
$ cat /tmp/ITTASKS-23802-1.sql
\o /tmp/fichier_dexport.csv \f ' ; ' \a \i /tmp/requete.sql
Contenu de la requete.sql
$ cat /tmp/requete.sql
select workspace_name,login,max(session_creation_date) as last_connection from account_session JOIN account using (account_id) JOIN workspace using (workspace_id) where session_creation_date < now() - INTERVAL '1 YEAR' and workspace_name like 'sg-%' and account_id not in (select account_id from account_session where session_creation_date > now() - INTERVAL '1 YEAR') group by workspace_name,login order by 1,2;
Dans le cron je l’exécute a 23h01 en appelant le fichier tache.sql dans la db “nom_de_la_db” et je renvoi vers un fichier de log si il a des erreurs a l’issu.
Dans le fichier base.sql he défini l’import de la requête, et l’export de celle ci vers un .csv en stipulant le séparateur.
Dans le fichier requete.sql, la commande.
Il y a sûrement plus simple !
Rappel des commandes interne
Lister les commandes
\h
Aide générale
\?
Lister les db’s
\l \l+
Db en cours d’utilisation
\conninfo SELECT CURRENT_DATABASE();
Liste les rôles des db’s
\dg \du
Liste les schémas
\dn \dn+ \dnS+ \dt \di
Pour lister les tables
\dt \dt+
Voir la description d’une table
\d nom_de_la_table
Liste les tables, vues et séquences et leur droits
\dp
Lister l’historique de toutes les commandes passées
\s
Changer de db, db/user
\c nom_de_la_base \c nom_de_la_base nom_du_user
Importer et exécuter un fichier
\i nom_du_fichier
Quitter psql
\q
Appeler une commande shell (limité a certaines commandes (dir, pwd, df…), la commande cd s’exécute directement)
\! commande_système
Modifie le répertoire courant par répertoire.
\cd nom_du_répertoire
Lister les rôles et configs par base
\drds : liste config/role et par base
Liste les tablespaces (objets globaux : pgdefault, pgglobal)
\db \db+
Lister les extensions installées
\dx \dx+
Exporter le contenu d’une requête vers un fichier pst
begin; \o /tmp/le_fichier_de_sorti.csv \f ' ; ' \a \i /tmp/la_requete.sql commit
Installation de PostgreSQL sur Ubuntu
$ sudo apt-get install postgresql postgresql-contrib
Connexion a postgres
# su - postgres
postgres@lnx:~$
Puis
psql nom-de_la_base
ou
$ sudo -i -u postgres
postgres@lnx:~$
Puis
psql nom-de_la_base
ou
psql -U postgres -h localhost -p 5432 -d postgres
Se connecter a la db (le prompt changera pour le nom de la db)
postgres@lnx:~$ psql db_de_test
psql (9.5.12) Type "help" for help. db_de_test=#
Création d’une table
Dans l’exemple, je crée une table d’utilisateurs avec id, prénom, nom et adresse émail
db_de_test=# CREATE TABLE utilisateurs (id int, prenom varchar, nom varchar, adresse_email varchar);
cela renverra
CREATE TABLE
Puis nous renseignons cette table avec les valeurs suivantes
db_de_test=# INSERT INTO utilisateurs VALUES (1, 'Pierre', 'doras', 'pierre.doras@domaine.tld');
cela renverra
INSERT 0 1
db_de_test=# INSERT INTO utilisateurs VALUES (2, 'Moussa', 'toure');
cela renverra
INSERT 0 1
db_de_test=# INSERT INTO utilisateurs VALUES (3, 'Lei', 'Wu', 'lei.wu@domaine.tld');
cela renverra
INSERT 0 1
Enfin, nous listons la table pour voir si tout est correct.
db_de_test=# SELECT * FROM utilisateurs;
id | prenom | nom | adresse_email ----+--------+-------+-------------------------- 1 | Pierre | doras | pierre.doras@domaine.tld 2 | Moussa | toure | 3 | Lei | Wu | lei.wu@domaine.tld (3 rows)
Modifier une ligne du schéma utilisateurs de la db db_de_test
On s’aperçoit que Moussa n’a pas d’adresse émail, on peut modifier la ligne avec la commande update.
db_de_test=# update utilisateurs set adresse_email = 'moussa.toure@domaine.tld' where id = 2 ;
Ce qui renvoi
UPDATE 1
Si on relance un select * from utilisateurs, la ligne est bien modifié.
Il est aussi possible de changer toute ou une partie des champs, voici une requête pour cela, imaginons que que Pierre Doras décide de changer de sexe, se marie et change de nom (soyons fou !)
2 façons d’écrire cette requête.
avec un saut de ligne pour une meilleure compréhension
db_de_test=# UPDATE utilisateurs SET prenom = 'Solange', nom = 'Eustache', adresse_email = 'solange.eustache@domaine.tld' WHERE id = 1;
Ou
UPDATE utilisateurs SET prenon = 'Solange', nom = 'Eustache', adresse_email = 'solange.eustache@domaine.tld' WHERE id = 1;
Le schéma est bien modifié.
db_de_test=# select * from utilisateurs; id | prenom | nom | adresse_email ----+---------+----------+------------------------------ 3 | Lei | Wu | lei.wu@domaine.tld 2 | Moussa | toure | moussa.toure@domaine.tld 1 | Solange | Eustache | solange.eustache@domaine.tld (3 rows)
Begin, commit et rollback
/!\ /!\ /!\ Important /!\ /!\ /!\
Il est conseillé d’utiliser la commande begin; avant chaque instruction.
Celle ci permet d’exécuter les commandes, de voir les erreurs et de rollback si besoin ou de valider avec la commande commit;
Pour reprendre la doc officiel
BEGIN initie un bloc de transaction, c'est-à-dire que toutes les instructions apparaissant après la commande BEGIN sont exécutées dans une seule transaction jusqu'à ce qu'un COMMIT ou ROLLBACK explicite soit exécuté. Par défaut (sans BEGIN), PostgreSQL™ exécute les transactions en mode « autocommit », c'est-à-dire que chaque instruction est exécutée dans sa propre transaction et une validation (commit) est traitée implicitement à la fin de l'instruction (si l'exécution a réussi, sinon une annulation est exécutée).
Suppression d’une db
/!\ La db et tout ce qu’elle contient sera intégralement supprimé.
taper la commande
postgres@lnx:~$ dropdb db_de_test;
Ce qui renverra en voulant se connecter
postgres@lnx:~$ psql db_de_test
psql: FATAL: database “db_de_test” does not exist
Lister les db
# psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+----------+-------------+-------------+-----------------------
db_de_test | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 |
postgres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 |
template0 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Pour lister le schéma d’une table
db_de_test=# select * from utilisateurs;
id | prenom | nom | adresse_email ----+--------+-------+-------------------------- 1 | Pierre | doras | pierre.doras@domaine.tld 2 | Moussa | toure | moussa.toure@domaine.tld 3 | Lei | Wu | lei.wu@domaine.tld (3 rows)
Pour créer un user
db_de_test=# CREATE ROLE "nom_du_user" WITH LOGIN;
Lister les schéma
db_de_test=# \dn
List of schemas Name | Owner --------+---------- public | postgres (1 row)
Ou lister les schemas/permissions
db_de_test=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(1 row)
Lister les utilisateurs
db_de_test=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
bef | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
ou lister un utilisateur
db_de_test=# \du bef
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
bef | | {}
Lister schema/relation/table
db_de_test=# \d
Pour afficher l’historique
db_de_test=# \s
Pour lister une table
db_de_test=# select * from utilisateurs;
id | prenom | nom | adresse_email ----+---------+----------+------------------------------ 3 | Lei | Wu | lei.wu@domaine.tld 2 | Moussa | toure | moussa.toure@domaine.tld 1 | Solange | Eustache | solange.eustache@domaine.tld (3 rows)
Créer une requête dans un fichier et l’importer
Sortir de la db, éditer un fichier et y renseigner sa requête
postgres@lnx:~$ vim test_requete
Puis pour l’exécution il faudra se reconnecter a la db et lancer la commande suivante (ne pas oublier de faire un begin; avant)
db_de_test=# \i /var/lib/pgsql/test_requete
Historique des commandes psql
# /root/.psql_history