Connexion

Usager postgres

sudo su - postgres

Outils

psql
client texte
psql -l -t
liste des base de données
pg_dump
Crée une archive d'une base de données
pg_restore
Réintègre une archive

Base de données

Il est conseiller d'utiliser un nom de base de données en lettre minuscule car certaines commande mettre en minuscule le nom automatiquement. Par exemple:

$ createdb databaseName;
$ psql
# CREATE ROLE myUser LOGIN PASSWORD 'myPassword'; -- cette commande crée l'utilisateur myuser (en minuscule)
# CREATE ROLE "myUser" LOGIN PASSWORD 'myPassword'; -- cette commande crée l'utilisateur myUser (voir avec \du)

# GRANT ALL PRIVILEGES ON DATABASE databaseName TO myUser; -- cette commande ne fonctionne pas car databaseName est convertit en databasename (minuscule) et myUser en myuser
# GRANT ALL PRIVILEGES ON DATABASE "databaseName" TO "myUser"; -- cette commande fonctionne (voir avec \l)

Créer une base de données pour fr_CA.utf8

J'ai essayé avec fr.CA sans l'encodage, et cela ne fonctionne pas.

service postgresql stop


# si le cluster existe déjà (vide)
/usr/bin/pg_dropcluster main

# vérification configuration dans /etc/postgresql/9.1/...

# créer et démarrer
/usr/bin/pg_createcluster --locale fr_CA.utf8 9.1 main
service postgresql start

# verification
psql -p5432 -c "SHOW lc_collate"

Action

\c NOM_BASE
Se connecter à une base de données

Interrogation

\?
Aide de psql
\l
Liste des base de données ou (SELECT oid,datname from pg_database)
?
Liste des schémas
\dt+
Liste les tables (sans schémas)
\dt+ *.*
Liste les tables (tous les schémas)
\dt+ NOM_SCHEMAS.*
Liste les tables (un seul schéma)
\du
Liste des utilisateurs
Liste des requêtes
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_userid(s.backendid) as userid, pg_stat_get_backend_activity_start(s.backendid) as start FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Objets de la base de données

DATABASE

CREATE DATABASE NOM_BASE;
Créer une base de données
DROP DATABASE NOM_BASE;
Supprimer une base de données

SEQUENCE

SELECT setval('sequencename_uid_seq', 1);
Change la valeur d'une séquence à 1

TABLE

GRANT ALL ON nomtable TO nomutilisateur;
Sélection
ALTER TABLE nomtable OWNER TO nomutilisateur;
Update/delete/sélection

Maintenance

Pour connaître le nom des tables, nombre (approximatif) de lignes et le numéro du fichiers (en étant connecter à une base de données):

select relname, reltuples, relfilenode from pg_class order by reltuples order by reltuples;

Les fichiers se retrouvent dans le répertoire avec le nom de fichier suivant:
/var/lib/postgresql/8.4/main/base/{pg_database.oid}/{pg_class.relfilenode}

Archive (backup)

# fichier sql
pg_dump -f nomfichier.pg_dump NOM_BASE

# restoration sql
psql> \c NOM_BASE
psql> \i nomfichier.pg_dump

# archive "custom = Fc" et restoration
pg_dump -b -f nomfichier.pg_dump -Fc NOM_BASE
pg_restore -c -d NOM_BASE nomfichier.pg_dump

Note de mise à jour de postgresql 8.2 vers 8.3 (lenny, sid)

Étape 1 - faire un backup

user$ su - 
root# su - postgres
postgres$ pg_dumpall > pg_dumpall_`date +%Y%m%d`.dump

Installer les nouveaux packages

user$ su - 
root# /etc/init.d/postgresql-8.2 stop
# s'assurer que la locale LC_ALL est correct avec la commande locale (soit fr_CA.utf8)
apt-get install postgresql-8.3 postgresql-client-8.3  postgresql-contrib-8.3

Vérifier la locale de 8.3

user$ su - 
root# su - postgres
psql -c "SHOW lc_collate"

Essayer un upgrade de cluster

user$ su - 
root# pg_dropcluster --stop 8.3 main
root# /etc/init.d/postgresql 8.3 stop
root# /etc/init.d/postgresql 8.2 start
root# pg_upgradecluster -v 8.3 8.2 main

Vérifier le fonctionnement du nouveau cluster

# vérifier que postgresql 8.3 fonctionne
user$ ps ax | grep postgresql | grep -v grep
# on peut aussi vérifier la lc_locate, mais c'est celle du système (root) qui devraient être utilisée.

# si on efface le cluster de 8.2, on devrait déplacer le dumpall vers un répertoire autre que /tmp comme /root.
root# pg_dropcluster 8.2 main

Migration 8.1 vers 9.1

Le mot clé "window" a été ajouté en 8.3. Il faut donc l'entourer de guillemets doubles dans les requêtes CREATE TABLE, INSERT, ...

CREATE TABLE (...  "window" TEXT NOT NULL, ...);

La barre oblique inversée (backslash) avec un guillemet simple provoque une erreur, il faut donc remplacer \' par les triples guillemets simples: '''.