Dom › postavke › Osnovne PostgreSQL naredbe. Osnovne PostgreSQL naredbe Kako vidjeti koja je verzija PostgreSQL-a pokrenuta

Osnovne PostgreSQL naredbe. Osnovne PostgreSQL naredbe Kako vidjeti koja je verzija PostgreSQL-a pokrenuta

U ovom ću članku pokazati 15 najkorisnijih naredbi za upravljanje postgreSQL.

1. Kako promijeniti root lozinku u PostgreSQL-u?

$ /usr/local/pgsql/bin/psql postgres postgres Lozinka: (stara lozinka) # ALTER USER postgres WITH PASSWORD ‘tmppassword’; $ /usr/local/pgsql/bin/psql postgres postgres Lozinka: (tmppassword)

Promjena lozinke za običnog korisnika funkcionira na isti način. Root korisnik može promijeniti lozinku za bilo kojeg korisnika.

# PROMIJENITE korisničko ime KORISNIKA SA LOZINKOM ‘tmppassword’;

2. Kako postaviti PostgreSQL na autostart?

$ 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 / itd/rc.d/init.d/postgresql

3. Provjerite status poslužitelja

$ /etc/init.d/postgresql status Lozinka: pg_ctl: poslužitelj radi (PID: 6171) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" [ Komentar: Ova poruka označava da je poslužitelj pokrenut i radi ispravno] $ /etc/init.d/postgresql status Lozinka: pg_ctl: server ne radi [ Komentar: Ova poruka označava da poslužitelj nije pokrenut]

4. Kako pokrenuti, zaustaviti, ponovno pokrenuti PostgreSQL?

# usluga postgresql stop Zaustavljanje PostgreSQL-a: poslužitelj je zaustavljen u redu # početak usluge postgresql Pokretanje PostgreSQL-a: ok # ponovno pokretanje usluge postgresql Ponovno pokretanje PostgreSQL-a: poslužitelj je zaustavljen u redu

5. Kako vidjeti koja verzija PostgreSQL-a radi?

$ /usr/local/pgsql/bin/psql test Dobro došli u psql 8.3.7, PostgreSQL interaktivni terminal. Tip: \autorska prava za uvjete distribucije \h za pomoć sa SQL naredbama \? za pomoć s psql naredbama \g ili završite točkom i zarezom za izvršenje upita \q za izlazak test=# selectversion(); verzija ——————————————————————————————— PostgreSQL 8.3.7 na i686-pc-linux-gnu, kompilirao GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 red) test=#

5. Kako kreirati korisnika u PostgreSQL-u?

Za to postoje dvije metode..

Metoda 1: U PSQL ljusci kreiramo korisnika pomoću naredbe CREATE USER.

# STVARAJTE KORISNIKA ramesh SA lozinkom 'tmppassword'; STVORI ULOGU

metoda2: Kreiramo korisnika preko createuser shell naredbe.

$ /usr/local/pgsql/bin/createuser sathiya Hoće li nova uloga biti superkorisnik? (y/n) Hoće li novoj ulozi biti dopušteno stvaranje baza podataka? (y/n) Hoće li novoj ulozi biti dopušteno stvaranje više novih uloga? (y/n) n STVARAJ ULOGU

6. Kako napraviti bazu podataka u PostgreSQL-u?

Za to postoje 2 metode.

metoda1: Stvorite bazu podataka pomoću PSQL ljuske pomoću naredbe CREATE DATABASE.

# STVARANJE BAZE PODATAKA mydb S VLASNIKOM ramesh; STVARANJE BAZE PODATAKA

metoda2: Koristimo naredbu createdb.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh STVARANJE BAZE PODATAKA

7. Dobivanje popisa svih baza podataka u Postgresql-u?

8. Kako obrisati bazu podataka u PostgreSQL-u?

9. Korištenje ugrađene pomoći za naredbe

Tim \? će prikazati liniju za pomoć za naredbu psql. \h CREATE će pokazati pomoć za sve naredbe koje počinju s CREATE.

# \? # \h STVORI # \h KREIRAJ INDEKS

10. Kako dobiti popis svih tablica u bazi u Postgresqlu?

# \d

Za praznu bazu podataka, dobit ćete poruku "Nisu pronađene relacije."

11. Kako mogu saznati vrijeme izvršenja zahtjeva?

# \timing - nakon izvršenja ove naredbe, svaki sljedeći zahtjev će pokazati vrijeme izvršenja.

# \ vrijeme Vrijeme je uključeno. # SELECT * iz pg_catalog.pg_attribute; Vrijeme: 9,583 ms

12. Kako sigurnosno kopirati i vratiti baze podataka i tablice u PostgreSQL?

Ovo pitanje je prilično veliko i objavit ću ga kasnije u posebnom članku.

13. Kako ispisati dostupne funkcije u PostgreSQL-u?

Za popis dostupnih funkcija, recite \df+

# \df # \df+

14. Kako urediti PostgreSQL upit u editoru?

# \e

\e će otvoriti uređivač u kojem možete urediti upit i spremiti ga.

15. Gdje mogu pronaći postgreSQL datoteku povijesti?

Kao i datoteka ~/.bash_history, postgreSQL pohranjuje sve sql naredbe u datoteku ~/.psql_history.

$ mačka ~/.psql_povijest promijenite korisnički postgres s lozinkom 'tmppassword'; \h alter korisnik odabere verziju(); kreirajte korisnika ramesh s lozinkom 'tmppassword'; \timing odaberite * iz pg_catalog.pg_attribute;

Pozdrav svima, danas želim napraviti mali podsjetnik o glavnim PostgreSQL naredbama. S PostgreSQL-om možete raditi i interaktivno i iz naredbenog retka. Program je psql. Siguran sam da će vam ovaj popis biti od velike koristi i da će vam uštedjeti vrijeme traženja raznih izvora. Dopustite mi da vas podsjetim da je ovo open-source projekt temeljen na Postgres DBMS-u, objavljen 1986., razvija ga PGDG global development group, u biti radi se o 5-8 ljudi, ali unatoč tome, razvija se vrlo intenzivno, uvodeći sve nove funkcije i ispravljajući stare bugove i greške.

Osnovne PostgreSQL naredbe u interaktivnom načinu rada:

\connect ime_db - povezivanje s bazom podataka pod nazivom ime_db
\du - popis korisnika
\dp (ili \z) - popis tablica, pogleda, nizova, prava pristupa njima
\di - indeksi
\ds - nizovi
\dt - popis tablica
\dt+ - popis svih tablica s opisom
\dt *s* - popis svih tablica koje sadrže s u nazivu
\dv - pogledi
\dS - sistemske tablice
\d+ - opis tablice
\o - poslati rezultate upita u datoteku
\l - popis baza podataka
\i - čitanje dolaznih podataka iz datoteke
\e - otvara trenutni sadržaj međuspremnika upita u uređivaču (osim ako nije drugačije navedeno u okruženju varijable EDITOR, koristit će se prema zadanim postavkama vi)
\d “table_name” – opis tablice
\i pokrenuti naredbu iz vanjske datoteke, npr. \i /my/directory/my.sql
\pset - naredba za postavljanje opcija oblikovanja
\echo - prikazuje poruku
\set - Postavlja vrijednost varijable okoline. Bez parametara, prikazuje popis trenutnih varijabli (\unset - briše).
\? - psql referenca
\help - SQL referenca
\q (ili Ctrl+D) - izlaz iz programa

Rad s PostgreSQL-om iz naredbenog retka:

-c (ili --command) - pokrenite SQL naredbu bez odlaska u interaktivni način rada
-f file.sql - izvršavanje naredbi iz datoteke file.sql
-l (ili --list) ispisuje dostupne baze podataka
-U (ili --username) - odredite korisničko ime (na primjer, postgres)
-W (ili --password) - upit za lozinku
-d dbname - povezivanje s bazom podataka dbname
-h - naziv hosta (poslužitelj)
-s - način rada korak po korak, odnosno morat ćete potvrditi sve naredbe
-S - način rada s jednim redom, to jest, novi red će izvršiti upit (riješi se; na kraju SQL konstrukcije)
-V - PostgreSQL verzija bez ulaska u interaktivni mod

Primjeri:

psql -U postgres -d dbname -c “CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);” - izvršavanje naredbe u bazi dbname.

psql -d dbname -H -c "SELECT * FROM my" -o my.html - izlazni rezultat upita u html datoteku.

PostgreSQL pomoćni programi (programi):

createdb i dropdb - kreiranje i ispuštanje baze podataka (respektivno)
createuser i dropuser - stvoriti i korisnik (odnosno)
pg_ctl je program dizajniran za rješavanje općih zadataka upravljanja (pokretanje, zaustavljanje, postavljanje parametara, itd.)
postmaster - PostgreSQL višekorisnički poslužiteljski modul (konfiguracija razina otklanjanja pogrešaka, portova, direktorija podataka)
initdb - stvaranje novih PostgreSQL klastera
initlocation - program za kreiranje direktorija za sekundarnu pohranu baza podataka
vacuumdb - fizičko i analitičko održavanje baze podataka
pg_dump - arhiviranje i vraćanje podataka
pg_dumpall - sigurnosna kopija cijelog PostgreSQL klastera
pg_restore - vraća bazu podataka iz arhiva (.tar, .tar.gz)

Primjeri sigurnosne kopije:

Izrada sigurnosne kopije mydb baze podataka, u komprimiranom obliku

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

Stvaranje sigurnosne kopije mydb baze podataka, kao obične tekstualne datoteke, uključujući naredbu za stvaranje baze podataka

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

Izrada sigurnosne kopije mydb baze podataka, u komprimiranom obliku, s tablicama koje sadrže plaćanja u nazivu

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

Ispis podataka samo jedne specifične tablice. Ako je potrebno napraviti sigurnosnu kopiju više od jedne tablice, tada se imena tablica ispisuju pomoću opcije -t za svaku tablicu.

pg_dump -a -t naziv_tablice -f naziv_datoteke naziv_baze_podataka

Napravite sigurnosnu kopiju s gz kompresijom

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

Popis najčešće korištenih opcija:

-h host - host, ako nije navedeno koristi se localhost ili vrijednost iz varijable okruženja PGHOST.
-p port - port, ako nije naveden koristi se 5432 ili vrijednost iz varijable okruženja PGPORT.
-u - korisnik, ako nije navedeno tada se koristi trenutni korisnik, vrijednost se također može navesti u varijabli okoline PGUSER.
-a, -data-only - samo ispis podataka, spremanje podataka i sheme prema zadanim postavkama.
-b - uključiti velike objekte (blogove) u dump.
-s, -schema-only - ispis samo shema.
-C, -create - dodaje naredbu za stvaranje baze podataka.
-c - dodaje naredbe za brisanje (ispuštanje) objekata (tablica, pogleda itd.).
-O Nemojte dodavati naredbe za postavljanje vlasnika objekta (tablica, pogleda itd.).
-F, -format (c|t|p) - ispis izlaznog formata, prilagođenog, tar ili običnog teksta.
-t, -table=TABLE - Navedite određenu tablicu za ispis.
-v, -verbose - opširni ispis.
-D, -attribute-inserts Dump pomoću naredbe INSERT s popisom naziva svojstava.

Sigurnosno kopirajte sve baze podataka pomoću naredbe pg_dumpall.

pg_dumpall > all.sql

Vraćanje tablica iz sigurnosnih kopija (sigurnosne kopije):

psql - vraćanje sigurnosnih kopija koje su pohranjene u običnoj tekstualnoj datoteci (plain text);
pg_restore - vraćanje komprimiranih sigurnosnih kopija (tar);

Vraćanje cijele sigurnosne kopije uz ignoriranje pogrešaka

psql -h lokalni host -U neki korisnik -d dbname -f mydb.sql

Vraćanje cijele sigurnosne kopije, zaustavljanje na prvoj pogrešci

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

Za vraćanje iz tarballa, prvo moramo stvoriti bazu podataka s CREATE DATABASE mydb; (ako opcija -C nije navedena prilikom izrade sigurnosne kopije) i vratiti

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

Vraćanje sigurnosne kopije baze podataka komprimirane s gz

psql -U postgres -d mydb -f mydb

Mislim da će vam postgresql baza podataka sada biti razumljivija. Nadam se da vam je ovaj popis PostgreSQL naredbi bio od pomoći.

postgres=# STVARANJE BAZE PODATAKA test_baza podataka; CREATE DATABASE postgres=# CREATE USER test_user SA lozinkom "qwerty"; CREATE ROLE postgres=# DODIJELI SVE privilegije NA BAZI PODATAKA test_database TO test_user; GRANT

Za izlaz iz ljuske unesite naredbu \q.

Pokušajmo sada raditi sa stvorenom bazom podataka u ime test_user:

psql -h lokalni host test_baze podataka test_korisnik

Kreirajmo novu tablicu:

Test_database=> CREATE SEQUENCE user_ids; CREATE SEQUENCE test_database=> CREATE TABLE korisnici (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("user_ids"), prijava CHAR(64), lozinka CHAR(64)); OBAVIJEST: CREATE TABLE / PRIMARY KEY će KREATI implicitni INDEKS "users_pkey" ZA TABLICU "users" CREATE TABLE

Brisanje korisnika

Da biste izbrisali korisnika, morate prenijeti njegova prava na drugog, a zatim samo izbrisati

PONOVNO DODIJELI VLASNIŠTVO osuđene_uloge NA nasljedničku_ulogu; DROP OWNED BY doomed_role;

- ponovite prethodne naredbe za svaku bazu u klasteru

ISPUŠTITI ULOGU osuđena_uloga;

Skripta za sigurnosno kopiranje tablice Postgres.

#!/bin/bash DBNAMES="web"; USER="postgres"; DB_NAME="web"; NEW_OWNER="user_remote"; DATE_Y=`/bin/datum "+%y"` DATE_M=`/bin/datum "+%m"` DATE_D=`/bin/datum "+%d"` SERVICE="pgdump" BACKUP_DIR="/var/ backup_db/20$(DATE_Y)/$(DATE_M)/$(DATE_D)" mkdir -p $BACKUP_DIR; za tbl u `psql -qAt -c "odaberite tablename iz pg_tables gdje je shemaname = "javno";" $(DB_NAME)` \ `psql -qAt -c "odaberite naziv sekvence iz sheme_informacija.sekvence gdje je shema_sekvence = "javno";" $(DB_NAME)` \ `psql -qAt -c "odaberite table_name iz information_schema.views gdje table_schema = "javno";" $(DB_NAME)` ; do echo "Izvoz tablice $tbl iz db $(DB_NAME) u datoteku 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 gotovo ##################BACKUP POSTGRES FUNKCIJE # Dump baze podataka bez datuma, kako bi ih ekstrahirali iz nje dalje funkcije pg_dump -Fc -s -f $BACKUP_DIR/db_dump $(DB_NAME) /bin/sleep 4; # Kreirajte funkciju popisa pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list ##Kako vratiti funkcije ########################## #pg_restore -h localhost -U korisničko ime -d osnovno ime - L lista_funkcija db_dump ###########################

Skripta za sigurnosno kopiranje tablice Postgres. napisano u perlu

Izvrši ispod user'a - postgres. Ako u crons-u, onda i ispod postgresql korisnika.

#!/usr/bin/env perl use strict; koristiti upozorenja; my $database_name = "knjižnica_knjiga"; moj $upit =<<"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=$tablename

Rječnici pretraživanja cijelog teksta u postgresql-u

Sigurnosno kopiranje baze podataka s udaljenog stroja

PGPASSWORD="PASSWORD" pg_dump -h $HOSTNAME -U databaseuser -Fc --verbose "database.itc-life.ru" | gzip > naziv baze podataka.gz

Sigurnosno kopiranje i vraćanje tablica

U PostgreSQL Postoje dva pomoćna programa za sigurnosna kopija pg_dump i pg_dumpall. pg_dump se koristi za backup jedne baze podataka, pg_dumpall se koristi za backup svih baza podataka i poslužitelja u cjelini (mora se pokrenuti kao postgresql superuser).

Izrada sigurnosne kopije baze podataka mydb, komprimirano

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

Izrada sigurnosne kopije baze podataka mydb, kao obična tekstualna datoteka, uključujući naredbu za stvaranje baze podataka

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

Izrada sigurnosne kopije baze podataka mydb, u komprimiranom obliku, s tablicama koje sadrže u nazivu plaćanja

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

Ispis podataka samo jedne specifične tablice. Ako trebate sigurnosno kopirati nekoliko tablica, nazivi tih tablica ispisuju se pomoću ključa -t za svaki stol.

pg_dump -a -t naziv_tablice -f naziv_datoteke naziv_baze_podataka

Napravite komprimiranu sigurnosnu kopiju gz

pg_dump -h lokalni host -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Popis najčešće korištenih opcija:

H host - host, ako nije navedeno onda se koristi lokalni domaćin PGHOST.

P port - port, ako nije naveden koristi se 5432 ili vrijednost iz varijable okoline PGPORT.

U - korisnik, ako nije navedeno koristi se trenutni korisnik, vrijednost se također može navesti u varijabli okruženja PGUSER.

A, --data-only - ispis samo podataka, podaci i shema spremaju se prema zadanim postavkama.

B - uključite velike objekte (blogove) u odlagalište.

S, --schema-only - Ispis samo shema.

C, --create - dodaje naredbu za stvaranje baze podataka.

C - dodaje naredbe za brisanje (ispuštanje) objekata (tablica, pogleda itd.).

O - ne dodavati naredbe za postavljanje vlasnika objekta (tablica, pogleda itd.).

F, --format (c|t|p) — ispis izlaznog formata, običaj, katran, ili čisti tekst.

T, --table=TABLE - odredite određenu tablicu za ispis.

V, --verbose - izlaz detaljnih informacija.

D, --attribute-inserts - ispis pomoću naredbe UMETNUTI s popisom naziva svojstava.

Sigurnosno kopirajte sve baze podataka pomoću naredbe pg_dumpall.

pg_dumpall > all.sql # provjeri sigurnosnu kopiju grep "^[\]connect" all.sql \connect db1 \connect db2

PostgreSQL ima dva pomoćna programa za oporavak pričuvne baze.

psql- vratiti sigurnosne kopije koje su pohranjene u običnoj tekstualnoj datoteci (plain text);
pg_vrati— oporavak komprimiranih sigurnosnih kopija (tar);

Oporavak baze podataka i

#pg_restore -v -e -d dbname dbname.dump

Vraćanje cijele sigurnosne kopije uz ignoriranje pogrešaka

psql -h lokalni host -U neki korisnik -d dbname -f mydb.sql

Vraćanje cijele sigurnosne kopije, zaustavljanje na prvoj pogrešci

psql -h lokalni host -U neki korisnik --set ON_ERROR_STOP=on -f mydb.sql

Za oporavak od katran-archive moramo prvo kreirati bazu koristeći CREATE DATABASE mydb; (ako opcija -C nije navedena prilikom izrade sigurnosne kopije) i vratiti

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

Vraćanje komprimirane sigurnosne kopije baze podataka gz

Gunzip mydb.gz psql -U postgres -d mydb -f mydb

Počevši od verzije 9.2, samo se struktura tablica može vratiti pomoću opcije --section

# stvoriti bazu podataka CREATE DATABASE mydb2; # vratiti pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

Održavanje stola

VACUUM ANALIZA stol; REINDEX BAZA PODATAKA dbName; REINDEX TABLE tabName;

Prijenos imenika podataka

Saznajte trenutni put

# način 1 $ su - postgres $ psql psql > PRIKAŽI imenik_podataka; # način 2 $ ps sjekira | grep "postgres -D"

Napravite novi imenik, dodijelite korisnika i inicijalizirajte

mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/data

Sada moramo ispraviti datoteku s uslugom koja se pokreće postgresql

# ispod luka linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pid

Čišćenje stola

Čišćenje stola naziv tablice i poništi brojač s ID-om.

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE ;

KASKADA potrebno u slučaju naziv tablice povezan s drugom tablicom.

Uklanjanje NULL iz polja

ALTER TABLE filmovi ALTER COLUMN godina DROP NOT NULL ;

Pokreće se pgbouncer

su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"

Odvojite korisnike od baze podataka

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = "mydb";`

15 korisnih PostgreSQL naredbi

Na internetu postoji mnogo priručnika za PostgreSQL koji opisuju osnovne naredbe. Ali kad dublje zaronimo u posao, postoje takva praktična pitanja koja zahtijevaju napredne naredbe.

Takve naredbe, ili isječci, rijetko su dokumentirani. Pogledajmo nekoliko primjera koji su korisni i programerima i administratorima baza podataka.

Dobivanje informacija o bazi podataka

Veličina baze podataka

Da biste dobili fizičku veličinu datoteka (pohranu) baze podataka, koristite sljedeći upit:

SELECT pg_database_size(current_database());

Rezultat će biti predstavljen kao broj poput 41809016.

current_database() je funkcija koja vraća naziv trenutne baze podataka. Umjesto toga, možete unijeti ime u tekstu:

SELECT pg_database_size("my_database");

Kako bismo dobili informacije u obliku čitljivom za čovjeka, koristimo funkciju pg_size_pretty:

SELECT pg_size_pretty(pg_database_size(current_database()));

Kao rezultat toga, dobivamo informacije poput 40 Mb.

Popis tablica

Ponekad trebate dobiti popis tablica baze podataka. Da bismo to učinili, koristimo sljedeći upit:

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

shema_informacija - standardna shema baze podataka koja sadrži zbirke pogleda (views), kao što su tablice, polja itd. Prikazi tablica sadrže informacije o svim tablicama baze podataka.

Upit opisan u nastavku će odabrati sve tablice iz navedene sheme trenutne baze podataka:

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

U posljednjoj klauzuli IN možete navesti naziv određene sheme.

Veličina stola

Po analogiji s dobivanjem veličine baze podataka, veličina podataka tablice može se izračunati pomoću odgovarajuće funkcije:

SELECT pg_relation_size("računi");

Funkcija pg_relation_size vraća veličinu koju navedeni sloj dane tablice ili indeksa zauzima na disku.

Naziv najveće tablice

Za prikaz popisa tablica u trenutnoj bazi podataka, poredanih po veličini tablice, pokrenite sljedeći upit:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Kako bismo prikazali informacije o najvećoj tablici, ograničavamo upit pomoću LIMIT:

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

relname je naziv tablice, indeksa, pogleda itd.
relpages - veličina prikaza ove tablice na disku u broju stranica (prema zadanim postavkama, jedna stranica je 8 KB).
pg_class je sistemska tablica koja sadrži informacije o odnosima tablica baze podataka.

Popis povezanih korisnika

Da biste saznali ime, IP i korišteni port povezanih korisnika, pokrenite sljedeći upit:

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity;

Aktivnost korisnika

Kako biste saznali aktivnost povezivanja određenog korisnika, upotrijebite sljedeći upit:

SELECT datname FROM pg_stat_activity WHERE usename = "devuser";

Rad s podacima i poljima tablice

Uklanjanje dvostrukih linija

Ako se dogodilo da tablica nema primarni ključ (primarni ključ), tada će sigurno biti duplikata među zapisima. Ako je za takvu tablicu, posebno veliku, potrebno postaviti ograničenja (ograničenje) za provjeru cjelovitosti, tada uklonite sljedeće elemente:

duplicirane linije
situacije u kojima se jedan ili više stupaca duplicira (ako se ti stupci trebaju koristiti kao primarni ključ).

Razmotrite tablicu s podacima o kupcima, gdje je cijeli red dupliciran (drugi u nizu).

Sljedeći upit pomoći će ukloniti sve duplikate:

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

Polje ctid, koje je jedinstveno za svaki zapis, skriveno je prema zadanim postavkama, ali je prisutno u svakoj tablici.

Posljednji zahtjev zahtjeva mnogo resursa, pa budite oprezni kada ga izvršavate na radnom projektu.

Sada razmotrite slučaj kada se vrijednosti polja ponavljaju.

Ako je prihvatljivo brisati duplikate bez spremanja svih podataka, izvršite sljedeći upit:

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

Ako su podaci važni, prvo morate pronaći zapise s duplikatima:

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

Prije brisanja takvih zapisa možete ih premjestiti u privremenu tablicu ili vrijednost customer_id u njima zamijeniti drugom.

Opći obrazac zahtjeva za brisanje gore opisanih zapisa je sljedeći:

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

Promjena vrste sigurnog polja

Možda postoji pitanje o uključivanju takvog zadatka na ovaj popis. Zaista, u PostgreSQL-u, promjena tipa polja vrlo je jednostavna pomoću naredbe ALTER. Pogledajmo ponovno tablicu kupaca kao primjer.

Polje customer_id koristi vrstu podataka niza varchar. Ovo je pogreška jer bi ovo polje trebalo pohranjivati ID-ove korisnika koji su u obliku cijelog broja. Korištenje varchara nije opravdano. Pokušajmo ispraviti ovaj nesporazum pomoću naredbe ALTER:

ALTER TABLE kupci ALTER COLUMN customer_id TYPE cijeli broj;

Ali kao rezultat izvršenja dobivamo pogrešku:

POGREŠKA: stupac "customer_id" ne može se automatski pretvoriti u tip integer
SQL stanje: 42804
Savjet: Navedite USING izraz za izvođenje pretvorbe.

To znači da ne možete samo uzeti i promijeniti vrstu polja kada u tablici ima podataka. Budući da je korišten tip varchar, DBMS ne može odrediti je li vrijednost cijeli broj. Iako podaci odgovaraju ovoj vrsti. Da bismo pojasnili ovu točku, poruka o pogrešci predlaže korištenje klauzule USING za ispravnu konverziju naših podataka u cijeli broj:

ALTER TABLE kupci ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);

Kao rezultat toga, sve je prošlo bez grešaka:

Napominjemo da je kod korištenja USING-a, osim određenog izraza, moguće koristiti funkcije, druga polja i operatore.

Na primjer, pretvorimo polje customer_id natrag u varchar, ali s konverzijom formata podataka:

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

Kao rezultat, tablica će izgledati ovako:

Potražite "izgubljene" vrijednosti

Budite oprezni pri korištenju sekvenci kao primarnog ključa: prilikom dodjele neki elementi sekvence se slučajno preskoče, kao rezultat rada s tablicom, neki se zapisi brišu. Takve se vrijednosti mogu ponovno koristiti, ali ih je teško pronaći u velikim tablicama.

Razmotrite dvije opcije pretraživanja.

Prvi način
Izvršimo sljedeći upit kako bismo pronašli početak intervala s "izgubljenom" vrijednošću:

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

Kao rezultat dobivamo vrijednosti: 5 , 9 i 11 .

Ako trebate pronaći ne samo prvo pojavljivanje, već i sve vrijednosti koje nedostaju, upotrijebite sljedeći (zahtjevan resursima!) upit:

WITH seq_max AS (SELECT max(customer_id) FROM kupaca), seq_min AS (SELECT min(customer_id) FROM kupaca) SELECT * FROM generate_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) EXCEPT SELECT customer_id FROM kupaca;

Kao rezultat, vidimo sljedeći rezultat: 5 , 9 i 6 .

Drugi način
Dobijte naziv niza povezanog s customer_id:

SELECT pg_get_serial_sequence("kupci", "customer_id");

I pronađite sve identifikatore koji nedostaju:

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 kupaca;

Prebrojavanje redaka u tablici

Broj redaka izračunava standardna funkcija brojanja, ali se može koristiti uz dodatne uvjete.

Ukupan broj redaka u tablici:

SELECT count(*) FROM tablice;

Broj redaka pod uvjetom da navedeno polje ne sadrži NULL:

SELECT count(col_name) FROM table;

Broj jedinstvenih redaka za navedeno polje:

SELECT count(distinct col_name) FROM table;

Korištenje transakcija

Transakcija kombinira niz akcija u jednu operaciju. Njegova je osobitost da u slučaju pogreške u izvršenju transakcije nijedan od rezultata radnji neće biti spremljen u bazu podataka.

Započnimo transakciju naredbom BEGIN.

Za vraćanje svih operacija nakon BEGIN koristite naredbu ROLLBACK.

A za primjenu - naredba COMMIT.

Pregledavanje i završavanje izvršnih upita

Da biste dobili informacije o zahtjevima, pokrenite sljedeću naredbu:

SELECT pid, age(query_start, clock_timestamp()), usename, query FROM pg_stat_activity WHERE upit != " " AND query NOT ILIKE "%pg_stat_activity%" ORDER BY query_start desc;

Da biste zaustavili određeni zahtjev, pokrenite sljedeću naredbu, navodeći ID procesa (pid):

SELECT pg_cancel_backend(procpid);

Da biste prekinuli zahtjev, pokrenite:

SELECT pg_terminate_backend(procpid);

Rad s konfiguracijom

Pronalaženje i promjena lokacije instance klastera

Moguća je situacija kada je nekoliko instanci PostgreSQL konfigurirano na istom operativnom sustavu, a koje "sjede" na različitim portovima. U ovom slučaju, pronalaženje puta do fizičke lokacije svake instance prilično je naporan zadatak. Da biste dobili ove informacije, pokrenite sljedeći upit prema bilo kojoj bazi podataka klastera od interesa:

PRIKAŽI imenik_podataka;

Promijenite lokaciju na drugu pomoću naredbe:

SET data_directory na new_directory_path;

Ali potrebno je ponovno pokretanje kako bi promjene stupile na snagu.

Dobivanje popisa dostupnih tipova podataka

Dobijte popis dostupnih tipova podataka pomoću naredbe:

SELECT typname, typlen iz pg_type gdje je typtype="b";

typname je naziv tipa podataka.
typlen - veličina tipa podataka.

Promjena postavki DBMS-a bez ponovnog pokretanja

PostgreSQL postavke nalaze se u posebnim datotekama kao što su postgresql.conf i pg_hba.conf. Nakon promjene ovih datoteka, trebate DBMS da ponovno dobijete postavke. Da biste to učinili, poslužitelj baze podataka se ponovno pokreće. Jasno je da to morate učiniti, ali na produkcijskoj verziji projekta, koju koriste tisuće korisnika, to je vrlo nepoželjno. Stoga PostgreSQL ima funkciju s kojom možete primijeniti promjene bez ponovnog pokretanja poslužitelja:

SELECT pg_reload_conf();

Ali, nažalost, ne odnosi se na sve parametre. U nekim slučajevima potrebno je ponovno podizanje sustava za primjenu postavki.

Pogledali smo naredbe koje će olakšati stvari programerima i administratorima baza podataka koji koriste PostgreSQL. Ali ovo nisu sve moguće metode. Ako naiđete na zanimljive zadatke, napišite o njima u komentarima. Podijelimo naše korisno iskustvo!

Zadnja izmjena: 17.03.2018

Tablice se kreiraju pomoću naredbe CREATE TABLE nakon koje slijedi naziv tablice. S ovom naredbom također možete koristiti brojne operatore koji definiraju stupce tablice i njihove atribute. Opća sintaksa za stvaranje tablice je sljedeća:

CREATE TABLE table_name (column_name1 data_type column_attributes1, column_name2 data_type column2 atributi, ..................................... .... .......... naziv_stupcaN tip_podatka atributi_stupcaN, atributi_tablice);

Nakon naziva tablice, u zagradama je navedena specifikacija za sve stupce. Štoviše, za svaki stupac morate navesti naziv i tip podataka koji će predstavljati. Tip podataka određuje koje podatke (brojeve, nizove itd.) stupac može sadržavati.

Na primjer, stvorimo tablicu u bazi podataka koristeći pgAdmin. Da biste to učinili, najprije odaberite ciljanu bazu podataka u pgAdmin-u, desnom tipkom miša kliknite na nju i odaberite Alat za upite... stavku u kontekstnom izborniku:

Nakon toga otvorit će se polje za unos koda u SQL. Štoviše, tablica će biti kreirana posebno za bazu podataka za koju ćemo otvoriti ovo polje za unos SQL-a.

CREATE TABLE kupci (Id SERIJSKI PRIMARNI KLJUČ, Ime ZNAK RAZLIČIT (30), Prezime ZNAK RAZLIČIT (30), E-pošta ZNAK RAZLIČIT (30), Dob CIJELI BROJ);

U ovom slučaju u tablici Kupci definirano je pet stupaca: Id, Ime, Prezime, Dob, E-mail. Prvi stupac, Id, predstavlja ID kupca, služi kao primarni ključ i stoga je tipa SERIAL. Zapravo, ovaj će stupac pohraniti numeričku vrijednost 1, 2, 3 itd., koja će se automatski povećati za jedan za svaki novi red.

Sljedeća tri stupca predstavljaju ime, prezime i adresu e-pošte kupca i tipa su CHARACTER VARYING(30), što znači da predstavljaju niz koji nije duži od 30 znakova.

Zadnji stupac - Dob predstavlja dob korisnika i tipa je INTEGER , odnosno pohranjuje brojeve.

A nakon izvršenja ove naredbe, tablica kupaca će biti dodana u odabranu bazu podataka.