PostgreSQL

ProgYSM Carte Développement Documentation Données Test Yan Morin

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
\dt nomtable: Affiche le nom du propriétaire
ALTER TABLE nomtable OWNER TO nomutilisateur;: Update/delete/sélection
COPY table TO '/tmp/table.copy';: Copie

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
# fichier sql (exclusion de schema s1 et schema s2)
pg_dump --exclude-schema s1 --exclude-schema s2 -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

# on different port
psql> CREATE DATABASE bla;
psql> CREATE ROLE bla;
pg_restore p 5434 -c -d NOM_BASE nomfichier.pg_dump
psql> \c bla

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: '''.

Migration 9.4 vers 9.6

## vérification des packages
# dpkg -l post*
ii  postgresql-9.4                                 9.4.19-0+deb8u1              amd64                        object-relational SQL database, version 9.4 server
ii  postgresql-9.6                                 9.6.10-0+deb9u1              amd64                        object-relational SQL database, version 9.6 server

## vérification des ports utilisé
# netstat -lntp | grep postgres
(Tous les processus ne peuvent être identifiés, les infos sur les processus
non possédés ne seront pas affichées, vous devez être root pour les voir toutes.)
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      585/postgres        
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      555/postgres        
tcp6       0      0 ::1:5432                :::*                    LISTEN      585/postgres        
tcp6       0      0 ::1:5433                :::*                    LISTEN      555/postgres 

# vérification port 5433, base de données
# su - postgres
$ psql -p 5433
psql (9.6.10)
postgres-# \l
                                  Liste des bases de données
    Nom    | Propriétaire | Encodage | Collationnement | Type caract. |    Droits d'accès     
-----------+--------------+----------+-----------------+--------------+-----------------------
 postgres  | postgres     | UTF8     | fr_CA.UTF-8     | fr_CA.UTF-8  | 
 template0 | postgres     | UTF8     | fr_CA.UTF-8     | fr_CA.UTF-8  | =c/postgres          +
           |              |          |                 |              | postgres=CTc/postgres
 template1 | postgres     | UTF8     | fr_CA.UTF-8     | fr_CA.UTF-8  | =c/postgres          +
           |              |          |                 |              | postgres=CTc/postgres

18.6 Upgrading a PostgreSQL Cluster
https://www.postgresql.org/docs/%EF%BC%99.6/upgrading.html

Way 1:
pg_dumpall > outputfile
service postgresql stop

remove...
psql -d postgres -f outputfile

ou:
Way 2:
pg_dumpall -p 5432 | psql -d postgres -p 5433

Way 3:
pg_upgrade...

Suppression

pg_dumapll -p 5432 > /backup/postgres/date-pg_dumpall-v9.4.sql
root# service postgresql stop
postgres$ pg_dropcluster 9.4 main   # no longer in /var/lib/postgresql/9.4
root# apt remove postgresql-9.4 postgresql-client-9.4 postgresql-contrib-9.4
root# vim /etc/postgresql/9.6/main/postgresql.conf
port = 5432

root# service postgresl start

Hyperliens...

Lire les commentaires | Laisser un commentaire

doc.progysm.com

Rechercher

Voir aussi...