Pentru a ieși din shell, introduceți comanda \q.
Acum să încercăm să lucrăm cu baza de date creată în numele test_user:
psql -h localhost test_database test_user
Să creăm un tabel nou:
Test_database => CREATE SEQUENCE user_ids; CREATE SEQUENCE test_database => CREATE TABLE utilizatori (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("user_ids")), login CHAR(64), parola CHAR(64)); ANUNȚ: CREATE TABLE / PRIMARY KEY va CREEA implicit INDEX „users_pkey” PENTRU TABEL „users” CREATE TABLE
Ștergerea unui utilizator
Pentru a șterge un utilizator, trebuie să transferați drepturile acestuia către altul și apoi doar să ștergeți
REATRIBUIREA DEȚINĂTĂ DE doomed_role LA succesor_role; DROP OWNED BY doomed_role;- repetați comenzile anterioare pentru fiecare bază din cluster
DROP ROLE doomed_role;
Script de backup pentru tabelul Postgres.
#!/bin/bash DBNAMES="web"; USER="postgres"; DB_NAME="web"; NEW_OWNER="user_remote"; DATE_Y=`/bin/date "+%y"` DATE_M=`/bin/date "+%m"` DATE_D=`/bin/date "+%d"` SERVICE="pgdump" BACKUP_DIR="/var/ backup_db/20$(DATA_Y)/$(DATE_L)/$(DATE_D)" mkdir -p $BACKUP_DIR; pentru tbl în `psql -qAt -c "selectați tablename din pg_tables unde schemaname = "public";" $(DB_NAME)` \ `psql -qAt -c "selectați numele_secvenței din schema_informației.secvențe unde schema_secvenței = "public";" $(DB_NAME)` \ `psql -qAt -c "selectați nume_tabel din schema_informații.views unde schema_tabel = "public";" $(DB_NAME)` ; do echo „Se exportă tabelul $tbl din db $(DB_NAME) în fișierul tables3/$tbl.backup” #pg_dump --format p --verbose --table public.$tbl $(DB_NAME) > $BACKUP_DIR/$tbl pg_dump - -format p --verbose --table public.$tbl $(DB_NAME) | gzip > $BACKUP_DIR/$tbl #pg_dump -a -d -t public.$tbl $(DB_NAME) > tables3/$tbl.sql terminat ##################BACKUP FUNCȚII POSTGRES # Dumpează baza de date fără dată, pentru a le extrage din ea funcții ulterioare pg_dump -Fc -s -f $BACKUP_DIR/db_dump $(DB_NAME) /bin/sleep 4; # Creați funcția de listă pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list ##Cum se restabilesc functiile ######################## #pg_restore -h localhost -U username -d basename - Lista de funcții L db_dump ########################Script de backup pentru tabelul Postgres. scris in perl
Executați de sub user'a - postgres. Dacă în crons, atunci și de sub utilizatorul postgresql.
#!/usr/bin/env perl use strict; folosiți avertismente; my $database_name = "book_library"; interogarea mea $ =<<"EOT"; SELECT n.nspname as table_schema, c.relname as table_name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ("r","") AND n.nspname NOT IN ("pg_catalog", "information_schema") AND n.nspname NOT LIKE "^pg_%" ; EOT $query =~ s/\n\s*/ /g; my @results = `echo "$query" | psql -At $database_name`; foreach (@results) { chomp; my ($schema, $table) = split /\|/, $_; next unless ($schema && $table); my $cmd = "pg_dump -U postgres -Fp -t $schema.$table -f $schema.$table.dump $database_name"; system($cmd); } #If you wanted to restore only a single function: ##pg_restore -U $username --dbname=$dbname --function=$functionname(args) #If you wanted to restore only a single table: ##pg_restore -U $username --dbname=$dbname --table=$tablenameDicționare de căutare text integral în postgresql
Backup de bază de date de pe o mașină de la distanță
PGPASSWORD="PAROLA" pg_dump -h $HOSTNAME -U utilizator bază de date -Fc --verbose "database.itc-life.ru" | gzip > databasename.gzFaceți backup și restaurați tabele
LA PostgreSQL Sunt doua utilitati pt backup pg_dump și pg_dumpall . pg_dump este folosit pentru a face backup unei singure baze de date, pg_dumpall este folosit pentru a face backup pentru toate bazele de date și serverul în întregime (trebuie să fie rulat ca superutilizator postgresql).
Crearea unei copii de siguranță a bazei de date mydb, comprimat
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb
Crearea unei copii de siguranță a bazei de date mydb, ca fișier text simplu, inclusiv o comandă pentru a crea o bază de date
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
Crearea unei copii de siguranță a bazei de date mydb, într-o formă comprimată, cu tabele care conțin în denumire plăți
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb
Dump de date pentru un singur tabel specific. Dacă trebuie să faceți copii de rezervă pentru mai multe tabele, atunci numele acestor tabele sunt listate folosind cheia -t pentru fiecare masă.
pg_dump -a -t nume_tabel -f nume_fișier nume_bază
Creați o copie de rezervă comprimată gz
pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gzLista celor mai frecvent utilizate opțiuni:
Gazdă H - gazdă, dacă nu este specificat, atunci folosită gazdă locală PGHOST.
Port P - port, dacă nu este specificat, atunci utilizat 5432 sau valoarea din variabila de mediu PGPORT.
U - utilizator, dacă nu este specificat atunci este utilizat utilizatorul curent, valoarea poate fi specificată și în variabila de mediu PGUSER.
A, --data-only - doar datele, datele și schema sunt salvate în mod implicit.
B - includeți obiecte mari (bloguri) în gunoi.
S, --schema-only - Numai schemele de descărcare.
C, --create - adaugă o comandă pentru a crea o bază de date.
C - adaugă comenzi pentru ștergerea (eliminarea) obiectelor (tabele, vizualizări etc.).
O - nu adăugați comenzi pentru a seta proprietarul unui obiect (tabele, vizualizări etc.).
F, --format (c|t|p) — format de ieșire de descărcare, personalizat, gudron, sau text simplu.
T, --table=TABLE - specificați un tabel specific pentru dump.
V, --verbose - scoate informații detaliate.
D, --attribute-inserts - dump folosind comanda INTRODUCE cu o listă de nume de proprietate.
Faceți backup pentru toate bazele de date utilizând comanda pg_dumpall.
pg_dumpall > all.sql # verifica backup grep „^[\]connect” all.sql \connect db1 \connect db2
PostgreSQL are două utilitare pentru recuperare baze de rezervă.
- psql- restaurați copiile de siguranță care sunt stocate într-un fișier text simplu (text simplu);
- pg_restore— recuperarea backup-urilor comprimate (tar);
Recuperarea bazei de date și
#pg_restore -v -e -d dbname dbname.dump
Restaurarea întregii copii de rezervă cu ignorarea erorilor
psql -h localhost -U someuser -d dbname -f mydb.sql
Restaurarea întregii copii de rezervă, oprirea la prima eroare
psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sqlPentru a recupera de la gudron-arhiva trebuie mai întâi să creăm o bază de date folosind CREATE DATABASE mydb; (dacă opțiunea -C nu a fost specificată la crearea copiei de rezervă) și restaurați
pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup
Restaurarea unei copii de siguranță a bazei de date comprimate gz
Gunzip mydb.gz psql -U postgres -d mydb -f mydb
Începând cu versiunea 9.2, numai structura tabelelor poate fi restaurată folosind opțiunea --section
# creați o bază de date CREATE DATABASE mydb2; # restaurare pg_restore --dbname=mydb2 --section=pre-date --jobs=4 mydb.backupÎntreținerea mesei
tabel VACUUM ANALYZE; REINDEX DATABASE dbName; REINDEX TABLE tabNume;Transferarea directorului de date
Aflați calea curentă
# way 1 $ su - postgres $ psql psql > SHOW data_directory; # cale 2 $ ps ax | grep "postgres -D"Creați un director nou, atribuiți un utilizator și inițializați
mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/dataAcum trebuie să corectăm fișierul cu serviciul care pornește postgresql
# sub arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pidCurățarea mesei
Curățarea mesei tablenameși resetați contorul cu ID.
TRUNCATE TABLE tablename RESTART IDENTITY CASCADE ;CASCADĂ necesar în caz tablename legat de un alt tabel.
Eliminarea NULL dintr-un câmp
ALTER TABLE filme ALTER COLUMN an DROP NOT NULL ;Rulează pgbouncer
su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"Detașați utilizatorii din baza de date
SELECTAȚI pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = "mydb";`Salutare tuturor, astăzi vreau să vă fac un mic memento despre principalele comenzi PostgreSQL. Puteți lucra cu PostgreSQL atât interactiv, cât și din linia de comandă. Programul este psql. Sunt sigur că această listă vă va fi foarte utilă și vă va economisi timp căutând prin diverse resurse. Permiteți-mi să vă reamintesc că acesta este un proiect open-source bazat pe DBMS Postgres, lansat în 1986, este dezvoltat de grupul de dezvoltare globală PGDG, în esență este de 5-8 persoane, dar, în ciuda acestui fapt, se dezvoltă foarte mult. intens, introducând toate funcțiile noi și reparând erorile și erorile vechi.
Comenzi de bază PostgreSQL în modul interactiv:
- \connect db_name - conectați-vă la o bază de date numită db_name
- \du - lista de utilizatori
- \dp (sau \z) - listă de tabele, vizualizări, secvențe, drepturi de acces la acestea
- \di - indici
- \ds - secvențe
- \dt - lista de tabele
- \dt+ - lista tuturor tabelelor cu descriere
- \dt *s* - lista tuturor tabelelor care conțin s în nume
- \dv - vederi
- \dS - tabele de sistem
- \d+ - descrierea tabelului
- \o - trimite rezultatele interogării într-un fișier
- \l - lista bazelor de date
- \i - citește datele primite dintr-un fișier
- \e - deschide conținutul curent al buffer-ului de interogări în editor (dacă nu se specifică altfel în mediul variabil EDITOR, acesta va fi folosit implicit vi)
- \d „nume_tabel” – descrierea tabelului
- \i rulează o comandă dintr-un fișier extern, de exemplu, \i /my/directory/my.sql
- \pset - comandă pentru setarea opțiunilor de formatare
- \echo - afișează un mesaj
- \set - Setează valoarea unei variabile de mediu. Fără parametri, afișează o listă de variabile curente (\unset - șterge).
- \? - referință psql
- \help - referință SQL
- \q (sau Ctrl+D) - ieșiți din program
Lucrul cu PostgreSQL din linia de comandă:
- -c (sau --command) - rulează comanda SQL fără a intra în modul interactiv
- -f file.sql - executa comenzi din fisierul file.sql
- -l (sau --list) listează bazele de date disponibile
- -U (sau --username) - specificați numele de utilizator (de exemplu, postgres)
- -W (sau --password) - solicitarea parolei
- -d dbname - se conectează la baza de date dbname
- -h - numele gazdei (server)
- -s - modul pas cu pas, adică va trebui să confirmați toate comenzile
- -S - modul cu o singură linie, adică o nouă linie va executa interogarea (scăpa de; la sfârșitul construcției SQL)
- -V - versiunea PostgreSQL fără a intra în modul interactiv
Exemple:
psql -U postgres -d dbname -c „CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);” - executarea unei comenzi în baza de date dbname.
psql -d dbname -H -c "SELECT * FROM my" -o my.html - iese rezultatul interogării în fișierul html.
Utilitare PostgreSQL (programe):
- createb și dropdb - creați și plasați o bază de date (respectiv)
- createuser și dropuser - creați și utilizator (respectiv)
- pg_ctl este un program conceput pentru a rezolva sarcini generale de control (pornire, oprire, setare parametri etc.)
- postmaster - Modul de server multi-utilizator PostgreSQL (configurarea nivelurilor de depanare, porturi, directoare de date)
- initdb - crearea de noi clustere PostgreSQL
- initlocation - un program pentru crearea directoarelor pentru stocarea secundară a bazelor de date
- vacuumdb - întreținerea fizică și analitică a bazei de date
- pg_dump - arhivarea și restaurarea datelor
- pg_dumpall - backup unui întreg cluster PostgreSQL
- pg_restore - restaurați baza de date din arhive (.tar, .tar.gz)
Exemple de backup:
Crearea unei copii de rezervă a bazei de date mydb, într-o formă comprimată
Pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb
Crearea unei copii de rezervă a bazei de date mydb, ca fișier text simplu, inclusiv o comandă pentru a crea o bază de date
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
Crearea unei copii de siguranță a bazei de date mydb, într-o formă comprimată, cu tabele care conțin plăți în nume
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb
Dump de date pentru un singur tabel specific. Dacă trebuie făcută o copie de rezervă a mai multor tabele, atunci numele tabelelor sunt listate folosind opțiunea -t pentru fiecare tabel.
pg_dump -a -t nume_tabel -f nume_fișier nume_bază
Creați o copie de rezervă cu compresie gz
Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz
Lista celor mai frecvent utilizate opțiuni:
- -h gazdă - gazdă, dacă nu este specificată atunci este utilizată localhost sau valoarea din variabila de mediu PGHOST.
- -p port - port, dacă nu este specificat, atunci se utilizează 5432 sau valoarea din variabila de mediu PGPORT.
- -u - utilizator, dacă nu este specificat atunci este utilizat utilizatorul curent, valoarea poate fi specificată și în variabila de mediu PGUSER.
- -a, -data-only - descărcați numai datele, salvați datele și schema în mod implicit.
- -b - include obiecte mari (bloguri) în gunoi.
- -s, -schema-only - numai scheme de descărcare.
- -C, -create - adaugă o comandă pentru a crea o bază de date.
- -c - adaugă comenzi pentru ștergerea (eliminarea) obiectelor (tabele, vizualizări etc.).
- -O Nu adăugați comenzi pentru a seta proprietarul unui obiect (tabele, vizualizări etc.).
- -F, -format (c|t|p) - format de evacuare, personalizat, tar sau text simplu.
- -t, -table=TABLE - Specificați un anumit tabel de dump.
- -v, -verbose - ieșire verbosă.
- -D, -attribute-inserts Dumpează folosind comanda INSERT cu o listă de nume de proprietăți.
Faceți backup pentru toate bazele de date utilizând comanda pg_dumpall.
pg_dumpall > all.sql
Restaurarea tabelelor din copii de rezervă (backup-uri):
psql - restaurați copiile de siguranță care sunt stocate într-un fișier text simplu (text simplu);
pg_restore - restaurare copii de rezervă comprimate (tar);
Restaurarea întregii copii de rezervă cu ignorarea erorilor
psql -h localhost -U someuser -d dbname -f mydb.sql
Restaurarea întregii copii de rezervă, oprirea la prima eroare
psql -h localhost -U someuser -set ON_ERROR_STOP=on -f mydb.sql
Pentru a restaura dintr-un tarball, trebuie mai întâi să creăm o bază de date cu CREATE DATABASE mydb; (dacă opțiunea -C nu a fost specificată la crearea copiei de rezervă) și restaurați
pg_restore -dbname=mydb -jobs=4 -verbose mydb.backup
Restaurarea unei copii de siguranță a bazei de date comprimată cu gz
psql -U postgres -d mydb -f mydb
Cred că baza de date postgresql va fi acum mai ușor de înțeles pentru tine. Sper că această listă de comenzi PostgreSQL v-a fost de ajutor.
În acest articol, voi arăta 15 comenzi cele mai utile pentru administrare postgreSQL.
1. Cum se schimbă parola root în PostgreSQL?
$ /usr/local/pgsql/bin/psql postgres postgres Parolă: (parolă veche) # ALTER USER postgres WITH PASSWORD ‘tmppassword’; $ /usr/local/pgsql/bin/psql postgres postgres Parola: (tmppassword)Schimbarea parolei pentru un utilizator obișnuit funcționează în același mod. Utilizatorul root poate schimba parola pentru orice utilizator.
# ALTER USER username WITH PASSWORD ‘tmppassword’;
2. Cum să setați PostgreSQL să ruleze automat?
$ 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/postgresql3. Verificați starea serverului
$ Starea /etc/init.d/postgresql Parola: pg_ctl: serverul rulează (PID: 6171) /usr/local/pgsql/bin/postgres „-D” „/usr/local/pgsql/data” [ cometariu: Acest mesaj indică faptul că serverul funcționează corect] $ Starea /etc/init.d/postgresql Parola: pg_ctl: niciun server nu rulează [ cometariu: Acest mesaj indică faptul că serverul nu rulează]4. Cum să porniți, opriți, reporniți PostgreSQL?
# service postgresql stop Oprirea PostgreSQL: serverul sa oprit ok # pornirea serviciului postgresql Pornirea PostgreSQL: ok # repornirea serviciului postgresql Repornirea PostgreSQL: serverul sa oprit ok5. Cum să vezi ce versiune de PostgreSQL rulează?
$ /usr/local/pgsql/bin/psql test Bun venit la psql 8.3.7, terminalul interactiv PostgreSQL. Tastați: \copyright pentru termenii de distribuție \h pentru ajutor cu comenzile SQL \? pentru ajutor cu comenzile psql \g sau terminați cu punct și virgulă pentru a executa interogarea \q pentru a ieși din test=# selectversion(); versiunea ————————————————————————————————- PostgreSQL 8.3.7 pe i686-pc-linux-gnu, compilat de GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 rând) test=#5. Cum se creează un utilizator în PostgreSQL?
Există două metode pentru aceasta..
Metoda 1: Creăm un utilizator în shell-ul PSQL folosind comanda CREATE USER.
# CREAȚI UTILIZATOR ramesh CU parola 'tmppassword'; CREAȚI ROL
Metodă2: Creăm un utilizator prin comanda createuser shell.
$ /usr/local/pgsql/bin/createuser sathiya Va fi noul rol un superutilizator? (da/n) I se va permite noului rol să creeze baze de date? (da/n) I se va permite noului rol să creeze mai multe roluri noi? (da/n) n CREAȚI ROL
6. Cum se creează o bază de date în PostgreSQL?
Există 2 metode pentru aceasta.
Metodă1: Creați o bază de date folosind shell-ul PSQL folosind comanda CREATE DATABASE.
# CREAȚI BAZĂ DE DATE mydb CU OWNER ramesh; CREAȚI BAZĂ DE DATE
Metodă2: Folosim comanda createdb.
$ /usr/local/pgsql/bin/createdb mydb -O ramesh CREAȚI BAZĂ DE DATE
7. Obțineți o listă cu toate bazele de date în Postgresql?
# \l Lista bazelor de date Nume | Proprietar | Codificare ———-+———-+———- backup | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF88. Cum se șterge o bază de date în PostgreSQL?
# \l Lista bazelor de date Nume | Proprietar | Codificare ———-+———-+———- backup | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8# DROP DATABASE mydb; DROP BAZA DE DATE9. Utilizarea ajutorului încorporat pentru comenzi
Echipa \? va afișa o linie de ajutor pentru comanda psql. \h CREATE va afișa un ajutor pentru toate comenzile care încep cu CREATE.
# \? # \h CREAȚI # \h CREAȚI INDEX
10. Cum să obțineți o listă a tuturor tabelelor dintr-o bază de date în Postgresql?
# \dPentru o bază de date goală, veți primi mesajul „Nu s-au găsit relații”.
11. Cum pot afla timpul de executare a unei cereri?
# \timing - după executarea acestei comenzi, fiecare cerere ulterioară va afișa timpul de execuție.
# \sincronizare Cronometrarea este activată. # SELECT * din pg_catalog.pg_attribute ; Timp: 9.583 ms
12. Cum să faceți backup și să restaurați bazele de date și tabele în PostgreSQL?
Această întrebare este destul de mare și o voi publica mai târziu într-un articol separat.
13. Cum să enumerați funcțiile disponibile în PostgreSQL?
Pentru o listă de funcții disponibile, spuneți \df+
# \df # \df+
14. Cum se editează o interogare PostgreSQL în editor?
# \e\e va deschide un editor unde puteți edita interogarea și o puteți salva.
15. Unde pot găsi fișierul istoric postgreSQL?
Ca și fișierul ~/.bash_history, postgreSQL stochează toate comenzile sql în fișierul ~/.psql_history.
$ cat ~/.psql_history modificați utilizatorul postgres cu parola „tmppassword”; \h alterează versiunea selectată de utilizator(); creați utilizator ramesh cu parola „tmppassword”; \timing select * din pg_catalog.pg_attribute;
Ultima actualizare: 17.03.2018
Tabelele sunt create folosind comanda CREATE TABLE urmată de numele tabelului. De asemenea, puteți utiliza o serie de operatori cu această comandă care definesc coloanele de tabel și atributele acestora. Sintaxa generală pentru crearea unui tabel este următoarea:
CREATE TABLE table_name (column_name1 data_type column_attributes1, column_name2 data_type column2 atribute, ..................................... .... .......... nume_coloanăN tip_date atribute_coloanăN, atribute_tabel);
După numele tabelului, specificația pentru toate coloanele este listată în paranteze. Mai mult, pentru fiecare coloană, trebuie să specificați numele și tipul de date pe care le va reprezenta. Tipul de date determină ce date (numere, șiruri de caractere etc.) poate conține coloana.
De exemplu, să creăm un tabel în baza de date folosind pgAdmin. Pentru a face acest lucru, mai întâi selectați baza de date țintă în pgAdmin, faceți clic dreapta pe ea și selectați elementul Instrument de interogare... din meniul contextual:
După aceea, se va deschide un câmp pentru introducerea codului în SQL. Mai mult, tabelul va fi creat special pentru baza de date pentru care vom deschide acest câmp pentru introducerea SQL.
CREATE TABLE clienti (Id SERIAL PRIMARY KEY, Prenume CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER);
În acest caz, în tabelul Clienți sunt definite cinci coloane: Id, Prenume, Nume, Vârstă, Email. Prima coloană, Id, reprezintă ID-ul clientului, servește ca cheie primară și, prin urmare, este de tip SERIAL . De fapt, această coloană va stoca valoarea numerică 1, 2, 3 etc., care va crește automat cu câte unul pentru fiecare rând nou.
Următoarele trei coloane reprezintă prenumele, numele și adresa de e-mail ale clientului și sunt de tip CHARACTER VARYING(30) , ceea ce înseamnă că reprezintă un șir de maximum 30 de caractere.
Ultima coloană - Vârsta reprezintă vârsta utilizatorului și este de tip INTEGER , adică stochează numere.
Și după executarea acestei comenzi, tabelul clienți va fi adăugat la baza de date selectată.
Ștergerea tabelelor
Pentru a elimina tabele, utilizați comanda DROP TABLE, care are următoarea sintaxă:
DROP TABLE table1 [, table2, ...];
De exemplu, ștergerea tabelului clienți.
15 comenzi utile PostgreSQL
Există multe manuale PostgreSQL pe net care descriu comenzile de bază. Dar atunci când vă scufundăm mai adânc în muncă, există astfel de probleme practice care necesită comenzi avansate.
Astfel de comenzi, sau fragmente, sunt rareori documentate. Să ne uităm la câteva exemple care sunt utile atât pentru dezvoltatori, cât și pentru administratorii de baze de date.
Obținerea de informații despre baza de date
Dimensiunea bazei de date
Pentru a obține dimensiunea fizică a fișierelor (stocarea) bazei de date, utilizați următoarea interogare:
SELECT pg_database_size(current_database());
Rezultatul va fi prezentat ca un număr ca 41809016.
current_database() este o funcție care returnează numele bazei de date curente. În schimb, puteți introduce un nume în text:
SELECTează pg_database_size("my_database");
Pentru a obține informații într-o formă care poate fi citită de om, folosim funcția pg_size_pretty:
SELECT pg_size_pretty(pg_database_size(current_database()));
Ca rezultat, obținem informații precum 40 Mb .
Lista de mese
Uneori trebuie să obțineți o listă de tabele de baze de date. Pentru a face acest lucru, folosim următoarea interogare:
SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("schema_informații","pg_catalog");
information_schema - o schemă standard de bază de date care conține colecții de vizualizări (vizualizări), cum ar fi tabele, câmpuri etc. Vizualizările tabelelor conțin informații despre toate tabelele bazei de date.
Interogarea descrisă mai jos va selecta toate tabelele din schema specificată a bazei de date curente:
SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ("schema_informatii", "pg_catalog") AND table_schema IN ("public", "myschema");
În ultima clauză IN, puteți specifica numele unei anumite scheme.
Dimensiunea mesei
Prin analogie cu obținerea dimensiunii bazei de date, dimensiunea datelor din tabel poate fi calculată folosind funcția corespunzătoare:
SELECT pg_relation_size("conturi");
Funcția pg_relation_size returnează dimensiunea pe care o ocupă stratul specificat al tabelului sau indexului dat pe disc.
Numele celui mai mare tabel
Pentru a afișa o listă de tabele din baza de date curentă, sortate după dimensiunea tabelului, rulați următoarea interogare:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
Pentru a afișa informații despre cel mai mare tabel, limităm interogarea folosind LIMIT:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;
relname este numele unui tabel, index, vizualizare etc.
relpages - dimensiunea reprezentării acestui tabel pe disc în număr de pagini (în mod implicit, o pagină este de 8 KB).
pg_class este un tabel de sistem care conține informații despre relațiile dintre tabelele bazei de date.
Lista utilizatorilor conectați
Pentru a afla numele, IP-ul și portul utilizat al utilizatorilor conectați, executați următoarea interogare:
SELECT date, nume de utilizare, adresa_client, port_client FROM pg_stat_activity;
Activitatea utilizatorului
Pentru a afla activitatea de conectare a unui anumit utilizator, utilizați următoarea interogare:
SELECTAȚI nume de date FROM pg_stat_activity WHERE nume de utilizare = "devuser";
Lucrul cu câmpuri de date și tabel
Eliminarea liniilor duplicate
Dacă s-a întâmplat ca tabelul să nu aibă o cheie primară (cheie primară), atunci cu siguranță vor exista duplicate printre înregistrări. Dacă pentru un astfel de tabel, în special unul mare, este necesar să setați constrângeri (constrângeri) pentru a verifica integritatea, atunci eliminați următoarele elemente:
- linii duplicate
- situații în care una sau mai multe coloane sunt duplicate (dacă aceste coloane sunt destinate a fi utilizate ca cheie primară).
Luați în considerare un tabel cu date despre clienți, în care întregul rând este duplicat (al doilea la rând).
Următoarea interogare va ajuta la eliminarea tuturor duplicatelor:
DELETE FROM customers WHERE ctid NOT IN (SELECTARE max(ctid) FROM customers GROUP BY clienti.*);
Câmpul ctid, care este unic pentru fiecare înregistrare, este ascuns în mod implicit, dar este prezent în fiecare tabel.
Ultima solicitare necesită resurse intensive, așa că aveți grijă când o executați pe un proiect de lucru.
Acum luați în considerare cazul când valorile câmpului sunt repetate.
Dacă este acceptabil să ștergeți duplicatele fără a salva toate datele, executați următoarea interogare:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Dacă datele sunt importante, atunci trebuie mai întâi să găsiți înregistrări cu duplicate:
SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Înainte de a șterge astfel de înregistrări, le puteți muta într-un tabel temporar sau puteți înlocui valoarea customer_id din ele cu alta.
Forma generală a unei cereri de ștergere a înregistrărilor descrise mai sus este următoarea:
DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY coloana1, );
Schimbarea tipului de câmp sigur
Este posibil să existe o întrebare despre includerea unei astfel de sarcini în această listă. Într-adevăr, în PostgreSQL, schimbarea tipului unui câmp este foarte simplă folosind comanda ALTER. Să ne uităm din nou la tabelul cu clienții ca exemplu.
Câmpul customer_id folosește tipul de date șir varchar. Aceasta este o eroare deoarece acest câmp ar trebui să stocheze ID-urile clienților, care sunt în format întreg. Utilizarea varchar nu este justificată. Să încercăm să remediam această neînțelegere folosind comanda ALTER:
ALTER TABLE clienți ALTER COLUMN customer_id TYPE întreg;
Dar, ca rezultat al execuției, obținem o eroare:
EROARE: coloana „customer_id” nu poate fi turnată automat la tipul întreg
Stare SQL: 42804
Sugestie: specificați o expresie USING pentru a efectua conversia.
Aceasta înseamnă că nu puteți doar să luați și să schimbați tipul câmpului atunci când există date în tabel. Deoarece a fost folosit tipul varchar, SGBD nu poate determina dacă valoarea este un număr întreg. Deși datele corespund acestui tip. Pentru a clarifica acest punct, mesajul de eroare sugerează utilizarea clauzei USING pentru a converti corect datele noastre în număr întreg:
ALTER TABLE clienți ALTER COLUMN customer_id TYPE întreg USING (customer_id::integer);
Drept urmare, totul a mers fără erori:
Vă rugăm să rețineți că atunci când utilizați UTILIZARE, pe lângă o expresie specifică, este posibil să utilizați funcții, alte câmpuri și operatori.
De exemplu, să convertim câmpul customer_id înapoi în varchar , dar cu conversia formatului de date:
ALTER TABLE clienți ALTER COLUMN customer_id TYPE varchar USING (customer_id || "-" || prenume);
Ca rezultat, tabelul va arăta astfel:
Căutați valori „pierdute”.
Aveți grijă când utilizați secvențe ca cheie primară: la atribuire, unele elemente ale secvenței sunt omise accidental, ca urmare a lucrului cu tabelul, unele înregistrări sunt șterse. Astfel de valori pot fi folosite din nou, dar sunt greu de găsit în tabelele mari.
Luați în considerare două opțiuni de căutare.
Prima cale
Să executăm următoarea interogare pentru a găsi începutul intervalului cu valoarea „pierdută”:
SELECT customer_id + 1 FROM customers mo UNDE NU EXISTĂ (SELECT NULL FROM customers mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY customer_id;
Ca rezultat, obținem valorile: 5 , 9 și 11 .
Dacă trebuie să găsiți nu numai prima apariție, ci și toate valorile lipsă, utilizați următoarea interogare (care necesită mult resurse!):
WITH seq_max AS (SELECT max(customer_id) FROM customers), seq_min AS (SELECT min(customer_id) FROM customers) SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) EXCEPT SELECT customer_id FROM clienți;
Ca rezultat, vedem următorul rezultat: 5 , 9 și 6 .
A doua cale
Obțineți numele secvenței asociate cu customer_id:
SELECT pg_get_serial_sequence("clienți", "codul_client");
Și găsiți toți identificatorii lipsă:
WITH 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 EXCEPT SELECT customer_id FROM clienți;
Numărarea numărului de rânduri dintr-un tabel
Numărul de rânduri este calculat de funcția de numărare standard, dar poate fi utilizat cu condiții suplimentare.
Numărul total de rânduri din tabel:
SELECT count(*) FROM tabel;
Numărul de rânduri cu condiția ca câmpul specificat să nu conțină NULL:
SELECT count(col_name) FROM tabel;
Numărul de rânduri unice pentru câmpul specificat:
SELECT count(nume_col distinct) FROM tabel;
Utilizarea tranzacțiilor
O tranzacție combină o secvență de acțiuni într-o singură operațiune. Particularitatea sa este că, în cazul unei erori în executarea tranzacției, niciunul dintre rezultatele acțiunilor nu va fi salvat în baza de date.
Să începem o tranzacție cu comanda BEGIN.
Pentru a derula înapoi toate operațiunile după BEGIN , utilizați comanda ROLLBACK.
Și pentru a aplica - comanda COMMIT.
Vizualizarea și terminarea interogărilor executabile
Pentru a obține informații despre solicitări, rulați următoarea comandă:
SELECTează pid, age(query_start, clock_timestamp()), nume de utilizare, interogare FROM pg_stat_activity WHERE interogare != "
Pentru a opri o anumită cerere, rulați următoarea comandă, specificând ID-ul procesului (pid):
SELECT pg_cancel_backend(procpid);
Pentru a termina cererea, rulați:
SELECTează pg_terminate_backend(procpid);
Lucrul cu configurația
Găsirea și modificarea locației unei instanțe de cluster
Este posibilă o situație atunci când mai multe instanțe PostgreSQL sunt configurate pe același sistem de operare, care „stau” pe porturi diferite. În acest caz, găsirea unei căi către locația fizică a fiecărei instanțe este o sarcină destul de stresantă. Pentru a obține aceste informații, executați următoarea interogare împotriva oricărei baze de date a clusterului de interes:
SHOW data_directory;
Schimbați locația în alta folosind comanda:
SETează directorul_date la calea_director_nou;
Dar este necesară o repornire pentru ca modificările să intre în vigoare.
Obținerea unei liste de tipuri de date disponibile
Obțineți o listă de tipuri de date disponibile folosind comanda:
SELECT TYpname, typlen din pg_type unde typtype="b";
typname este numele tipului de date.
typlen - dimensiunea tipului de date.
Modificarea setărilor DBMS fără a reporni
Setările PostgreSQL sunt localizate în fișiere speciale precum postgresql.conf și pg_hba.conf. După modificarea acestor fișiere, aveți nevoie de SGBD pentru a obține din nou setările. Pentru a face acest lucru, serverul de baze de date este repornit. Este clar că trebuie să faci asta, dar în versiunea de producție a proiectului, care este folosită de mii de utilizatori, acest lucru este foarte nedorit. Prin urmare, PostgreSQL are o funcție cu care puteți aplica modificări fără a reporni serverul:
SELECTează pg_reload_conf();
Dar, din păcate, nu se aplică tuturor parametrilor. În unele cazuri, este necesară o repornire pentru a aplica setările.
Am analizat comenzile care vor ușura lucrurile pentru dezvoltatori și DBA care folosesc PostgreSQL. Dar acestea nu sunt toate metodele posibile. Dacă întâlniți sarcini interesante, scrieți despre ele în comentarii. Să împărtășim experiența noastră utilă!