Postgres psql

Très bon site qui explique le fonctionnement des requêtes et surtout des index

http://use-the-index-luke.com/fr/sql/preface

apt-get install postgresql

Pour un accès distant, changer le mot de passe de l’utilisateur par défaut postgres

su - postgres
psql
postgres=# \password

Modifier le fichier de config pour permettre la connexion à distance
Il est plus propre de créer son propre fichier de config afin d’éviter de modifier le fichier de config de base. Cela facilitera les mises à jour

Editez le fichier de config principal pour qu’il lise votre fichier de config

vi /etc/postgresql/9.4/main/postgresql.conf

Décommentez include_dir = ‘conf.d’

#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------

# These options allow settings to be loaded from files other than the
# default postgresql.conf.

include_dir = 'conf.d'     # include files ending in '.conf' from
          # directory 'conf.d'
#include_if_exists = 'exists.conf'  # include file only if it exists
#include = 'special.conf'   # include file

mkdir /etc/postgresql/9.4/main/conf.d
vi /etc/postgresql/9.4/main/conf.d/maconf.conf

Ajoutez

listen_addresses = '*'

ou remplacé * par les adresses ip qui pourront se connecter au serveur (sinon gérez ça dans le firewall)

Autorisez l’utilisateur postgres à pouvoir se connecter depuis une autre machine

vi /etc/postgresql/9.4/main/pg_hba.conf

et ajoutez la ligne

host    all       0.0.0.0/0      postgres                                md5

0.0.0.0/0 correspond à toutes les ip possibles

Redémarrez le serveur postgresql

service postgresql restart

Avant tout, connectez vous avec l’utilisateur postgres

su - postgres

Pour créer un utilisateur

createuser toto

Créer une base de données pour cette utilisateur

createdb --encoding=UTF8 --owner=toto bdd_toto

Se connecter à une base de données:

su - postgres

$ psql postgres # base de données par défaut
$ psql nom_de_la_bd

Connexion en tant qu’un utilisateur:

$ psql postgres nom_de_la_bd
$ psql -U rickastley nom_de_la_bd

Se connecter en utilisant TCP/IP (psql utilise par défaut un socket UNIX).

$ psql -h localhost -p 5432 nom_de_la_bd

Demander à psql de nous demander le mot de masse:

$ psql -W nom_de_la_bd
Password:

Une fois entré dans l’invite de commande de PostgreSQL, voici quelques commandes utiles.

postgres=# \h                # aide sur les commandes SQL
postgres=# \?                # aide sur les commandes psql
postgres=# \l                # lister les bases de données
postgres=# \l+               # lister les bases de données avec des infos supplémentaires (taille, tablespace etc..)
postgres=# \c nom_bd         # se connecter à une base de données
postgres=# \dn               # liste les schémas
postgres=# \dt               # liste des tables (par défaut du schéma public)
postgres=# \dt *.*           # liste toutes les tables de tous les schémas
postgres=# \dt mon_schema.*  # liste les tables du schéma mon_schema
postgres=# \db               # liste les tablespaces
postgres=# \d nom_table      # schema d'une table donnée
postgres=# \du               # lister les roles
postgres=# \e                # édition dans $EDITOR

À ce stade, vous pouvez simplement entrer des requêtes SQL et elles seront exécutés sur la base de données présentement sélectionnée.

Connaître la taille d’une base

SELECT pg_database_size('maBase');
SELECT pg_size_pretty(pg_database_size('maBase'));

Connaître la taille d’une table sans les index

SELECT pg_size_pretty(pg_relation_size('maTable'));

Connaître la taille d’une table avec les index

SELECT pg_size_pretty(pg_total_relation_size('maTable'));

Afficher toutes les tables et leur taille

SELECT
   relname AS "Table",
   pg_size_pretty(pg_total_relation_size(relid)) AS "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

En environnement de production, il est bien sûr nécessaire de créer des utilisateurs et de leurs donner les bons droits, voici quelques commandes que j’utilise fréquemment.

Création d’un nouvel utilisateur:

CREATE USER rickastley WITH PASSWORD 'unturbomotdepasse';

Création d’une nouvelle base de données:

CREATE DATABASE rickastley_bd;

Et pour terminer, donner les droits sur la base de données rickastley_bd à l’utilisateur rickastley:

GRANT ALL PRIVILEGES ON DATABASE rickastley_bd TO rickastley;

Voir la liste des process

SELECT * FROM pg_stat_activity;

Backup: (-Fc est un format compressé qui prend donc moins de place)

$ pg_dump -U {user-name} {source_db} -Fc -f {dumpfilename.backup}

Consulter la liste des éléments sauvegardés dans le fichier de backup

$ pg_restore -l {dumpfilename.backup}

Vous pouvez copier cette liste dans un fichier et commenter les éléments que vous ne souhaitez pas restaurer. Utilisez ensuite la commande suivante

$ pg_restore -L {fichier_avec_ma_liste} {dumpfilename.backup}

Restore: (-d {destination_db} n’est nécessaire que si le nom de la base de données est différente)

$ pg_restore -U {username} -d {destination_db} {dumpfilename.backup}

Décompressez le fichier .backup pour lire le SQL

$ pg_restore -f {fichier.sql} {dumpfilename.backup}

You can backup all the databases using pg_dumpall command.

$ pg_dumpall > all.sql

Backup a specific postgres table

$ pg_dump --table products -U geekstuff article -f onlytable.sql
$ pg_dump --table products -U geekstuff article -Fc -f onlytable.backup

Pour exporter avec pg_dump et réimporter avec mysql, tapez la commande suivante

pg_dump -d --data-only --no-owner --no-acl --attribute-inserts --disable-dollar-quoting -t NOMdeLAtable -U USERpostgres NOM_de_la_BDD -f VotreFichier.sql

Vous aurez un fichier contenant des requêtes INSERT INTO que mysql sera capable d’interpréter.

Vérifier quand même l’entête du fichier afin de supprimer les lignes de commandes postgres (ex: SET …)

Concordance entre les types mysql et postresql

• https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL

Se connecter au serveur

psql postgres

Puis lancer la requête

SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity;

Admettons un tiret bas spécial de ce type http://www.decodeunicode.org/en/u+2013/properties

Sa valeur hexadécimal est le 0xE28093 pour le rechercher il faut faire la requête suivante

select * from site where adresse like E'%\xE2\x80\x93%'

insert into new_table select distinct * from old_table

Pour une raison inconnue, lors d’un insert dans une table, un message d’erreur me dit

ERROR:  duplicate key value violates unique constraint "pk_id"
DETAIL : Key (id)=(947833) already exists.

or mon max id est à 949000, il y a donc un problème dans la génération des nouveaux id. Voici comment régler le problème

-- repérer le nom de la variable dans la structure de la table
  id INTEGER NOT NULL DEFAULT NEXTVAL('meteo_id_seq'::regclass),
-- ici 'meteo_id_seq'
 
-- afficher le dernier id
SELECT MAX(id) FROM your_table;
 
-- afficher le dernier numero de sequence
-- il devrait être inférieur, ce qui pose bien entendu un problème
SELECT NEXTVAL('your_table_id_seq');
 
-- ici on met à jour le numéro de séquence 
SELECT SETVAL('your_table_id_seq', (SELECT MAX(id) FROM your_table));
 
-- if your tables might have no rows
-- false means the set value will be returned by the next nextval() call    
SELECT SETVAL('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), FALSE);

Installation

apt-get install postgis

Activation dans postgres, se connecter avec psql ou pgadmin et lancer la requête sql

Enable PostGIS (includes raster)

CREATE EXTENSION postgis;

Enable Topology

CREATE EXTENSION postgis_topology;

A tester ⇒ http://postgis.net/docs/performance_tips.html

• http://www.pgbackman.org/
• https://einstein.e-mc2.net/pgbackman/1.2.0/docs/pgbackman-manual-1.2.0.html#introduction

• http://www.pgbarman.org/
• http://docs.pgbarman.org/release/2.3/