[[postgresql]]
Piste: » postgresql
PostgreSQL
Introduction
Postgresql un SGBD (gestionnaire de base de donnée) très performant sous license BSD dont les performances sont comparables à Oracle 9, qui reste le SGBD le plus rapide et le plus utilisé en entreprise de nos jours.
Ce SGBD est peu connu bien que riche en fonctionnalités et facile d'utilisation. Il respecte les standard SQL : SQL 92, 99 et en partie la norme SQL2003 et intègre plusieurs langages embarqués (perl, python, java et SQL ) depuis de nombreuses années. Pour en savoir plus
Pour ceux qui voudraient faire l'installation en le compilant c'est ici : http://www.system-linux.eu/ catégorie Postgresql.
Historique
Le lien vers le : site officiel
Le lien vers : postgresqlfr.org
psql: could not connect to server: No such file or directory Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Pour régler ceci il faut dans le terminal entrer:
sudo pg_createcluster 8.3 main --start
Installation du serveur PostgreSQL
On commence par installer le paquet postgresql.
Cela installera également les paquets suivants :
- openssl
- openssl-blacklist
- postgresql
- postgresql-8.3
- postgresql-client-8.3
- postgresql-client-common
- postgresql-common ssl-cert
| Installer en 1 clic |
|---|
|
Notez que l'installation ajoute l'utilisateur postgres à la liste des utilisateurs. Utiliser finger si vous souhaitez de plus amples informations sur ce nouvel utilisateur.
finger postgres
L'utilisateur postgres
PostgreSQL est un serveur qui permet de se connecter à différentes bases de données. Par défaut, seul l'utilisateur postgres peut se connecter.
Toutes les opérations d'administration se font, au départ, avec l'utilisateur postgres. À la fin de l'installation, celui-ci ne possède pas de mot de passe : c'est un utilisateur bloqué et le mieux est qu'il le reste. La première chose à faire sera de créer un nouvel utilisateur, mais pour ce faire, il faut se connecter au moins une fois en tant qu'utilisateur postgres. Pour devenir postgres et faire les opérations d'administration qui suivent, utilisez sudo :
$ sudo -s -u postgres Password:
pour pouvoir commencer à utiliser PostgreSQL. exit permettra, à la fin de cette session d'administration dans PostgreSQL, de reprendre la main en tant qu'utilisateur du système.
Désormais, l'invite de commande doit mentionner que vous êtes actif en tant que postgres. Pour lancer l'outil d'administration de PostgreSQL, tapez simplement :
psql
Vous devriez obtenir quelque chose comme :
Bienvenue dans psql 8.2.3, l'interface interactive de PostgreSQL.
Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
postgres=#
Notez que vous avez une interface en ligne de commande, et que vous êtes connecté en tant que "root", comme l'indique le # en bout de ligne.
Cette ligne de commande sera bien sûr utilisée pour exécuter des requêtes SQL, mais aussi des commandes internes à postgreSQL (elles commencent toutes par un antislash)
Créer un utilisateur PostgreSQL
Méthode rapide, non sécurisée
postgres est à psql ce que root est à Ubuntu. Il est préférable de ne l'utiliser que pour les tâches d'administration.
Il peut être commode d'assimiler les utilisateurs de votre système à ceux qui se connecteront avec psql, mais ce n'est nullement obligatoire. Par la suite, nous supposerons que votre login système est nom_utilisateur.
Commencez par créer un nouvel utilisateur, qui portera par exemple le même nom que vous. Dans la ligne de commande ci-dessous, tapez (rappel : la partie « postgres=# » est l'invite de commande, il ne faut pas la retaper ! Ainsi, la commande ci-dessous requière de taper « CREATE USER <nom_utilisateur>; », en remplaçant <nom_utilisateur> par un identifiant bien choisi) :
postgres=# CREATE USER <nom_utilisateur>;
N'oubliez pas le point-virgule à la fin…
Par défaut, votre nouvel utilisateur n'a aucun droit. Donnez-lui la possibilité de créer de nouvelles bases de données:
postgres=# ALTER ROLE <nom_utilisateur> WITH CREATEDB;
Ensuite, créez une base de données pour votre utilisateur. Par exemple, vous pouvez prendre comme nom : nom_utilisateur. Ainsi, la connexion sera automatique.
CREATE DATABASE <nom_utilisateur>;
Attribuer un mot de passe à l'utilisateur pour qu'il puisse se connecter à la base :
ALTER USER <nom_utilisateur> WITH ENCRYPTED PASSWORD 'mon_mot_de_passe' ;
Vous pouvez maintenant quitter, "redevenir vous même" et relancer psql directement, en étant cette fois connecté sous votre propre nom :
postgres=# \q postgres@ubuntu:~$ exit nom_utilisateur@ubuntu:~$ psql
La ligne de commande est devenue :
nom_utilisateur=>
Notez la transformation du # en > : vous n'êtes plus superutilisateur…
Pour aller plus loin
Méthode alternative pour créer un utilisateur
Vous pouvez aussi créer vos utilisateurs directement en ligne de commande, depuis le terminal standard, mais en étant connecté en tant que postgres, bien sûr.
Cette méthode est beaucoup plus sûre que la précédente. Tous les utilisateurs auront des mots de passe, cryptés. (Ce qui n'est pas le cas lorsque l'on emploie la méthode précédente)
Il faut d'abord modifier le fichier de configuration pour autoriser les connexions via mot de passe cryptés :
sudo nano /etc/postgresql/x.x/main/pg_hba.conf
Remplacez "x.x" par le numéro de version de PostgreSQl installée sur votre ordinateur (exemple : 8.3).
Les utilisateurs "avancés" noteront que le fichier est amplement commenté. Voir la documentation pour plus d'informations.
Modifier le fichier en remplaçant ident sameuser par md5 afin d'obtenir les lignes suivantes :
... local all postgres md5 # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
On peut ensuite créer notre utilisateur :
$ sudo -s -u postgres $ createuser <nom_utilisateur>
Vous devrez ensuite répondre à une série de questions :
Le nouvel rôle est-il superutilisateur ? (o/n) n Le nouvel rôle est-il autorisé à créer des bases de données ? (o/n) o Le nouvel rôle est-il autorisé à créer de nouveaux rôles ? (o/n) n
En revanche, ici l'utilisateur n'est pas encore opérationnel, et il faut lui attribuer un mot de passe :
$ psql -d template1 -c "alter user <nom_utilisateur> with password '<votre_mot_de_passe>'"
Notez bien les guillemets simples autour du mot de passe …
Puis, on lui crée une base de données dédiée :
$ createdb -O <nom_utilisateur> -E UTF8 <ma_database>
Par précaution, on ajoute un mot de passe postgres : (C'est un mot de passe POUR psql) Les utilisateurs postgres n'ont a priori rien à voir avec les utilisateurs système, mais il peut être intéressant de garder un parallèle entre les deux :
$ psql -d postgres -c "ALTER USER postgres WITH PASSWORD 'mot_de_passe'"
Il faut enfin relancer le serveur psql pour que les modifications de mots de passe soient prises en compte :
$ sudo /etc/init.d/postgresql-8.2 restart
Attention ! Le mot de passe à entrer est celui de postgres si vous êtes connecté sous ce nom-ci. (postgres a les droits d'administration standards)
Ensuite, on peut se connecter via :
$ psql -U <nom_utilisateur> Mot de passe pour l'utilisateur <nom_utilisateur> :
$ psql -U <nom_utilisateur> <nom_database_de_l_utilisateur> Mot de passe pour l'utilisateur <nom_utilisateur> :
P.S. : <nom_database_de_l_utilisateur> est en fait le nom que vous avez indiqué lors de la création de la base de l'utilisateur <nom_utilisateur>.
Rôles, privilèges
En cours de rédaction … On attend !!!!!!!!!!!!!
Gestion des connexions
En cours de rédaction …
Clients graphiques
Le client pgadmin3
PgAdmin III est un outil graphique d'administration de votre serveur PostgreSQL. L'application pgAdmin III peut être utilisé pour administrer les serveurs PostgreSQL 7.3 et les versions supérieures. PgAdmin III existe pour toutes les plateformes dont, évidemment, Ubuntu, KUbuntu, XUbuntu.
PgAdmin III a été conçu pour répondre aux besoins de tous les utilisateurs, depuis la rédaction de simple requêtes SQL au développement complexe de base de données. L'interface graphique supporte toutes les fonctionnalités de PostGreSQL et permet une administration simple. L'application inclut aussi un éditeur de requête avec coloration syntaxique, un éditeur de code, un agent de gestion de tâche automatique, un support pour les réplication via Slony-I et bien d'autres fonctionnalités.
Installation du client pgadmin3
On commence par installer les paquets nécessaires : pgadmin3
sudo apt-get install pgadmin3
Cela va également installer les paquets suivants :
- libpq4
- libwxbase2.6-0
- libwxgtk2.6-0
- pgadmin3-data
| Installer en 1 clic |
|---|
|
Lancement du client pgadmin3
Sous Ubuntu, pour lancer PgAdmin III, aller dans le menu Applications ⇒ Outils Système ⇒ pgAdmin III
En suivant la section ci-haut "Méthode rapide, non sécurisée" pgadmin3 n'acceptera pas la connexion aux bases de données. Il faudra definir un mot de passe à l'utilisateur avant (voir la procédure sécurisée)
$ psql -d template1 -c "alter user <nom_utilisateur> with password '<votre_mot_de_passe>'"
Utilisation du client pgadmin3
Le client phppgadmin
Installation du client phppgadmin
Il suffit d'installer le paquet phppgadmin, à condition que vous ayez un serveur Web fonctionnel et reconnaissant php.
| Le serveur Web php en un clic |
|---|
|
| phppgadmi en un clic |
|---|
|
extension=pgsql.so à la main (après celle sur mysql) dans le fichier /etc/php5/apache2/php.ini. A confirmer ou infirmer.
Confirmé, chez moi idem. Et sous gutsy, comme pour phpmyadmin, il faut aussi créer le lien symbolique vers /usr/share/phppgadmin.
sudo ln -s /etc/phppgadmin/apache.conf /etc/apache2/conf.d/phppgadmin.conf
puis redemarrer apache:
sudo /etc/init.d/apache2 reload
Lancement du client phppgadmin
Lance votre navigateur Web préféré à l'adresse http://localhost/phppgadmin/.
sudo ln -s /usr/share/phppgadmin/ /var/www/
Utilisation du client phppgadmin
Connexions vers PHP
PHP 4 : installer php4-pgsql.
PHP 5 : installer php5-pgsql.
php5-pdo (voir la documentation officiel pour PDO) :
sudo pear install pecl-pdo_pgsql
PHP 5.2 : Cela se fait automatiquement.
: A compléter
Postgresql 8.3
Pour ceux n'ayant pas upgradé leur ubuntu, ils peuvent installer postgresql 8.3 grâce aux backports.
La solution à ce problème est assez simple.
1. Faites un backup avec pg_dumpall
mkdir /tmp/backup chown postgres /tmp/backup cd /tmp/backup/ su postgres pg_dumpall > backup.dmp
2. Recréez les fichiers d'initialisation à un autre endroit avec POSIX comme locale. Cet autre endroit doit appartenir à postgres et avoir 700 comme permission.
/usr/lib/postgresql/8.3/bin/initdb --locale=POSIX -D/var/lib/postgresql/8.3-posix/main
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 POSIX.
The default database encoding has accordingly been set to SQL_ASCII.
The default text search configuration will be set to "english".
fixing permissions on existing directory /var/lib/postgresql/8.3-posix/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 24MB/153600
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/8.3-posix/main/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.
Success. You can now start the database server using:
/usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3-posix/main
or
/usr/lib/postgresql/8.3/bin/pg_ctl -D /var/lib/postgresql/8.3-posix/main -l logfile start
3. Changez dans le fichier de configuration le data_directory
cd /etc/postgresql/8.3/main sudo nano postgresql.conf
Dans postgresql.conf, vous devez avoir la ligne suivante
data_directory = '/var/lib/postgresql/8.3-posix/main'
4. Relancez postgresql après avoir créer les liens des certificates et clefs (pour le SSL)
sudo -s Password: cd /var/lib/postgresql/8.3-posix/main ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt ln -s /etc/postgresql-common/root.crt root.crt ln -s /etc/ssl/private/ssl-cert-snakeoil.key server.key /etc/init.d/postgresql-8.3 restart
5. restaurez le backup
sudo -s -u postgres /usr/lib/postgresql/8.3/bin/psql -f /tmp/backup/backup.dmp
5. Si vous avez utilisé les backports: Postgreql 8.3 fonctionne sur le port 5433 et la version 8.2 sur le port 5432. Vous souhaitez pouvoir utiliser par défaut la version 8.3, crééez le fichier ~/.postgresqlrc, il devrait contenir
8.3 localhost:5433 *

