Praktický návod k PgSQL

28. 3. 2003 | Pavel Kysilka

Články - Praktický návod k PgSQL  

Migrace z MySQL, její důvody a co obnáší. Možnosti databáze. Manuál a pár triků k textové konsoli psql a programu pg_dump.

Před pár měsíci jsem dělal jěstě na databázi MySQL. Nepočítám se za nějakého odborníka na databáze. S databází MySQL jsem byl zpočátku velmi spokojen, ale jak postupem doby programujete, začínáte vidět nějaké ty "mouchy", že by se dalo něco zlepšit, dělat jednodušeji a ne jenom editovat text. Dostal jsem jeden projekt a jako databáze byla zvolena PgSQL. A od té doby nechci příliš o databázi MySQL slyšet. PgSQL je trochu o něčem jiném.

Od tohoto článku můžete očekávat pohled programátora, který s touto databázi dělá, ale není na ni příliš velký odborník. Vzhledem k době, po kterou pracuji s touto databází, se řadím mezi začátečníky, a proto je tento článek psán především pro začátečníky. Patřím k programatorům, kteří dělají databáze řádově o 20-100 tabulkách.

Očekává se určitá znalost jazyka SQL a schopnost práce s nějakým databázovým programem. Můzete zde najít pár důvodů, proč jsem přešel z MySQL, a co to obnášelo. Konsolisté zde najdou popis nástroje psql a pg_dump . I když jsem si původně myslel, že s MySQL umím většinu věcí, tak u PgSQL jsem nabyl dojmu, že jsem absolutní začátečník a nějak se s tím snažím, za pomoci dokumentace a zkoušení všeho možného na projektech a v konsoli, vyrovnat.

Uvedu pár důvodů, proč používám PgSQL. Výhody i nevýhody postgresu. A to především nestranným pohledem. Nemám zájem nějak rozpoutat flame.

Moje důvody migrace z MySQL na PgSQL

ty pozitivní:

• Funkce - ušetří mnoho práce a můžete je používat i na dalších projetech, nemusíte pracovat s databázi pouze na aplikační úrovni. Do postgresu můžete i implementovat kód vytvořený v jiném jazyku, třeba v Perlu nebo C. Standardním jazykem SQL funkci v postgresu funkcí je jazyk Pl/SQL.
• Triggery - spustíte příkaz po nějakém příkazu a nemusíte se o spuštění dalšího starat pomocí jiné aplikace. Stačí nadefinovat trigger na danou operaci a k němu danou funkci.
• Transakce - když přijdete o data či provedete něco v databázi špatně, můžete si zadat pár chybějících dat ručně i když nejste sekretářka, ale programátor. Nemusíte se spoléhat na to, že máte zálohu, a nebo si hledat jiné zaměstnání. Hodí se to i při psaní aplikací. Pořád je to lepší než zadávat celý formulář se 40 položkami ručně při pádu jednoho dotazu. Případně pomocí transakcí můžete zkoušet nějaké destruktivní operace na databázi. Neříkám, že MySQL nemá transakce v novějších verzích, ale u Postgresu to bude trošku o něčem jiném vzhledem k době vývoje postgresu.
• Integrita dat pomocí cizích kličů.
• Složené dotazy. Například typu SELECT neco FROM neco WHERE neco IN (SELECT neco_1 FROM ...);
• Možnosti této databáse. Připouštím, že porovnávat tyto dvě databáze úplně nejde.
• Databázová konsole psql - tak dobré intuitivní uživatelské prostředí jsem mimo editoru Vim a Bashe neviděl.
• Lepší system práv, přístupů a zabezpečení.
• Cena za databási tohoto typu a cena za to, co tato databáse umí. Na Oracle či MSSQL skutečně nemám peníze. A nemám skutečně čas ani náladu čekat na postih od softwarové policie z důvodu udání nejmenovaných osob o držení jmenovaného nezaplaceného a cracknutého software. A pokud jednou naprogramujete alespoň 10000 řádků kódu, poznáte jejich cenu. Mimo soutež a bez reakcí v diskusi: kolik z Vás, kdo čte, má na PC pouze legální software?
• Stabilita.
• Řekl bych i lepší dokumentace oproti MySQL, ale za cenu toho, že se ji musíte občas naučit hledat.

ty nepříliš pozitivní:

• Dá to trošku práce se něco naučit a objevit pár věcí. Trpělivost je zde skutečně nutností.
• Musíte být pečlivější (někomu to může vadit).
• PgSQL je vůčí MySQL několikrát pomalejší při menším počtu uživatelů. Nicméně rychlost je zde vykoupena tím, co databáze PgSQL umí.
• PgSQL není na všech serverech - zde mám na mysli především webhostingy
• fulltext není přímo v PgSQL - Musíte použít nějakou nástavbu. Nabízí se openfts. Případně si, jako já, můžete napsat svůj vlastní (nic extra) fulltext.
• komplikovanější instalace na platformě Windows přes Cygwin (zde zapomeňte na nějaký user-frendly instalátor).

Poznámka: Neříkám, že MySQL se nesnaží dohánět, co týče vlastností, PgSQL či jiné databáse. Přeci jenom ale vetšina věcí bude v PgSQL na dost vyšší úrovni. Zde mám na mysli případné flame typu "subselecty, transakce či funkce".

Celkově bych chtěl říci, že volba databáze závisí na aplikaci, pro kterou danou databázi používáte, na možnostech HW, ale i třeba na možnostech a schopnostech programátorského týmu.

První kontakt pro mě s touto databází byla její konverze z databáze MySQL. Na tomto nejprve vysvětlím rozdíly mezi těmito dvou databázemi. Pokud budete potřebovat převést databázi z MySQL, je pár projektů a z nich si můžete stáhnout patřičné konverzní programy. Mě se vcelku osvědčil mysqlpgsql.pl. Informace o těchto skriptech najdete v dokumentaci o přechodu databází na PgSQL techdocs.postgresql.org.

Ne vše převede skript korektně. To potom zjisíte při následném dumpu. Něco je třeba opravit ručně. Dá se to napravit nějakým rozumnějším editorem. Mně se osvědčil Vim, ale při zpracovávání souboru velikého desítky MB a více není zrovna nejrychlejší. Pro větší možnoství dat a z důvodů se něco naučit, bych doporučil grep, sed, cut a tr. Případně můžete využít tyto editory či textové procesory na vytvoření testovacích dat s parsováním nějakého textu do souboru s dumpem. Jsem tvrdým zastáncem konsole a grafiku na svém stroji příliš nepěstuji a nevyužívám.

Není od věci zeditovat celou databazí v nějakém rozumnějším editoru. Využijete možnosti nahrazování a především se naučíte znát příkazy SQL jazyka a strukturu databáze. Naučíte se rozumně pojmenovávat sloupce či tabulky a dáte jménům objektů v databázi nějaký systém. Následně při programování nepotřebujete zjišťovat, jak se ten či onen sloupec a tabulka jmenuje. Někdy se děsím některých vizuálních programátorů, co neumí vytvořit tabulku příkazem CREATE TABLE či změnit nějaký její sloupec pomocí příkazu ALTER TABLE. Neberu však nikomu vizuální prostředí. Každý dovede vstřebat informace jinak. Závisí to skutečně na každém individuálně. A někdy je to dost námahy v konsoli obstát v prostředí klikačů. Nicméně svoji snahu časem mnohanásobně zúročíte.

Konverze a odlišnosti databází:

PgSQL dodržuje ve více případech než MySQL standard SQL92,95. A také budete muset dodržovat standard při psaní aplikací i vy. Očekávajte tedy větší počet chybových hlášek než na MySQL. Pokud bych měl k něčemu přirovnat MySQL databázi, tak k rychle vyšlechtěné okurce. Pokud chcete dostat z této databáze více, není už příliš šancí. A dalším důvodem je i její rozšíření a jednoduchost. Ale svůj účel plní. Tyto věty, ale nemyslím hanlivě.

• Měl jsem problémy s komentářemi u tabulek MySQL. Vyřešil jsem to jejich smazáním a potom není problém tabulky dodatečně okomentovat.

• Další problémy a odlišnosti jsou v sekvencích. V MySQL se to řeší pomocí autoinkrementu. V postgresu toto není, ale jsou zde sequence. Dost začátečníků toto zaskočí. Stačí ale v konsoli (musíte mít nainstalovaný postgres) napsat třeba man CREATE*SEQEUNCE, vzít nějaký vzorový příklad a vytvořit si sequenci vlastní.

  Zde je malý příklad:

  CREATE SEQUENCE "tabulka_id_seq" start 1 increment 1
maxvalue 9223372036854775807 minvalue 1 cache 1;

CREATE TABLE "tabulka" (
"id" integer DEFAULT nextval('tabulka_id_seq'::text) NOT NULL,
"id_vazba" bigint DEFAULT '0' NOT NULL,
"polozka_0" integer,
"polozka_1" integer DEFAULT '0'
);

SELECT setval ('"tabulka_id_seq"', 1, false); /*nastavení hodnoty sekvence v případě, že potřebujete mít jinou výchozí hodnotu než 1 */

• Nedoporučuji používat konstrukce typu INSERT INTO tabulka SET jmeno_sloupce=hodnota. Začal jsem tuto konstrukci používat na jednom projektu s tím, že jsem ji doporučil i používat i mým kolegům. Výsledkem bylo to, že jsem si jako hlavní programátor značně pocvičil nahrazovačky a usínal s písmenky před očima. Skutečně nedoporučuji. Lepší bude psát trošku podle standardu SQL 92 a používat konstrukci typu INSERT INTO tabulka (promenna_0,...,promenna_n) VALUES (hodnota_0,...,hodnota_n);

• Rozdíl je i ve formátech data a času. Další rozdíl je v použivání klausule LIMIT. Zápis pro PgSQL je LIMIT <kolik_vypis>, <od_kolikateho_zaznamu>. U MySQL je to opačně.

• Při grupování a agregačních dotazech musíte být pečlivějsí. Je potřeba grupovat podle všech sloupců na výpisu, které nejsou ovlivněny agregační funkcí.

  SELECT MAX(polozka_0)
polozka_1

FROM tabulka

GROUP BY polozka_0,polozka_1


  Ne jen podle jednoho. Naučíte se poté i používat klausuli DISTINCT.

• Trošku více je v postgre řešena bezpečnost a nemůžete dělat všechno. Oproti tomu můžete chránit data a své výtvory před zraky nepovolaných a kopírujících uživatelů.

• Novinkou pro Vás asi bude přejmenování tabulek a sloupců a mazání sloupců. Toto je v MySQL jednodušší. Mazání či přejmenování sloupců je řešeno pomocí vytvoření dočasné tabulky nebo view. Doporučuji se naučit konstrukci typu CREATE nova_tabulka AS SELECT sloupce FROM tabulka_odkud_chci_kopirovat;. Může pomoci i přejmenování sloupce pomocí ALTER tabulka RENAME COLUMN sloupec TO nove_jmeno. Ale toto bude spíše platit pro konsolisty.

• Ještě uvedu jednu vychytávku z Postgresu. Naplnil jsem jednou tabulku 25000 záznamy pro nějaké testování. Po smazání testovacích dat se zpomalily výrazně operace nad touto tabulkou. Zkuste se někdy podívat, co znamená příkaz VACUUM.

Instalací PgSQL se zde nechci zabývat. Toto ponechám na někoho zkušenějšího. Pokud by jste ale hledali, zkuste třeba zde dokumentaci v pdf nebo si na linuxworldu.cz vyhledejte slovo PgSQL.

Přístupy do databáze

Co vás může zmást po instalaci postgresu je, že se nemůžete dostat do databáze ani vytvořit novou databázi přes createdb, případně vytvořit uživatele pomocí createuser anebo přistoupit do psql konsole. Musíte být připojeni jako uživatel root či pokud chcete přistupovat poprvé do databáze, tak jako uživatel postgres. Stačí dát místo parametru 'password' parametr 'trust' v konfiguračním souboru /etc/postgresql/pg_hba.conf. Připojte se bez hesla a můžete tak posílat do databáze přímo data přes psql. Změňte parametr na trust. Restartněte postgres pomocí /etc/init.d/posgresql restart. Vytvořte uživatele pomocí ALTER USER jmeno_usera PASSWORD 'moje_heslo'; a nebo pomocí createuser. Změnte potom parametr 'trust' na 'password'. A opět restart postgres.

Jinak se dá ještě dost vyblbnout přes autentifikaci ident, kerberosa md5.

Tady je část konfiguráku postgree týkající se přístupů. Doporučuju přečíst tento soubor. Hodí se i pro případnou další konfiguraci: /etc/postgresql/pg_hba.conf.

local all password
host moje_database vzdalene_ip sitova_maska password

host all 127.0.0.1 sitova_maska password
host moje_database 127.0.0.1 255.0.0.0 password
host all 0.0.0.0 0.0.0.0 reject


A potom uz jen psql moje_database -U uzivatel ( -h vzdalena_masina ).

Konfigurační soubory najdete v adresaři /etc/postgresql/. Další vychytávkou může být kódování či nesprávné řazení. To může být zapříčíněno tím, že nemáte zakompilované locales a zde je nutné si postgres přeložit ze zdrojáku či znova nainstalovat. Zjistíte to tak, že pokud si dáte seřadit nějaké řádky typu řetězec, tak česká písmena budou na konci.

Vývojová prostředí

Pokud Vy nebo Váš tým děláte s touto databází, nebude od věci najít aplikace (pro vaše kolegy a především pro ty, co potřebují intuitivní prostředí pro jejich myši).

pro drsňáky:

• psql - databázová konsole. Co více si můžete přát ve spolupráci s vaším oblíbeným externím editorem a příkazem less. Standardně je s instalací PgSQL databáze.
• telnet - zatim jsem nepřisel jak na to a zda to vůbec jde (prostor v diskusi určitě je ....)

trošku více user-frendly:

• PgAccess - není to nic extra, ale stačí a je i rychlý. Tl/Tcl rozhraní.
• phpPgAdmin - Webové rozhraní. Chodí všude. Není nejkrásnější, ale účel plní. A i kolegové z nejrozšířenější platformy světa s ním mohou pracovat.

Pro windows jsem jestě viděl

• PgAdmin II. Ten jsem se pro změnu pokoušel zprovoznit pod Windows, avšak až při změně paměti ze 128 MB na 256 začal chodit přijatelnou rychlostí.

komfortnější:

• Tora - podle screenshotu vypadá dobře. Nepovedlo se mi ji připojit k databázi. Původně je tento databazový nástroj pod Oracle, ale měl by chodit na PgSQL a i na MySQL. Existuje port i na Windows.
• AQuerix - nezkoušel jsem. Mimo jiné, český produkt firmy XTG. Není free.

A nebo použít nějaký program, který používá ODBC ovladače.

SW pro návrhy databází:

Zatím jsem nepotřeboval, i když dělám databáze přes 50 tabulek. Dostačuje tužka, papír, barvičky (to už musí být něco těžšího) a dobrá dokumentace od zadavatele především. Ale u větších projektu je nějaký nástroj patrně pro někoho nutností. Toto vidím jako jeden z nedostatků Linuxu a to nějaký grafický free nástroj na tvorbu databází. Z nástrojů, co jsem viděl, by mozná mohla být vyhovující Tora.

psql - konsole

Už jsem zde napsal, ze patřím mezi "konsolisty" a jako databázové rozhraní používam psql. Tento nástroj mě vcelku dost překvapil. Trochu se o něm rozepíši. Předpokládám, že máte již nakonfigurovaný Postgres. psql spustíte pomocí příkazu psql <jmeno_database>. Případně dodejte jméno uživatele, pokud jste na konsoli jako jiný uživatel, než který má přístup k dané databázi: -U <jmeno_uzivatele>. Budete dotazáni na heslo. Po jeho správném zodpovězení či uhodnutí, se ocitnete na konsoli databáze. Pokud jste si neprečetli žádné informace typu man psql, což předem doporučuji, je třeba ozkoušet linuxové chvaty typu \? a \h. Asi jako zásadní příkaz ješte uvedu \q. Není umění něco spustit, ale umět to i vypnout. Ti, co někdy zkoušeli a už nějaký rok zkouší editor VIM, asi budou vědet svoje (:x, :q, :q!, ZZ).

Pokud hledáte nápovědu na bashové konsoli, může pomoci man + hvězdičková konvence. Zkuste si man alter*tabl*. Pokusím se nějak popsat nejpoužívanější a nejužitečnější příkazy z postgresové konsole. Řazeno abecedně podle zkratek. Jako pomůcku uvedu znak \ a za to nějaký příkaz či objekt vašeho zájmu. Do toho všeho se jetě pokusím dodat nejaké své poznatky, znalosti a užitečnosti. Určitě v tom najdete spojitost s mnoha linuxovými příkazy. Nastíním ještě jak fungují některé přepínače typu \[neco]. \x zapni přepínač. Další \x vypni přepínač. Toto je třeba například pro: vypisuj každé políčko výsledku tabulky sql dotazu do samostatného bloku.

Příkazy psql konsole

• \a toggle between unaligned and aligned output mode

  Zarovnávaný či nezarovnávaný výstup.

• \c[onnect] [DBNAME|- [USER]] connect to new database (currently "moje_database")

  Přikonektění k jiné databázi.

• \C TITLE set table title

  Nastavení názvu tabulky - je možno generovat html výstup. Četl jsem něco i o postscriptu.

• \cd [DIRNAME] change the current working directory

  Změna pracovního adresáře v shellu.

• \copy ... perform SQL COPY with data stream to the clienthost

  Kopíruj nějaký vstup, výstup odněkud a za použití oddělovačů. Příklad: třeba pošli to přes grep, sed, cut, recode, setříd to sortem, pošli zpátky do databáze a chybový výstup mi pošli mailem. Nebo načíst data z/do souboru - data jsou v tom souboru oddělena pomocí oddělovačů.

• \d{t|i|s|v}... list tables/indexes/sequences/views

  Zobraz tabulky, indexy, relace, view (super to věc) příklad: \d moje_tabulka můžeme použít i regulární výrazy v názvu při zobrazení \dt, \ds, \di moje.*abulka pomocí tabelátoru se dají při \d tabulky a sql příkazy doplňovat.

• \d{p|S|l} list access privileges, system tables, or large objects

  Výpis přístupových privilegíí, systémových tabulek (doporučuji zkusit pár selectů) a dlouhých objektů.

• \da list aggregate functions

  Seznam agregačních funkcí. Není nad dokumentaci na dosah. A především nad dokumentaci, která se rychle zobrazí.

• \dd NAME show comment for table, type, function, or operator

  Je-li někde \d[+neco] - je to nějaký výpis informací.

• \df list functions

  Seznam funkcí online. Záleží na verzi postgre. Názvy funkcí, jejich vstupní parametry a návratové hodnoty.

• \do list operators

  Seznam operátorů. Třeba ~= něco jako like , +, >= a podobně. Můžete si definovat i své vlastní operátory.

• \dT list data types

  Datové typy na dosah.

• \e FILENAME edit the current query buffer or file with external editor

  Skutečně vychytávka. K tomu oblíbený externí editor a editujete cokoli. Pokud zadáte jenom \e tak editujete dočasný soubor a v něm příkaz či poslední prováděný příkaz. Nejvíce se mi osvědčil editor Vim. Je v něm zabudovaná zvýrazněná syntax pro soubory sql. Případně se dá zapnout pomocí :syntax on anebo setsyntax=sql Problém ale je, pokud neotevíráte již existující soubor, ale dočasný soubor. Potom je třeba syntax nastavit v konfiguráku editoru. Ukončením práce s editorem spustíte příkaz. Zde skutečně opatrně s ostrými daty. Případně použít transakce. Anebo v jedné konsoli mít psql a v druhé editovat sql soubor a v 3.konsoli dělat raději zálohu. Případně použít singlestep mod - psql -s

• \encoding ENCODING set client encoding

  Nastavení kódování.

• \f STRING set field separator

  Nastavení oddělovače mezi sloupci ve výsledku. Ne každému vyhovuje znak | .

• \g FILENAME send SQL command to server (and write results to file or |pipe)

  Pokud v sql příkazu nezadáte na konci znak ";", tak potom pomocí \g můžete tento příkaz odstartovat. Případně přesměrovat jeho výstup do souboru.

• \h NAME help on syntax of SQL commands, * for all commands

  Další online nápověda týkající se SQL příkazů. \h jméno_příkazu a máte to i se syntaxí a stručným popisem daného příkazu. Přitom můžete doplňovat příkazy pomocí tabelátoru jako v Bashi. Třeba \h SELECT, \h CREATE, ...

• \H toggle HTML output mode (currently off)

  Html výstup výsledku dotazu. Tady jsem nějak nepřišel na to, jak přesměrovat výstup z dotazu do jiného programu než less. Občas by neškodilo obarvit výstup pro lepší orientaci v textu a informacích.

• \i FILENAME execute commands from file

  Proveď příkazy ze souboru.

• \l list all databases

  Seznam databází.

• \lo_export, \lo_import, \lo_list, \lo_unlink large object operations

  Operace s "dlouhými objekty". Zatím jsem neměl důvod je nějak použít a predevším čas přečíst dokumentaci. To spíše zatím nechám na ty, co do PgSQL více vidí. Měly by sloužit na ukládání binarních dat a třeba obrázků do databáze.

• \o FILENAME send all query results to file or |pipe

  Přesměrování výstupu dotazu do souboru či roury. Získáte tak úhledné textové tabulky s výsledky. Případně něco jiného. Záleží na oddělovačích.

• \p show the content of the current query buffer

  Ukáže obsah bufferu dotazu. Doslova vám vypíše dotaz (ne jeho výsledek), který máte v bufferu.

• \pset VAR set table output option (VAR := {format|border|expanded| fieldsep|null|recordsep|tuples_only|title|tableattr|pager})

  Nastavení systémových proměnných konsole. Třeba rámeček.

• \q quit psql

  Ukončení práce s konsolí.

• \r reset (clear) the query buffer

  Vyčistění bufferu dotazu. Zrušení ještě neprovedeného dotazu.

• \s FILENAME print history or save it to file

  Výpis historie příkazové řádky.

• \set NAME VALUE set internal variable

  Nastavení systémových proměnných konsole.

• \tshow only rows (currently off)

  Výpis pouze výsledku dotazu - ne se záhlavím tabulky a jmény sloupců.

• \T TEXT set HTML table tag attributes

  Nastavení html výstupu.

• \unset NAME unset (delete) internal variable

  Zrušení nastavení systémové proměnné.

• \w FILENAME write current query buffer to file

  Zápis výstupu dotazu do souboru.

• \x toggle expanded output (currently off)

  Rozšířený výstup. Super věc. Vypisuje se do bloku pod sebe do sloupce každá buňka tabulky výsledku dotazu.

• \z list table access privileges

  Výpis privilegií a přístupu k daným tabulkám a objektům.

• \! [COMMAND] execute command in shell or start interactive shell

  Spuštěni externího příkazu. Třeba něčeho na konsoli.

Možná je toho na začátek moc. Vřele doporučím zkoušet příkazy konsole a učit se je. Zapamatovat si ty nejužitečnější a druhý den ještě zkusit. Mám odzkoušeno, ze učit se je nazpamět nemá cenu. Musíte bezpečne vědet, co se vám k čemu bude hodit. Data sice už umíme podle článku nějak zpracovat v konsoli, ale jak je dostat ještě ven z databaze nebo do ní. Ať už se jedná o data či o struktury tabulek. K tomu slouží příkazy psql, pg_dump. A nebo pg_dump_all pro dump z více databází či všech databází.

pg_dump

Uvedu jenom pár příkladů a užitečných věcí.

dump z databáze

$ pg_dump <moje_database> > <soubor_do_ktereho_to_presmeruji_ze_standartniho_vystupu>

dump do databáze

$ psql -d <moje_database> -f <soubor_s_daty>

• -a -- pouze data
• -c - vydumpovat databázi a i příkazy na smazání existující a vytvoření daných struktur
• -d - několik hodnot záznamu za sebou - krátké inserty INSERT INTO tabulka VALUES (hodnota_1,..,hodnota_n);
• -D dlouhé inserty - INSERT INTO tabulka (jmeno_sloupce_1,..,jmeno_sloupce_n) VALUES (hodnota_1,..,hodnota_n);

Vhodné třeba pro následnou editaci a opravu (pokud chcete mít puštěnou syntax svého oblíbeného editoru), ale zase to dlouho trvá při dumpu. Co řádek, to dotaz INSERT INSERT INTO tabulka (jmena_sloupcu) VALUES (hodnoty);. Před velkým dumpem doporučuji odrušit sequence a indexy

• -f file - soubor, odkud chceme něco nadumpovat
• -Z 0..9 --compress=0..9 komprese - level -- vhodné třeba při práci na pomalé lince. Případně použít tunel v kombinaci s ssh.
• -h host host - vzdálený stroj, databáze na dálkové ovládání (platí i pro psql)
• -p port -- ne vždy vám poběží PgSQL na portu 5432
• -O - pokud dumpujete data z dané databáze a nejste daným vlastníkem, tak použijte tento přepínač. Postgre si kontroluje dané vlastnictví dat a struktur.
• -t <+jmeno_konkretni_tabulky> - dumpuj pouze tuto tabulku či objekt
• -U - identifikuj se jako jiný uživatel

Pokud hledáte nějakou dokumentaci k PgSQL, tak tu je možné najít na techdocs.postgreesql.org a archives.postgresql.org . Potom projít třeba mailinglisty (zde se dá najít mnoho praktických rad). Případně si stáhnout celou online dokumentaci v html. Také můžete zkusit příkaz man nebo man -k něco. Nějaká dokumentace je také v adresáři /usr/doc/postgree*. Pokud si myslíte, že něco nezvládate ani po neúspěšném hledání, zkuste nějakou tu konferenci. Třeba databases@linux.cz . Pokud hledáte nějaké články o PgSQL zkuste to na rootu - stačí dát vyhledat slovo PgSQL, případně to samé na linuxworldu.

Náměty k dalším článkům a do diskuse:

• parsování dat do databáze a z databáze
• obarvení psql konsole
• pokud by někdo chtěl napsat članek na nějakém linuxovém serveru, o triggerech, funkcích, dlouhých objektech a agregátech pro ty, co používají čí znají pouze "select ainsert" je to určitě vítáno.

uzivatel@linuxu:/psql PgSQL Password: Welcome to psql, the PostgreSQL interactive terminal. databaze_ctenari# SELECT MAX(uspech), MAX(znalosti), MAX(hodne_pile) FROM PgSQL GROUP BY hodne_pile ASC, znalosti ASC, uspech ASC; databaze_ctenari# INSERT INTO autor (jmeno, prezdivka, prijmeni) VALUES ('Pavel','\'Goldenfish\'','Kysilka'); databaze_ctenari# \q

 

Související články

<flame>
Tvorba databází v MySQL - II (Proč právě MySQL?)
</flame>

Odkazy a zdroje

PgSQL
techdocs.postgreesql.org

Diskuse k tomuto článku

[ovo]

SELECT MAX(polozka_0) polozka_1 FROM tabulka GROUP BY polozka_0,polozka_1 urcite se to v pgsql pise takhle? I kdyz opomenu carku mezi sloupci v sekci SELECT, tak groupovat pres sloupec, z ktereho neco pocitam je docela divne, ne?

[pavel 'goldenfish' kysilka]

diky za opravu

[Pavel Stehule]

Je tu samozrejme emacs :->, resp. sql-postgres mod. M-x sql-postgres. Jako CASE s postgresem dobre spolupracuje cesky CASE Studio 2, http://www.casestudio.com v cene pod 6000Kc. Krome psql muzete jeste pouzit pgbash, ktery neni az tak pohodlny, ale zase se pohybujete v prostredi standardniho bashe( http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html)

[Jiri Chaloupka]

na starsich verzich uvedena syntaxe funguje, nikoliv jiz na 7.3.x. Lepe psat rovnou LIMIT OFFSET ... Co se proceduralniho jazyka tyce, v PgSQL se pouziva plpgsql, ktery se od klasickeho Oracliho Pl/SQL trochu odlisuje, res. neumi vse co je mozne v Pl/SQL. Jinak obrovskou vyhodou je ze databaze *funguje* velmi podobne jako velke databaze, takze pripadny prechod z PgSQL na Oracle ci DB2 je podstatne prirozenejsi ...

[Zdik Kudrle]

No, s tim prechodem na DB2 bych si dovolil trochu polemizovat. Prave takovou vec ted totiz resim a reknu vam, nic prijemneho. I kdyz jsem priznivce spise MySQL nez PgSQL (na PG se mi nelibi cela filozofie veci - napr. ze se clovek musi prihlasovat k databazi), tak zaplatpanbu za tyhle dva softy. Protoze, pratele, vsechny velke komercni DB (a jako ze jsem jiz na par delal) vetsinou nezvladaji spousty veci, co tyhle. (tim nemyslim supr-upr transakcni zpracovani, v tom samozrejme vedou, myslim spise spousty vychytavek, ktere zprijemnuji programatorovi zivot)

[Jirka Jurek]

Tora umi pracovat s PgSQL pomoci libqsqlpsql.so pluginu z qt knihoven.

[donald]

No okrem toho, ze umi select-ovat, insert-ovat, update-ovat z tabulek a zobrazovat jednotlive objekty v databaze, tora nic jineho s PostgreSQL neumi. Tora je spis orientovana na Oracle.. a jen tak povrxne na PostgreSQL a MySQL

[Karel Zak]

Pekne, jen male postouchnuti. Ta DB se jmenuje PostgreSQL (!= PgSQL / postgres / Postgres) a stalo by za to ujednotit si mam-li na zacatku psat male ci velke pismeno. Dejte si do .vimrc: "iab PG PostgreSQL" a budete mi vystarano a muzete pouzivat jen "PG"

[theo]

Ac to neni primo o trigerech, vysel pred cca pul rokem na root.cz serial o ucetnictvi na linuxu. Ten serial neni az tak o ucetnictvi, jako o PostgreSQL.

[Jan Kubik]

"Funkce ušetří mnoho práce ... nemusite pracovat s databázi pouze na aplikační úrovni." - NAOPAK -pracovat pouze na aplikacni urovni usetri spoustu prace "Triggery.." - a modularizace reseni je fuc "Transakce..." - i u transakci je nutno zalohovat, jinak je nutno hledat nove zamestnani "Integrita dat pomocí cizích kličů" - je z duvodu modularizace reseni lepsi ralizovana aplikaci "Složené dotazy" - pro modularizaci reseni velmi slaba pomucka "Možnosti této databáse." - jsou dalekosahle ... "Databázová konsole psql " - pocet ruznych nazoru na kvalitu cehokoliv se blizi vzdy 6 miliardam "Lepší system práv, přístupů a zabezpečení." - pristupy a prava se resi zasadne v aplikaci "Cena za databási ..." - ???? "Stabilita." ... viz obsahla dokumentace k MySQL - pojednani o stabilite "Řekl bych i lepší dokumentace oproti MySQL..." - NAOPAK - ta nejvetsi vyhoda MySQL je bohata dokumentace - viz plne regaly v knihkupectvi To samozrejme neznamena, ze Postgresql je spatne software, nebo ze MySQL je super.

[pavel 'goldenfish' kysilka]

trochu jsem nepochopil o co vam, jde.mozna by neskodilo dopsat vetu, ze se mnou v par vecech nesouhlasite. ale dobre budu vam oponovat.
1)osobne zastavam nazor, ze resit veci na aplikacni urovni neni vzdy jaksi to prave orechove. ale zalezi to skutecne na programatorovi. me osobne vyhovuje vice reseni, kdy to, co jde delam pres v databasi.samozrejme ne za kazdou cenu.
pokud mate neco portovat na jiny programovaci jazyk, tak muzete prepisovat celou aplikacni vrstvu.
2)prava a pristupy- v clanku mam na mysli prava a pristupy k databasovym strukturam a objektum. a vcelku by me zajimalo, zda je mozne se na mysql nejak kryptovane pripojit? ted nemyslim pres ssh tunel.
3) stabilita - zatim jsem neslysel o tom, ze by nekomu sletela database na postgressu. o mysql ano.
i kdyz kazdy ma nejake zkusenosti s nejakou databasi a nerikam, ze postgress sletet nemuze.
4) dokumentace - mam na mysli dokumentaci na internetu, co se tyce postgreee a dokumentace typu man ( ktera neni mezi uzivateli prilis v oblibe).beru to, ze v mysql dela dost lidi diky jeji jednoduchosti, ale daji se na mysql delat potom umerne slozite ci jednoduche veci. ne vzdy sebou nosim knihu. a pokud u sebe nemam knihu, staci napsat na konsoli man neco a je to. a urcite je to rychlejsi nez zacit listovat v knizce.
5) cena za databasi. vemte si MSSQL co umi a co umi PgSQL.pomer cena ku moznosti je podle me vcelku dost dobry.a pokud si dam na projekt nejakou databasi, budu premyslet o tom, jak muzu casem vyuzit jeji moznosti vzhledem k projektu. a u mysql mi to nevychazi dobre. zde v par prikladech muzu pockat, az vyvojari MySQL doprogramuji par vlastnosti a bude to poradne otestovano.

[Jan Kubik]

cilem meho prispevku bylo poukazat na to, ze podle bodu, ktere jste uvedl neni duvod menit databazi. Samozrejme pouze v tech pripadech, kdy je reseni provedeno na aplikacni urovni. Existuje rada priznivcu Vami preferovaneho pristupu, argumenty pro a proti jsou na internetu k vyhledani a nechci je zde opakovat. Navrhuji spolecne pripraveny clanek, ktery pojednava o teto problematice. Ukaze-li se , ze aplikacni pristup reseni je vyhodnejsi, pak neplati argumenty ve vasem clanku, ktere se poji k teto problematice a zmena databaze neni nutna. Pristupova prava.) Mam na mysli rizeni pristupu k nejake tabulce, sloupci. tedy napr. uzivatel XYZ nesmi mazat v tabulce ZAKAZNIK apod. Jestlize mluvime o tom samem, tak pak opakuji, jestlize se pro takove veci vyuziva funktionality database, tak se jedna jednoznacne o chybny navrh. A proto je nesmyslne prechazet kvuli necemu, co neni potreba z jedne databaze na druhou. Stabilita.) Zde jsme partne stejneho mineni. Mohu doplnit, ze jsem mel tu cest pracovat v projektech snad se vsemi beznymi databazemi a zatim padaly vsechny. Jiste tedy zadny argument, ke zmene databaze. Dokumentace) Musim priznat, ze o tomto bode je mozno u piva prima diskutovat. A vsadim se, ze vam nekdo rozbije pullitr o hlavu, kdyz reknete neco spatneho o phpAdmin pro MySQL. Tento bod radeji vynechame. Ze by nekdo musel pryc od MySQL, kvuli spatne dokumentaci si nedovedu predstavit. Cena) Puvodne jsem chtel k te cene napsat vetu meho znameho - je nakupci a casto klade tu znamou filozofickou otazku "co stoji auto?". Chtel jsem tim naznacit, ze takove otazky jsou velmi komplikovane, jestlize nezname v konkretnim pripade blizsi okolnosti. Abych se ale nevyhybal odpovedi: MSSQL nas nezajima, neb bezi na jedine platforme a to jeste na takove ne prave orechove . Zbyva jako priklad komercni databaze Oracle ve srovnani s Postgresql. U velke zakazky za 150 tEU je podil Oracle ca. 15 tEU - 10%. Vedeni rozhodne pro Oracle. Nemusite tedy prejit z MySQL na Postgresql, protoze velkou zakazku stejne nedostanete. U male zakazky za 15 tEU nemuzete samozrejme prijit s Oracle, zde je mozno nabidnout Postgresql nebo MySQL. Protoze se jedna o jednodussi aplikaci , vystacite s MySQL - neni li dokonce lepsi. Opet neni treba menit databazi. Tot vse a pekny zbytek weekendu

[theo]

ad pristupova prava: prectete si sql standard, neco o zabezpeceni dat a (znovu?) neco navrhu databazi.

[Jan Kubik]

kdo si ma precist ten standard - kubik nebo kysilka? A proc?

[theo]

jednoznacne kubik. proc? no kdyz nekdo chce neco kazat, pak by bylo dobre, aby o tematu taky neco vedel. nejsem databazovy expert a dokonce ani zadny veliky vyvojar, ale tolik toho o databazich vim, ze si dovoluji mit nazor, ze kazete bludy. je mi lito. nechci napadat vasi osobu, ale to co tvrdite (tj. vase myslenky) je prinejmensim uhozene. ps: nerad flamuju, tim min o tak stupidnim tematu jako je PostgreSQL verzus MySQL.

[Jan Kubik]

halo brnaku .. vubec - i kdyz to tak nevypada, mi nejde o flame. Poukazuji na diskuzi z 11.10.2002 na cz.comp.database.misc - tak jsem tuto problematiku nadhodil - s vysledkem, ze zkuseni kolegove potvrdili, ze pristupova prava se resi v aplikacni vrstve. Samozrejme jsou proto jeste jine duvody, nez ze par lidi v nejake konferenci reklo... Ale jak jsem napsal jiz kolegovi Kysilkovi, je na case sepsat kriticky clanek k SQL. Abychom zde netlachali zadarmo.

[theo]

takze to neni z vasi hlavy? tak to jsem klidny. zeptam se jinak: jaky vyzam maji pristupova prava v aplikacni urovni? imho a afaik jsou v databazich pristupova prava k tomu, aby ridila pristup uzivatelu k databazi tj. kdo kam muze zapisovat, kdo muze co cist etc... jestlize jsou pristupova prava resena nejakym proprietalnim zpusobem na aplikacni urovni, je veskera bezpecnost dat v hajicku dubovem, protoze kterekoliv hovado ovladajici sql a rekneme komandlajnu (kdyz bude stejne jako ja dostatecne zdechly stahovat si nejake graficke udeladla :) muze delat v databazi curbes. mimoto -- i ta stup... pardon mysql ma tenhle system (byt imho implementovany nemozne, ale ma). rizeni pristupovych prav na aplikacni urovni si muzete dovolit u proprietalnich databazi, ale ani tam to neni idealni. klasicke sql servery (tim minim Oracle, Ingres, PostgreSQL, dokonce i MySQL a mnohe dalsi) maji zakladni zabezpeceni dat resene na urovni databaze jako acl. jinak s argumentem, ze bezpecnost na aplikacni urovni je database-independent sice souhlasim, ale ruku na srdce -- kdyz zacnu vyvijet nejaky system postaveney na databazi, pak obvykle nejdrive zvolim databazi a nasledne zacnu neco delat. opacny postup je naprosto sileny, ne-li primo idiotsky. v momente kdy mam databazi, ktera uz sama resi bezpecnost nativne (ale musi ji resit kvalitne), je jakekoliv psani dalsi bezpecnostni vrstvy jednak vynalezanim kola a druhak zpomalovanim celeho vysledneho systemu (dalsi kontroly prav navic, ktere se navic budou zrejme kontrolovat proti nejake databazi :). takze asi tak btw: ja v brne jenom studuju, ale on ten hantec je takovy paradne nakazlivy :) no vypada to, ze zacinam psat pojednani o databazich na pokracovani

[pavel 'goldenfish' kysilka]

zdravim,
urcite se pridam jeste do flame asi zitra nad ranem. ted jsem grogy.zase se to programoavani zvrtlo.
jinak ale musim uznat, ze tato diskuse bude patrne stat za to.ted mysleno v dobrem.
zatim goldenfish

[pavel 'goldenfish' kysilka]

dobre ranko,
pokusim se nejak kratce odpovedet.
1) zmena database - zalezi to na svobodny volbe kazdyho. nekdy je mysql vyhovujici a jsou mista, kam bych pgsql nedal, ani kdyby mi platili. ja nechci sirit pgsql jako nabozenstvi a menit za kazdou cenu vsechny database na mysql. to fakt ne.
2) prava v databasi+reseni pres aplikacni vrstvu. a podobne.
celkove co jde resim v databasi.ne na ukor rychlosti a znacne slozitosti.jednoduche pocty. pokud pracujete pouze s databasi je to rychlejsi. pokud do toho jeste pridate aplikaci mate jednu vrstu navic. a taky se zvysuje moznost chyb.
dalsi duvod pro me je zmena progeramovaciho jazyka aplikace a moznost prenosu na jine projekty.muzete pouzivate to co vytvorili kolegove a nemusite kvuli tomu prepisovat do jineho jazyka celou aplikacni vrstvu.to je podle me hlavni myslenka.database vam zustava stejna, ale muzete menit programovaci jazyk.
3)clanek - mozna v kvetnu budu mit cas.bez zaruky. ted neco pisu a vcelku by to mohla byt bomba. co, to neprozradim. ale nejsem proti tomu kdyz neco vyjde. pripadne se mi pripomente v kvetnu na goldenfish256@centrum.cz .diky.
4) celkove zaverem - pgsql me vcelku dost chytlo. hlavne pro to co umi a pro moznosti. nejhorsi je na jakemkoli jazyku ci treba databasi, kdyz zjistite, ze uz nejsou nove veci a nemuzete dal. pak uz prejit na neco jineho. jako ja snad pristi mesic na neco objektoveho.a prave kdyz jsem cetl manualy k pgsql, tak jsem se nestacil divit a bude mi to trvt pekne dlouho nez do hlavy dostanu alespon zlomek toho, co pgsql umi.
jinak dobra flame, ale je na case ji utnout a rozpoutat dalsi treba az pri nejakem dalsim clanku.zatim goldenfish

[Abraxis]

"Triggery - a modularizace reseni je fuc" - jak jinak chces delat audity a podobne veci? Bez toho se to delat DOST blbe... "Transakce - i u transakci je nutno zalohovat, jinak je nutno hledat nove zamestnani" - transakce NEJSOU v ZADNEM pripade kvuli zalohovani. To by mel vedet kazdy, kdo ma za sebou aspon jeden semestr z databazi "Integrita dat pomocí cizích kličů - je z duvodu modularizace reseni lepsi ralizovana aplikaci" - a to je presne ten duvod, proc tady cizi klice jsou, protoze v aplikaci (cim vetsi tim casteji) se velmi casto zapomenes a prusvih je na svete (a kdo to ma ladit )) "Složené dotazy - pro modularizaci reseni velmi slaba pomucka" - to, ze MySQL neumi vnorene dotazy, to mu doposud nejsem schopen zapomenout

[PaJaSoft]

Tak to chci videt, jak se da udrzet datova/referencni integrita na aplikacni urovni, kdyz aplikace nema zadnou moznost zajistit, ze s databazi bude pracovat jen ona a ze nikdo jiny nezmrvi data... Kdyz pomoci DML databazi sdelite, co a jak ma byt, tak proste nemate sanci ty data zmrsit, pokud si je nezmrsi datastor sam a pak je treba jej vyhodit...

A jeste jedna vec, kdyz uz budu predpokladat, ze k databazi pristupuje jen ma aplikace, tak zrejme jste toho napsal opravdu velmi malo, protoze velmi casto se stane, ze operaci nelze zapsat atomicky - ano narazim spise na transakce - a kdyz v teto neatomicke operaci z duvodu, ktere naprosto nemuzete ovlivnit ta operace neni korektne dokoncena, tak mate po integrite a jiz nemate sanci zjistit, co jste pomoci sve kvalitni aplikace podelal...

[Jan Kubik]

halo pane inzenyre, uz vam asi pres rok dluzim odpoved, proc je SQL srot. No snad se k tomu dostaneme s "goldenfishem" v tom spolecnem clanku. Uvitam samozrejme konstruktivni kritiku a jiz se tesim na mnohe argumenty. Jinak co se tyce toho samostatneho pristupu aplikace k databazi, tak je to skutecne tak, ze veskere objekty databaze patri jedinemu uzivateli a toho zna pouze aplikace. Zadni jini uzivatele vubec neexistuji a proto take nemohou nic zkazit. Transakce jako takove nekritizuji a vsude tam kde je to potreba se pouziji. Zaverem jeste k te aplikacni vrstve. Dnes jeste natrefime obcas aplikace, kde se nalezaji stovky ci tisice SQL-statements, ktere programatori skutecne napsali od ruky. A kdyz se neco zmeni, tak jdou do toho kodu a meni ty SQL statements a trigerry atd. Takovou aplikaci samozrejme nemam na mysli a nedovedu si ani predstavit, ze by se dnes na univerzitach jeste tohle prednaselo. - no, tak jisty si vlastne nejsem, kdyz tak ctu ty blahovosti napr. pana Hofmana, ze se firma rozhodne pro nejakou databazi a eventuelne jeste pouzije ty proprieterni skopiciny te ktere databaze.

[Aristo]

hmm, som podobný prišelec z MySQL a práve riešim problém s autoicrementom (len viac úrovňovým). Ten príklad vyššie je fajn avšak ako spraviť: id, id_vazba tak aby po zadaní akejkoľvek hodnoty do ID, bola id_vazba incrementované o 1 len v rámci tejto hodnoty (groupy ID)?????

Príklad: Tabulka s poliami ID a ID_VAZBA. ID_VAZBA je AUTOINCREMENT. na obe polia je UNIQE INDEX.

po pridaní položiek s ID - 1, 1, 1, 2, 1, 2, 1, 3 je:

- výsledok v MySQL 1;1 1;2 1;3 2;1 1;4 2;2 1;5 3;1

- no v PgSQL 1;1 1;2 1;3 2;4 1;5 2;6 1;7 3;8

Ako sa dá tento problém vyriešiť v PqSQL?????

[michal00]

žeby before trigger ktorý volá inú sequence podľa typu/group ?

[Peša]

Můžu se zptat jestli se někdy někomu podařilo rozběhat PgSQL na windows Vista??? A jestli jo tak jestli by mi mohl prozradit jak. :)) Diky

[Ivan]

Jednou se mi to podařilo, ale po odinstalování už mi to znova nainstolovat nejde. Možná že to požaduje neaktualizovaný windows.