Shellist väljumiseks sisestage käsk \q.
Proovime nüüd loodud andmebaasiga testi_kasutaja nimel töötada:
psql -h localhost test_database test_user
Loome uue tabeli:
Test_andmebaas=> CREATE SEQUENCE user_ids; CREATE SEQUENCE test_database=> CREATE TABLE kasutajad (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("kasutaja_id"), login CHAR(64), parool CHAR(64)); MÄRKUS: TABELI LOOMI / ESMANE VÕTME LOOMIB kaudse INDEKSI "users_pkey" TABLE "kasutajad" jaoks CREATE TABLE
Kasutaja kustutamine
Kasutaja kustutamiseks peate tema õigused teisele üle andma ja alles seejärel kustutama
MÄÄRAMINE ÜMBER hukule määratud_rolli OMANUKS järglase_rolliks; Drop OMAND BY doomed_roll;- korrake eelmisi käske iga klastri baasi jaoks
DROP ROLE hukule määratud_roll;
Postgresi tabeli varukoopia skript.
#!/bin/bash DBNAMES="veeb"; USER="postgres"; DB_NAME="veeb"; NEW_OWNER="kasutaja_kaugjuhtimine"; DATE_Y=`/bin/date "+%y"` DATE_M=`/bin/date "+%m"` DATE_D=`/bin/date "+%d"` SERVICE="pgdump" BACKUP_DIR="/var/ backup_db/20$(DATE_Y)/$(DATE_M)/$(DATE_D)" mkdir -p $ BACKUP_DIR; tbl jaoks `psql -qAt -c "vali tabelinimi tabelitest pg_tables, kus skeeminimi = "avalik";" $(DB_NAME)` \ `psql -qAt -c "vali jada_nimi loendist information_schema.sequences, kus järjestuse_skeem = "avalik";" $(DB_NAME)` \ `psql -qAt -c "vali tabeli_nimi saidist information_schema.views kus tabeli_skeem = "avalik";" $(DB_NAME)` ; tee echo "Tabeli $tbl eksportimine andmebaasist $(DB_NAME) failitabelitesse3/$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 tehtud ##################VARUKUPPA POSTGRESi FUNKTSIOONID # Tühistab andmebaas ilma kuupäevata, et neid sealt välja võtta täiendavad funktsioonid pg_dump -Fc -s -f $BACKUP_DIR/db_dump $(DB_NAME) /bin/sleep 4; # Loo loendi funktsioon pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list ##Kuidas funktsioone taastada ########################## #pg_restore -h localhost -U kasutajanimi -d baasnimi - L function_list db_dump ########################Postgresi tabeli varukoopia skript. kirjutatud perlis
Käivita kasutaja'a alt - postgres. Kui cronsis, siis ka postgresql kasutaja alt.
#!/usr/bin/env perl use range; kasutada hoiatusi; minu $andmebaasi_nimi = "raamatuteek"; minu $päring =<<"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=$tablenameTäistekstiotsingu sõnastikud postgresql-is
Andmebaasi varukoopia kaugmasinast
PGPASSWORD="PAROOL" pg_dump -h $HOSTNAME -U andmebaasi kasutaja -Fc --verbose "database.itc-life.ru" | gzip > andmebaasinimi.gzTabelite varundamine ja taastamine
AT PostgreSQL Selle jaoks on kaks kommunaalteenust varukoopia pg_dump ja pg_dumpall . pg_dump kasutatakse ühe andmebaasi varundamiseks, pg_dumpall kasutatakse kõigi andmebaaside ja serveri kui terviku varundamiseks (tuleb käivitada postgresql superkasutaja all).
Andmebaasi varukoopia loomine mydb, kokkusurutud
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb
Andmebaasi varukoopia loomine mydb, lihttekstifailina, mis sisaldab käsku andmebaasi loomiseks
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
Andmebaasi varukoopia loomine mydb, tihendatud kujul, tabelitega, mis sisaldavad nime maksed
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *maksed* -f payment_tables.backup mydb
Ainult ühe konkreetse tabeli andmetõmmis. Kui teil on vaja varundada mitu tabelit, loetletakse nende tabelite nimed klahvi abil -t iga laua jaoks.
pg_dump -a -t tabeli_nimi -f faili_nimi andmebaasi_nimi
Looge tihendatud varukoopia gz
pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gzKõige sagedamini kasutatavate valikute loend:
H host – host, kui pole määratud, siis kasutatakse kohalik host PGHOST.
P port - port, kui pole määratud, siis kasutatakse 5432 või väärtus keskkonnamuutujast PGPORT.
U - kasutaja, kui pole määratud, siis kasutatakse praegust kasutajat, väärtuse saab määrata ka keskkonnamuutujas PGUSER.
A, --data-only – vaikimisi salvestatakse ainult andmed, andmed ja skeem.
B - lisage prügimäele suured objektid (blogid).
S, --schema-only – tühjendage ainult skeemid.
C, --create – lisab käsu andmebaasi loomiseks.
C - lisab käsud objektide (tabelid, vaated jne) kustutamiseks (kukkumiseks).
O – ära lisa käske objekti omaniku määramiseks (tabelid, vaated jne).
F, --vorming (c|t|p) - tühjendusväljundvorming, kohandatud, tõrv, või lihttekst.
T, --table=TABLE – määrake tõmmise jaoks konkreetne tabel.
V, --verbose - väljastab üksikasjalikku teavet.
D, --attribute-inserts - dump käsuga LISA kinnisvaranimede loeteluga.
Varundage kõik andmebaasid, kasutades käsku pg_dumpall.
pg_dumpall > all.sql # kontrollige varukoopiat grep "^[\]connect" all.sql \connect db1 \connect db2
PostgreSQL-il on kaks utiliiti taastumine varubaasid.
- psql- taastada varukoopiad, mis on salvestatud lihtteksti faili (lihttekst);
- pg_restore— tihendatud varukoopiate (tar) taastamine;
Andmebaasi taastamine ja
#pg_restore -v -e -d dbname dbname.dump
Kogu varukoopia taastamine vigade ignoreerimisega
psql -h localhost -U someuser -d dbname -f mydb.sql
Kogu varukoopia taastamine, peatudes esimese vea korral
psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sqlEt taastuda tõrva-arhiiv peame esmalt looma andmebaasi kasutades CREATE DATABASE mydb; (kui varukoopia loomisel ei määratud suvandit -C) ja taasta
pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup
Tihendatud andmebaasi varukoopia taastamine gz
Gunzip mydb.gz psql -U postgres -d mydb -f mydb
Alates versioonist 9.2 saab suvandi --section abil taastada ainult tabelite struktuuri
# loo andmebaas CREATE DATABASE mydb2; # taastama pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backupLaua hooldus
VAKUUMANALÜÜSI tabel; REINDEX DATABASE dbName; REINDEX TABLE vahekaardiNimi;Andmekataloogi ülekandmine
Uurige praegust teed
# viis 1 $ su - postgres $ psql psql > NÄITA andmekataloogi; # viis 2 $ ps kirves | grep "postgres -D"Looge uus kataloog, määrake kasutaja ja lähtestage
mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/dataNüüd peame käivitatava teenusega faili parandama postgresql
# under arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pidLaua puhastamine
Laua puhastamine tabelinimi ja lähtesta loendur ID-ga.
TRUNCATE TABLE tabelinimi RESTART IDENTITY CASCADE ;KASKAAD juhuks vaja tabelinimi lingitud teise tabeliga.
NULL-i eemaldamine väljalt
ALTER TABLE filmid ALTER COLUMN aasta DROP NOT NULL ;Töötab pgbouncer
su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"Eraldage kasutajad andmebaasist
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = "mydb";`Tere kõigile, täna tahan teha väikese meeldetuletuse peamiste PostgreSQL-i käskude kohta. PostgreSQL-iga saab töötada nii interaktiivselt kui ka käsurealt. Programm on psql. Olen kindel, et see loend on teile väga kasulik ja säästab teie aega erinevate ressursside otsimisel. Tuletan meelde, et tegemist on 1986. aastal välja antud Postgres DBMS-il põhineva avatud lähtekoodiga projektiga, mida arendab PGDG globaalne arendusgrupp, sisuliselt on see 5-8 inimest, kuid sellest hoolimata areneb see väga intensiivselt, juurutades kõiki uusi funktsioone ning parandades vanu vigu ja vigu.
PostgreSQL-i põhikäsud interaktiivses režiimis:
- \connect db_name – ühendumine andmebaasiga nimega db_name
- \du – kasutajate loend
- \dp (või \z) - tabelite, vaadete, järjestuste loend, juurdepääsuõigused neile
- \di - indeksid
- \ds - jadad
- \dt - tabelite loend
- \dt+ - kõigi tabelite loend koos kirjeldusega
- \dt *s* - kõigi tabelite loend, mille nimes on s
- \dv – vaated
- \dS - süsteemitabelid
- \d+ - tabeli kirjeldus
- \o - päringu tulemuste saatmine faili
- \l - andmebaaside loend
- \i - loeb failist sissetulevaid andmeid
- \e - avab redaktoris päringupuhvri praeguse sisu (kui muutujakeskkonnas EDITOR pole teisiti määratud, kasutab seda vaikimisi vi)
- \d "tabeli_nimi" – tabeli kirjeldus
- \i käivitan käsu välisest failist, nt \i /minu/kataloog/minu.sql
- \pset - käsk vormindamisvalikute määramiseks
- \echo – kuvab teate
- \set – määrab keskkonnamuutuja väärtuse. Ilma parameetriteta kuvab praeguste muutujate loendi (\unset – kustutab).
- \? - psql viide
- \help – SQL-i viide
- \q (või Ctrl+D) – programmist väljumine
PostgreSQL-iga töötamine käsurealt:
- -c (või --command) - käivitage SQL-käsk interaktiivsesse režiimi minemata
- -f fail.sql – failist fail.sql käskude täitmine
- -l (või --list) loetleb saadaolevad andmebaasid
- -U (või --username) – määrake kasutajanimi (näiteks postgres)
- -W (või --parool) - parooli küsimine
- -d dbname – ühendub andmebaasiga dbname
- -h - hostinimi (server)
- -s - samm-sammult režiim, see tähendab, et peate kõik käsud kinnitama
- -S - üherealine režiim, see tähendab, et reavahetus käivitab päringu (eraldub; SQL-i konstruktsiooni lõpus)
- -V – PostgreSQL-i versioon interaktiivsesse režiimi sisenemata
Näited:
psql -U postgres -d dbname -c "LOO TABEL minu(some_id serial PRIMARY KEY, some_text text);" - käsu täitmine andmebaasis dbname.
psql -d dbname -H -c "SELECT * FROM my" -o my.html - päringutulemuse väljund html-faili.
PostgreSQL-i utiliidid (programmid):
- Createdb ja dropdb - looge ja kukutage andmebaas (vastavalt)
- createuser ja dropuser – loo ja kasutaja (vastavalt)
- pg_ctl on programm, mis on loodud üldiste juhtimisülesannete lahendamiseks (käivitamine, seiskamine, parameetrite määramine jne)
- postmaster – PostgreSQL-i mitme kasutaja serveri moodul (silumistasemete, portide, andmekataloogide konfigureerimine)
- initdb – uute PostgreSQL-klastrite loomine
- initlocation - programm andmebaaside teisese salvestamise kataloogide loomiseks
- vacuumdb - andmebaasi füüsiline ja analüütiline hooldus
- pg_dump - andmete arhiveerimine ja taastamine
- pg_dumpall – kogu PostgreSQL-klastri varundamine
- pg_restore – andmebaasi taastamine arhiividest (.tar, .tar.gz)
Varundamise näited:
Mydb andmebaasi varukoopia tegemine tihendatud kujul
Pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb
Mydb andmebaasi varukoopia loomine lihttekstifailina, sealhulgas käsk andmebaasi loomiseks
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
Mydb andmebaasi varukoopia loomine tihendatud kujul tabelitega, mis sisaldavad nimes makseid
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *maksed* -f payment_tables.backup mydb
Ainult ühe konkreetse tabeli andmetõmmis. Kui varundada on vaja rohkem kui üks tabel, loetletakse tabelinimed, kasutades iga tabeli puhul suvandit -t.
pg_dump -a -t tabeli_nimi -f faili_nimi andmebaasi_nimi
Looge varukoopia gz tihendamisega
Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz
Kõige sagedamini kasutatavate valikute loend:
- -h host - host, kui pole määratud, siis kasutatakse localhosti või PGHOST keskkonnamuutuja väärtust.
- -p port - port, kui pole määratud, siis kasutatakse 5432 või PGPORT keskkonnamuutuja väärtust.
- -u - kasutaja, kui pole määratud, siis kasutatakse praegust kasutajat, väärtuse saab määrata ka keskkonnamuutujas PGUSER.
- -a, -data-only - tühjendage ainult andmed, salvestage andmed ja skeem vaikimisi.
- -b - lisab prügimäele suured objektid (blogid).
- -s, -schema-only - tühjendage ainult skeemid.
- -C, -create – lisab käsu andmebaasi loomiseks.
- -c - lisab käsud objektide (tabelid, vaated jne) kustutamiseks (langetamiseks).
- -O Ärge lisage käske objektide omaniku määramiseks (tabelid, vaated jne).
- -F, -vorming (c|t|p) - tühjendage väljundvorming, kohandatud, tar või lihttekst.
- -t, -table=TABEL – määrake konkreetne tabel, mida kustutada.
- -v, -verbose - paljusõnaline väljund.
- -D, -attribute-inserts Dump kasutades INSERT käsku koos atribuutide nimede loendiga.
Varundage kõik andmebaasid, kasutades käsku pg_dumpall.
pg_dumpall > all.sql
Tabelite taastamine varukoopiatest (varukoopiad):
psql - taastage varukoopiad, mis on salvestatud lihtteksti faili (lihttekst);
pg_restore - taastab tihendatud varukoopiad (tar);
Kogu varukoopia taastamine vigade ignoreerimisega
psql -h localhost -U someuser -d dbname -f mydb.sql
Kogu varukoopia taastamine, peatudes esimese vea korral
psql -h localhost -U someuser -set ON_ERROR_STOP=on -f mydb.sql
Tarballist taastamiseks peame esmalt looma andmebaasi käsuga CREATE DATABASE mydb; (kui varukoopia loomisel ei määratud suvandit -C) ja taasta
pg_restore -dbname=mydb -jobs=4 -verbose mydb.backup
Gz-ga tihendatud andmebaasi varukoopia taastamine
psql -U postgres -d mydb -f mydb
Arvan, et postgresql-i andmebaas on nüüd teile arusaadavam. Loodan, et see PostgreSQL-i käskude loend on teile abiks olnud.
Selles artiklis näitan haldamiseks 15 kõige kasulikumat käsku postgreSQL.
1. Kuidas muuta PostgreSQL-is juurparooli?
$ /usr/local/pgsql/bin/psql postgres postgres Parool: (vana parool) # ALTER USER postgres WITH PASSWORD ‘tmpparool’; $ /usr/local/pgsql/bin/psql postgres postgres Parool: (tmppassword)Tavakasutaja parooli muutmine toimib samamoodi. Juurkasutaja saab muuta iga kasutaja parooli.
# MUUDA KASUTAJA kasutajanimi PAROOLIGA ‘tmpparool’;
2. Kuidas seada PostgreSQL automaatkäivitusrežiimile?
$ su - juur # 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. Kontrollige serveri olekut
$ /etc/init.d/postgresql olek Parool: pg_ctl: server töötab (PID: 6171) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" [ kommenteerida: See teade näitab, et server töötab korralikult] $ /etc/init.d/postgresql olek Parool: pg_ctl: ükski server ei tööta [ kommenteerida: see teade näitab, et server ei tööta]4. Kuidas PostgreSQL-i käivitada, peatada, taaskäivitada?
# teenuse postgresql peatus PostgreSQL-i peatamine: server peatus ok # teenuse postgresql käivitamine PostgreSQL-i käivitamine: ok # teenuse postgresql taaskäivitamine PostgreSQL-i taaskäivitamine: server peatus ok5. Kuidas näha, milline PostgreSQL-i versioon töötab?
$ /usr/local/pgsql/bin/psql test Tere tulemast PostgreSQL interaktiivsesse terminali psql 8.3.7. Tüüp: \copyright levitamistingimuste jaoks \h SQL-käskude abi saamiseks \? abi saamiseks psql käskudega \g või lõpetage päringu käivitamiseks semikooloniga \q väljumiseks test=# selectversion(); versioon ————————————————————————————————— PostgreSQL 8.3.7 i686-pc-linux-gnu, kompileeris GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 rida) test=#5. Kuidas luua kasutajat PostgreSQL-is?
Selleks on kaks meetodit..
1. meetod: Loome kasutaja PSQL-i kestas, kasutades käsku CREATE USER.
# LOO KASUTAJA ramesh parooliga 'tmppassword'; LOO ROLL
meetod2: Loome kasutaja shell-käsu Createuser kaudu.
$ /usr/local/pgsql/bin/createuser sathiya Kas uueks rolliks saab superkasutaja? (ja/ei) Kas uuel rollil on lubatud andmebaase luua? (ja/n) Kas uuel rollil on lubatud luua rohkem uusi rolle? (ja/n) n LOO ROLL
6. Kuidas luua andmebaasi PostgreSQL-is?
Selleks on 2 meetodit.
meetod1: Looge andmebaas PSQL-kesta abil, kasutades käsku CREATE DATABASE.
# LOO ANDMEBAAS mydb KOOS OMANIKUGA ramesh; LOO ANDMEBAAS
meetod2: Kasutame käsku Createb.
$ /usr/local/pgsql/bin/createdb mydb -O ramesh LOO ANDMEBAAS
7. Kas hankite loendi kõigist Postgresqli andmebaasidest?
# \l Andmebaaside loend Nimi | Omanik | Kodeering ———-+———-+———- varukoopia | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 mall0 | postgres | UTF8 mall1 | postgres | UTF88. Kuidas kustutada andmebaasi PostgreSQL-is?
# \l Andmebaaside loend Nimi | Omanik | Kodeering ———-+———-+———- varukoopia | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 mall0 | postgres | UTF8 mall1 | postgres | UTF8# DROPP ANDMEBAAS mydb; ANDMEBAAS DROPIDA9. Käskude sisseehitatud abi kasutamine
Meeskond \? kuvab psql käsu abirea. \h CREATE näitab abi kõigi käskude jaoks, mis algavad sõnaga CREATE.
# \? # \h LOO # \h LOO INDEX
10. Kuidas saada Postgresqli andmebaasis kõigi tabelite loend?
# \dTühja andmebaasi puhul kuvatakse teade "Seoseid ei leitud".
11. Kuidas ma saan teada päringu täitmise aja?
# \timing – pärast selle käsu täitmist näitab iga järgmine päring täitmise aega.
# \ajastus Ajastus on sisse lülitatud. # SELECT * alates pg_catalog.pg_attribute ; Aeg: 9,583 ms
12. Kuidas varundada ja taastada andmebaase ja tabeleid PostgreSQL-is?
See küsimus on üsna mahukas ja avaldan selle hiljem eraldi artiklis.
13. Kuidas PostgreSQL-is saadaolevaid funktsioone loetleda?
Saadaolevate funktsioonide loendi vaatamiseks öelge \df+
# \df # \df+
14. Kuidas redigeerida redaktoris PostgreSQL-i päringut?
# \e\e avab redaktori, kus saate päringut redigeerida ja salvestada.
15. Kust ma leian postgreSQL-i ajaloofaili?
Nagu fail ~/.bash_history, salvestab postgreSQL kõik sql-käsud faili ~/.psql_history.
$ kass ~/.psql_history muuda kasutaja postgres parooliga 'tmppassword'; \h muuda kasutajat vali versioon(); loo kasutaja ramesh parooliga 'tmppassword'; \timing vali * kataloogist pg_catalog.pg_attribute;
Viimane uuendus: 17.03.2018
Tabelid luuakse käsu CREATE TABLE abil, millele järgneb tabeli nimi. Selle käsuga saate kasutada ka mitmeid operaatoreid, mis määravad tabeli veerud ja nende atribuudid. Tabeli loomise üldine süntaks on järgmine:
CREATE TABLE tabeli_nimi (veeru_nimi1 andmetüüp veeru_atribuudid1, veeru_nimi2 andmetüüp veeru2 atribuudid, ..................................... .... ........ veeru_nimiN andmetüüp veeru_atribuudidN, tabeli_atribuudid);
Tabeli nime järel on sulgudes loetletud kõigi veergude spetsifikatsioonid. Lisaks peate iga veeru jaoks määrama nime ja andmetüübi, mida see esindab. Andmetüüp määrab, milliseid andmeid (numbreid, stringe jne) veerg võib sisaldada.
Näiteks loome andmebaasis tabeli pgAdmini abil. Selleks valige pgAdminis esmalt sihtandmebaas, paremklõpsake sellel ja valige kontekstimenüüst üksus Päringutööriist...:
Pärast seda avaneb SQL-i koodi sisestamise väli. Veelgi enam, tabel luuakse spetsiaalselt selle andmebaasi jaoks, mille jaoks SQL-i sisestamiseks selle välja avame.
CREATE TABLE kliendid (ID SERIAL PRIMARY KEY, Eesnimi MÄRK VARYING(30), Perekonnanimi MÄRK VARYING(30), E-post MÄRK VARYING(30), Vanus TÄISARV);
Sel juhul on tabelis Kliendid määratletud viis veergu: Id, Eesnimi, Perekonnanimi, Vanus, E-post. Esimene veerg Id tähistab kliendi ID-d, see toimib esmase võtmena ja on seetõttu SERIAL tüüpi. Tegelikult salvestab see veerg arvväärtused 1, 2, 3 jne, mis suureneb iga uue rea puhul automaatselt ühe võrra.
Järgmised kolm veergu tähistavad kliendi eesnime, perekonnanime ja e-posti aadressi ning on tüüpi CHARACTER VARYING(30) , mis tähendab, et need tähistavad stringi, mis ei ole pikem kui 30 tähemärki.
Viimane veerg – Vanus tähistab kasutaja vanust ja on tüüpi INTEGER , see tähendab, et see salvestab numbreid.
Ja pärast selle käsu täitmist lisatakse klientide tabel valitud andmebaasi.
Tabelite kustutamine
Tabelite eemaldamiseks kasutage käsku DROP TABLE, millel on järgmine süntaks:
DROPP TABLE tabel1 [, tabel2, ...];
Näiteks klientide tabeli kustutamine.
15 kasulikku PostgreSQL-i käsku
Netis on palju PostgreSQL-i käsiraamatuid, mis kirjeldavad põhikäske. Kuid töösse süvenedes tekib selliseid praktilisi küsimusi, mis nõuavad edasijõudnud käsklusi.
Selliseid käske või juppe dokumenteeritakse harva. Vaatame mõnda näidet, mis on kasulikud nii arendajatele kui ka andmebaasiadministraatoritele.
Andmebaasi kohta teabe hankimine
Andmebaasi suurus
Andmebaasi failide (salvestusruumi) füüsilise suuruse saamiseks kasutage järgmist päringut:
SELECT pg_andmebaasi_suurus(praegune_andmebaas());
Tulemus kuvatakse numbrina, näiteks 41809016.
current_database() on funktsioon, mis tagastab praeguse andmebaasi nime. Selle asemel saate nime teksti sisestada:
SELECT pg_database_size("minu_andmebaas");
Selleks, et saada teavet inimesele loetaval kujul, kasutame funktsiooni pg_size_pretty:
SELECT pg_size_pretty(pg_andmebaas_size(praegune_andmebaas()));
Selle tulemusena saame teavet nagu 40 Mb .
Tabelite loend
Mõnikord peate hankima andmebaasi tabelite loendi. Selleks kasutame järgmist päringut:
SELECT tabeli_nimi FROM info_skeem.tabelid WHERE tabeli_skeem EI OLE ("teabe_skeem","lk_kataloog");
information_schema – standardne andmebaasiskeem, mis sisaldab vaadete (vaadete) kogusid, nagu tabelid, väljad jne. Tabelivaated sisaldavad teavet kõigi andmebaasi tabelite kohta.
Allpool kirjeldatud päring valib kõik tabelid praeguse andmebaasi määratud skeemist:
SELECT tabeli_nimi FROM info_skeem.tabelid WHERE tabeli_skeem EI OLE ("teabe_skeem", "pg_catalog") AND tabeli_skeem IN("avalik", "müskeem");
Viimases IN-klauslis saate määrata konkreetse skeemi nime.
Tabeli suurus
Analoogiliselt andmebaasi suuruse saamisega saab tabeli andmete suuruse arvutada vastava funktsiooni abil:
SELECT pg_relation_size("kontod");
Funktsioon pg_relation_size tagastab suuruse, mille antud tabeli või indeksi määratud kiht kettal hõivab.
Suurima tabeli nimi
Praeguses andmebaasis olevate tabelite loendi kuvamiseks tabeli suuruse järgi sorteerituna käivitage järgmine päring:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
Suurima tabeli teabe kuvamiseks piirame päringut, kasutades LIMIT:
SELECT relname, repages FROM pg_class ORDER BY repages DESC LIMIT 1;
relname on tabeli, indeksi, vaate jne nimi.
relpages – selle tabeli esituse suurus kettal lehtede arvus (vaikimisi on üks leht 8 KB).
pg_class on süsteemitabel, mis sisaldab teavet andmebaasi tabelisuhete kohta.
Ühendatud kasutajate loend
Ühendatud kasutajate nime, IP-aadressi ja kasutatud pordi väljaselgitamiseks käivitage järgmine päring:
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;
Kasutaja tegevus
Konkreetse kasutaja ühendustegevuse väljaselgitamiseks kasutage järgmist päringut:
SELECT datname FROM pg_stat_activity WHERE kasutajanimi = "devuser";
Andmete ja tabeliväljadega töötamine
Duplikaatridade eemaldamine
Kui juhtus nii, et tabelis pole primaarvõtit (primary key), siis on kirjete hulgas kindlasti duplikaate. Kui sellise tabeli, eriti suure tabeli jaoks on vaja terviklikkuse kontrollimiseks seada piirangud (piirangud), eemaldage järgmised elemendid:
- dubleerivad read
- olukorrad, kus üks või mitu veergu on dubleeritud (kui need veerud on mõeldud kasutamiseks primaarvõtmena).
Mõelge kliendiandmetega tabelile, kus kogu rida on dubleeritud (teine rida).
Järgmine päring aitab eemaldada kõik duplikaadid:
DELETE FROM klientidest WHERE ctid NOT IN (SELECT max(ctid) FROM klientidest GROUP BY kliendid.*);
Iga kirje jaoks unikaalne ctid väli on vaikimisi peidetud, kuid see on olemas igas tabelis.
Viimane päring on ressursimahukas, seega olge selle tööprojekti täitmisel ettevaatlik.
Mõelge nüüd juhtumile, kui välja väärtusi korratakse.
Kui duplikaatide kustutamine ilma kõiki andmeid salvestamata on vastuvõetav, täitke järgmine päring:
DELETE FROM klientidest WHERE ctid NOT IN (SELECT max(ctid) FROM klientidest GROUP BY kliendi_id);
Kui andmed on olulised, peate esmalt leidma duplikaatidega kirjed:
SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Enne selliste kirjete kustutamist saate need teisaldada ajutisse tabelisse või asendada neis oleva kliendi_id väärtuse teisega.
Ülalkirjeldatud kirjete kustutamise taotluse üldine vorm on järgmine:
DELETE FROM tabeli_nimi WHERE ctid NOT IN (SELECT max(ctid) FROM tabeli_nimi GROUP BY veerg1, );
Ohutu väljatüübi muutmine
Sellise ülesande sellesse loendisse lisamise kohta võib tekkida küsimus. Tõepoolest, PostgreSQL-is on välja tüübi muutmine käsu ALTER abil väga lihtne. Vaatame näitena uuesti klientide tabelit.
Väli customer_id kasutab stringi andmetüüpi varchar. See on viga, kuna see väli peaks salvestama kliendi ID-d, mis on täisarvu vormingus. Varchari kasutamine ei ole õigustatud. Proovime seda arusaamatust parandada käsu ALTER abil:
ALTER TABLE kliendid ALTER COLUMN kliendi_id TYPE täisarv;
Kuid täitmise tulemusena saame vea:
VIGA: veergu „customer_id” ei saa automaatselt täisarvu tüübiks üle kanda
SQL-i olek: 42804
Vihje: määrake teisenduse teostamiseks avaldis USING.
See tähendab, et te ei saa lihtsalt võtta ja muuta välja tüüpi, kui tabelis on andmeid. Kuna kasutati varchari tüüpi, ei saa DBMS kindlaks teha, kas väärtus on täisarv. Kuigi andmed vastavad sellele tüübile. Selle punkti selgitamiseks soovitab veateade meie andmete õigeks täisarvudeks teisendamiseks kasutada klauslit USING:
ALTER TABLE kliendid ALTER COLUMN kliendi_id TYPE täisarv USING (kliendi_id::täisarv);
Selle tulemusel sujus kõik vigadeta:
Pange tähele, et USING-i kasutamisel on lisaks konkreetsele avaldisele võimalik kasutada ka funktsioone, muid välju ja operaatoreid.
Näiteks teisendame välja customer_id tagasi varchariks, kuid andmevormingu teisendusega:
ALTER TABLE kliendid ALTER COLUMN kliendi_id TYPE varchar USING (kliendi_id || "-" || eesnimi);
Selle tulemusel näeb tabel välja selline:
Otsige "kadunud" väärtusi
Jadade primaarvõtmena kasutamisel olge ettevaatlik: määramisel jäetakse osa jada elemente kogemata vahele, tabeliga töötamise tulemusena osa kirjeid kustutatakse. Selliseid väärtusi saab uuesti kasutada, kuid neid on suurtes tabelites raske leida.
Mõelge kahele otsinguvõimalusele.
Esimene viis
Käivitame järgmise päringu, et leida intervalli algus väärtusega "kadunud":
SELECT kliendi_id + 1 klientidelt mo WHERE NOT EXISTS (SELECT NULL FROM klientidest mi WHERE mi.kliendi_id = mo.kliendi_id + 1) ORDER BY kliendi_id;
Selle tulemusena saame väärtused: 5 , 9 ja 11 .
Kui teil on vaja leida mitte ainult esimene esinemisjuht, vaid kõik puuduvad väärtused, kasutage järgmist (ressursimahukat!) päringut:
WITH seq_max AS (SELECT max(kliendi_id) FROM klientidelt), seq_min AS (SELECT min(kliendi_id) FROM klientidest) SELECT * FROM gener_series((SELECT min FROM sek_min),(SELECT max FROM sek_max)) EXCEPT SELECT kliendi_id FROM klientidest;
Selle tulemusena näeme järgmist tulemust: 5 , 9 ja 6 .
Teine viis
Hankige kliendi_id-ga seotud jada nimi:
SELECT pg_get_serial_sequence("kliendid", "kliendi_id");
Ja otsige üles kõik puuduvad identifikaatorid:
WITH järjestuse_info AS (SELECT algusväärtus, viimane_väärtus FROM "Skeeminimi"."Järjenimi") SELECT genereeriv_seeria ((järjestuse_info.algusväärtus), (järjestuse_info.viimane_väärtus)) FROM järjestuse_info VÄLJA VALIK kliendi_id klientidelt;
Tabeli ridade arvu loendamine
Ridade arvu arvutab standardne loendusfunktsioon, kuid seda saab kasutada lisatingimustega.
Tabeli ridade koguarv:
SELECT count(*) FROM tabelist;
Ridade arv tingimusel, et määratud väli ei sisalda NULL-i:
SELECT count(veeru_nimi) FROM tabelist;
Määratud välja kordumatute ridade arv:
SELECT count(distinct col_name) FROM tabelist;
Tehingute kasutamine
Tehing ühendab toimingute jada üheks toiminguks. Selle eripära on see, et tehingu sooritamisel ilmnenud vea korral ei salvestata ühtegi toimingu tulemust andmebaasi.
Alustame tehingut käsuga BEGIN.
Kõigi toimingute tagasipööramiseks pärast BEGIN , kasutage käsku ROLLBACK.
Ja rakendamiseks - käsk COMMIT.
Käivitatavate päringute vaatamine ja lõpetamine
Taotluste kohta teabe saamiseks käivitage järgmine käsk:
SELECT pid, vanus(päringu_start, kella_ajatempel()), kasutajanimi, päring FROM pg_stat_activity WHERE päring != "
Konkreetse päringu peatamiseks käivitage järgmine käsk, määrates protsessi ID (pid):
SELECT pg_cancel_backend(procpid);
Taotluse lõpetamiseks käivitage:
SELECT pg_terminate_backend(procpid);
Konfiguratsiooniga töötamine
Klastri eksemplari asukoha leidmine ja muutmine
Võimalik on olukord, kui samas operatsioonisüsteemis on konfigureeritud mitu PostgreSQL-i eksemplari, mis "istuvad" erinevates portides. Sellisel juhul on iga eksemplari füüsilise asukoha tee leidmine üsna närvesööv ülesanne. Selle teabe saamiseks käivitage huvipakkuva klastri mis tahes andmebaasis järgmine päring:
NÄITA andmete_kataloogi;
Muutke asukohta teise, kasutades käsku:
SET andmekataloogi uus_kataloogi_tee;
Kuid muudatuste jõustumiseks on vaja taaskäivitamist.
Saadaolevate andmetüüpide loendi hankimine
Hankige saadaolevate andmetüüpide loend, kasutades käsku:
SELECT tüpnimi, tüpleen alates pg_type kus typtype="b";
tüüpnimi on andmetüübi nimi.
typelen – andmetüübi suurus.
DBMS-i sätete muutmine ilma taaskäivituseta
PostgreSQL-i sätted asuvad spetsiaalsetes failides, nagu postgresql.conf ja pg_hba.conf. Pärast nende failide muutmist vajate seadete uuesti hankimiseks DBMS-i. Selleks taaskäivitatakse andmebaasiserver. On selge, et peate seda tegema, kuid projekti tootmisversioonis, mida kasutavad tuhanded kasutajad, on see väga ebasoovitav. Seetõttu on PostgreSQL-il funktsioon, mille abil saate muudatusi rakendada ilma serverit taaskäivitamata:
SELECT pg_reload_conf();
Kuid kahjuks ei kehti see kõigi parameetrite kohta. Mõnel juhul on sätete rakendamiseks vajalik taaskäivitamine.
Oleme uurinud käske, mis muudavad PostgreSQL-i kasutavate arendajate ja DBA-de asjad lihtsamaks. Kuid see pole kõik võimalikud meetodid. Kui leiate huvitavaid ülesandeid, kirjutage neist kommentaarides. Jagame oma kasulikku kogemust!