Commandes et scripts Postgres. Commandes PostgreSQL de base Comment connaître le temps d'exécution d'une requête

Dans cet article, je vais vous montrer 15 des commandes les plus utiles pour gérer postgreSQL.

1. Comment changer le mot de passe root dans PostgreSQL ?

$ /usr/local/pgsql/bin/psql postgres postgres Mot de passe : (ancien mot de passe) # ALTER USER postgres WITH PASSWORD 'tmppassword'; $ /usr/local/pgsql/bin/psql postgres postgres Mot de passe : (tmppassword)

Changer le mot de passe pour un utilisateur régulier est le même. L'utilisateur root peut changer le mot de passe pour n'importe quel utilisateur.

# ALTER USER nom d'utilisateur AVEC MOT DE PASSE 'tmppassword' ;

2. Comment configurer PostgreSQL pour qu'il s'exécute automatiquement ?

$ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib / start-scripts / linux /etc/rc.d/init.d/postgresql # chmod a + x / etc / rc.d / init.d / postgresql

3. Vérification de l'état du serveur

$ /etc/init.d/postgresql status Mot de passe : pg_ctl : le serveur est en cours d'exécution (PID : 6171) / usr / local / pgsql / bin / postgres « -D » « / usr / local / pgsql / data » [ Commenter: Ce message indique que le serveur est opérationnel et fonctionne normalement] $ /etc/init.d/postgresql status Mot de passe : pg_ctl : aucun serveur en cours d'exécution [ Commenter: Ce message indique que le serveur n'est pas en cours d'exécution]

4. Comment démarrer, arrêter, redémarrer PostgreSQL ?

# arrêt du service postgresql Arrêt de PostgreSQL : le serveur s'est arrêté ok # démarrage du service postgresql Démarrage de PostgreSQL : ok # redémarrage du service postgresql Redémarrage de PostgreSQL : le serveur s'est arrêté ok

5. Comment puis-je voir quelle version de PostgreSQL est en cours d'exécution ?

$ /usr/local/pgsql/bin/psql test Bienvenue dans psql 8.3.7, le terminal interactif PostgreSQL. Tapez : \ copyright pour les termes de distribution \ h pour obtenir de l'aide sur les commandes SQL \? pour obtenir de l'aide avec les commandes psql \ g ou terminer par un point-virgule pour exécuter la requête \ q pour quitter test = # sélectionnez la version (); version —————————————————————————————————- PostgreSQL 8.3.7 sur i686-pc-linux-gnu, compilé par GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 rangée) test = #

5. Comment créer un utilisateur dans PostgreSQL ?

Il existe deux méthodes pour cela ..

Méthode 1 : Créez un utilisateur via le shell PSQL à l'aide de la commande CREATE USER.

# CRÉER UN UTILISATEUR ramesh AVEC le mot de passe 'tmppassword' ; CRÉER UN RLE

Méthode2: Créez un utilisateur dans la commande createuser via le shell.

$ /usr/local/pgsql/bin/createuser sathiya Le nouveau rôle sera-t-il un superutilisateur ? (o / n) n Le nouveau rôle sera-t-il autorisé à créer des bases de données ? (o / n) n Le nouveau rôle sera-t-il autorisé à créer davantage de nouveaux rôles ? (o / n) n CRÉER UN RLE

6. Comment créer une base de données dans PostgreSQL ?

Il existe 2 méthodes pour cela.

Méthode1: Créez une base de données via le shell PSQL à l'aide de la commande CREATE DATABASE.

# CRÉER LA BASE DE DONNÉES mydb AVEC LE PROPRIÉTAIRE ramesh ; CRÉER UNE BASE DE DONNÉES

Méthode2: Nous utilisons la commande createdb.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh CRÉER UNE BASE DE DONNÉES

7. Obtenir une liste de toutes les bases de données dans Postgresql ?

8. Comment supprimer une base de données dans PostgreSQL ?

9. Utilisation de l'aide intégrée pour les commandes

Équipe \? affichera la ligne d'aide pour la commande PSQL. \ h CREATE affichera l'aide pour toutes les commandes commençant par CREATE.

# \? # \ h CRÉER # \ h CRÉER UN INDICE

10. Comment obtenir une liste de toutes les tables d'une base de données dans Postgresql ?

# \ ré

Pour une base vide, vous recevrez le message "Aucune relation trouvée".

11. Comment puis-je connaître le délai d'exécution d'une demande ?

# \ timing - après l'exécution de cette commande, chaque requête suivante affichera le temps d'exécution.

# \ Horaire Le chronométrage est activé. # SELECT * de pg_catalog.pg_attribute ; Temps : 9,583 ms

12. Comment sauvegarder et restaurer des bases de données et des tables dans PostgreSQL ?

Cette question est assez vaste et je la publierai plus tard dans un article séparé.

13. Comment voir la liste des fonctions disponibles dans PostgreSQL ?

Pour une liste des fonctions disponibles, dites \ df +

# \ df # \ df +

14. Comment éditer une requête PostgreSQL dans l'éditeur ?

# \ e

\ e ouvrira un éditeur dans lequel vous pourrez éditer la requête et l'enregistrer.

15. Où puis-je trouver le fichier d'historique postgreSQL ?

Comme le fichier ~ / .bash_history, postgreSQL stocke toutes les commandes SQL dans le fichier ~ / .psql_history.

$ chat ~ / .psql_history modifier l'utilisateur postgres avec le mot de passe 'tmppassword' ; \ h modifier l'utilisateur sélectionner la version (); créer l'utilisateur ramesh avec le mot de passe 'tmppassword' ; \ timing select * from pg_catalog.pg_attribute;

15 commandes PostgreSQL utiles

Il existe de nombreux tutoriels PostgreSQL sur le net qui décrivent les commandes de base. Mais au fur et à mesure que vous approfondissez le travail, des problèmes pratiques surgissent qui nécessitent des équipes avancées.

De telles commandes, ou extraits de code, sont rarement documentés. Regardons quelques exemples, utiles à la fois pour les développeurs et les administrateurs de bases de données.

Obtenir des informations sur une base de données

Taille de la base de données

Pour obtenir la taille physique des fichiers (stockage) de la base de données, nous utilisons la requête suivante :

SELECT pg_database_size (current_database ());

Le résultat sera présenté sous la forme d'un nombre comme 41809016.

current_database() est une fonction qui renvoie le nom de la base de données actuelle. Au lieu de cela, vous pouvez entrer un nom dans le texte :

SELECT pg_database_size ("my_database");

Afin d'obtenir des informations sous une forme lisible par l'homme, nous utilisons la fonction pg_size_pretty :

SELECT pg_size_pretty (pg_database_size (current_database ()));

En conséquence, nous obtenons des informations de la forme 40 Mb.

Liste des tableaux

Parfois, vous souhaitez obtenir une liste des tables de base de données. Pour ce faire, utilisez la requête suivante :

SELECT table_name FROM information_schema.tables O table_schema NOT IN ("information_schema", "pg_catalog");

information_schema est un schéma de base de données standard qui contient des collections de vues telles que des tables, des champs, etc. Les vues de table contiennent des informations sur toutes les tables d'une base de données.

La requête décrite ci-dessous sélectionnera toutes les tables du schéma spécifié de la base de données actuelle :

SELECT table_name FROM information_schema.tables O table_schema NOT IN ("information_schema", "pg_catalog") ET table_schema IN ("public", "myschema");

Dans la dernière clause IN, vous pouvez spécifier le nom d'un schéma spécifique.

Taille du tableau

Par analogie avec l'obtention de la taille de la base de données, la taille des données de la table peut être calculée à l'aide de la fonction correspondante :

SELECT pg_relation_size ("comptes");

La fonction pg_relation_size renvoie la quantité d'espace que la couche spécifiée de la table ou de l'index spécifié occupe sur le disque.

Le nom de la plus grande table

Afin d'afficher une liste des tables de la base de données courante, triées par taille de table, exécutez la requête suivante :

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Afin d'afficher des informations sur la plus grande table, nous limitons la requête à l'aide de LIMIT :

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;

relname est le nom de la table, de l'index, de la vue, etc.
relpages - la taille de la vue sur disque de cette table en nombre de pages (par défaut, une page fait 8 Ko).
pg_class est une table système qui contient des informations sur les relations entre les tables de la base de données.

Liste des utilisateurs connectés

Pour connaître le nom, l'IP et le port utilisé des utilisateurs connectés, exécutez la requête suivante :

SELECT nom dat, nom d'utilisateur, client_addr, client_port FROM pg_stat_activity;

Activité de l'utilisateur

Pour connaître l'activité de connexion d'un utilisateur spécifique, nous utilisons la requête suivante :

SELECT datname FROM pg_stat_activity WHERE usename = "devuser";

Utilisation des champs de données et de table

Supprimer les lignes en double

S'il se trouve que la table n'a pas de clé primaire, il est probable qu'il y ait des doublons parmi les enregistrements. Si pour une telle table, surtout une grande, il est nécessaire de définir des contraintes pour vérifier l'intégrité, alors on supprimera les éléments suivants :

lignes en double,
situations où une ou plusieurs colonnes sont dupliquées (si ces colonnes sont censées être utilisées comme clé primaire).

Considérons un tableau avec des données client, où une ligne entière est dupliquée (la deuxième d'affilée).

La requête suivante aidera à supprimer tous les doublons :

DELETE FROM clients WHERE ctid NOT IN (SELECT max (ctid) FROM clients GROUP BY clients. *);

Le champ ctid, unique pour chaque enregistrement, est masqué par défaut, mais il est présent dans chaque table.

La dernière requête est gourmande en ressources, soyez donc prudent lorsque vous l'exécutez sur un projet de production.

Considérons maintenant le cas où les valeurs de champ sont répétées.

S'il est possible de supprimer les doublons sans enregistrer toutes les données, nous exécuterons la requête suivante :

DELETE FROM clients WHERE ctid NOT IN (SELECT max (ctid) FROM clients GROUP BY customer_id);

Si les données sont importantes, vous devez d'abord rechercher les enregistrements contenant des doublons :

SELECT * FROM clients WHERE ctid NOT IN (SELECT max (ctid) FROM clients GROUP BY customer_id);

Avant de supprimer ces enregistrements, vous pouvez passer à une table temporaire ou remplacer la valeur customer_id qu'elles contiennent par une autre.

La forme générale d'une demande de suppression des enregistrements décrits ci-dessus est la suivante :

DELETE FROM table_name WHERE ctid NOT IN (SELECT max (ctid) FROM table_name GROUP BY colonne1,);

Changer le type de champ en toute sécurité

La question peut se poser d'inclure une telle tâche dans cette liste. En effet, dans PostgreSQL il est très simple de changer le type d'un champ à l'aide de la commande ALTER. Jetons à nouveau un coup d'œil à la table des clients à titre d'exemple.

Le champ customer_id utilise le type de données de chaîne varchar. Il s'agit d'une erreur car ce champ est censé stocker les identifiants client, qui sont au format entier. L'utilisation de varchar n'est pas justifiée. Essayons de corriger ce malentendu à l'aide de la commande ALTER :

ALTER TABLE clients ALTER COLUMN customer_id TYPE entier ;

Mais à la suite de l'exécution, nous obtenons une erreur :

ERREUR : la colonne « customer_id » ne peut pas être convertie automatiquement pour saisir un entier
État SQL : 42804
Astuce : spécifiez une expression USING pour effectuer la conversion.

Cela signifie que vous ne pouvez pas simplement prendre et modifier le type d'un champ s'il y a des données dans la table. Étant donné que le type varchar a été utilisé, le SGBD ne peut pas déterminer si la valeur est un entier. Bien que les données correspondent exactement à ce type. Pour clarifier ce point, le message d'erreur suggère d'utiliser l'expression USING pour convertir correctement nos données en un entier :

ALTER TABLE clients ALTER COLUMN customer_id TYPE integer USING (customer_id :: entier);

En conséquence, tout s'est déroulé sans erreur :

Veuillez noter que lors de l'utilisation de USING, en plus d'une expression spécifique, il est possible d'utiliser des fonctions, d'autres champs et opérateurs.

Par exemple, reconvertissons le champ customer_id en varchar, mais avec la conversion du format de données :

ALTER TABLE clients ALTER COLUMN customer_id TYPE varchar USING (customer_id || "-" || first_name);

En conséquence, le tableau ressemblera à ceci :

Trouver des valeurs "perdues"

Soyez prudent lorsque vous utilisez des séquences comme clé primaire : lors de l'affectation, certains éléments de la séquence sont accidentellement ignorés et, en raison de l'utilisation de la table, certains enregistrements sont supprimés. Ces valeurs peuvent être réutilisées, mais elles sont difficiles à trouver dans les grands tableaux.

Considérons deux options de recherche.

La première façon
Exécutons la requête suivante pour trouver le début de l'intervalle avec la valeur « perdue » :

SELECT customer_id + 1 FROM clients mo WHERE NOT EXISTS (SELECT NULL FROM clients mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY customer_id;

En conséquence, nous obtenons les valeurs : 5, 9 et 11.

Si vous avez besoin de trouver non seulement la première occurrence, mais toutes les valeurs manquantes, nous utilisons la requête suivante (intensive en ressources !) :

AVEC seq_max AS (SELECT max (customer_id) FROM clients), seq_min AS (SELECT min (customer_id) FROM clients) SELECT * FROM generate_series ((SELECT min FROM seq_min), (SELECT max FROM seq_max)) SAUF SELECT customer_id FROM clients ;

En conséquence, nous voyons le résultat suivant : 5, 9 et 6.

Deuxième voie
On obtient le nom de la séquence associée au customer_id :

SELECT pg_get_serial_sequence ("clients", "id_client");

Et on retrouve tous les identifiants manquants :

AVEC sequence_info AS (SELECT start_value, last_value FROM "SchemaName". "SequenceName") SELECT generate_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info SAUF SELECT customer_id FROM clients ;

Compter le nombre de lignes d'un tableau

Le nombre de lignes est calculé par la fonction de comptage standard, mais il peut être utilisé avec des conditions supplémentaires.

Le nombre total de lignes dans le tableau :

SELECT nombre (*) FROM table ;

Nombre de lignes à condition que le champ spécifié ne contienne pas NULL :

SELECT compte (col_name) FROM table ;

Le nombre de lignes uniques pour le champ spécifié :

SELECT compte (nom_col distinct) FROM table ;

Utilisation des opérations

Une transaction combine une séquence d'actions en une seule opération. Sa particularité est que s'il y a une erreur dans l'exécution d'une transaction, aucun des résultats des actions ne sera enregistré dans la base de données.

Commençons une transaction à l'aide de la commande BEGIN.

Pour annuler toutes les opérations après BEGIN, utilisez la commande ROLLBACK.

Et pour appliquer - la commande COMMIT.

Affichage et exécution des requêtes exécutables

Afin d'obtenir des informations sur les requêtes, exécutez la commande suivante :

SELECT pid, age (query_start, clock_timestamp ()), nom d'utilisateur, requête FROM pg_stat_activity WHERE requête ! = " "ET requête PAS ILIKE"% pg_stat_activity% "ORDER BY query_start desc ;

Afin d'arrêter une requête spécifique, exécutez la commande suivante, en spécifiant l'ID de processus (pid) :

SELECT pg_cancel_backend (procpid);

Pour mettre fin à la demande, exécutez :

SELECT pg_terminate_backend (procpid);

Travailler avec la configuration

Recherche et modification de l'emplacement d'une instance de cluster

Une situation est possible lorsque plusieurs instances PostgreSQL sont configurées sur un même système d'exploitation, qui "se trouvent" sur des ports différents. Dans ce cas, trouver un chemin vers l'emplacement physique de chaque instance est une tâche plutôt éprouvante pour les nerfs. Afin d'obtenir ces informations, nous exécuterons la requête suivante pour toute base de données du cluster d'intérêt :

SHOW data_directory;

Changeons l'emplacement en quelque chose d'autre en utilisant la commande :

SET data_directory à new_directory_path ;

Mais un redémarrage est nécessaire pour que les modifications prennent effet.

Obtenir une liste des types de données disponibles

Obtenons une liste des types de données disponibles à l'aide de la commande :

SELECT nom_type, typelen de pg_type où type_type = "b" ;

nomtype est le nom du type de données.
typelen est la taille du type de données.

Modification des paramètres du SGBD sans redémarrage

Les paramètres de PostgreSQL se trouvent dans des fichiers spéciaux tels que postgresql.conf et pg_hba.conf. Après avoir modifié ces fichiers, il est nécessaire que le SGBD reçoive à nouveau les paramètres. Pour ce faire, le serveur de base de données est redémarré. Il est clair que vous devez le faire, mais sur la version de production du projet, qui est utilisée par des milliers d'utilisateurs, c'est très indésirable. Par conséquent, PostgreSQL a une fonction avec laquelle vous pouvez appliquer des modifications sans redémarrer le serveur :

SELECTIONNER pg_reload_conf ();

Mais, malheureusement, cela ne s'applique pas à tous les paramètres. Dans certains cas, un redémarrage est nécessaire pour appliquer les paramètres.

Nous avons couvert les commandes pour faciliter les choses pour les développeurs et les administrateurs de base de données utilisant PostgreSQL. Mais ce ne sont pas toutes les techniques possibles. Si vous rencontrez des problèmes intéressants, écrivez-les dans les commentaires. Partageons une expérience utile !

Dernière mise à jour : 17.03.2018

Pour créer des tables, utilisez la commande CREATE TABLE suivie du nom de la table. Il existe également un certain nombre d'opérateurs pouvant être utilisés avec cette commande pour définir les colonnes du tableau et leurs attributs. La syntaxe générale pour créer une table est la suivante :

CREATE TABLE nom_table (nom_colonne1 type de données attributs_colonne1, nom_colonne2 type de données attribut_colonne2, ...................................... ..........nom_colonneN type de données attributs_colonneN, attributs_table);

La spécification de toutes les colonnes est indiquée entre parenthèses après le nom de la table. De plus, pour chaque colonne, le nom et le type de données qu'elle représentera doivent être indiqués. Le type de données détermine quelles données (nombres, chaînes, etc.) une colonne peut contenir.

Par exemple, créons une table dans la base de données via pgAdmin. Pour ce faire, sélectionnez d'abord la base de données cible dans pgAdmin, cliquez dessus avec le bouton droit et sélectionnez l'élément Query Tool ... dans le menu contextuel :

Après cela, un champ de saisie du code SQL s'ouvrira. De plus, la table sera créée spécifiquement pour la base de données pour laquelle nous ouvrirons ce champ de saisie SQL.

CREATE TABLE clients (Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING (30), LastName CHARACTER VARYING (30), Email CHARACTER VARYING (30), Age INTEGER);

Dans ce cas, la table Customers définit cinq colonnes : Id, FirstName, LastName, Age, Email. La première colonne, Id, représente l'ID client, elle sert de clé primaire et est donc de type SERIAL. En fait, cette colonne stockera la valeur numérique 1, 2, 3, etc., qui augmentera automatiquement de un pour chaque nouvelle ligne.

Les trois colonnes suivantes représentent le prénom, le nom et l'adresse e-mail du client et sont de type CHARACTER VARYING (30), c'est-à-dire qu'elles représentent une chaîne de 30 caractères maximum.

La dernière colonne, Age, représente l'âge de l'utilisateur et est de type INTEGER, c'est-à-dire qu'elle stocke des nombres.

Et après avoir exécuté cette commande, la table clients sera ajoutée à la base de données sélectionnée.

Suppression de tables

Pour supprimer des tables, utilisez la commande DROP TABLE, qui a la syntaxe suivante :

DROP TABLE table1 [, table2, ...];

Par exemple, supprimer la table clients.

Bonjour, aujourd'hui je souhaite faire un petit mémo sur les commandes de base de PostgreSQL. Vous pouvez travailler avec PosgreSQL à la fois de manière interactive et à partir de la ligne de commande. Le programme est psql. Je suis sûr que cette liste vous sera très utile et vous fera gagner du temps en cherchant dans diverses ressources. Permettez-moi de vous rappeler qu'il s'agit d'un projet open source, basé sur le SGBD Postgres, sorti en 1986, il est développé par un groupe mondial de développeurs PGDG, en substance il s'agit de 5 à 8 personnes, mais malgré cela, il se développe de manière très intensive, introduisant toutes les nouvelles fonctions et corrigeant les anciens bogues et erreurs.

Commandes PostgreSQL de base en mode interactif :

\ connect db_name - se connecte à une base de données nommée db_name
\ du - liste des utilisateurs
\ dp (ou \ z) - liste des tables, vues, séquences, droits d'accès à celles-ci
\ di - indices
\ ds - séquences
\ dt - liste des tables
\ dt + - liste de toutes les tables avec description
\ dt * s * - liste de toutes les tables contenant s dans le nom
\ dv - vues
\ dS - tables système
\ d + - description du tableau
\ o - envoyer les résultats de la requête dans un fichier
\ l - liste des bases de données
\ i - lire les données entrantes d'un fichier
\e - ouvre le contenu courant du tampon de requête dans l'éditeur (sauf indication contraire dans l'environnement de la variable EDITOR, vi sera utilisé par défaut)
\ d "nom_table" - description de la table
\ j'exécute la commande à partir d'un fichier externe comme \ i /my/directory/my.sql
\ pset - commande pour définir les paramètres de formatage
\ echo - affiche un message
\ set - définit la valeur d'une variable d'environnement. Sans paramètres, affiche une liste des variables actuelles (\ unset - supprime).
\? - référence psql
\ help - référence SQL
\ q (ou Ctrl + D) - quitter le programme

Travailler avec PostgreSQL à partir de la ligne de commande :

-c (ou -command) - Exécute la commande SQL sans passer en mode interactif
-f file.sql - exécute les commandes du fichier file.sql
-l (ou -list) - répertorie les bases de données disponibles
-U (ou -username) - spécifiez le nom d'utilisateur (par exemple postgres)
-W (ou -password) - invite de mot de passe
-d dbname - se connecter à la base de données dbname
-h - nom d'hôte (serveur)
-s - mode pas à pas, c'est-à-dire que vous devrez confirmer toutes les commandes
–S - mode monoligne, c'est-à-dire qu'une nouvelle ligne exécutera la requête (supprimer ; à la fin de l'instruction SQL)
-V - Version PostgreSQL sans entrer en mode interactif

Exemples:

psql -U postgres -d dbname -c "CREATE TABLE my (some_id serial PRIMARY KEY, some_text text);" - exécution de la commande dans la base de données dbname.

psql -d dbname -H -c "SELECT * FROM my" -o my.html - affiche le résultat de la requête dans un fichier html.

Utilitaires PosgreSQL (programmes) :

createdb et dropdb - créent et suppriment une base de données (respectivement)
createuser et dropuser - créer et user (respectivement)
pg_ctl - un programme conçu pour résoudre les tâches de gestion générales (démarrer, arrêter, configurer les paramètres, etc.)
postmaster - Module serveur multi-utilisateurs PostgreSQL (définir les niveaux de débogage, les ports, les répertoires de données)
initdb - crée de nouveaux clusters PostgreSQL
initlocation - un programme pour créer des répertoires pour le stockage secondaire de bases de données
vacuumdb - maintenance physique et analytique de la base de données
pg_dump - sauvegarde et restauration des données
pg_dumpall - sauvegarde l'intégralité du cluster PostgreSQL
pg_restore - restauration d'une base de données à partir d'archives (.tar, .tar.gz)

Exemples de création de sauvegardes :

Création d'une sauvegarde de la base de données mydb, sous forme compressée

Pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

Création d'une sauvegarde de la base de données mydb, sous la forme d'un fichier texte brut, comprenant une commande pour créer une base de données

Pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

Création d'une sauvegarde de la base de données mydb, sous forme compressée, avec des tables contenant les paiements au nom

Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t * paiements * -f payment_tables.backup mydb

Videz les données d'une seule table spécifique. Si vous devez sauvegarder plusieurs tables, les noms de ces tables sont répertoriés à l'aide du commutateur -t pour chaque table.

Pg_dump -a -t nom_table -f nom_fichier nom_base de données

Faire une sauvegarde avec la compression gz

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c> mydb.gz

Une liste des options les plus couramment utilisées :

-h host - host, s'il n'est pas spécifié, localhost ou la valeur de la variable d'environnement PGHOST est utilisé.
-p port - port, s'il n'est pas spécifié, alors 5432 ou la valeur de la variable d'environnement PGPORT est utilisé.
-u - utilisateur, s'il n'est pas spécifié, l'utilisateur actuel est utilisé, la valeur peut également être spécifiée dans la variable d'environnement PGUSER.
-a, -data-only - ne sauvegarde que les données, les données et le schéma sont enregistrés par défaut.
-b - inclut des objets volumineux (blogs) dans le dump.
-s, -schema-only - vidage du schéma uniquement.
-C, -create - ajoute une commande pour créer une base de données.
-c - ajoute des commandes pour supprimer (supprimer) des objets (tables, vues, etc.).
-O - n'ajoute pas de commandes pour définir le propriétaire d'un objet (tables, vues, etc.).
-F, -format (c | t | p) - format de vidage de sortie, personnalisé, tar ou texte brut.
-t, -table = TABLE - spécifie une table spécifique pour le vidage.
-v, -verbose - affiche des informations détaillées.
-D, -attribute-inserts - vidage à l'aide de la commande INSERT avec la liste des noms de propriété.

Sauvegardez toutes les bases de données à l'aide de la commande pg_dumpall.

Pg_dumpall> all.sql

Restauration de tables à partir de sauvegardes (sauvegardes) :

psql - restaure les sauvegardes stockées dans un fichier texte brut ;
pg_restore - restaure les sauvegardes compressées (tar) ;

Restauration de l'intégralité de la sauvegarde, en ignorant les erreurs

Psql -h localhost -U someuser -d dbname -f mydb.sql

Restauration de l'intégralité de la sauvegarde, arrêt à la première erreur

Psql -h localhost -U someuser -set ON_ERROR_STOP = on -f mydb.sql

Pour restaurer à partir d'une archive tar, nous devons d'abord créer une base de données en utilisant CREATE DATABASE mydb ; (si l'option -C n'a pas été spécifiée lors de la création d'une sauvegarde) et restaurer

Pg_restore -dbname = mydb -jobs = 4 -verbose mydb.backup

Restauration d'une sauvegarde de base de données compressée avec gz

psql -U postgres -d madb -f madb

Je pense que la base de données postgresql sera désormais plus claire pour vous. J'espère que cette liste de commandes PostgreSQL vous a été utile.

Une alternative au travail avec la base de données via la console est un environnement plus intuitif pour tout client doté d'une interface graphique. Par exemple, pgAdmin... L'installer est très simple, nous l'exécutons avec les droits de superutilisateur :

sudo apt-get install pgadmin3

Maintenant tu peux courir pgAdmin via une interface graphique. Sélectionnez le serveur de base de données local dans la liste (il a un port par défaut de 5432) et ajoutez la base de données que nous avons déjà créée avec les paramètres que nous avons spécifiés.

pgAdmin

Dans ce serveur local en plus du nœud Bases de données vous pouvez également trouver un nœud appelé Rôles de connexion- tous les rôles disponibles.

Choisissons parmi toutes les bases de données disponibles celle avec laquelle nous avons créé. La base de données contient de nombreux types d'objets différents. Parmi les attentions particulières, il convient de prêter une attention particulière aux tableaux et aux séquences ( séquences).

Le plus souvent, une table ordinaire contient une clé primaire sous la forme d'une valeur numérique positive. Cette valeur doit être unique pour chaque ligne, donc afin de ne pas la définir directement dans la requête à chaque fois, vous pouvez définir la séquence comme valeur par défaut.

Tout d'abord, créons une séquence. Nous choisissons Séquences - Nouvelle séquence... Sur le premier onglet, entrez le nom et spécifiez-le comme Propriétaire le rôle que nous avons créé. Cette séquence ne sera pas disponible pour les autres rôles. Sur le deuxième onglet dans les champs Incrément et Démarrer nous entrons un par un (sauf indication contraire de votre part). Sur le dernier onglet de la boîte de dialogue, vous pouvez voir la requête SQL résultante qui sera exécutée sur la base de données.

Une fois la séquence créée, commençons à créer la table. Nous indiquons également son nom et son propriétaire (propriétaire). Sur le quatrième onglet Colonnes on ajoute tout d'abord la clé primaire. Bouton Ajouter, dans la fenêtre qui apparaît, indiquez le nom de la colonne, par exemple, identifiant... Nous sélectionnons comme type de données bigint... Sur le deuxième onglet Définition dans le champ Valeur par défaut nous indiquons notre séquence. Le champ doit contenir une valeur de la forme nextval ("message_id_seq" :: regclass)... C'est-à-dire qu'à chaque fois qu'une nouvelle ligne est ajoutée, la valeur suivante sera extraite de la séquence. Ajout d'autres colonnes au besoin. Enfin, dans l'onglet Contraintes ajouter une contrainte sur la clé primaire ( Clé primaire). Sur le dernier onglet, nous pouvons admirer le code SQL résultant que pgAdmin a généré. Après avoir cliqué sur OK, le tableau sera créé.

Au lieu de bigint comme clé primaire, vous pouvez le spécifier comme type de colonne grande série... Ce type est incrémenté automatiquement à chaque nouvelle ligne ajoutée, il n'est donc pas nécessaire de créer une séquence pour cela. Autrement dit, dans le cas le plus simple, si vous n'avez pas de règles spéciales pour générer l'ID d'enregistrement, vous pouvez recommander d'utiliser bigserial.

Voyons le contenu de notre table. Pour ce faire, faites un clic droit dessus et sélectionnez dans le menu contextuel Afficher les données - Afficher 100 lignes.

Dans la même fenêtre, vous pouvez modifier rapidement la valeur de n'importe quelle cellule du tableau. Si votre table contient plus de 100 enregistrements, modifiez le nombre d'enregistrements affichés dans la liste déroulante en haut de la fenêtre. Vous pouvez afficher 100, 500, 1 000 ou tous les enregistrements. Mais ce n'est rien de plus qu'un moyen de regarder rapidement le contenu du tableau et il ne faut pas s'y habituer. Si vous avez des dizaines de milliers d'enregistrements dans votre table, je ne recommanderais pas d'afficher tous les enregistrements à la fois - dans ce cas, il est préférable d'écrire une requête à l'aide de paramètres limite et décalage.