Shell ukončíte zadáním příkazu \q.
Nyní zkusme pracovat s vytvořenou databází jménem test_user:
psql -h localhost testovací_databáze testovací_uživatel
Vytvoříme novou tabulku:
Test_database=> CREATE SEQUENCE user_ids; CREATE SEQUENCE test_database=> CREATE TABLE uživatelé (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("user_ids"), přihlášení CHAR(64), heslo CHAR(64)); UPOZORNĚNÍ: VYTVOŘENÍ TABULKY / PRIMÁRNÍ KLÍČ VYTVOŘÍ implicitní INDEX "users_pkey" PRO TABULKU "uživatelé" CREATE TABLE
Smazání uživatele
Chcete-li smazat uživatele, musíte převést jeho práva na jiného a pak teprve smazat
ZMĚNIT PŘIŘAZENÍ VLASTNĚNÍ DOOMED_role TO follower_role; DROP OWNED BY doomed_role;- opakujte předchozí příkazy pro každou základnu v clusteru
DROP ROLE doomed_role;
Skript zálohování tabulky 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$(DATE_Y)/$(DATE_M)/$(DATE_D)" mkdir -p $BACKUP_DIR; pro tbl v `psql -qAt -c "vyberte název tabulky z pg_tables, kde schemaname = "public";" $(DB_NAME)` \ `psql -qAt -c "vyberte název_sekvence z information_schema.sequences kde sekvence_schema = "veřejné";" $(DB_NAME)` \ `psql -qAt -c "vyberte název_tabulky z information_schema.views kde tabulka_schema = "veřejné";" $(DB_NAME)` ; do echo "Exportování tabulky $tbl z db $(DB_NAME) do souboru 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 hotovo #################BACKUP POSTGRES FUNKCE # Vypište databázi bez data, abyste je z ní extrahovali další funkce pg_dump -Fc -s -f $BACKUP_DIR/db_dump $(JMÉNO_DB) /bin/sleep 4; # Vytvořit funkci seznamu pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list ##Jak obnovit funkce ######################### #pg_restore -h localhost -U username -d basename - L function_list db_dump #########################Skript zálohování tabulky Postgres. napsané v perlu
Spustit z pod user'a - postgres. Pokud v cronech, tak i z pod uživatele postgresql.
#!/usr/bin/env perl use strict; používat varování; moje $database_name = "knihovna_knih"; můj $ dotaz =<<"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=$tablenameSlovníky fulltextového vyhledávání v postgresql
Zálohování databáze ze vzdáleného počítače
PGPASSWORD="PASSWORD" pg_dump -h $HOSTNAME -U databaseuser -Fc --verbose "database.itc-life.ru" | gzip > název databáze.gzZálohování a obnovení tabulek
V PostgreSQL K dispozici jsou dvě inženýrské sítě pro záloha pg_dump a pg_dumpall . pg_dump se používá k zálohování jedné databáze, pg_dumpall se používá k zálohování všech databází a serveru jako celku (musí být spuštěn pod superuživatelem postgresql).
Vytvoření zálohy databáze mydb, stlačený
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb
Vytvoření zálohy databáze mydb, jako prostý textový soubor, včetně příkazu k vytvoření databáze
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
Vytvoření zálohy databáze mydb, v komprimované podobě, s tabulkami, které obsahují v názvu Platby
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *platby* -f payment_tables.backup mydb
Výpis dat pouze jedné konkrétní tabulky. Pokud potřebujete zálohovat několik tabulek, názvy těchto tabulek jsou uvedeny pomocí klíče -t pro každý stůl.
pg_dump -a -t název_tabulky -f název_souboru název_databáze
Vytvořte komprimovanou zálohu gz
pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gzSeznam nejčastěji používaných možností:
H hostitel - hostitel, pokud není uvedeno, použije se localhost PGHOST.
P port - port, pokud není uveden, pak se používá 5432 nebo hodnota z proměnné prostředí PGPORT.
U - uživatel, pokud není zadán, použije se aktuální uživatel, hodnotu lze také zadat v proměnné prostředí PGUSER.
A, --data-only - ve výchozím nastavení se ukládají pouze data, data a schéma.
B - zahrnout velké objekty (blogy) do skládky.
S, --schema-only - Vypsat pouze schémata.
C, --create - přidá příkaz pro vytvoření databáze.
C - přidává příkazy pro mazání (drop) objektů (tabulky, pohledy atd.).
O - nepřidávat příkazy pro nastavení vlastníka objektu (tabulky, pohledy atd.).
F, --format (c|t|p) – výstupní formát výpisu, zvyk, dehet, nebo prostý text.
T, --table=TABLE - specifikuje konkrétní tabulku pro výpis.
V, --verbose - výstup podrobných informací.
D, --attribute-inserts - výpis pomocí příkazu VLOŽIT se seznamem jmen nemovitostí.
Zálohujte všechny databáze pomocí příkazu pg_dumpall.
pg_dumpall > all.sql # zkontrolovat zálohu grep "^[\]připojit" all.sql \connect db1 \connect db2
PostgreSQL má dva nástroje pro zotavení záložní základny.
- psql- obnovit zálohy, které jsou uloženy v prostém textovém souboru (prostý text);
- pg_restore— obnova komprimovaných záloh (tar);
Obnova databáze a
#pg_restore -v -e -d název_databáze název_databáze.dump
Obnovení celé zálohy s ignorováním chyb
psql -h localhost -U someuser -d dbname -f mydb.sql
Obnovení celé zálohy, zastavení při první chybě
psql -h localhost -U someuser --set ON_ERROR_STOP=on -f mydb.sqlVzpamatovat se z dehet-archive musíme nejprve vytvořit databázi pomocí CREATE DATABASE mydb; (pokud nebyla při vytváření zálohy zadána volba -C) a obnovit
pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup
Obnovení komprimované zálohy databáze gz
Gunzip mydb.gz psql -U postgres -d mydb -f mydb
Počínaje verzí 9.2 lze pomocí volby --section obnovit pouze strukturu tabulek
# vytvořit databázi CREATE DATABASE mydb2; # obnovit pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backupÚdržba stolu
stůl VACUUM ANALYZE; REINDEX DATABASE název_db; REINDEX TABLE název záložky;Přenos datového adresáře
Zjistěte aktuální cestu
# cesta 1 $ su - postgres $ psql psql > SHOW data_directory; # cesta 2 $ ps sekera | grep "postgres -D"Vytvořte nový adresář, přiřaďte uživatele a inicializujte
mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/dataNyní musíme opravit soubor pomocí služby, která se spustí postgresql
# pod arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Prostředí =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pidČištění stolu
Čištění stolu název tabulky a resetujte počítadlo s ID.
TRUNCATE TABLE název tabulky RESTART IDENTITY CASCADE ;KASKÁDA potřeba v případě název tabulky propojený s jinou tabulkou.
Odebrání hodnoty NULL z pole
filmy ALTER TABLE ALTER COLUMN rok DROP NOT NULL;Spuštěn pgbouncer
su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"Odpojit uživatele od databáze
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = "mydb";`Zdravím všechny, dnes chci malou připomínku hlavních příkazů PostgreSQL. S PostgreSQL můžete pracovat jak interaktivně, tak z příkazové řádky. Program je psql. Jsem si jist, že tento seznam bude pro vás velmi užitečný a ušetří vám čas při hledání různých zdrojů. Připomínám, že se jedná o open-source projekt založený na Postgres DBMS, vydaný v roce 1986, vyvíjí ho globální vývojová skupina PGDG, v podstatě je to 5-8 lidí, ale i přes to se vyvíjí velmi intenzivně zavádět všechny nové funkce a opravovat staré chyby a chyby.
Základní příkazy PostgreSQL v interaktivním režimu:
- \connect název_db - připojení k databázi s názvem název_db
- \du - seznam uživatelů
- \dp (nebo \z) - seznam tabulek, pohledů, sekvencí, přístupová práva k nim
- \di - indexy
- \ds - sekvence
- \dt - seznam tabulek
- \dt+ - seznam všech tabulek s popisem
- \dt *s* - seznam všech tabulek obsahujících s v názvu
- \dv - pohledy
- \dS - systémové tabulky
- \d+ - popis tabulky
- \o - odeslat výsledky dotazu do souboru
- \l - seznam databází
- \i - čtení příchozích dat ze souboru
- \e - otevře aktuální obsah bufferu dotazu v editoru (pokud není v prostředí proměnné EDITOR uvedeno jinak, bude standardně používán vi)
- \d „název_tabulky“ – popis tabulky
- \i spusťte příkaz z externího souboru, např. \i /muj/adresar/my.sql
- \pset - příkaz pro nastavení možností formátování
- \echo - zobrazí zprávu
- \set - Nastaví hodnotu proměnné prostředí. Bez parametrů zobrazí seznam aktuálních proměnných (\unset - smaže).
- \? - odkaz na psql
- \help - SQL reference
- \q (nebo Ctrl+D) - ukončení programu
Práce s PostgreSQL z příkazového řádku:
- -c (nebo --command) - spustí příkaz SQL bez přechodu do interaktivního režimu
- -f soubor.sql - spouštět příkazy ze souboru soubor.sql
- -l (nebo --list) uvádí dostupné databáze
- -U (nebo --username) - zadejte uživatelské jméno (například postgres)
- -W (nebo --password) - výzva k zadání hesla
- -d dbname - připojení k databázi dbname
- -h - název hostitele (serveru)
- -s - režim krok za krokem, to znamená, že budete muset potvrdit všechny příkazy
- -S - jednořádkový režim, to znamená, že nový řádek provede dotaz (zbaví se; na konci konstrukce SQL)
- -V - Verze PostgreSQL bez vstupu do interaktivního režimu
Příklady:
psql -U postgres -d název_databáze -c “VYTVOŘIT TABULKU my(nějaké_id sériového PRIMÁRNÍHO KLÍČE, text nějakého_textu);” - provedení příkazu v databázi dbname.
psql -d dbname -H -c "SELECT * FROM my" -o my.html - výstup výsledku dotazu do html souboru.
PostgreSQL utility (programy):
- createdb a dropdb - vytvoření a zrušení databáze (v tomto pořadí)
- createuser a dropuser - vytvořit a uživatel (respektive)
- pg_ctl je program určený k řešení obecných řídicích úloh (start, stop, nastavení parametrů atd.)
- postmaster - modul víceuživatelského serveru PostgreSQL (konfigurace úrovní ladění, portů, datových adresářů)
- initdb - vytváření nových clusterů PostgreSQL
- initlocation - program pro vytváření adresářů pro sekundární ukládání databází
- vákuumdb - fyzická a analytická údržba databáze
- pg_dump - archivace a obnova dat
- pg_dumpall - zálohování celého clusteru PostgreSQL
- pg_restore - obnovení databáze z archivů (.tar, .tar.gz)
Příklady zálohování:
Vytvoření zálohy databáze mydb v komprimované podobě
Pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb
Vytvoření zálohy databáze mydb, jako prostého textového souboru, včetně příkazu pro vytvoření databáze
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
Vytvoření zálohy databáze mydb v komprimované podobě s tabulkami, které obsahují platby v názvu
Pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *platby* -f payment_tables.backup mydb
Výpis dat pouze jedné konkrétní tabulky. Pokud je třeba zálohovat více než jednu tabulku, pak jsou názvy tabulek uvedeny pomocí volby -t pro každou tabulku.
pg_dump -a -t název_tabulky -f název_souboru název_databáze
Vytvořte zálohu s kompresí gz
Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz
Seznam nejčastěji používaných možností:
- -h hostitel - hostitel, pokud není zadán, použije se localhost nebo hodnota z proměnné prostředí PGHOST.
- -p port - port, pokud není zadán, použije se 5432 nebo hodnota z proměnné prostředí PGPORT.
- -u - uživatel, pokud není zadán, použije se aktuální uživatel, hodnotu lze také zadat v proměnné prostředí PGUSER.
- -a, -data-only - vypíše pouze data, ve výchozím nastavení uloží data a schéma.
- -b - zahrnout do výpisu velké objekty (blogy).
- -s, -schema-only - vypíše pouze schémata.
- -C, -create - přidá příkaz pro vytvoření databáze.
- -c - přidá příkazy pro mazání (odhazování) objektů (tabulek, pohledů atd.).
- -O Nepřidávejte příkazy pro nastavení vlastníka objektu (tabulky, pohledy atd.).
- -F, -format (c|t|p) - vypíše výstupní formát, vlastní, tar nebo prostý text.
- -t, -table=TABLE - Zadejte konkrétní tabulku, která se má vypsat.
- -v, -verbose - podrobný výstup.
- -D, -attribute-inserts Vypíše výpis pomocí příkazu INSERT se seznamem názvů vlastností.
Zálohujte všechny databáze pomocí příkazu pg_dumpall.
pg_dumpall > all.sql
Obnovení tabulek ze záloh (záloh):
psql - obnoví zálohy, které jsou uloženy v prostém textovém souboru (prostý text);
pg_restore - obnoví komprimované zálohy (tar);
Obnovení celé zálohy s ignorováním chyb
psql -h localhost -U someuser -d dbname -f mydb.sql
Obnovení celé zálohy, zastavení při první chybě
psql -h localhost -U someuser -set ON_ERROR_STOP=on -f mydb.sql
Chcete-li obnovit z tarballu, musíme nejprve vytvořit databázi pomocí CREATE DATABASE mydb; (pokud nebyla při vytváření zálohy zadána volba -C) a obnovit
pg_restore -dbname=mydb -jobs=4 -podrobný mydb.backup
Obnovení zálohy databáze komprimované pomocí gz
psql -U postgres -d mydb -f mydb
Myslím, že databáze postgresql pro vás nyní bude srozumitelnější. Doufám, že tento seznam příkazů PostgreSQL byl pro vás užitečný.
V tomto článku ukážu 15 nejužitečnějších příkazů pro správu postgreSQL.
1. Jak změnit heslo root v PostgreSQL?
$ /usr/local/pgsql/bin/psql postgres postgres Heslo: (staré heslo) # ALTER USER postgres WITH HESLO ‘tmppassword’; $ /usr/local/pgsql/bin/psql postgres postgres Heslo: (tmppassword)Změna hesla pro běžného uživatele funguje stejně. Uživatel root může změnit heslo libovolného uživatele.
# ALTER USER uživatelské jméno WITH PASSWORD ‘tmppassword’;
2. Jak nastavit automatické spouštění PostgreSQL?
$ 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. Zkontrolujte stav serveru
$ /etc/init.d/postgresql stav Heslo: pg_ctl: server běží (PID: 6171) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" [ Komentář: Tato zpráva označuje, že server je v pořádku a běží správně] $ /etc/init.d/postgresql stav Heslo: pg_ctl: žádný server neběží [ Komentář: Tato zpráva označuje, že server neběží]4. Jak spustit, zastavit, restartovat PostgreSQL?
# služba postgresql stop Zastavení PostgreSQL: server se zastavil v pořádku # start služby postgresql Spuštění PostgreSQL: ok # restart služby postgresql Restartování PostgreSQL: server se zastavil v pořádku5. Jak zjistit, jaká verze PostgreSQL běží?
$ /usr/local/pgsql/bin/psql test Vítejte v psql 8.3.7, interaktivním terminálu PostgreSQL. Zadejte: \copyright pro distribuční podmínky \h pro nápovědu k příkazům SQL \? pro pomoc s příkazy psql \g nebo ukončete středníkem pro provedení dotazu \q pro ukončení testu=# selectversion(); verze ————————————————————————————————- PostgreSQL 8.3.7 na i686-pc-linux-gnu, zkompilovaný GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 řádek) test=#5. Jak vytvořit uživatele v PostgreSQL?
Jsou na to dvě metody..
Metoda 1: Uživatele vytvoříme v PSQL shellu pomocí příkazu CREATE USER.
# VYTVOŘTE UŽIVATELE ramesh S heslem 'tmppassword'; VYTVOŘIT ROLE
Metoda2: Uživatele vytvoříme pomocí příkazu createuser shell.
$ /usr/local/pgsql/bin/createuser sathiya Bude novou rolí superuživatel? (y/n) Bude mít nová role povoleno vytvářet databáze? (y/n) Bude mít nová role povoleno vytvářet další nové role? (y/n) n VYTVOŘIT ROLE
6. Jak vytvořit databázi v PostgreSQL?
Jsou na to 2 způsoby.
Metoda1: Vytvořte databázi pomocí prostředí PSQL pomocí příkazu CREATE DATABASE.
# VYTVOŘIT DATABÁZI mydb S VLASTNÍKEM ramesh; VYTVOŘIT DATABÁZI
Metoda2: Použijeme příkaz createdb.
$ /usr/local/pgsql/bin/createdb mydb -O ramesh VYTVOŘIT DATABÁZI
7. Získání seznamu všech databází v Postgresql?
# \l Seznam databází Název | Majitel | Kódování ———-+———-+———- záloha | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 šablona0 | postgres | UTF8 šablona1 | postgres | UTF88. Jak smazat databázi v PostgreSQL?
# \l Seznam databází Název | Majitel | Kódování ———-+———-+———- záloha | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 šablona0 | postgres | UTF8 šablona1 | postgres | UTF8# DROP DATABASE mydb; DROP DATABASE9. Použití vestavěné nápovědy k příkazům
Tým \? zobrazí řádek nápovědy pro příkaz psql. \h CREATE zobrazí nápovědu pro všechny příkazy, které začínají CREATE.
# \? # \h VYTVOŘIT # \h VYTVOŘIT INDEX
10. Jak získat seznam všech tabulek v databázi v Postgresql?
# \dU prázdné databáze se zobrazí zpráva „Nenalezeny žádné vztahy“.
11. Jak zjistím dobu vyřízení žádosti?
# \timing - po provedení tohoto příkazu každý následující požadavek zobrazí čas provedení.
# \načasování Načasování je zapnuté. # SELECT * from pg_catalog.pg_attribute ;Čas: 9,583 ms
12. Jak zálohovat a obnovovat databáze a tabulky v PostgreSQL?
Tato otázka je poměrně rozsáhlá a později ji zveřejním v samostatném článku.
13. Jak vypsat dostupné funkce v PostgreSQL?
Pro seznam dostupných funkcí řekněte \df+
# \df # \df+
14. Jak upravit PostgreSQL dotaz v editoru?
# \E\e otevře editor, kde můžete upravit dotaz a uložit jej.
15. Kde najdu soubor historie postgreSQL?
Stejně jako soubor ~/.bash_history, postgreSQL ukládá všechny příkazy SQL do souboru ~/.psql_history.
$ cat ~/.psql_history změnit uživatele postgres pomocí hesla 'tmppassword'; \h alter user select version(); vytvořit uživatele ramesh s heslem 'tmppassword'; \timing select * from pg_catalog.pg_attribute;
Poslední aktualizace: 17.03.2018
Tabulky se vytvářejí pomocí příkazu CREATE TABLE následovaného názvem tabulky. S tímto příkazem můžete také použít řadu operátorů, které definují sloupce tabulky a jejich atributy. Obecná syntaxe pro vytvoření tabulky je následující:
CREATE TABLE název_tabulky (název_sloupce1 datový_typ atribut_sloupce1, název_sloupce2 datový_typ sloupec2 atributy, ................................. .... .......... název_sloupceN datový_typ atribut_sloupceN, atributy_tabulky);
Za názvem tabulky je v závorkách uvedena specifikace pro všechny sloupce. Navíc pro každý sloupec musíte zadat název a datový typ, který bude reprezentovat. Datový typ určuje, jaká data (čísla, řetězce atd.) může sloupec obsahovat.
Vytvořme například tabulku v databázi pomocí pgAdmin. Chcete-li to provést, nejprve vyberte cílovou databázi v pgAdmin, klikněte na ni pravým tlačítkem a v kontextovém menu vyberte položku Query Tool...:
Poté se otevře pole pro zadání kódu v SQL. Navíc bude tabulka vytvořena speciálně pro databázi, pro kterou toto pole otevřeme pro zadání SQL.
CREATE TABLE customers (ID SÉRIOVÝ PRIMÁRNÍ KLÍČ, JMÉNO ZNAK VARYING(30), Příjmení CHARACTER VARYING(30), E-mail CHARACTER VARYING(30), INTEGER INTEGER);
V tomto případě je v tabulce Zákazníci definováno pět sloupců: ID, Jméno, Příjmení, Věk, Email. První sloupec Id představuje ID zákazníka, slouží jako primární klíč a je tedy typu SERIAL . Ve skutečnosti bude tento sloupec ukládat číselnou hodnotu 1, 2, 3 atd., která se automaticky zvýší o jednu pro každý nový řádek.
Další tři sloupce představují křestní jméno, příjmení a e-mailovou adresu zákazníka a jsou typu CHARACTER VARYING(30) , což znamená, že představují řetězec ne delší než 30 znaků.
Poslední sloupec - Věk představuje věk uživatele a je typu INTEGER , tedy ukládá čísla.
A po provedení tohoto příkazu bude tabulka zákazníků přidána do vybrané databáze.
Mazání tabulek
Chcete-li zrušit tabulky, použijte příkaz DROP TABLE, který má následující syntaxi:
DROP TABLE tabulka1 [, tabulka2, ...];
Například smazání tabulky zákazníků.
15 Užitečné příkazy PostgreSQL
Na internetu je mnoho příruček PostgreSQL, které popisují základní příkazy. Ale když se ponoříte hlouběji do práce, existují takové praktické problémy, které vyžadují pokročilé příkazy.
Takové příkazy nebo úryvky jsou zřídka dokumentovány. Podívejme se na pár příkladů, které jsou užitečné pro vývojáře i správce databází.
Získání informací o databázi
Velikost databáze
Chcete-li získat fyzickou velikost souborů (úložiště) databáze, použijte následující dotaz:
SELECT pg_database_size(aktuální_databáze());
Výsledek bude prezentován jako číslo jako 41809016.
current_database() je funkce, která vrací název aktuální databáze. Místo toho můžete zadat název v textu:
SELECT pg_database_size("moje_databaze");
Abychom získali informace ve formě čitelné pro člověka, používáme funkci pg_size_pretty:
SELECT pg_size_pretty(pg_database_size(current_database()));
V důsledku toho získáme informace jako 40 Mb.
Seznam tabulek
Někdy potřebujete získat seznam databázových tabulek. K tomu použijeme následující dotaz:
SELECT název_tabulky FROM informační_schéma.tabulky WHERE schéma_tabulky NENÍ IN ("informační_schéma","pg_katalog");
information_schema - standardní databázové schéma, které obsahuje kolekce pohledů (pohledů), jako jsou tabulky, pole atd. Tabulkové pohledy obsahují informace o všech databázových tabulkách.
Dotaz popsaný níže vybere všechny tabulky ze zadaného schématu aktuální databáze:
SELECT název_tabulky FROM schéma_informací.tabulky WHERE schéma_tabulky NENÍ IN ("schéma_informací", "katalog_pg") AND schéma_tabulky IN("veřejné", "myschema");
V poslední klauzuli IN můžete zadat název konkrétního schématu.
Velikost stolu
Analogicky k získání velikosti databáze lze velikost dat tabulky vypočítat pomocí odpovídající funkce:
SELECT pg_relation_size("účty");
Funkce pg_relation_size vrací velikost, kterou zadaná vrstva dané tabulky nebo indexu zabírá na disku.
Název největší tabulky
Chcete-li zobrazit seznam tabulek v aktuální databázi seřazený podle velikosti tabulky, spusťte následující dotaz:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
Abychom zobrazili informace o největší tabulce, omezíme dotaz pomocí LIMIT:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;
relname je název tabulky, indexu, pohledu atd.
relpages - velikost zobrazení této tabulky na disku v počtu stránek (standardně je jedna stránka 8 KB).
pg_class je systémová tabulka, která obsahuje informace o vztazích databázových tabulek.
Seznam připojených uživatelů
Chcete-li zjistit jméno, IP a použitý port připojených uživatelů, spusťte následující dotaz:
SELECT dataname,usename,client_addr,client_port FROM pg_stat_activity;
Aktivita uživatele
Chcete-li zjistit aktivitu připojení konkrétního uživatele, použijte následující dotaz:
SELECT dataname FROM pg_stat_activity WHERE usename = "devuser";
Práce s datovými a tabulkovými poli
Odstranění duplicitních řádků
Pokud by se stalo, že tabulka nemá primární klíč (primární klíč), pak mezi záznamy budou jistě duplikáty. Pokud je pro takovou tabulku, zejména velkou, nutné nastavit omezení (omezení) pro kontrolu integrity, odstraňte následující prvky:
- duplicitní řádky
- situace, kdy je duplikován jeden nebo více sloupců (pokud se tyto sloupce mají používat jako primární klíč).
Uvažujme tabulku s údaji o zákaznících, kde je celý řádek duplikován (druhý v řadě).
Následující dotaz pomůže odstranit všechny duplikáty:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customers.*);
Pole ctid, které je jedinečné pro každý záznam, je ve výchozím nastavení skryté, ale je přítomno v každé tabulce.
Poslední požadavek je náročný na zdroje, takže buďte opatrní při jeho provádění na pracovním projektu.
Nyní zvažte případ, kdy se hodnoty pole opakují.
Pokud je přijatelné odstranit duplikáty bez uložení všech dat, proveďte následující dotaz:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Pokud jsou data důležitá, musíte nejprve najít záznamy s duplikáty:
SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
Před odstraněním takových záznamů je můžete přesunout do dočasné tabulky nebo v nich nahradit hodnotu customer_id jinou.
Obecná forma žádosti o smazání výše popsaných záznamů je následující:
DELETE FROM název_tabulky WHERE ctid NOT IN (SELECT max(ctid) FROM název_tabulky GROUP BY sloupec1, );
Změna typu bezpečného pole
Může se vyskytnout otázka o zařazení takového úkolu do tohoto seznamu. V PostgreSQL je změna typu pole velmi jednoduchá pomocí příkazu ALTER. Podívejme se opět jako příklad na tabulku zákazníků.
Pole customer_id používá datový typ řetězce varchar. Toto je chyba, protože toto pole má ukládat ID zákazníků, která jsou ve formátu celých čísel. Použití varchar není opodstatněné. Pokusme se toto nedorozumění napravit pomocí příkazu ALTER:
ALTER TABLE zákazníci ALTER COLUMN customer_id TYPE celé číslo;
Ale v důsledku provedení dostaneme chybu:
CHYBA: sloupec „customer_id“ nelze automaticky přetypovat na typ celé číslo
Stav SQL: 42804
Tip: Pro provedení převodu zadejte výraz USING.
To znamená, že nemůžete jen vzít a změnit typ pole, když jsou v tabulce data. Protože byl použit typ varchar, DBMS nemůže určit, zda je hodnota celé číslo. I když údaje tomuto typu odpovídají. Abychom tento bod objasnili, chybová zpráva navrhuje použít klauzuli USING ke správnému převodu našich dat na celé číslo:
ALTER TABLE zákazníci ALTER COLUMN customer_id TYPE integer USING (customer_id::integer);
Výsledkem bylo, že vše proběhlo bez chyb:
Upozorňujeme, že při použití POUŽÍVÁNÍ je možné kromě konkrétního výrazu používat funkce, další pole a operátory.
Pojďme například převést pole customer_id zpět na varchar , ale s převodem formátu dat:
ALTER TABLE zákazníci ALTER COLUMN customer_id TYPE varchar USING (customer_id || "-" || first_name);
Ve výsledku bude tabulka vypadat takto:
Hledejte „ztracené“ hodnoty
Při použití sekvencí jako primárního klíče buďte opatrní: při přiřazování jsou omylem přeskočeny některé prvky sekvence, v důsledku práce s tabulkou jsou některé záznamy smazány. Takové hodnoty lze znovu použít, ale je obtížné je najít ve velkých tabulkách.
Zvažte dvě možnosti vyhledávání.
První způsob
Proveďme následující dotaz, abychom našli začátek intervalu s hodnotou „ztraceno“:
SELECT customer_id + 1 FROM customers mo WHERE NOT EXISTS (SELECT NULL FROM customers mi WHERE mi.customer_id = mo.customer_id + 1) ORDER BY customer_id;
Ve výsledku dostaneme hodnoty: 5 , 9 a 11 .
Pokud potřebujete najít nejen první výskyt, ale všechny chybějící hodnoty, použijte následující (na zdroje náročný!) dotaz:
WITH seq_max AS (SELECT max(customer_id) FROM customers), seq_min AS (SELECT min(customer_id) FROM customers) SELECT * FROM create_series((SELECT min FROM seq_min),(SELECT max FROM seq_max)) EXCEPT SELECT customer_id FROM customers;
V důsledku toho vidíme následující výsledek: 5 , 9 a 6 .
Druhý způsob
Získejte název sekvence spojené s customer_id:
SELECT pg_get_serial_sequence("zákazníci", "id_zákazníka");
A najděte všechny chybějící identifikátory:
WITH sequence_info AS (SELECT start_value, last_value FROM "SchemaName"."SequenceName") SELECT create_series ((sequence_info.start_value), (sequence_info.last_value)) FROM sequence_info EXCEPT SELECT customer_id FROM customers;
Počítání počtu řádků v tabulce
Počet řádků se vypočítává standardní funkcí počítání, ale lze ji použít s dalšími podmínkami.
Celkový počet řádků v tabulce:
SELECT pocet(*) FROM tabulka;
Počet řádků za předpokladu, že zadané pole neobsahuje NULL:
SELECT počet(název_sloupce) FROM tabulka;
Počet jedinečných řádků pro zadané pole:
SELECT pocet(rozdilne jmeno_sloupce) FROM tabulka;
Pomocí transakcí
Transakce kombinuje sled akcí do jediné operace. Jeho zvláštností je, že v případě chyby při provádění transakce se žádný z výsledků akcí neuloží do databáze.
Začněme transakci příkazem BEGIN.
Chcete-li vrátit zpět všechny operace po BEGIN, použijte příkaz ROLLBACK.
A aplikovat - příkaz COMMIT.
Zobrazení a ukončení spustitelných dotazů
Chcete-li získat informace o požadavcích, spusťte následující příkaz:
SELECT pid, age(query_start, clock_timestamp()), usename, query FROM pg_stat_activity WHERE query != "
Chcete-li zastavit konkrétní požadavek, spusťte následující příkaz a zadejte id procesu (pid):
SELECT pg_cancel_backend(procpid);
Chcete-li ukončit požadavek, spusťte:
SELECT pg_terminate_backend(procpid);
Práce s konfigurací
Nalezení a změna umístění klastrové instance
Nastává situace, kdy je na stejném operačním systému nakonfigurováno několik instancí PostgreSQL, které „sedí“ na různých portech. Najít cestu k fyzickému umístění každé instance je v tomto případě docela nervy drásající úkol. Chcete-li získat tyto informace, spusťte následující dotaz proti jakékoli databázi shluku zájmu:
SHOW datový_adresář;
Změňte umístění na jiné pomocí příkazu:
SET data_directory to new_directory_path;
Aby se změny projevily, je však vyžadován restart.
Získání seznamu dostupných datových typů
Získejte seznam dostupných datových typů pomocí příkazu:
SELECT typname, typlen from pg_type kde typtype="b";
typname je název datového typu.
typlen - velikost datového typu.
Změna nastavení DBMS bez restartu
Nastavení PostgreSQL se nachází ve speciálních souborech jako postgresql.conf a pg_hba.conf . Po změně těchto souborů potřebujete, aby DBMS znovu získal nastavení. Za tímto účelem se databázový server restartuje. Je jasné, že to musíte udělat, ale na produkční verzi projektu, kterou používají tisíce uživatelů, je to velmi nežádoucí. PostgreSQL má proto funkci, pomocí které můžete aplikovat změny bez restartování serveru:
SELECT pg_reload_conf();
Ale bohužel to neplatí pro všechny parametry. V některých případech je k použití nastavení vyžadován restart.
Podívali jsme se na příkazy, které usnadní práci vývojářům a správcům databází používajícím PostgreSQL. Ale to nejsou všechny možné metody. Pokud narazíte na zajímavé úkoly, napište o nich do komentářů. Podělme se o naše užitečné zkušenosti!