====== Postgres psql ======

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

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


===== Installation =====

  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'**
<code>
#------------------------------------------------------------------------------
# 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
</code>

  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

==== Le type d'authentification ====

  * https://docs.postgresql.fr/10/client-authentication.html#auth-pg-hba-conf

Indique la méthode d'authentification à utiliser lors d'une connexion via cet enregistrement. Les choix possibles sont résumés ici ; les détails se trouvent dans la Section 20.2, « Méthodes d'authentification ».

=== trust ===
Autorise la connexion sans condition. Cette méthode permet à quiconque peut se connecter au serveur de bases de données de s'enregistrer sous n'importe quel utilisateur PostgreSQL™ de son choix sans mot de passe ou autre authentification. Voir la Section 20.3.1, « Authentification trust » pour les détails.

=== reject ===
Rejette la connexion sans condition. Ce cas est utile pour « filtrer » certains hôtes d'un groupe, par exemple une ligne reject peut bloquer la connexion d'un hôte spécifique alors qu'une ligne plus bas permettra aux autres hôtes de se connecter à partir d'un réseau spécifique.

=== scram-sha-256 ===
Réalise une authentification SCRAM-SHA-256 afin de vérifier le mot de passe utilisateur. Voir Section 20.3.2, « Authentification par mot de passe » pour les détails.

=== md5 ===
Réalise une authentification SCRAM-SHA-256 ou MD5 afin de vérifier le mot de passe utilisateur. Voir Section 20.3.2, « Authentification par mot de passe » pour les détails.

=== password ===
Requiert que le client fournisse un mot de passe non chiffré pour l'authentification. Comme le mot de passe est envoyé en clair sur le réseau, ceci ne doit pas être utilisé sur des réseaux non dignes de confiance. Voir la Section 20.3.2, « Authentification par mot de passe » pour les détails.

=== gss ===
Utilise GSSAPI pour authentifier l'utilisateur. Disponible uniquement pour les connexions TCP/IP. Voir Section 20.3.3, « Authentification GSSAPI » pour les détails.

=== sspi ===
Utilise SSPI pour authentifier l'utilisateur. Disponible uniquement sur Windows. Voir Section 20.3.4, « Authentification SSPI » pour plus de détails.

=== ident ===
Récupère le nom de l'utilisateur en contactant le serveur d'identification sur le poste client, et vérifie que cela correspond au nom d'utilisateur de base de données demandé. L'authentification Ident ne peut être utilisée que pour les connexions TCP/IP. Pour les connexions locales, elle sera remplacée par l'authentification peer.

=== peer ===
Récupère le nom d'utilisateur identifié par le système d'exploitation du client et vérifie que cela correspond au nom d'utilisateur de base de données demandé. Peer ne peut être utilisée que pour les connexions locales. Voir la Section 20.3.6, « Peer Authentication » ci-dessous pour les details.

=== ldap ===
Authentification par un serveur LDAP. Voir la Section 20.3.7, « Authentification LDAP » pour les détails.

=== radius ===
Authentification par un serveur RADIUS. Voir Section 20.3.8, « Authentification RADIUS » pour les détails.

=== cert ===
Authentification par certificat client SSL. Voir Section 20.3.9, « Authentification de certificat » pour les détails.

=== pam ===
Authentification par les Pluggable Authentification Modules (PAM) fournis par le système d'exploitation. Voir la Section 20.3.10, « Authentification PAM » pour les détails.

=== bsd ===
Authentification utilisant le service BSD Authentication fourni par le système d'exploitation. Voir Section 20.3.11, « Authentification BSD » pour plus de détails.


Redémarrez ou rechargez le serveur postgresql
  service postgresql restart
ou
  systemctl reload postgresql-9.6.service
==== utilisateur, bdd ====

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


===== Base =====

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 -U rickastley -W nom_de_la_bd

Demander à psql de nous demander le mot de masse:

  $ psql -U rickastley -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.

===== Taille =====

Connaître la taille de toutes les bases et trier par la plus grosse.
<code sql>
SELECT pg_database.datname, 
pg_size_pretty(pg_database_size(pg_database.datname)) AS size , pg_database_size(pg_database.datname) as octets
FROM pg_database order by octets desc;
</code>

Connaître la taille d'une base
<code sql>
SELECT pg_database_size('maBase');
SELECT pg_size_pretty(pg_database_size('maBase'));
</code>

Connaître la taille d'une table sans les index
<code sql>SELECT pg_size_pretty(pg_relation_size('maTable'));</code>

Connaître la taille d'une table avec les index
<code sql>SELECT pg_size_pretty(pg_total_relation_size('maTable'));</code>

Afficher toutes les tables et leur taille
<code sql>
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;
</code>

===== Création =====

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:

<code sql>CREATE USER rickastley WITH PASSWORD 'unturbomotdepasse';</code>

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

<code sql>CREATE DATABASE rickastley_bd;</code>

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

<code sql>GRANT ALL PRIVILEGES ON DATABASE rickastley_bd to rickastley;</code>
  
  
Voir la liste des process
  
<code sql>SELECT * from pg_stat_activity;</code>
  
===== Backup =====


  # dump to single SQL file
  $ pg_dump -d mydb -n public -f mydb.sql

  # dump to a custom format file
  $ pg_dump -d mydb -n public -Fc -f mydb.pgdmp


  # restoring from a SQL dump file, the simple version
  $ psql -d mydb_new < mydb.sql

  # restoring from a SQL dump file, the recommended version
  $ PGOPTIONS='--client-min-messages=warning' psql -X -q -1 -v ON_ERROR_STOP=1 --pset pager=off -d mydb_new -f mydb.sql -L restore.log

  # restoring from a dump written to a custom format file
  $ pg_restore -d mydb_new -v -1 mydb.pgdmp

  # restore a single table from the dump
  $ pg_restore -d mydb_new --table=mytable -v -1 mydb.pgdmp

  # restore a single function from the dump
  $ pg_restore -d mydb_new --function=myfunc -v -1 mydb.pgdmp


--- https://www.opsdash.com/blog/postgresql-backup-restore.html


Option de pg_restore
  * ''-a'' ne restaure que les données
  * ''-c'' efface les objets avant de les recréer (ajouter ''--if-exists'' pour éviter les messages d'erreurs en cas de suppression d'un objet qui n'existe pas
  * ''-C'' crée la database avec le nom renseigné dans ''-d''
  * ''-e'' exit on error
  * ''-I'' ne restaure que l'index spécifié
  * ''-P'' ne restaure que la fonction spécifiée
  * ''-T'' ne restaure que le trigger spécifié
  * ''-l'' liste les objets sauvegardés
  * ''-d'' spécifie la database
  * ''-n'' spécifie le schéma
  * ''-t'' spécifie la table



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


Autre facon de faire un backup sous forme de fichier

  pg_dump -Fd -f dbName_objects -j 10 -t 'thr_*' -U userName dbName

Cette commande utilise ''-Fd'' qui est un format de backup sous forme de fichier\\
''-f'' spécifie non pas le nom du fichier de backup mais le répertoire\\
''-j 10'' spécifie 10 jobs simultanés pour faire le backup\\
''-t'' permet de définir un pattern pour ne sauvegarder que certaine table avec un prefixe ou autre

L'inconvénient est que les fichiers ont le nom de l'id des objets, difficile donc de savoir ou est la table que l'on recherche.

Utilisez la commande suivante pour voir la correspondance

  pg_restore --list -Fd dbName_objects/ | grep 'TABLE DATA'

in order to have each file not compressed (in raw SQL)

  pg_dump --data-only --compress=0 --format=directory --file=dbName_objects --jobs=10 --table='thr_*' --username=userName -- dbname=dbName
  
Sinon un script bash qui boucle sur la liste des tables 

<code bash>
#!/bin/bash

# Config:
DB=rezopilotdatabase
U=postgres
# tablename searchpattern, if you want all tables enter "":
P=""
# directory to dump files without trailing slash:
DIR=~/psql_db_dump_dir

mkdir -p $DIR
TABLES="$(psql -d $DB -U $U -t -c "SELECT table_name FROM 
information_schema.tables WHERE table_type='BASE TABLE' AND table_name 
LIKE '%$P%' ORDER BY table_name")"
for table in $TABLES; do
  echo backup $table ...
  pg_dump $DB -U $U -w -t $table > $DIR/$table.sql;
done;
echo done
</code>


===== pg_dump to mysql =====

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



===== Qui fait quoi à l'instant T =====

Se connecter au serveur
  su postgres
  psql
ou
  psql postgres

Puis lancer la requête
<code sql>
Version 9.1
SELECT datname,usename,application_name,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity;</code>

<code sql>
Version 9.6
SELECT datname,usename,application_name,pid,client_addr,state,query_start,query FROM pg_stat_activity;</code>

<code sql>
Version 11
SELECT datname,usename,application_name,pid,client_addr,state,query_start,query FROM pg_stat_activity;</code>

Repérer le PID qui vous intéresse puis terminez la requête
  SELECT pg_cancel_backend(PID);
ou tuez la
  SELECT pg_terminate_backend(PID);

===== Requête sur un caractère spécial =====

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%'

===== Copier une table dans une autre en ignorant les doublons =====

  insert into new_table select distinct * from old_table

===== Réinitialiser la séquence d'un id (clé primaire) =====
Pour une raison inconnue, lors d'un insert dans une table, un message d'erreur me dit 
<code>ERROR:  duplicate key value violates unique constraint "pk_id"
DETAIL : Key (id)=(947833) already exists.
</code>

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

<code sql>
-- 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);
</code>

Alternative

<code sql>
ALTER SEQUENCE meteo_id_seq RESTART WITH 1453
</code>
===== postgis =====

Installation 
  apt-get install postgis

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

  psql -d nom_bdd

Enable PostGIS (includes raster)
  CREATE EXTENSION postgis;
Enable Topology
  CREATE EXTENSION postgis_topology;


Sinon à la main, retrouvez tous les fichiers sql à importer dans:
  /usr/pgsql-XX/share/contrib/postgis-2.X/
  psql -d madatabase -f ....../....sql

Doc => http://postgis.net/docs/postgis_installation.html#idm559


A tester => http://postgis.net/docs/performance_tips.html


https://gis.stackexchange.com/questions/43187/using-schema-other-than-public-in-postgis
  UPDATE pg_extension SET extrelocatable = TRUE WHERE extname = 'postgis';


Pour tester si postgis fonctionne et voir sa version
  select postgis_version()

===== Outils de backup =====

  * 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/


===== Analiser une requete =====

<code sql>
begin;
explain (analyze,buffers,timing)
delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6';
rollback;
</code>

===== Point de vigilance =====

La fonction SUM() peut retourner une valeur arrondie

Dans ce cas, caster le champ en décimal 
  SUM( colonne::decimal )