Ci-dessous, les différences entre deux révisions de la page.
Les deux révisions précédentes Révision précédente Prochaine révision | Révision précédente | ||
linux:postgresql [31-08-2016 16:50] edmc73 |
linux:postgresql [23-03-2023 14:57] (Version actuelle) edmc73 [Taille] |
||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
====== Postgres psql ====== | ====== Postgres psql ====== | ||
+ | |||
+ | ===== Doc ===== | ||
+ | Très bon site qui explique le fonctionnement des requêtes et surtout des index | ||
+ | |||
+ | http:// | ||
+ | |||
===== Installation ===== | ===== Installation ===== | ||
Ligne 43: | Ligne 49: | ||
0.0.0.0/0 correspond à toutes les ip possibles | 0.0.0.0/0 correspond à toutes les ip possibles | ||
+ | ==== Le type d' | ||
- | Redémarrez le serveur postgresql | + | * https:// |
+ | |||
+ | Indique la méthode d' | ||
+ | |||
+ | === trust === | ||
+ | Autorise la connexion sans condition. Cette méthode permet à quiconque peut se connecter au serveur de bases de données de s' | ||
+ | |||
+ | === 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' | ||
+ | |||
+ | === gss === | ||
+ | Utilise GSSAPI pour authentifier l' | ||
+ | |||
+ | === sspi === | ||
+ | Utilise SSPI pour authentifier l' | ||
+ | |||
+ | === ident === | ||
+ | Récupère le nom de l' | ||
+ | |||
+ | === peer === | ||
+ | Récupère le nom d' | ||
+ | |||
+ | === 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' | ||
+ | |||
+ | === bsd === | ||
+ | Authentification utilisant le service BSD Authentication fourni par le système d' | ||
+ | |||
+ | |||
+ | Redémarrez | ||
service postgresql restart | service postgresql restart | ||
+ | ou | ||
+ | systemctl reload postgresql-9.6.service | ||
+ | ==== utilisateur, | ||
+ | |||
+ | Avant tout, connectez vous avec l' | ||
+ | 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 ===== | ===== Base ===== | ||
Ligne 62: | Ligne 128: | ||
Se connecter en utilisant TCP/IP (psql utilise par défaut un socket UNIX). | Se connecter en utilisant TCP/IP (psql utilise par défaut un socket UNIX). | ||
- | $ psql -h localhost -p 5432 nom_de_la_bd | + | $ psql -h localhost -p 5432 -U rickastley -W nom_de_la_bd |
Demander à psql de nous demander le mot de masse: | Demander à psql de nous demander le mot de masse: | ||
- | $ psql -W nom_de_la_bd | + | $ psql -U rickastley |
Password: | Password: | ||
Ligne 74: | Ligne 140: | ||
postgres=# \? # aide sur les commandes psql | postgres=# \? # aide sur les commandes psql | ||
postgres=# \l # lister les bases de données | 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 | postgres=# \c nom_bd | ||
postgres=# \dn # liste les schémas | postgres=# \dn # liste les schémas | ||
Ligne 87: | Ligne 154: | ||
===== Taille ===== | ===== 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; | ||
+ | </ | ||
Connaître la taille d'une base | Connaître la taille d'une base | ||
Ligne 131: | Ligne 205: | ||
| | ||
===== Backup ===== | ===== 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=' | ||
+ | |||
+ | # 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:// | ||
+ | |||
+ | |||
+ | Option de pg_restore | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | * '' | ||
+ | |||
+ | |||
Backup: (-Fc est un format compressé qui prend donc moins de place) | Backup: (-Fc est un format compressé qui prend donc moins de place) | ||
Ligne 142: | Ligne 258: | ||
Restore: (-d {destination_db} n'est nécessaire que si le nom de la base de données est différente) | 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} | $ 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. | You can backup all the databases using pg_dumpall command. | ||
Ligne 150: | Ligne 269: | ||
$ pg_dump --table products -U geekstuff article -f onlytable.sql | $ 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 ' | ||
+ | |||
+ | Cette commande utilise '' | ||
+ | '' | ||
+ | '' | ||
+ | '' | ||
+ | |||
+ | L' | ||
+ | |||
+ | Utilisez la commande suivante pour voir la correspondance | ||
+ | |||
+ | pg_restore --list -Fd dbName_objects/ | ||
+ | |||
+ | 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=' | ||
+ | | ||
+ | Sinon un script bash qui boucle sur la liste des tables | ||
+ | |||
+ | <code bash> | ||
+ | #!/bin/bash | ||
+ | |||
+ | # Config: | ||
+ | DB=rezopilotdatabase | ||
+ | U=postgres | ||
+ | # tablename searchpattern, | ||
+ | P="" | ||
+ | # directory to dump files without trailing slash: | ||
+ | DIR=~/ | ||
+ | |||
+ | mkdir -p $DIR | ||
+ | TABLES=" | ||
+ | information_schema.tables WHERE table_type=' | ||
+ | LIKE ' | ||
+ | for table in $TABLES; do | ||
+ | echo backup $table ... | ||
+ | pg_dump $DB -U $U -w -t $table > $DIR/ | ||
+ | done; | ||
+ | echo done | ||
+ | </ | ||
+ | |||
===== pg_dump to mysql ===== | ===== pg_dump to mysql ===== | ||
Ligne 169: | Ligne 334: | ||
Se connecter au serveur | Se connecter au serveur | ||
+ | su postgres | ||
+ | psql | ||
+ | ou | ||
psql postgres | psql postgres | ||
Puis lancer la requête | Puis lancer la requête | ||
- | <code sql> | + | <code sql> |
+ | Version 9.1 | ||
+ | SELECT datname, | ||
+ | |||
+ | <code sql> | ||
+ | Version 9.6 | ||
+ | SELECT datname, | ||
+ | |||
+ | <code sql> | ||
+ | Version 11 | ||
+ | SELECT datname, | ||
+ | |||
+ | 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 ===== | ===== Requête sur un caractère spécial ===== | ||
Ligne 214: | Ligne 397: | ||
</ | </ | ||
+ | Alternative | ||
+ | |||
+ | <code sql> | ||
+ | ALTER SEQUENCE meteo_id_seq RESTART WITH 1453 | ||
+ | </ | ||
===== postgis ===== | ===== postgis ===== | ||
Ligne 221: | Ligne 409: | ||
Activation dans postgres, se connecter avec psql ou pgadmin et lancer la requête sql | Activation dans postgres, se connecter avec psql ou pgadmin et lancer la requête sql | ||
- | -- Enable PostGIS (includes raster) | + | |
+ | |||
+ | Enable PostGIS (includes raster) | ||
CREATE EXTENSION postgis; | CREATE EXTENSION postgis; | ||
- | -- Enable Topology | + | Enable Topology |
CREATE EXTENSION postgis_topology; | CREATE EXTENSION postgis_topology; | ||
+ | |||
+ | |||
+ | Sinon à la main, retrouvez tous les fichiers sql à importer dans: | ||
+ | / | ||
+ | psql -d madatabase -f ....../ | ||
+ | |||
+ | Doc => http:// | ||
+ | |||
+ | |||
+ | A tester => http:// | ||
+ | |||
+ | |||
+ | https:// | ||
+ | UPDATE pg_extension SET extrelocatable = TRUE WHERE extname = ' | ||
+ | |||
+ | |||
+ | Pour tester si postgis fonctionne et voir sa version | ||
+ | select postgis_version() | ||
+ | |||
+ | ===== Outils de backup ===== | ||
+ | |||
+ | * http:// | ||
+ | * https:// | ||
+ | |||
+ | * http:// | ||
+ | * http:// | ||
+ | |||
+ | |||
+ | ===== Analiser une requete ===== | ||
+ | |||
+ | <code sql> | ||
+ | begin; | ||
+ | explain (analyze, | ||
+ | delete from mydata where id=' | ||
+ | rollback; | ||
+ | </ | ||
+ | |||
+ | ===== Point de vigilance ===== | ||
+ | |||
+ | La fonction SUM() peut retourner une valeur arrondie | ||
+ | |||
+ | Dans ce cas, caster le champ en décimal | ||
+ | SUM( colonne:: | ||
+ |