WIKI - PostgreSQL - Let's Talk about Technology

Introduction

Ces temps c i j'effectue pas mal de migration PostgreSQL, du coup je vais vous mettre a disposition un petit WIKI qui vous permet de prendre en main PostGreSQL. Cela me serre egalement de WIKI car il est parfois pas evident de se souvenir des commandes de base.

Se connecter à la base de données

Pour se connecter sous Linux en ligne de commande, on basculera en utilisateur postgres :

su - postegres

Ensuite on lance la commande :

psql

Pour se connecter a une base de donnée distance via l'IP ou son nom DNS

psql "postgresql://login:password@host:port/database?sslmode=require"

Si vous êtes sous Windows Il est possible d'utiliser des logiciels graphiques comme pgAdmin ou HeidiSQL.

Commandes depuis la console psql

Une fois connecté en ligne de commande on est sur la base postgres :

Pour lister les bases de données :

defaultdb=> \l

defaultdb=> \l+

Pour se connecter à une base de données :

defaultdb=> \c kermit01

On a une sortie nous indiquant la bascule :

 \c kermit01psql (12.18 (Ubuntu 12.18-0ubuntu0.20.04.1), server 14.13)WARNING: psql major version 12, server major version 14.         Some psql features might not work.SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)You are now connected to database "kermit01" as user "doadmin".kermit01=>

Le prompt change avec le nom de la base de données :

kermit01=>

Pour lister les tables :

kermit01=> \dt

pour quitter

kermit01=> \q

Commandes de base pour créer et supprimer des bases

Pour crée une base de données, saisir simplement (exemple avec gonzo_test) :

CREATE DATABASE gonzo_testWITHOWNER = doadminENCODING = 'UTF8'LC_COLLATE = 'en_US.UTF-8'LC_CTYPE = 'en_US.UTF-8'TABLESPACE = pg_defaultCONNECTION LIMIT = -1;

Pour la supprimer (exemple avec gonzo_test) :

DROP DATABASE gonzo_test

Commandes de base pour utiliser les bases

Pour se connecter à la base :

Gestion des utilisateurs

Créer des utilisateurs

CREATE ROLE adrien WITH    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1    PASSWORD 'SuperMot2Passe';

Attribuer des droits aux utilisateurs

Pour attribuer tous les droits à un utilisateur (en faire en quelque sortes un deuxième root) :

Changer un mot de passe d'utilisateur de PostgreSQL

Cette commande fonctionne uniquement pour PostgreSQL :

Copier vers le presse-papierCode SQL :

ALTER USER adrien WITH PASSWORD 'SuperMot2Passe!';

Voir les utilisateurs

Pour voir les utilisateurs créés :

Copier vers le presse-papierCode SQL :

\du

Voir les droits des utilisateurs

Pour un utilisateur donné, on peut voir ses droits de la façon suivante :

[code=sql][/code]

Renommer un utilisateur

Si on souhaite renommer un utilisateur, on utilisera ALTER USER :

Copier vers le presse-papierCode SQL :

ALTER USER adrien RENAME TO nouveauadrien;

Tables

Créer et supprimer des tables

Pour créer une table simple, voici un exemple :

Copier vers le presse-papierCode SQL :

CREATE TABLE table1       (                         
    id serial PRIMARY KEY,    message text          );

Ici serial est in entier qui s'autoincrémente.

SELECT

INSERT

UPDATE

DELETE

ALTER

Donner les droits d'une table à un utilisateur (table1 à adrien) :

Copier vers le presse-papierCode SQL :

ALTER TABLE table1 OWNER TO adrien;

RENAME

Administration avancée

Ici, on retrouve des commandes d'admin avancées.

Recharger la config PostgresSQL

Lorsqu'on modifie le fichier pg_hba.conf, pour prendre en compte les nouveaux paramétrages à chaud (sans interrompre la prod), on va pouvoir lancer la commande SQL suivante :

Copier vers le presse-papierCode BASH :

SELECT pg_reload_conf();

Changer le owner de toutes les tables

Quand on a une base avec 6300 tables, pas facile de changer le propriétaire des tables.

Voici un script BASH (oneliner) utilisant la commande psql depuis l'utilisateur postgres :

Copier vers le presse-papierCode BASH :

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" VOTRE_BDD` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" VOTRE_BDD; done

Supprimer toutes les tables d'une BDD

Quand on a une base avec 6300 tables, pas facile de supprimer toutes les tables. L'idée est de réimporter un dump sans recréer la base.

Voici un script BASH (oneliner) utilisant la commande psql depuis l'utilisateur postgres :

Copier vers le presse-papierCode BASH :

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" VOTRE_BDD` ; do  psql -c "drop table \"$tbl\" CASCADE " VOTRE_BDD ; done

Killer les sessions

Si vous souhaiter réimporter une base (et donc supprimer et recréer la base) et qu'elle est en cours d'utilisation, vous pouvez avoir ce type de message :

Copier vers le presse-papierCode :ERREUR: la base de données « adrien_test » est en cours d'utilisation par d'autres utilisateurs DÉTAIL : 3 autres sessions utilisent la base de données.

Récupérez les PID des connexions (exemple avec la base adrien_test) :

Copier vers le presse-papierCode SQL :

SELECT pid FROM pg_stat_activity WHERE datname='adrien_test';

Vous avez une sortie de ce type :

Copier vers le presse-papierCode TEXT :

   pid   
---------
 2633163
 2633180
 2633196
(3 lignes)

Il suffit de kill les sessions avec cette commande :

Copier vers le presse-papierCode SQL :

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid IN (2633163, 2633180 ,2633196 );

On a un retour "t" sur chaque connexion terminées :

Copier vers le presse-papierCode TEXT :

 pg_terminate_backend 
----------------------
 t
 t
 t
(3 lignes)

Cloner une base de données à chaud

On peut le faire en oneliner avec pg_dump suivi de psql (depuis la console Linux connecté en root OU postgres) :

Copier vers le presse-papierCode BASH :

pg_dump -U postgres adrien_db | psql -d adrien_db_copie -U postgres