PDF Note
Bloc-note 'postgreSQL'
Le 3 janvier 2006 à 01:09.

Bloc Notes sur Postgresql

Initialisation d'un espace de travail

Créer un espace de travail dans le dossier /bases/postgresql (doit être vide et existant)

initdb /bases/postgresql

Lancement manuel

Exécuter dans un terminal

postmaster -D /bases/postgresql

Création d'un utilisateur

Ajout de l'utilisateur mon_user dans la base par défaut

createuser -P mon_user

Création d'une base de donnée

createdb -U mon_user ma_base

Etendre l'accès socket

Modifier /var/lib/pgsql/postgresql.conf et passer la valeur de localhost à * en ayant pris soin de décommenter la ligne.

Dans le fichier /var/lib/pgsql/data/pg_hba.conf ajouter :

host all all 192.168.0.1/24 trust

Création de tables

Auto incrémentation des id

La commande SQL suivante permet de créer un ID unique, auto-incrémenté, utilisé comme clef primaire. Cela correspond à la commande mySql id int not null auto_increment primary key.

id serial NOT NULL PRIMARY KEY

A noter que cet "raccourci" induit la création d'une séquence nommée nomTable_nomChamp_seq et d'une contrainte "clef primaire" nommée nomTable_pkey.

Clefs étrangères

Soit une table TA avec comme clef primaire TA.id et une table TB contenant un champ TA_id. On exprime la valeur de TA_id comme référence à la clef étrangère TA.id par la commande suivante :

TA_id integer REFERENCE TA(id)

Les énumérations

La commande SQL suivante permet de gérer une valeur d'énumération pour valeur d'un champ de table. Ceci corresponda à la syntaxe mySql couleur enum('rouge', 'bleu') default 'rouge'

couleur text CHECK (couleur IN ('rouge'::text,'bleu'::text)) DEFAULT 'rouge',

Vacuum

Dans la longue série du je parle avant de comprendre, j'ai failli récemment virer postgreSql pour cause de trop grosse consommation de CPU. 80% pour afficher une page de ce site pour un utilisateur me semblait déraisonnable... Heureusement, après quelques recherches, je me suis rendu compte d'un oubli de taille, le vaccuum. Le Vacuum est à lancer après l'installation qui précède et de manière récurrente par le suite pour optimiser l'organisation de l'espace de stockage de postgresql. La commande "magique" qui m'a donc permis de passer de 80% à 0.5% de cpu fût :

vacuumdb --analyze --verbose --all -U mon_user

Cela a mouliné un certain temps et après, le miracle était accomplis...

Faire un backup

Pour faire un backup SQL de toute la base :

pg_dumpall -U db_user -h localhost

Fusionner une colonne date et une colonne time dans une colonne timestamp

UPDATE ma_tavle SET timestamp = CAST(date || ' ' || time AS timestamp);

Utiliser syslog comme sortie des traces

Modifier le fichier /var/lib/pgsql/postgresql.conf et changer la valeur de log_destination à syslog

Liste des bases de données

psql -l
# équivalent à /l dans psql

Recopier une base complète d'une machine à une autre

Violent et efficace Wink

#! /bin/sh

# ./update-db.sh MACHINE BASE
dropdb $2 -U postgres
createdb --encoding=UNICODE -U postgres $2
ssh $1 "pg_dump -U postgres $2 | gzip" | gunzip | psql -U postgres  $2

Insertion à partir d'un select

INSERT INTO books (id, title, author_id, subject_id)
   SELECT NEXTVAL('book_ids'), title, author_id, subject_id
   FROM book_queue WHERE approved;

Chercher/remplacer

UPDATE foin SET meule=REPLACE(filepath,'aiguille','ciseaux') WHERE meule LIKE '%aiguille%';

Mise à jour à partit d'un select

UPDATE
   node_project_release
SET
   project=uri
FROM
   (
      SELECT
        r.nid,p.uri
      FROM
         node_project_release r
      INNER JOIN node_project p ON p.nid=r.pid
   ) AS foo

WHERE
   node_project_release.nid=foo.nid;

Changer d'un coup le propriétaire des tables d'une base

echo '\d' | psql -U postgres base | awk ' {printf("alter table %s  owner to postgres;\n", $3)}' | psql -U postgres base

Commentaires

Poster un nouveau commentaire

Le contenu de ce champ est gardé secret et ne sera pas montré publiquement.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • To highlight piece of code, just surround them with <code type="language"> Your code &tl;/code>>. Language can be java,c++,bash,etc... Everything Geshi support.
  • Les lignes et les paragraphes vont à la ligne automatiquement.
  • Textual smileys will be replaced with graphical ones.
  • Les adresses de pages web et de messagerie électronique sont transformées en liens automatiquement.

Plus d'informations sur les options de formatage

Rechercher
Connexion utilisateur
Du même tonneau...
Abonnements
Les derniers bavardages...
  • Ulhume: @JIL merci pour l'info, effectivement il sem ...
  • JJL: Ulhume: pour info ce dont tu parles (différe ...
  • Ulhume: Non, c'est toi qui a raison :) C'est corrigé.
  • feilong74: Problème avec ce tuto, les deux lignes ci-de ...
  • Ulhume: @cenwen attends un peu avant d'imprimer, je ...
Autres Lieux