Base de données

Créer un utilisateur pour se connecter à Postgresql en bash

Base de données / Par

Pour les besoins de code PHP, vous devez vous connecter à la base de données avec PDO. Si vous n’avez jusqu’à présent fait une connexion qu’avec l’utilisateur postgres, vous ne pouvez faire ça avec ce dernier dans vos scripts PHP. Nous allons donc créer un utilisateur refschool.

Créer un utilisateur dans postgresql

Vous devez vous connecter avec l’utilisateur postgres, qui agit comme le superadministrateur, car vous ne pouvez pas vous connecter en root à postgresql, en fait c’est l’équivalent du root dans Mysql

sudo -u postgres psql  // attention c'est un u minuscule
// postgresql

CREATE USER refschool WITH LOGIN PASSWORD '123';

GRANT CONNECT ON DATABASE devdb TO refschool;
GRANT USAGE ON SCHEMA public TO refschool;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO refschool;

Notez que public est un SCHEMA et pas une table ! Si vous venez de MySQL comme moi, un SCHEMA est une table. Apparemment la logique de Postgresql est plus répandue du moins chez les grands systèmes de SGBDR comme SQLSERVER et ORACLE.

Création d’une table dans le SCHEMA public

CREATE TABLE public.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

// mention du SCHEMA public, ce dernier étant par défaut dans Postgesql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Autres commandes de Postgresql

//

SELECT current_database();

//

Installation de Postgresql dans un container docker Debian

Base de données, Linux / Par

Installation de postgresSQL

apt update
apt install postgresql

Nous sommes dans le cas où il n’y a pas systemd ou supervisor, et cet article reprend la suite de la procédure d’installation de nginx et php-fpm

Normalement si vous avez installé avec apt, pas besoin de créer un répertoire ni d’initialiser une base de données.

Nous allons maintenant lancer postgresql, la commande est un peut longue et vous ne pouvez pas lancer en root

postgres /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main


"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise.  See the documentation for
more information on how to properly start the server.

De plus si on veut ne pas taper cette commande il vaut mieux ajouter au PATH

export PATH="$PATH:/usr/lib/postgresql/15/bin"

La commande à faire est via un utilisateur non root, il faut créer un utilisateur

// création d'utilisateur 
adduser refschool   // il vaut mieux utiliser adduser car useradd nécessite de setter le password avec la commande passwd

// on ajouter refschool dans la liste de sudoer
usermod -aG sudo refschool  // en fait on l'ajoute au groupe sudoer

On exécute en tant que utilisateur postgres

sudo -u postgres /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main

Si vous avez l’erreur suivante : postgres: could not access the server configuration file « /var/lib/postgresql/15/main/postgresql.conf »: No such file or directory

c’est que la base de donnée a été initialisée mais ne contient pas le fichier de configuration postgresql.conf.

Nous allons initialiser la base dans un autre répertoire

mkdir -p /opt/postgres_data
chown postgres:postgres /opt/postgres_data
sudo -u postgres /usr/lib/postgresql/15/bin/initdb -D /opt/postgres_data

Une base de données sera initialisée dans le répertoire /opt/postgres_data

Sortie de cette commande

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".                                                               The default database encoding has accordingly been set to "SQL_ASCII".                                                  The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /opt/postgres_data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/15/bin/pg_ctl -D /opt/postgres_data -l logfile start

Attention lorsque vous arrêtez Postgresql, la commande pour le redémarrer est différente, en effet, on fait ici (ci-dessus) un initdb, qu’on fait une seule fois, pour les fois suivantes:

// à exécuter lorsque vous êtes loggé en utilisateur refschool (mais pas root)


sudo -u postgres /usr/lib/postgresql/15/bin/postgres -D /opt/postgres_data

Pratique des commandes principales de Postgresql

Une fois que vous êtes dans le prompt de Postgresql, vous pouvez faire les commandes suivantes:

Je rappelle qu’il faut démarrer postgresql avant, vous ne pouvez le faire en root, donc switchez sur un user normal et lancez la commande suivante : 

// vous êtes en user normal (non root)

sudo -u postgres /usr/lib/postgresql/15/bin/postgres -D /opt/postgres_data

//puis dans un autre shell connectez vous avec la commande psql en tant que user postgres

psql -U postgres

lister les bases de données

\l  ou \list

postgres=# \list
                                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+-----------+---------+-------+------------+-----------------+-----------------------
 devdb     | devuser  | SQL_ASCII | C       | C     |            | libc            | =Tc/devuser          +
           |          |           |         |       |            |                 | devuser=CTc/devuser
 postgres  | postgres | SQL_ASCII | C       | C     |            | libc            | 
 template0 | postgres | SQL_ASCII | C       | C     |            | libc            | =c/postgres          +
           |          |           |         |       |            |                 | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     |            | libc            | =c/postgres          +
           |          |           |         |       |            |                 | postgres=CTc/postgres
(4 rows)

Choisir une base de donnés

Nous devons comme dans tous les sytème sde base de données choisir une base pour faire des requêtes

postgres=# \c devdb
You are now connected to database "devdb" as user "postgres".

// à noter que le prompt a changé

Créer une table et lister les tables

Coller ce code

devdb=# CREATE TABLE produit (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    description TEXT,
    prix NUMERIC(10,2) NOT NULL,
    stock INTEGER DEFAULT 0,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


devdb=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | produit | table | postgres
(1 row)

Accessoirement pour lister les schémas dans toutes les bases de données
\dt *.*

pour sortir du mode "pager" appuyez sur la touche "q"

Exécuter une requête SQL pour insérer des données

On va insérer des données : 

devdb=# INSERT INTO produit (nom, description, prix, stock)
VALUES 
('Iphone', 'Description du Iphone', 1999, 10),
('Samsung', 'Description du Samsung', 299, 5);

// afficher les données de la table avec un SELECT
// attention il faut respecter la casse

SELECT * FROM produit

Faire un dump de la base de données

On va utiliser l’utilitaire pg_dump

pg_dump -U postgres -d devdb -f sav.sql
Retour en haut