Ako nainštalovať a používať PostgreSQL na Ubuntu

Úvod

Systémy správy databáz sú základnou súčasťou mnohých webových stránok a aplikácií, poskytujúc štruktúrovaný spôsob ukladania, organizovania a prístupu k informáciám. PostgreSQL, bežne známy ako Postgres, je vedúcim open-source relačným systémom správy databáz. Poskytuje kompletnú implementáciu jazyka SQL pre dotazy a je dobre známy svojou zhodou so štandardmi a pokročilými funkciami, ako sú spoľahlivé transakcie a súbežnosť bez zamykania čítania.

Tento sprievodca demonštruje kompletný proces nastavenia PostgreSQL na serveri Ubuntu, začínajúc inštaláciou potrebných softvérových balíkov. Následne sa naučíte, ako pracovať s autentifikačným systémom Postgres, ktorý sa spravuje prostredníctvom „rolí“, vrátane vytvárania nových používateľov a databáz. Potom pokryjeme základy správy databáz: pripojenie k novej databáze, definovanie dátovej štruktúry vytváraním tabuliek a manipulácia s dátami pomocou základných SQL príkazov. Taktiež preskúmame kľúčové administratívne témy, vrátane základného nastavenia pre výkon, stratégie zálohovania a obnovy, porovnanie PostgreSQL s inými databázovými systémami a príručku riešenia bežných problémov.

Predpoklady

Aby ste mohli sledovať tento tutoriál, budete potrebovať jeden server Ubuntu, ktorý bol nakonfigurovaný podľa nášho sprievodcu Počiatočné nastavenie servera pre Ubuntu. Po dokončení tohto predbežného tutoriálu by mal mať váš server ne-root používateľa s oprávneniami sudo a základný firewall.

Krok 1 — Inštalácia PostgreSQL

Ak chcete nainštalovať PostgreSQL, najprv obnovte lokálny index balíkov vášho servera:

sudo apt update

Potom nainštalujte balík postgres spolu s balíkom -contrib, ktorý pridáva niektoré dodatočné nástroje a funkcie:

sudo apt install postgresql postgresql-contrib

Verziu môžete skontrolovať spustením nasledujúceho príkazu:

psql --version

Uistite sa, že služba je spustená:

sudo systemctl start postgresql.service

Krok 2 — Používanie rolí a databáz v PostgreSQL

PostgreSQL štandardne používa koncept nazývaný „role“ na správu autentifikácie a autorizácie. Tieto sú v niektorých ohľadoch podobné bežným používateľom a skupinám v štýle Unixu.

Po inštalácii je Postgres nastavený na používanie peer autentifikácie, čo znamená, že spája Postgres roly s zodpovedajúcim účtom v systéme Unix/Linux. Ak rola existuje v Postgres, používateľské meno na Unix/Linux so zhodným názvom sa môže prihlásiť ako táto rola.

Inštalačný postup vytvoril používateľský účet s názvom postgres, ktorý je spojený s predvolenou rolou Postgres. Existuje niekoľko spôsobov, ako tento účet využiť na prístup k Postgres. Jedným zo spôsobov je prepnúť sa na účet postgres na vašom serveri spustením nasledujúceho príkazu:

sudo -i -u postgres

Potom môžete získať prístup k výzve Postgres spustením:

psql

Týmto sa prihlásite do PostgreSQL príkazového riadku a odtiaľ môžete okamžite začať komunikovať so systémom správy databáz.

Ak chcete ukončiť príkazový riadok PostgreSQL, spustite nasledujúce:

\q

Tým sa vrátite späť na príkazový riadok Linuxu postgres. Ak sa chcete vrátiť k svojmu bežnému používateľovi systému, spustite príkaz exit:

exit

Ďalší spôsob, ako sa pripojiť k príkazovému riadku Postgres, je spustiť príkaz psql priamo ako účet postgres pomocou sudo:

sudo -u postgres psql

Týmto sa prihlásite priamo do Postgres bez medzičlánku v podobe bash shellu.

Opäť, interaktívnu reláciu Postgres môžete ukončiť spustením nasledujúceho príkazu:

\q

Krok 3 — Vytvorenie novej roly

Ak ste prihlásený ako účet postgres, môžete vytvoriť novú rolu spustením nasledujúceho príkazu:

createuser --interactive

Ak však radšej chcete používať sudo pre každý príkaz bez prepínania zo svojho bežného účtu, spustite:

sudo -u postgres createuser --interactive

Tak či onak, skript vám ponúkne niekoľko možností a na základe vašich odpovedí vykoná správne príkazy Postgres na vytvorenie používateľa podľa vašich špecifikácií.

Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Krok 4 — Vytvorenie novej databázy

Ďalším predpokladom, ktorý systém overovania Postgres predvolene robí, je, že pre akúkoľvek rolu používanú na prihlásenie bude táto rola mať databázu s rovnakým názvom, ku ktorej má prístup.

To znamená, že ak používateľ, ktorého ste vytvorili v predchádzajúcej časti, sa volá sammy, táto rola sa pokúsi pripojiť k databáze, ktorá sa podľa predvoleného nastavenia volá tiež „sammy“. Príslušnú databázu môžete vytvoriť pomocou príkazu createdb.

Ak ste prihlásený ako účet postgres, zadali by ste niečo podobné:

createdb sammy

Ak však uprednostňujete používanie sudo pri každom príkaze bez prechodu z vášho bežného účtu, použijete:

sudo -u postgres createdb sammy

Krok 5 — SQL metóda na vytvorenie nového používateľa a databázy (voliteľné)

Príkazy shellu createuser a createdb sú užitoční pomocníci. Pre väčšiu kontrolu však môžete rovnaké akcie vykonať priamo v PostgreSQL pomocou SQL príkazov. Tento prístup je často jasnejší pri nastavovaní hesiel alebo prideľovaní konkrétnych oprávnení od začiatku.

1. Najprv sa pripojte ako administrátorský používateľ postgres:

   sudo -u postgres psql

   Keď ste na výzve PostgreSQL, môžete použiť CREATE ROLE a CREATE DATABASE na vytvorenie nového používateľa a databázy.

2. Vytvoriť novú rolu (používateľa): Kým príkaz shell createuser je interaktívny, príkaz CREATE ROLE vám umožňuje definovať všetko v jednom vyhlásení. Ak chcete vytvoriť používateľa menom sammy, ktorý sa môže prihlásiť (LOGIN) a má heslo, spustite:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password';

   Ak bude tento používateľ potrebovať aj vytvárať databázy, môžete mu toto oprávnenie udeliť súčasne:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password' CREATEDB;

   Toto poskytuje podmnožinu oprávnení superužívateľa, konkrétne umožňuje používateľovi vytvárať nové databázy. Odpoveďou 'áno' na otázku o superužívateľovi v interaktívnom sprievodcovi sa tiež udeľuje toto oprávnenie, samotná rola SUPERUSER je však oveľa širšia a nebezpečnejšia.

3. Vytvoriť novú databázu: Ďalej vytvorte databázu. Je dobrou praxou priradiť vlastníctvo novej databázy novej roli, ktorú ste práve vytvorili.

   CREATE DATABASE sammydb OWNER sammy;

   Ak chcete používateľovi sammy udeliť oprávnenie pripojiť sa k novej databáze (čo je bežný ďalší krok), môžete spustiť:

   GRANT ALL PRIVILEGES ON DATABASE sammydb TO sammy;

   Toto udeľuje oprávnenia na úrovni databázy, ako je CONNECT, ale nie oprávnenia k konkrétnym tabuľkám. Upozorňujeme, že toto automaticky nepridelené oprávnenia na prezeranie alebo úpravu tabuliek v databáze; na to sú potrebné ďalšie príkazy GRANT na schéme (napr. public) a samotných tabuľkách.

4. Ukončite reláciu postgres: Teraz môžete opustiť výzvu psql pre používateľa postgres.

   \q

Keďže ste nastavili heslo, tento nový používateľ sa môže autentifikovať pomocou iných metód ako ident, čo je potrebné pre vzdialené pripojenia.

Krok 6 — Otvorenie Postgres konzoly s novou rolou

Na prihlásenie pomocou autentifikácie založenej na ident budete potrebovať používateľa Linuxu s rovnakým menom ako vaša rola a databáza Postgres.

Ak nemáte k dispozícii zodpovedajúceho používateľa Linuxu, môžete si ho vytvoriť pomocou príkazu adduser. Budete to musieť urobiť zo svojho ne-root účtu s oprávneniami sudo (teda neprihlásený ako používateľ postgres):

sudo adduser sammy

Keď bude tento nový účet k dispozícii, môžete sa buď prepnúť a pripojiť k databáze spustením nasledujúceho:

sudo -i -u sammy

psql

Alebo to môžete urobiť priamo v riadku:

sudo -u sammy psql

Tento príkaz vás automaticky prihlási, za predpokladu, že všetky komponenty boli správne nakonfigurované.

Ak chcete, aby sa váš používateľ pripojil k inej databáze, môžete to urobiť tak, že určíte databázu nasledovne:

psql -d postgres

Po prihlásení si môžete skontrolovať svoje aktuálne informácie o pripojení spustením:

\conninfo

You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Toto je užitočné, ak sa pripájate k databázam, ktoré nie sú predvolené, alebo používate nepoužívané používateľské účty.

Krok 7 — Vytváranie a odstraňovanie tabuliek

Teraz, keď už viete, ako sa pripojiť k databázovému systému PostgreSQL, môžete sa naučiť niektoré základné úlohy správy Postgresu.

Základná syntax na vytváranie tabuliek je nasledovná:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

Ako vidíte, tieto príkazy pridelia tabuľke názov, a potom definujú stĺpce, ako aj typ stĺpca a maximálnu dĺžku dát poľa. Voliteľne môžete tiež pridať obmedzenia tabuľky pre každý stĺpec.

Ak sa chcete dozvedieť viac o vytváraní a mazaní tabuliek, pozrite si náš článok o tom, ako vytvárať a spravovať tabuľky v Postgrese.

Na ukážkové účely vytvorte nasledujúcu tabuľku:

CREATE TABLE playground (

    equip_id serial PRIMARY KEY,

    type varchar (50) NOT NULL,

    color varchar (25) NOT NULL,

    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),

    install_date date

);

Tento príkaz vytvorí tabuľku, ktorá inventarizuje ihriskové vybavenie. Prvý stĺpec tabuľky bude obsahovať ID čísla vybavenia typu serial, čo je automaticky zvyšujúce sa celé číslo. Tento stĺpec má tiež obmedzenie PRIMARY KEY, čo znamená, že hodnoty v ňom musia byť jedinečné a nesmú byť null.

Nasledujúce dva riadky vytvárajú stĺpce pre vybavenie typ a farba, z ktorých ani jeden nemôže byť prázdny. Riadok po nich vytvára stĺpec miesto ako aj obmedzenie, ktoré vyžaduje, aby hodnota bola jednou z ôsmich možných hodnôt. Posledný riadok vytvára stĺpec dátum, ktorý zaznamenáva dátum, kedy ste vybavenie nainštalovali.

Pre dva stĺpce (equip_id a install_date) príkaz nešpecifikuje dĺžku poľa. Dôvodom je, že niektoré dátové typy nevyžadujú nastavenú dĺžku, pretože dĺžka alebo formát je predpokladaná.

Novú tabuľku si môžete pozrieť zadaním:

\d

                  List of relations
 Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Vaša tabuľka playground je tu, ale existuje aj niečo, čo sa nazýva playground_equip_id_seq, čo je typu sequence. Toto je reprezentácia typu serial, ktorý ste priradili svojmu stĺpcu equip_id. Tento sleduje ďalšie číslo v sekvencii a je automaticky vytvorený pre stĺpce tohto typu.

Ak chcete vidieť len tabuľku bez sekvencie, môžete zadať:

\dt

          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

S pripravenou tabuľkou ju využime na precvičovanie správy údajov.

Krok 8 — Pridávanie, dotazovanie a mazanie údajov v tabuľke

Teraz, keď máte tabuľku, môžete do nej vložiť nejaké údaje. Ako príklad pridajte slide a swing tak, že zavoláte tabuľku, do ktorej chcete dáta pridať, pomeníte stĺpce a potom poskytnete údaje pre každý stĺpec, napríklad takto:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');

INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Pri zadávaní údajov by ste mali byť opatrní, aby ste sa vyhli niekoľkým bežným problémom. Napríklad, názvy stĺpcov neuzatvárajte do úvodzoviek, ale hodnoty stĺpcov, ktoré zadávate, úvodzovky potrebujú.

Ďalšia vec, na ktorú si treba dávať pozor, je, že do stĺpca equip_id nezadávate žiadnu hodnotu. Je to preto, že sa táto hodnota generuje automaticky zakaždým, keď pridáte nový riadok do tabuľky.

Na získanie pridaných informácií zadajte:

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

Tu môžete vidieť, že vaše equip_id bolo úspešne vyplnené a všetky ostatné údaje sú správne usporiadané.

Ak sa šmykľavka na ihrisku pokazí a musíte ju odstrániť, môžete tiež odstrániť riadok z tabuľky tak, že zadáte:

DELETE FROM playground WHERE type = 'slide';

Znova dotazujte tabuľku:

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Všimnite si, že riadok slide už nie je súčasťou tabuľky.

Krok 9 — Pridávanie a odstraňovanie stĺpcov z tabuľky

Po vytvorení tabuľky ju môžete upravovať pridávaním alebo odstraňovaním stĺpcov. Pridajte stĺpec na zobrazenie poslednej údržbovej návštevy pre každý kus zariadenia takto:

ALTER TABLE playground ADD last_maint date;

Ak si znovu pozriete informácie o svojej tabuľke, uvidíte, že nový stĺpec bol pridaný, ale žiadne údaje neboli zadané:

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   | 
(1 row)

Ak zistíte, že váš pracovný tím používa samostatný nástroj na sledovanie histórie údržby, môžete stĺpec odstrániť zadaním:

ALTER TABLE playground DROP last_maint;

Týmto sa odstráni stĺpec last_maint a všetky hodnoty v ňom, ale všetky ostatné údaje zostanú nezmenené.

Krok 10 — Aktualizácia údajov v tabuľke

Doteraz ste sa naučili, ako pridávať záznamy do tabuľky a ako ich mazat, ale tento tutoriál zatiaľ nepokryl, ako upravovať existujúce záznamy.

Hodnoty existujúceho záznamu môžete aktualizovať tak, že vyhľadáte požadovaný záznam a nastavíte stĺpec na hodnotu, ktorú chcete použiť. Môžete vyhľadať záznam swing (týmto sa zhodujú všetky hojdačky vo vašej tabuľke) a zmeniť jeho farbu na red. Toto môže byť užitočné, ak ste hojdačke dali nový náter:

UPDATE playground SET color = 'red' WHERE type = 'swing';

Môžete overiť, že operácia bola úspešná, opätovným dotazovaním údajov:

SELECT * FROM playground;

 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2018-08-16
(1 row)

Ako vidíte, snímok je teraz zaznamenaný ako červený.

Konfigurácia a tipy na výkon PostgreSQL

Správna konfigurácia PostgreSQL je nevyhnutná pre stabilnú a rýchlu databázu. Predvolené nastavenia sú navrhnuté tak, aby boli bezpečné pre akýkoľvek hardvér, čo znamená, že takmer nikdy nie sú vhodné pre produkčné zaťaženie. Ladenie výkonu zahŕňa úpravu alokácie zdrojov – najmä pamäte, I/O a spracovania pripojení – tak, aby zodpovedali hardvéru vášho servera a konkrétnym vzorom dotazov vašej aplikácie.

Poďme si prebrať niekoľko kľúčových konfiguračných parametrov a všeobecných stratégií na zlepšenie výkonu databázy.

Hlavné konfiguračné súbory

Pred ladením musíte vedieť, kde sa nachádzajú konfiguračné súbory. Na Ubuntu sú umiestnené v /etc/postgresql/[verzia]/main/:

• postgresql.conf: Hlavný konfiguračný súbor servera. Tu sa nastavujú všetky parametre súvisiace s výkonom.
• pg_hba.conf: Tento súbor riadi autentifikáciu klientov (ktorí používatelia sa môžu pripojiť z ktorých adries). Priamo neovplyvňuje výkon dotazov, ale je základný pre bezpečnosť.

Po úprave súboru postgresql.conf môžete zmeny použiť načítaním služby PostgreSQL:

sudo systemctl reload postgresql

Niektoré parametre (označené v dokumentácii ako kontext postmaster) však vyžadujú úplné reštartovanie:

sudo systemctl restart postgresql

Môžete spustiť nasledujúci príkaz, aby ste skontrolovali, či je potrebné reštartovať alebo znovu načítať po zmene:

SELECT context FROM pg_settings WHERE name = 'parameter_name';

Naopak, zmeny v súbore pg_hba.conf vyžadujú iba načítanie.

sudo systemctl reload postgresql

Konfigurácia pamäte

Pamäť je najdôležitejším faktorom pri výkonnosti databázy. PostgreSQL používa zdieľanú pamäť na ukladanie dát do vyrovnávacej pamäte a samostatnú pamäť pre každé pripojenie na operácie ako triedenie.

shared_buffers

Toto je najdôležitejšie nastavenie pamäte. Určuje množstvo pamäte, ktorú PostgreSQL vyhradí pre svoj dátový cache. Väčší cache znamená, že viac vašich pracovných dát môže byť načítaných z rýchlej pamäte namiesto pomalého disku.

• Smernica: Začnite nastavením shared_buffers na 25 % celkovej pamäte RAM vášho systému.

• Príklad: Na serveri s 16 GB RAM je dobrý východiskový bod 4GB.

• Konfigurácia (postgresql.conf):

  # Default is 128MB, which is too low
  shared_buffers = 4GB

work_mem

Toto nastavenie určuje množstvo pamäte, ktoré môže každá jednotlivá databázová operácia (ako triedenie, hash join alebo bitmapa) použiť, než začne zapisovať do dočasných diskových súborov. Zložité dotazy môžu mať viacero operácií, z ktorých každá využíva toto množstvo pamäte.

Nastavenie tohto príliš vysoko môže viesť k vyčerpaniu pamäte, ak súčasne beží veľa zložitých dotazov. Nastavenie príliš nízko spôsobuje časté a pomalé triedenie na disku.

• Smernica: Začnite s predvolenou hodnotou (4MB). Analyzujte svoje pomalé dotazy pomocou EXPLAIN ANALYZE. Ak vidíte „Sort Method: external merge Disk” alebo „HashAgg Disk”, znamená to, že work_mem je príliš malé. Postupne ho zvyšujte (napr. 16MB, 32MB) pre vašu reláciu na testovanie a nastavujte ho globálne iba vtedy, ak si ste istí.

pamäť_pre_údržbu

Toto nastavenie vyhradzuje pamäť pre interné údržbové operácie, ako napríklad VACUUM, CREATE INDEX a ALTER TABLE ADD FOREIGN KEY.

• Smernica: Nastavte toto vyššie ako work_mem (napr. 128MB alebo 256MB). Pretože tieto operácie sa nevykonávajú tak často ako používateľské dotazy, môžete byť pri nich štedrejší, aby ste urýchlili údržbu.

Konfigurácia kontrolného bodu a WAL

Kontrolné body sú procesy, pri ktorých PostgreSQL zapisuje všetky „špinavé“ údaje (zmeny uložené v pamäti) do trvalých súborov na disku. Ide o veľmi náročnú operáciu na vstupno-výstupné operácie. Denník Write-Ahead Log (WAL) zaznamenáva každú transakciu. Ladenie týchto procesov pomáha vyvážiť záťaž I/O a čas obnovy.

16MB

Spracovanie pripojenia

Každé pripojenie k PostgreSQL využíva pamäť. Vysoký počet pripojení môže vyčerpať zdroje servera.

• max_connections: Maximálny počet súbežných pripojení. Predvolená hodnota je 100. Aj keď je lákavé túto hodnotu zvýšiť, každé pripojenie spotrebuje pamäť.
• Použite Connection Pooler: Pre aplikácie, ktoré otvárajú a zatvárajú veľa pripojení (ako väčšina webových aplikácií), nezvyšujte max_connections. Namiesto toho použite externý connection pooler, ako je PgBouncer alebo Pgpool-II. Pooler udržiava malý súbor perzistentných pripojení k databáze a obsluhuje mnoho súbežných pripojení aplikácie, čo je oveľa efektívnejšie.

Plánovanie a analýza dotazov

Žiadne vyladenie servera nemôže opraviť neefektívny dopyt.

• EXPLAIN ANALYZE: Toto je váš hlavný nástroj na ladenie dotazov. Spustite tento príkaz na svojich pomalých dotazoch, aby ste videli skutočný plán vykonávania a strávený čas.

  EXPLAIN ANALYZE SELECT * FROM users WHERE last_name = 'Smith';

  Hľadajte „Sekvenčné prehľadávanie“ (Seq Scan) na veľkých indexovaných tabuľkách. To často znamená, že vám chýba index alebo dotaz nie je dostatočne selektívny.

• Vytvorte indexy: Ak často filtrujete alebo spájate podľa konkrétnych stĺpcov (napr. user_id, email, created_at), sú vhodnými kandidátmi na index.

  CREATE INDEX idx_users_on_email ON users (email);

• Spustite VACUUM ANALYZE: PostgreSQL používa plánovač dotazov, ktorý sa spolieha na štatistiky o dátach vo vašej tabuľke. Tieto štatistiky sú aktualizované pomocou ANALYZE. Hoci autovacuum toto robí automaticky, je dobré spustiť manuálne VACUUM ANALYZE; po veľkých nahratiach dát alebo ak si všimnete, že plány dotazov sú slabé.

Zálohovanie a obnova databáz PostgreSQL

Ochrana vašich dát je hlavnou zodpovednosťou správy databázy. Pravidelné, overené zálohy sú najefektívnejšou obranou proti poškodeniu dát, zlyhaniu hardvéru alebo ľudskej chybe. PostgreSQL poskytuje súbor robustných nástrojov príkazového riadku na vytváranie logických záloh a obnovy.

Existujú dve hlavné kategórie záloh:

1. Logické zálohy (SQL dumpy): Tieto zálohy pozostávajú z SQL príkazov, ktoré po vykonaní znovu vytvoria databázové objekty (ako sú tabuľky a indexy) a údaje. Vytvárajú sa pomocou utilít pg_dump a pg_dumpall. Táto metóda je flexibilná a prenosná medzi rôznymi verziami PostgreSQL.
2. Fyzické zálohy (na úrovni súborového systému): Táto metóda zahŕňa kopírovanie fyzických dátových súborov z dátového adresára PostgreSQL. Zvyčajne sa používa pre obnovu do konkrétneho času (PITR), čo je pokročilé nastavenie, ktoré bude podrobne diskutované neskôr v tejto časti.

Vytváranie logických záloh pomocou pg_dump

Nástroj pg_dump zálohuje jednu databázu. Nezálohuje globálne objekty, ako sú roly alebo tablespaces. Je veľmi flexibilný a môže byť spustený na aktívnej databáze bez blokovania čitateľov alebo zapisovateľov.

Bežné možnosti pg_dump

Výstup pg_dump môžete ovládať pomocou niekoľkých príkazových argumentov:

• -F c (Formát: vlastný): Toto je odporúčaný formát pre väčšinu záloh. Vytvára komprimovaný binárny archív, ktorý nie je čitateľný pre človeka, ale umožňuje paralelné obnovenie a preusporiadanie alebo vylúčenie objektov počas obnovy.
• -F p (Formát: plain): Toto je predvolené nastavenie. Vytvára veľký súbor .sql. Hlavnou nevýhodou je, že ho treba obnoviť naraz a nepodporuje paralelné obnovovanie.
• -s (len schéma): Zálohuje iba štruktúru databázy (tabuľky, pohľady, indexy) bez akýchkoľvek dát.
• -a (Len dáta): Zálohuje iba dáta, predpokladajúc, že schéma už existuje v cieľovom prostredí.
• --exclude-table=TABLE_NAME: Vylúči konkrétnu tabuľku zo zálohy. Je to užitočné pri obchádzaní veľkých, dočasných alebo nie kritických tabuliek.
• -f FILENAME: Určuje výstupný súbor.
• -U USER: Určuje používateľa PostgreSQL, ku ktorému sa pripojiť.

Toto je odporúčaný príkaz pre väčšinu zálohovacích úloh. Pripája sa k databáze mydatabase ako používateľ postgres a vytvára komprimovaný súbor s názvom mydatabase.dump.

pg_dump -U postgres -F c -f mydatabase.dump mydatabase

Obnova logických záloh

Nástroj, ktorý používate na obnovu, závisí od formátu vášho záložného súboru.

Obnovenie z vlastného archívu (.dump)

Použite nástroj pg_restore. Tento nástroj ponúka značnú flexibilitu, vrátane možnosti paralelného obnovovania a vykonávania predbežných kontrol.

Namiesto ručného vytvárania databázy najprv je najlepšou praxou použiť prepínač --create. Tento prepínač prikáže pg_restore, aby z archívu vykonal príkaz CREATE DATABASE. Na to sa musíte pripojiť k inému existujúcemu databázovému systému, napríklad k predvolenej databáze postgres.

Nasledujúci príkaz sa pripája k databáze postgres, zmaže a znovu vytvorí mydatabase (vďaka --clean a --create) a používa 4 paralelné úlohy na jeho obnovenie.

pg_restore -U postgres -d postgres --create --clean -j 4 mydatabase.dump

• -d postgres: Pripojí sa k databáze postgres, aby vykonal príkaz na vytvorenie.
• --create: Ukladá pg_restore, aby vytvoril cieľovú databázu (napr. mydatabase) pred obnovením do nej.
• --clean: Keď sa používa s --create, tento prepínač odstráni a znovu vytvorí celú cieľovú databázu. Keď sa používa samostatne (pri obnove do existujúcej databázy), odstráni existujúce databázové objekty pred ich opätovným vytvorením.
• -j 4: Používa 4 paralelné úlohy na obnovenie.

Pripájame sa k databáze postgres, pretože pg_restore potrebuje pripojenie k existujúcej databáze na serveri na vykonanie príkazu CREATE DATABASE pre našu novú cieľovú databázu.

Obnovenie z obyčajného textového súboru (.sql)

Použite štandardný klient psql. Táto metóda vyžaduje, aby cieľová databáza už existovala.

1. Vytvorte databázu:

   createdb -U postgres mydatabase_new

2. Nasmerujte súbor .sql do psql:

   psql -U postgres -d mydatabase_new < mydatabase.sql

Zálohovanie všetkých databáz (pg_dumpall)

Ak chcete zálohovať všetky databázy na serveri, ako aj všetky globálne objekty (role, používateľov a tablespace), použite nástroj pg_dumpall. Tento nástroj vytvára len obyčajný textový súbor .sql.

# Run as the postgres superuser

pg_dumpall -U postgres > all_databases.sql

Na obnovenie zo súboru pg_dumpall ho nasmerujete do psql. Musíte sa pripojiť ako superužívateľ (napríklad postgres) k predvolenej databáze (napríklad k samotnej postgres). Skript obsahuje všetky potrebné príkazy CREATE ROLE a CREATE DATABASE a vykoná ich odtiaľ.

# Run as the postgres superuser

psql -U postgres -d postgres -f all_databases.sql

Zálohovanie iba rolí a globálnych premenných

Bežným a užitočným vzorom je zálohovať len globálne objekty. Robí sa to pomocou prepínača --globals-only a je to efektívny spôsob, ako replikovať používateľské oprávnenia medzi servermi.

pg_dumpall -U postgres --globals-only > roles.sql

Zhrnutie stratégie zálohovania a obnovy

Nasledujúca tabuľka zhrňuje stratégiu zálohovania a obnovy:

.sql or .dump

.sql

Najlepšie postupy pre automatizáciu a zálohovanie

Záložný plán nie je kompletný, kým nie je automatizovaný, overený a bezpečný.

• Automatizujte svoje zálohy: Použite štandardný nástroj Linuxu, ako je cron, na naplánovanie spúšťania vašich príkazov pg_dump alebo pg_dumpall každú noc.
• Spravujte heslá bezpečne: Ak chcete spúšťať zálohy v neinteraktívnom skripte, nezadávajte heslá priamo do kódu. Použite súbor .pgpass. Vytvorte súbor v domovskom adresári používateľa (napr. /var/lib/postgresql/.pgpass). Súbor musi mať prísne oprávnenia 0600, inak ho PostgreSQL bude ignorovať. Formát je hostname:port:databáza:používateľ:heslo. Môžete použiť * ako zástupný znak pre ľubovoľné pole, napr. localhost:5432:*:postgres:your_password.
• Otestujte svoje obnovenia: Záloha je užitočná len vtedy, ak ju je možné obnoviť. Pravidelne si nacvičujte obnovovanie svojich záloh na samostatnom serveri mimo produkcie, aby ste overili ich integritu.
• Overte svoje zálohy: Po vytvorení zálohy pridajte krok skriptu, ktorý skontroluje, či má súbor nenulovú veľkosť. Pre vlastné formáty môžete spustiť pg_restore -l mydatabase.dump &> /dev/null (čo vypíše obsah do /dev/null), aby ste overili, že archív nie je poškodený kontrolou návratového kódu príkazu.
• Ukladajte zálohy na diaľku: Nepokladajte svoje zálohy na rovnakom serveri ako vašu databázu. Kompletné zlyhanie servera by spôsobilo stratu oboch. Skopírujte zálohy do vzdialeného umiestnenia, ako sú Spaces od Linux-Console.net alebo iný server, ako posledný krok vášho skriptu.

Porovnanie PostgreSQL s ostatnými databázovými enginmi na Ubuntu

Ubuntu repozitáre balíkov poskytujú prístup k širokej škále databázových engineov. Najlepšia voľba závisí úplne od požiadaviek vašej aplikácie, ako je jej dátový model, potreby škálovateľnosti a záruky konzistencie.

• PostgreSQL: Táto objektovo-relačná databáza je známa svojou vysokou mierou súladu so štandardmi, integritou dát a robustnou sadou funkcií. Je plne ACID kompatibilná a dobre podporuje pokročilé dátové typy a zložité dotazy, čo z nej robí bežnú voľbu ako primárnu „systém záznamov“ databázu.
• MySQL: Ako súčasť pôvodného LAMP stacku je MySQL jednou z najpopulárnejších open-source databáz. Je známa svojou jednoduchou použiteľnosťou, historicky silným výkonom pri čítaní a silnou komunitou. Je tiež plne kompatibilná s ACID (s predvoleným enginom InnoDB) a obľúbená pre jednoduché webové aplikácie.
• MariaDB: Komunitou riadený fork MySQL. Hoci si zachováva vysokú kompatibilitu (najmä so staršími verziami), vyvíjal sa ďalej a zavádza vlastné funkcie, takže nemusí byť úplnou náhradou pre aplikácie postavené na moderných funkciách MySQL 8.0 a novších.
• SQLite: Toto nie je databáza klient-server. Je to knižnica v jazyku C, ktorá vkladá plnohodnotný databázový engine priamo do aplikácie a ukladá celú databázu do jedného súboru. Dobre zvláda viacerých súčasných čitateľov, ale nie je ideálna pre aplikácie s častými súčasnými zápismi alebo pre viacužívateľské scenáre cez sieť.
• MongoDB: Vedúca databáza NoSQL, MongoDB ukladá údaje vo flexibilných dokumentoch podobných JSON. To umožňuje flexibilnú schému a je navrhnutá pre vysokú dostupnosť a horizontálne škálovanie. Aj keď je známa svojou flexibilitou, MongoDB od verzie 4.0 podporuje aj ACID transakcie s viacerými dokumentmi, čo ju robí vhodnou voľbou pre aplikácie vyžadujúce silnú konzistenciu.
• Redis: Je to úložisko dátových štruktúr v pamäti, čo znamená, že všetky svoje dáta uchováva v RAM pre výnimočnú rýchlosť. Nie je to všeobecná náhrada databázy založenej na disku, ale takmer vždy sa používa spolu s ňou (napríklad PostgreSQL) ako vysokorýchlostná cache alebo úložisko relácií.

Tu je porovnanie PostgreSQL s ostatnými populárnymi databázovými systémami dostupnými na Ubuntu.

Porovnanie databázových enginov

Bežné problémy s PostgreSQL a riešenie problémov v Ubuntu

Spúšťanie PostgreSQL na Ubuntu môže predstavovať určité výzvy, najmä pre používateľov nových v jeho architektúre na systémoch založených na Debiane. Väčšina chýb spadá do predvídateľných kategórií: konflikty pri inštalácii, pravidlá overovania, oprávnenia súborov a správa služieb.

Pred riešením problémov je vaším najdôležitejším nástrojom súbor protokolu PostgreSQL. Na štandardných inštaláciách Ubuntu ho nájdete na /var/log/postgresql/postgresql-[verzia]-main.log. Tento protokol si vždy skontrolujte ako prvý, pretože poskytuje konkrétne chybové hlásenia, ktoré vám pomôžu pri diagnostike.

Problémy s inštaláciou

Tieto problémy sa zvyčajne vyskytujú počas počiatočného procesu apt install alebo keď initdb prvýkrát skúsi vytvoriť nový databázový klaster.

Konflikty balíkov alebo nezhody verzií

Tento problém nastane, keď sa váš systém snaží nainštalovať balíky PostgreSQL z dvoch rôznych zdrojov, napríklad z predvoleného repozitára Ubuntu a oficiálneho repozitára PostgreSQL Global Development Group (PGDG). Repozitáre Ubuntu často obsahujú staršie, stabilné verzie, zatiaľ čo repozitár PGDG poskytuje najnovšie vydania. Ich miešanie môže viesť k poškodeným závislostiam.

Riešenie problémov:

• Skontrolujte svoje zdroje: Použite apt-cache policy postgresql, aby ste videli, ktoré verzie sú dostupné a z ktorého úložiska.
• Vyberte jeden zdroj: Dôrazne sa odporúča použiť repozitár PGDG pre najaktuálnejšiu verziu. Ak pridáte repozitár PGDG, môže byť potrebné použiť apt pripnutie, aby jeho balíky mali prednosť pred predvolenými balíkmi Ubuntu.
• Opraviť poškodené inštalácie: Ak sa už nachádzate v konfliktnom stave, sudo apt-get -f install môže problém vyriešiť. Ak nie, možno budete musieť úplne odinštalovať všetky balíky PostgreSQL (sudo apt-get purge "postgresql-*") a nainštalovať ich znova z jedného čistého zdroja.

Port 5432 je už používaný

Táto chyba znamená, že na predvolenom porte PostgreSQL (5432) už počúva iný program. Je to bežné, ak stará, odinštalovaná inštancia PostgreSQL sa nepodarila vypnúť alebo ak máte spustených viacero klastrov PostgreSQL (funkcia systému pg_ctlcluster na Ubuntu).

Riešenie problémov:

• Identifikujte proces: Použite netstat na zistenie, čo používa port.

  sudo netstat -tulpn | grep 5432

• Skontrolujte ostatné clustre: Ak je proces postgres, použite príkaz pg_lsclusters na zobrazenie všetkých nakonfigurovaných clustrov a ich stavov.

  pg_lsclusters

  Výstup môže zobraziť jeden ‚online‘ a jeden ‚offline‘.

• Zastavte nežiadaný cluster: Ak nájdete starý alebo nechcený cluster, ktorý beží, zastavte ho pomocou jeho verzie a názvu.

  sudo pg_ctlcluster 13 main stop

• Zmeňte port: Ak iná služba správne používa port 5432, musíte nakonfigurovať svoj nový PostgreSQL cluster tak, aby používal iný port úpravou jeho súboru postgresql.conf.

Chyby lokalizácie a kódovania počas initdb

Chyby ako „invalid locale name“ alebo „could not determine default locale“ počas počiatočného nastavenia klastru (initdb) znamenajú, že nastavenia lokalizácie vášho systému Ubuntu chýbajú alebo sú nesprávne nakonfigurované. PostgreSQL tieto nastavenia preberá z operačného systému, aby určoval poradie zoradenia (koláciu), klasifikáciu znakov a formátovanie.

Riešenie problémov:

• Skontrolujte jazyky svojho systému: Spustite locale -a pre zobrazenie všetkých dostupných jazykov.

• Vygenerujte chýbajúce locale: Ak váš požadovaný locale (napr. en_US.UTF-8) nie je uvedený, musíte ho vygenerovať.

  sudo locale-gen en_US.UTF-8
  
  sudo update-locale

• Opätovne inicializujte klaster: Po nastavení systémových lokalizácií môže byť potrebné klaster PostgreSQL odstrániť a znova vytvoriť. Príkaz pg_createcluster je štandardnou metódou v Ubuntu na tento účel.

  Upozornenie: Toto vymaže všetky údaje v klastri

  sudo pg_dropcluster [version] main
  
  sudo pg_createcluster [version] main

Problémy s overením

Toto sú najčastejšie problémy, s ktorými sa používatelia stretávajú po úspešnej inštalácii.

Overenie rovnocenného uzla zlyhalo

Toto je najčastejšia chyba. Znamená to, že sa pokúšate pripojiť lokálne a vaše aktuálne prihlásené používateľské meno v Ubuntu sa nezhoduje s názvom roly PostgreSQL databázy, ktorú sa snažíte použiť.

V predvolenom nastavení PostgreSQL na Ubuntu používa peer autentifikáciu pre lokálne pripojenia. Táto metóda sa pýta operačného systému: „Aké je používateľské meno procesu, ktorý sa ku mne pripája?“ a umožní pripojenie, ak sa toto používateľské meno zhoduje s požadovanou databázovou rolou.

Napríklad, ak ste prihlásený ako myuser a spustíte psql -U postgres, zlyhá to, pretože operačný systém hlási vaše používateľské meno ako myuser, nie postgres.

Riešenie:

Správny spôsob pripojenia ako superpoužívateľ postgres je najprv prevziať identitu systémového používateľa postgres pomocou sudo.

sudo -u postgres psql

Ak chcete toto správanie zmeniť, musíte nastaviť heslo pre rolu postgres a potom upraviť súbor pg_hba.conf tak, aby používal md5 alebo scram-sha-256 namiesto peer.

Zlá konfigurácia pg_hba.conf

Chyby v súbore pg_hba.conf (Host-Based Authentication) sú hlavnou príčinou zlyhania pripojenia. Tento súbor, ktorý sa nachádza na /etc/postgresql/[version]/main/pg_hba.conf, je súbor pravidiel, ktorý určuje kto sa môže pripojiť odkiaľ k ktorej databáze a akou metódou. Jedna chybná syntax alebo nesprávne poradie pravidiel vás môže zablokovať.

Riešenie problémov:

• Poradie pravidiel je dôležité: PostgreSQL číta tento súbor odhora nadol a používa prvé pravidlo, ktoré zodpovedá. Konkrétnejšie pravidlá musia byť vždy umiestnené pred všeobecnejšími.

• Obnoviť po zmenách: Po úprave súboru pg_hba.conf musíte reštartovať službu PostgreSQL, aby sa zmeny prejavili:

  sudo systemctl reload postgresql

Tu sú bežné chyby v konfigurácii:

host all all 127.0.0.1/32 scram-sha-256

host all all 0.0.0.0/0 reject, host all all 192.168.1.100/32 md5

local all postgres md5

Problémy s povoleniami

Tieto chyby sa často objavujú v protokoloch alebo pri pokuse o pripojenie ako „Prístup zamietnutý“.

Nie je možné pristúpiť k súboru socket

Chyby ako „nedá sa pripojiť na server: Súbor alebo adresár neexistuje“ alebo „Prístup odmietnutý“ pri odkazovaní na /var/run/postgresql/.s.PGSQL.5432 zvyčajne znamenajú jednu z dvoch vecí:

1. Služba nebeží: Súbor soketu sa odstráni, keď sa služba zastaví. Skontrolujte stav služby pomocou sudo systemctl status postgresql.
2. Povolenia používateľa: Adresár soketu vlastní postgres a je prístupný pre skupinu postgres. Ak váš systémový používateľ nie je v skupine postgres, nemôžete použiť soket.

Riešenie:

Pridajte svojho používateľa do skupiny postgres. Musíte sa odhlásiť a znova prihlásiť, aby sa táto zmena prejavila.

sudo usermod -a -G postgres $USER

Oprávnenia priečinka s údajmi

Ak služba PostgreSQL nedokáže spustiť a v záznamoch sa objaví „Prístup zamietnutý“ súvisiaci s dátovým adresárom (napr. /var/lib/postgresql/[verzia]/main), vlastníctvo adresára alebo oprávnenia súborov sú nesprávne.

PostgreSQL vyžaduje, aby jeho dátový adresár bol výlučne vo vlastníctve systémového používateľa postgres a mal práva 0700 (iba vlastník môže čítať, zapisovať a spúšťať). Toto je bezpečnostné opatrenie. Tento problém sa často vyskytuje po obnovení zálohy alebo po manuálnom presunutí dátového adresára.

Riešenie:

Rekurzívne resetujte vlastníctvo a povolenia.

# Correct the ownership (replace 18 with your version)

sudo chown -R postgres:postgres /var/lib/postgresql/18/main



# Correct the permissions

sudo chmod 0700 /var/lib/postgresql/18/main

Správa služieb

Tieto problémy súvisia so spúšťaním, zastavovaním a správou samotnej služby postgresql.

Služba sa nepodarí spustiť po zmenách konfigurácie

Ak sa služba po úprave súborov postgresql.conf alebo pg_hba.conf nedokáže spustiť okamžite, pravdepodobne ste zaviedli syntaktickú chybu.

Riešenie problémov:

1. Skontrolujte logy: Toto je najrýchlejší spôsob, ako nájsť chybu. sudo journalctl -u postgresql alebo konkrétny log na /var/log/postgresql/postgresql-[version]-main.log často vypíše presné číslo riadku neplatného nastavenia.

2. Bežné chyby v postgresql.conf:

   • Neuvoľnené reťazcové hodnoty (napr. log_destination=stderr je správne; log_destination=/var/log/my.log je nesprávne, malo by to byť '/var/log/my.log').
   • Neplatné hodnoty nastavení (napr. shared_buffers=10GB, keď máte k dispozícii iba 8GB RAM).

Zmätenie kvôli viacerým klasrom

Balík postgresql-common v Ubuntu obsahuje sadu obalových skriptov (pg_lsclusters, pg_ctlcluster, pg_createcluster), ktoré vám umožňujú spúšťať viaceré samostatné inštancie PostgreSQL (klastre) na rovnakom počítači, často rôznych verzií.

Toto je výkonná funkcia, ale môže byť mätúca. Môžete upravovať konfiguračný súbor pre verziu 18, ale pripojovať sa k verzii 13, čo vás môže viesť k tomu, že si myslíte, že sa vaše "zmeny neuplatňujú."

Riešenie problémov:

• Zoznam všetkých klastrov: Vždy začnite tu, aby ste videli, čo beží, na ktorom porte a kde sa nachádzajú jeho konfiguračné súbory.

  pg_lsclusters
  # Ver Cluster Port Status Owner    Data directory                       Log file
  # 13  main    5432 down   postgres /var/lib/postgresql/13/main         ...
  # 18  main    5433 online postgres /var/lib/postgresql/18/main         ...

• Spravovať konkrétne clustre: Pri spúšťaní, zastavovaní alebo načítavaní vždy uveďte cluster, ktorý chcete zmeniť:

  sudo pg_ctlcluster 18 main reload

Limity zdrojov

Tieto chyby sa vyskytujú, keď PostgreSQL požaduje viac prostriedkov (napríklad pamäte), než je nakonfigurované operačným systémom.

Chyby zdieľanej pamäte

Chyby pri spustení ako „nedá sa vytvoriť segment zdieľanej pamäte: Neplatný argument“ alebo „Na zariadení nie je voľné miesto“ často znamenajú, že nastavenie shared_buffers v PostgreSQL je väčšie ako limit zdieľanej pamäte (SHMMAX) vášho operačného systému.

PostgreSQL používa veľký blok zdieľanej pamäte na kešovanie a medziprocesovú komunikáciu. Ak požiada o viac, než OS povoľuje, OS žiadosť odmietne a PostgreSQL sa nespustí. Toto je bežné v prostrediach s obmedzenou pamäťou alebo v kontajneroch.

Riešenie problémov:

1. Skontrolujte limity OS:

   cat /proc/sys/kernel/shmmax

2. Skontrolujte postgresql.conf: Pozrite sa na vaše nastavenie shared_buffers.

3. Riešenie 1 (Odporúčané): Znížte hodnotu shared_buffers v súbore postgresql.conf tak, aby bola bezpečne pod limitom shmmax.

4. Riešenie 2 (pokročilé): Zvýšte limit shmmax jadra. Upraviť /etc/sysctl.conf a pridať riadok ako kernel.shmmax=134217728 (pre 128 MB). Použiť ho pomocou sudo sysctl -p. Toto je zvyčajne potrebné len pri optimalizácii vysokého výkonu.

Často kladené otázky

1. Čo je PostgreSQL a prečo by som ho mal používať na Ubuntu?

PostgreSQL, často nazývaný Postgres, je výkonný otvorený objektovo-relačný databázový systém. Má silnú povesť spoľahlivosti, robustnosti funkcií a integrity údajov, pričom je aktívne vyvíjaný už viac ako 30 rokov.

Mali by ste zvážiť jeho použitie vo svojich aplikáciách, pretože plne podporuje súlad s ACID (Atómovosť, Konzistencia, Izolácia, Trvácnosť), dobre zvláda vysokú záťaž a podporuje širokú škálu pokročilých dátových typov a rozšírení, ako je napríklad rozšírenie PostGIS pre geopriestorové údaje.

Používanie PostgreSQL na Ubuntu je bežnou voľbou, pretože Ubuntu je populárna a stabilná linuxová distribúcia pre servery. PostgreSQL je súčasťou predvolených repozitárov balíkov Ubuntu, čo robí inštaláciu a získavanie včasných bezpečnostných aktualizácií jednoduchým procesom spravovaným správcom balíkov apt systému.

2. Ako nainštalujem PostgreSQL na Ubuntu?

PostgreSQL môžete nainštalovať priamo z predvolených úložísk Ubuntu pomocou príkazu apt.

Najprv obnovte lokálny index balíkov na vašom serveri, aby ste mali najnovšie zoznamy balíkov:

sudo apt update

Ďalej nainštalujte hlavný balík PostgreSQL a balík contrib, ktorý obsahuje ďalšie nástroje a rozšírenia:

sudo apt install postgresql postgresql-contrib

3. Ako spustím a povolím službu PostgreSQL?

Po úspešnej inštalácii by sa služba PostgreSQL mala spustiť automaticky. Jej stav môžete overiť pomocou systemctl.

sudo systemctl status postgresql

Môžete vidieť výstup ako tento:

Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2025-10-29 10:30:01 IST; 1min 12s ago
   Main PID: 1234 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4662)
...

Ak služba nebeží, môžete ju spustiť manuálne:

sudo systemctl start postgresql

Aby sa zabezpečilo, že databáza sa spustí automaticky pri každom štarte servera, musíte povoliť službu:

sudo systemctl enable postgresql

4. Ako môžem vytvoriť novú databázu v PostgreSQL?

Najjednoduchšia metóda je použiť príkazový nástroj createdb. Tento príkaz musí byť spustený rolou PostgreSQL, ktorá má oprávnenie na vytváranie databáz.

Predvolene je účet používateľa Linux postgres nakonfigurovaný na pripojenie ako superpoužívateľ databázy postgres. Môžete sa prepnúť na tohto používateľa a spustiť príkaz:

sudo -i -u postgres

createdb my_database

Prípadne sa môžete prihlásiť do interaktívneho prostredia PostgreSQL (psql) a použiť SQL príkaz CREATE DATABASE.

Najprv pristúpte k výzve ako používateľ postgres:

sudo -i -u postgres

psql

Potom, z prostredia výzvy psql (ktorá vyzerá ako postgres=#, pretože postgres je superužívateľ), spustite SQL príkaz. Nezabudnite ukončiť SQL príkazy bodkočiarkou.

CREATE DATABASE my_database;

Z príkazového riadku psql môžete odísť zadaním \q a potom zadajte exit, aby ste sa vrátili k bežnému používateľovi.

5. Ako vytvárať a spravovať používateľov a roly v PostgreSQL?

V PostgreSQL sa správa používateľov rieši prostredníctvom rolí. Rola môže byť databázovým používateľom alebo skupinou používateľov, ale rola s právom LOGIN sa považuje za používateľa.

1. Vytvorenie používateľa

Môžete použiť príkazový nástroj createuser. Najlepšie je spustiť ho ako používateľ postgres.

sudo -i -u postgres

Odporúčanou metódou je použiť príznak --interactive na zobrazenie výzvy na nastavenia a príznak --pwprompt na okamžité nastavenie hesla.

createuser --interactive --pwprompt

Toto si vyžiada názov roly, heslo a či by rola mala byť superpoužívateľom alebo mať iné vysokoúrovňové oprávnenia.

Output
Enter name of role to add: sammy
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Používateľa môžete vytvoriť aj jedným príkazom SQL z výzvy psql. Týmto spôsobom vznikne minimálny používateľ, ktorý sa môže len prihlásiť.

CREATE ROLE sammy WITH LOGIN PASSWORD 'secure_password';

2. Udeľovanie privilégií

Vytvorenie používateľa mu automaticky nedáva prístup k databáze. Práva musíte udeliť výslovne. Tento proces má niekoľko krokov:

1. Povoliť prístup Grant Connect: Najprv povoľte novej role prístup na pripojenie k vašej databáze.

   GRANT CONNECT ON DATABASE my_database TO sammy;

   Bežný omyl: Používanie GRANT ALL PRIVILEGES ON DATABASE my_database TO sammy; je mätúce. Tento príkaz udeľuje len povolenia CONNECT, CREATE a TEMP na samotnú databázu, nie na tabuľky v nej.

2. Použitie oprávnenia k schéme: Ďalej musíte udeliť povolenie na prístup k schéme v rámci databázy. Bez toho sa používateľ môže pripojiť, ale nevidí žiadne tabuľky.

   GRANT USAGE ON SCHEMA public TO sammy;

3. Udeľovanie povolení tabuľky: Teraz môžete používateľovi udeliť konkrétne povolenia, ktoré potrebuje na objektoch v rámci schémy. Na toto sa pripojte k databáze ako superpoužívateľ:

   \c my_database

   Teraz ste pripojení k my_database.

4. Udeľte povolenia na existujúce objekty: Ak chcete roli sammy poskytnúť prístup na čítanie/zápis ku všetkým existujúcim tabuľkám, spustite:

   GRANT ALL ON ALL TABLES IN SCHEMA public TO sammy;
   
   GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO sammy;

   Ak chcete udeliť iba prístup na čítanie, mali by ste použiť GRANT SELECT namiesto GRANT ALL.

5. (Nepovinné) Udeliť oprávnenia pre budúce objekty: Príkazy vyššie ovplyvňujú iba existujúce tabuľky. Ak chcete automaticky udeliť oprávnenia pre akékoľvek nové tabuľky vytvorené v budúcnosti (napr. administrátorom), musíte zmeniť predvolené oprávnenia:

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sammy;
   
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sammy;

6. Aké sú základné príkazy PostgreSQL pre začiatočníkov?

Najdôležitejším nástrojom na učenie je interaktívny terminál psql. Keď ste prihlásený (napr. spustením sudo -i -u postgres a potom psql), môžete používať meta-príkazy (ktoré začínajú spätným lomítkom \) na navigáciu a kontrolu vašej databázy.

Tu sú niektoré z najbežnejších príkazov psql:

\l or \list

\c database_name

\conninfo

\dt

\du

\d table_name

\?

\q

7. Ako sa pripojím k databáze PostgreSQL z terminálu?

Spôsob pripojenia závisí od autentifikačnej metódy nakonfigurovanej v súbore pg_hba.conf. (Tento súbor sa zvyčajne nachádza na /etc/postgresql/<version>/main/pg_hba.conf). Predvolene PostgreSQL na Ubuntu používa peer autentifikáciu pre lokálne pripojenia.

Overovanie peer funguje tak, že získava používateľské meno operačného systému klienta z jadra a používa ho ako povolené používateľské meno databázy. Toto funguje iba pre lokálne pripojenia cez Unix socket.

1. Pripojenie ako administrátor postgres (lokálna autentifikácia peer)

Toto je najjednoduchší spôsob, ako sa pripojiť lokálne. Používate sudo na spustenie príkazov ako Linux užívateľ postgres, ktorého peer autentifikácia automaticky mapuje na superužívateľa databázy postgres.

sudo -i -u postgres

psql

Týmto sa pripájate cez lokálnu Unix socket k predvolenej databáze s názvom postgres.

2. Pripojenie ako konkrétny používateľ (lokálne overenie peer vs. TCP/IP overenie password)

Povedzme, že ste vytvorili rolu PostgreSQL s názvom sammy, ale ste prihlásený na svoj server ako iný používateľ Linuxu (napr. ubuntu).

• Skúška 1: Neúspech (Nezhoda overenia peer): Ak spustíte psql -U sammy -d my_database, pravdepodobne to zlyhá. Tento príkaz sa pokúša použiť lokálny Unix socket, takže overenie peer sa snaží zosúladiť váš OS používateľa (ubuntu) s databázovým používateľom (sammy). Keďže sa nezhodujú, pripojenie je odmietnuté.

• Pokus 2: Úspech (Overenie heslom): Ak chcete obísť autentifikáciu peer, môžete vynútiť TCP/IP (sieťové) pripojenie pomocou -h localhost. Predvolene je súbor pg_hba.conf v Ubuntu nakonfigurovaný na použitie autentifikácie pomocou md5 alebo scram-sha-256 (heslo) pre lokálne TCP/IP pripojenia.

  psql -U sammy -d my_database -h localhost

  Terminál vás potom vyzve na zadanie hesla, ktoré ste nastavili pre rolu sammy.

  Tip: Aby ste sa vyhli zadávaniu hesla pri každom pripojení, môžete si vytvoriť súbor .pgpass vo svojom domovskom adresári. Tento súbor bezpečne uchováva vaše prihlasovacie heslá. Uistite sa, že jeho oprávnenia sú nastavené na 0600 (čítanie/zápis len pre váš účet).

• Pokus 3: Úspech (zhoda overenia cez Peer): Ak máte tiež na Linuxe používateľa systému s menom sammy, môžete použiť sudo na spustenie psql ako tento používateľ. Overenie peer by potom bolo úspešné.

  sudo -i -u sammy
  
  psql -d my_database

Záver

Tento návod poskytol kompletný postup na inštaláciu a správu PostgreSQL na serveri s Ubuntu. Začali ste s počiatočnou inštaláciou softvéru, nastavili autentifikáciu používateľov vytváraním rolí a založili nové databázy. Následne sme prešli základné SQL príkazy na manipuláciu s dátami a preskúmali podstatné administratívne úlohy, vrátane konfigurácie výkonu, postupov zálohovania a obnovy, a riešenia bežných problémov.

Teraz máte funkčný databázový server a základné zručnosti potrebné na jeho bezpečnú a efektívnu údržbu. Schopnosť spravovať údaje, riadiť prístup používateľov a zabezpečiť integritu databázy vás pripravuje na podporu zložitých aplikácií.

Ak sa chcete dozvedieť viac o Postgrese a o tom, ako ho používať, odporúčame vám pozrieť si nasledujúce návody:

• SQLite vs MySQL vs PostgreSQL: Porovnanie relačných databázových systémov
• Cvičte spúšťanie dotazov pomocou SQL

Ďakujeme, že sa učíte s komunitou Linux-Console.net.