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
|
1 |
CREATE DATABASE mabasededonnees; |
Modification du nom de la DB
|
1 |
ALTER DATABASE mabasededonnees RENAME TO ma_base_de_donnees; |
Supprimer la DB
|
1 |
DROP DATABASE ma_base_de_donnees; |
Pour lister les db’s
|
1 |
\l |
|
1 |
\l+ |
Se connecter a une autre db depuis posgresql
|
1 |
\c nom_de_la_db |
Se connecter a une autre db avec un autre utilisateur depuis posgresql
|
1 |
\c nom_de_la_db nom_du_user |
Créer, modifier, supprimer un schéma
Création d’un schéma
|
1 |
CREATE SCHEMA monschema; |
Modification du nom d’un schéma
|
1 |
ALTER SCHEMA monschema RENAME TO mon_schema; |
Pour lister les schémas
|
1 |
\dnS |
|
1 |
\dnS+ |
Pour connaître le schéma en cours
|
1 |
show search_path; |
ou
|
1 |
select current_schema(); |
Pour changer le schéma par défaut
|
1 |
set search_path to mon_schema; |
Supprimer un schéma vide
|
1 |
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).
|
1 |
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.
|
1 2 3 4 5 |
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
|
1 |
\dt |
|
1 |
\dt+ |
Voir la description d’une table
|
1 |
\d nom_de_la_table |
Modification du nom de la table
|
1 |
ALTER TABLE matable RENAME TO ma_table; |
Supprimer une table
|
1 |
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.
|
1 |
INSERT INTO ma_table (colonne_Nom,colonne_Prenom,colonne_Email) VALUES ('Jean','Dupont','jean.dupont@monemail.fr'); |
Vider une table de toutes ces donnees
|
1 |
TRUNCATE ma_table; |
Copie de table
Copier une table avec ces clés mais sans ces données
|
1 |
CREATE TABLE copie_de_ma_table (LIKE ma_table INCLUDING ALL); |
Copier une table sans ces clés mais avec ces données
|
1 |
CREATE TABLE copie_2_de_ma_table AS SELECT * FROM ma_table; |
Modifier une table
Ajouter une colonne (sans constraint)
|
1 |
ALTER TABLE ma_table ADD COLUMN colonne_Pays VARCHAR(20); |
Ajouter la constraint d’une colonne
|
1 |
ALTER TABLE ma_table ALTER COLUMN colonne_Pays SET NOT NULL; |
Supprimer la constraint d’une colonne
|
1 |
ALTER TABLE ma_table ALTER COLUMN colonne_Pays DROP NOT NULL; |
Renommer une colonne
|
1 |
ALTER TABLE ma_table RENAME COLUMN colonne_Pays to colonne_Location; |
Modifier le type de données d’une colonne
|
1 |
ALTER TABLE ma_table ALTER COLUMN colonne_Location TYPE CHAR(10); |
Supprimer une colonne
|
1 |
ALTER TABLE ma_table DROP COLUMN colonne_Location; |
Mise en place d’une tache en cron pour une requête
Sous le user postgres
|
1 |
$ crontab -l |
|
1 |
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
|
1 |
$ cat /tmp/ITTASKS-23802-1.sql |
|
1 2 3 4 |
\o /tmp/fichier_dexport.csv \f ' ; ' \a \i /tmp/requete.sql |
Contenu de la requete.sql
|
1 |
$ cat /tmp/requete.sql |
|
1 |
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
|
1 |
\h |
Aide générale
|
1 |
\? |
Lister les db’s
|
1 2 |
\l \l+ |
Db en cours d’utilisation
|
1 2 |
\conninfo SELECT CURRENT_DATABASE(); |
Liste les rôles des db’s
|
1 2 |
\dg \du |
Liste les schémas
|
1 2 3 4 5 |
\dn \dn+ \dnS+ \dt \di |
Pour lister les tables
|
1 2 |
\dt \dt+ |
Voir la description d’une table
|
1 |
\d nom_de_la_table |
Liste les tables, vues et séquences et leur droits
|
1 |
\dp |
Lister l’historique de toutes les commandes passées
|
1 |
\s |
Changer de db, db/user
|
1 2 |
\c nom_de_la_base \c nom_de_la_base nom_du_user |
Importer et exécuter un fichier
|
1 |
\i nom_du_fichier |
Quitter psql
|
1 |
\q |
Appeler une commande shell (limité a certaines commandes (dir, pwd, df…), la commande cd s’exécute directement)
|
1 |
\! commande_système |
Modifie le répertoire courant par répertoire.
|
1 |
\cd nom_du_répertoire |
Lister les rôles et configs par base
|
1 |
\drds : liste config/role et par base |
Liste les tablespaces (objets globaux : pgdefault, pgglobal)
|
1 2 |
\db \db+ |
Lister les extensions installées
|
1 2 |
\dx \dx+ |
Connaître la version de Postgres
|
1 |
$ postgres -V |
Exporter le contenu d’une requête vers un fichier pst
|
1 2 3 4 5 6 |
begin; \o /tmp/le_fichier_de_sorti.csv \f ' ; ' \a \i /tmp/la_requete.sql commit |
Installation de PostgreSQL sur Ubuntu
|
1 |
$ sudo apt-get install postgresql postgresql-contrib |
Connexion a postgres
|
1 |
# su - postgres |
postgres@lnx:~$
Puis
|
1 |
psql nom-de_la_base |
ou
|
1 |
$ sudo -i -u postgres |
postgres@lnx:~$
Puis
|
1 |
psql nom-de_la_base |
ou
|
1 |
psql -U postgres -h localhost -p 5432 -d postgres |
Se connecter a la db (le prompt changera pour le nom de la db)
|
1 |
postgres@lnx:~$ psql db_de_test |
|
1 2 3 4 |
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
|
1 |
db_de_test=# CREATE TABLE utilisateurs (id int, prenom varchar, nom varchar, adresse_email varchar); |
cela renverra
|
1 |
CREATE TABLE |
Puis nous renseignons cette table avec les valeurs suivantes
|
1 |
db_de_test=# INSERT INTO utilisateurs VALUES (1, 'Pierre', 'doras', 'pierre.doras@domaine.tld'); |
cela renverra
|
1 |
INSERT 0 1 |
|
1 |
db_de_test=# INSERT INTO utilisateurs VALUES (2, 'Moussa', 'toure'); |
cela renverra
|
1 |
INSERT 0 1 |
|
1 |
db_de_test=# INSERT INTO utilisateurs VALUES (3, 'Lei', 'Wu', 'lei.wu@domaine.tld'); |
cela renverra
|
1 |
INSERT 0 1 |
Enfin, nous listons la table pour voir si tout est correct.
|
1 |
db_de_test=# SELECT * FROM utilisateurs; |
|
1 2 3 4 5 6 |
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.
|
1 |
db_de_test=# update utilisateurs set adresse_email = 'moussa.toure@domaine.tld' where id = 2 ; |
Ce qui renvoi
|
1 |
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
|
1 2 3 4 5 |
db_de_test=# UPDATE utilisateurs SET prenom = 'Solange', nom = 'Eustache', adresse_email = 'solange.eustache@domaine.tld' WHERE id = 1; |
Ou
|
1 |
UPDATE utilisateurs SET prenon = 'Solange', nom = 'Eustache', adresse_email = 'solange.eustache@domaine.tld' WHERE id = 1; |
Le schéma est bien modifié.
|
1 2 3 4 5 6 7 |
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
|
1 |
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
|
1 |
postgres@lnx:~$ dropdb db_de_test; |
Ce qui renverra en voulant se connecter
|
1 |
postgres@lnx:~$ psql db_de_test |
psql: FATAL: database “db_de_test” does not exist
Lister les db
|
1 |
# psql -l |
|
1 2 3 4 5 6 7 8 9 10 |
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
|
1 |
db_de_test=# select * from utilisateurs; |
|
1 2 3 4 5 6 |
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
|
1 |
db_de_test=# CREATE ROLE "nom_du_user" WITH LOGIN; |
Lister les schéma
|
1 |
db_de_test=# \dn |
|
1 2 3 4 5 |
List of schemas Name | Owner --------+---------- public | postgres (1 row) |
Ou lister les schemas/permissions
|
1 |
db_de_test=# \dn+ |
|
1 2 3 4 5 6 |
List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | postgres | postgres=UC/postgres+| standard public schema | | =UC/postgres | (1 row) |
Lister les utilisateurs
|
1 |
db_de_test=# \du |
|
1 2 3 4 5 |
List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- bef | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
ou lister un utilisateur
|
1 |
db_de_test=# \du bef |
|
1 2 3 4 |
List of roles Role name | Attributes | Member of -----------+------------+----------- bef | | {} |
Lister schema/relation/table
|
1 |
db_de_test=# \d |
Pour afficher l’historique
|
1 |
db_de_test=# \s |
Pour lister une table
|
1 |
db_de_test=# select * from utilisateurs; |
|
1 2 3 4 5 6 |
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
|
1 |
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)
|
1 |
db_de_test=# \i /var/lib/pgsql/test_requete |
Historique des commandes psql
# /root/.psql_history
Tester une connexion psql
Pour tester l’acces d’un compte a une db
Installer le paquet psql et renseigner le serveur, un utilisateur autorisé a se connecter a une db
|
1 |
$ psql -h nom_du_serveur -U user_postgres bdd |
Dump, sauvegarde / restauration, importer / exporter une bdd (selon le language)
Sauvegarde de la db
|
1 |
$ sudo pg_dump --format=custom --file nom_de_la_sauvegarde.dump nom_de_la_bdd |
Restauration de la db
|
1 |
$ sudo pg_restore --clean --dbname nom_de_la_bdd nom_de_la_sauvegarde.dump |