Ceci est une ancienne révision du document !
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
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=# \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}
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
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
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
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;