Installer et utiliser PostgreSQL sur CentOS 7... en 3 minutes

Les référentiels par défaut de CentOS contiennent des paquets Postgres, donc nous pouvons les installer sans tracas en utilisant le système de paquets yum. Installez le paquet postgresql-server et le paquet "contrib", qui ajoute quelques utilitaires et fonctionnalités supplémentaires:

sudo yum install postgresql-server postgresql-contrib

Maintenant que postgresqlest installé, nous devons effectuer quelques étapes avant de pouvoir l'utiliser. Créez un nouveau cluster de base de données PostgreSQL:

sudo postgresql-setup initdb

Par défaut, PostgreSQL n'autorise pas l'authentification par mot de passe. Nous allons changer cela en éditant sa configuration d'authentification basée sur l'hôte (HBA). Ouvrez la configuration HBA avec votre éditeur de texte favori. Nous allons utiliser vi:

sudo vi /var/lib/pgsql/data/pg_hba.conf

Remplacez "ident" par "md5" :

sudo vi /var/lib/pgsql/data/postgresql.conf

Maintenant, démarrez et activez PostgreSQL:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Par défaut, Postgres utilise un concept appelé "rôles" pour faciliter l'authentification et l'autorisation. Ceux-ci sont, à certains égards, similaires aux comptes habituels de type Unix, mais Postgres ne fait pas de distinction entre les utilisateurs et les groupes et préfère à la place le terme plus souple "rôle". Lors de l'installation, Postgres est configuré pour utiliser l'authentification "ident", ce qui signifie qu'il associe les rôles Postgres à un compte système Unix / Linux correspondant. Si un rôle Postgres existe, il peut être connecté en se connectant au compte système Linux associé. La procédure d'installation a créé un compte utilisateur appelé postgres associé au rôle Postgres par défaut. Pour utiliser Postgres, nous devons nous connecter à ce compte. Vous pouvez le faire en tapant:

sudo -i -u postgres

Maintenant vous pouvez obtenir une invite Postgres immédiatement en tapant:

psql

Vous serez connecté automatiquement et serez en mesure d'interagir avec le système de gestion de base de données tout de suite. Cependant, nous allons vous expliquer un peu comment utiliser d'autres rôles et bases de données afin d'avoir une certaine flexibilité quant à l'utilisateur et la base de données avec lesquels vous souhaitez travailler.

Créer un nouveau rôle

À partir du compte Linux postgres, vous avez la possibilité de vous connecter au système de base de données. Cependant, nous allons également montrer comment créer des rôles supplémentaires. Le compte Linux postgres, associé au rôle administratif de Postgres, a accès à certains utilitaires pour créer des utilisateurs et des bases de données. Nous pouvons créer un nouveau rôle en tapant:

createuser --interactive

Créer une nouvelle base de données

Vous pouvez créer la base de données appropriée en appelant simplement cette commande en tant qu'utilisateur postgres:

createdb sasdb

La commande \list permet de lister les bases présentes dans Postgres :

select * from pg_roles;

ALTER ROLE "sasdb" WITH LOGIN;

Pour autoriser un accès distant ( à partir de SAS par exemple), il faut modifier le fichier pg_hba.conf : Sans cette modification, vous pourriez avoir les messages d'erreurs suivant lors d'une tentative de connexion depuis SAS : ERROR: CLI error trying to establish connection: FATAL: password authentication failed for user "sasdb" ERROR: Error in the LIBNAME statement. ERROR: CLI error trying to establish connection: could not connect to server: Connection refused Is the server running on host "10.104.30.45" and accepting TCP/IP connections on port 5432? ERROR: Invalid connection options. Lack PASSWORD option. ERROR: Error in the LIBNAME statement. ERROR: CLI error trying to establish connection: FATAL: no pg_hba.conf entry for host "10.104.18.148", user "sasdb", database "sasdb", SSL off ERROR: Error in the LIBNAME statement.

Monitoring

Pour monitorer les sessions, vous pouvez utiliser la requête SQL suivante :

select usename,client_addr,state,backend_start,query_start from pg_stat_activity where client_addr is not null;

Pour afficher toutes les bases de données PostgreSQL et leur taille :

select
 datname as base,
 pg_size_pretty(pg_database_size(datname)) as taille
from pg_database
order by pg_database_size(datname) desc;

  Afficher les requêtes en cours d’exécution :

SELECT
 S.pid, age(clock_timestamp(), query_start),usename,query,L.mode,L.locktype,L.granted FROM pg_stat_activity S 
inner join pg_locks L on S.pid = L.pid WHERE usename != 'postgres' order by L.granted, L.pid DESC;