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

• 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 ».

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.

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.

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.

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.

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.

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

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

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.

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.

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

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

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

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.

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

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

Connaître la taille de toutes les bases et trier par la plus grosse.

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;

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;

# 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

#!/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

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

su postgres
psql

ou

psql postgres

Puis lancer la requête

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

Version 9.6
SELECT datname,usename,application_name,pid,client_addr,state,query_start,query FROM pg_stat_activity;

Version 11
SELECT datname,usename,application_name,pid,client_addr,state,query_start,query FROM pg_stat_activity;

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);

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);

Alternative

ALTER SEQUENCE meteo_id_seq RESTART WITH 1453

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()

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

BEGIN;
EXPLAIN (analyze,buffers,timing)
DELETE FROM mydata WHERE id='897b4dde-6a0d-4159-91e6-88e84519e6b6';
ROLLBACK;

La fonction SUM() peut retourner une valeur arrondie

Dans ce cas, caster le champ en décimal

SUM( colonne::decimal )