Praktična lista psql komandi za PostgreSQL administratore na Linuxu

Zašto koristiti psql na Linuxu i šta možete odmah postići

Kao administrator PostgreSQL na Linuxu, često će vam biti najbrži i najpouzdaniji alat komandna linija psql. Psql je interaktivni klijent koji omogućava direktan rad sa bazom: izvršavanje SQL upita, pokretanje skripti, pristup informacijama o shemi i podešavanjima servera, te hitne administrativne zahvate. Vi preko psql dobijate potpunu kontrolu, nisku latenciju i mogućnost skriptovanja kroz shell.

U ovom prvom delu pružiću vam pregled kako se povezati, osnovne opcije pokretanja i najkorisnije meta-komande koje uštede vreme. Cilj je da već pri prvom pokretanju znate kako da pristupite instanci, brzinski dobijete informacije o bazama i tabelama, i bezbedno izvršite osnovne radnje.

Pravo pokretanje i načini autentikacije

Na Linuxu najčešći načini pokretanja psql su:

Pokretanje kao PostgreSQL korisnik: sudo -u postgres psql ili su – postgres -c psql — korisno kada radite lokalno i nemate lozinku za korisnika baze.
Direktno sa parametrima: psql -U korisnik -d baza -h host -p port — koristi se za povezivanje na udaljeni ili lokalni server sa specificiranim korisnikom i portom.
Korišćenje datoteke lozinki: ~/.pgpass — omogućava automatsko prijavljivanje bez unošenja lozinke, uz odgovarajuće permisije (chmod 600).
Okruženje: PGHOST, PGPORT, PGUSER, PGDATABASE — možete postaviti promenljive okruženja da biste izbegli ponavljanje parametara.

Napomena o socket konekcijama: kada se povezujete lokalno, psql obično koristi Unix socket (bez -h) — to može promeniti pravila autentikacije definisana u pg_hba.conf.

Brzi skup meta-komandi koje ćete često koristiti

Meta-komande psql počinju kosom crtom i nisu SQL — daju vam brzi uvid i kontrolu nad okruženjem. Najvažnije su:

? – prikaz pomoći za meta-komande.
h – pomoć za SQL komandu (na primer h SELECT).
q – izlaz iz psql.
l ili list – lista baza podataka.
c baza – poveži se na drugu bazu (CONNECT).
dt – prikaži tabele u trenutnoj bazi; d za objekte, di za indekse.
i fajl.sql – izvrši SQL skriptu iz fajla; alternativno psql -f fajl.sql iz shell-a.
e – otvori trenutni upit u uređivaču (EDITOR), korisno za duže skripte.

Ove komande daju vam čvrst osnov. U sledećem delu prelazimo na praktične primere administrativnih zadataka: kako brzo proveriti stanje konekcija, menjati konfiguraciju i upravljati korisnicima koristeći psql.

Provera aktivnih veza i rešavanje zastoja

Kao administrator, prvo što ćete često raditi je brzo identifikovati ko se povezao, koje upite izvršava i da li nešto drži låčke koje blokiraju druge procese. U psql to radite preko pogleda pg_stat_activity i sistema za låčke.

Brzi pregled aktivnih veza (filtrirano da izostavi idle):

SELECT pid, usename, datname, client_addr, state, query_start,
       now() - query_start AS runtime, substring(query for 200) AS query
FROM pg_stat_activity
WHERE state  'idle'
ORDER BY runtime DESC;

Pronalazak dugotrajnih upita (npr. > 5 minuta):

SELECT pid, usename, datname, now() - query_start AS duration, substring(query for 200)
FROM pg_stat_activity
WHERE query  '' AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

Ako jedna sesija drži låčku i blokira druge, proverite låčke i povezane upite:

SELECT l.relation::regclass AS table, l.mode, l.granted, a.pid, a.usename, substring(a.query for 200)
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL;

Ako je neophodno prekinuti problematičnu sesiju (pažljivo — rizik gubitka rada), korisna je funkcija pg_terminate_backend:

-- primer: terminiraj PID 12345
SELECT pg_terminate_backend(12345);

Uvek proverite razlog i obavestite korisnike pre terminate; umesto prekida, ponekad je bolje kontaktirati aplikacioni tim ili sačekati kraj transakcije.

Upravljanje konfiguracijom u runtime (bez restarta kada je moguće)

Pre nego što menjate konfiguraciju, proverite gde je učitan fajl i trenutno podešavanje:

SHOW config_file;
SELECT name, setting, source, pending_restart FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','max_connections','log_min_duration_statement');

Za mnoge parametre možete koristiti ALTER SYSTEM da promenite vrednost iz psql-a i potom ponovo učitati konfiguraciju bez restartovanja servera:

-- postavi parametar
ALTER SYSTEM SET work_mem = '16MB';
-- učitaj novu konfiguraciju
SELECT pg_reload_conf();

Napomena: ALTER SYSTEM menja postgresql.auto.conf; neke promene zahtevaju restart (pending_restart = true u pg_settings). Ako je restart potreban, koristite sistemski alat (sudo systemctl restart postgresql) u održavanom prozoru — restart prekida sve konekcije.

Brzo privremeno podešavanje za trenutnu sesiju (samo za vašu vezu):

SET work_mem = '32MB';
-- za trajanje sesije ili transakcije:
SET LOCAL work_mem = '32MB';

Za audit promena koristite SHOW posle ALTER SYSTEM i proverite sadržaj postgresql.auto.conf iz OS-a (potreban root/postgres pristup).

Upravljanje korisnicima i pravima direktno iz psql

Osnovne meta-komande i SQL komande za korisnike:

Lista role: du ili SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;
Kreiranje role sa loginom i lozinkom (izbegavajte lozinku u istoriji komandi):

CREATE ROLE app_user WITH LOGIN PASSWORD 'tajna_lozinka' NOSUPERUSER NOCREATEDB;
-- bezbednija alternativa: CREATE ROLE ...; pa uputite korisnika da izvrši password app_user

Menjanje privilegija i dodela prava:

-- dodela prava na bazu
GRANT CONNECT ON DATABASE moja_baza TO app_user;
-- dodela prava na šemu/tabele
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO app_user;
-- da nova prava automatski važe na buduće tabele:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;

Menjanje lozinke iz psql (preporučeno jer ne ostavlja lozinku u shell istoriji):

password app_user

Uklanjanje role (samo kad nema zavisnosti):

DROP ROLE app_user;
-- ili prvo onemogućite login:
ALTER ROLE app_user NOLOGIN;

Uvek proverite postoje li aktivne veze korisnika pre drop-a i po potrebi terminirajte sesije. Kao dobar običaj, pravite promene u testnom okruženju pre primene u produkciji.

Brzi skriptovi i automatizacija

Da biste ponovljive administrativne zadatke učinili pouzdanim, kombinujte psql sa shell skriptima i alatima za zakazivanje:

Jednolinijske komande iz shell-a: psql -U postgres -d moja_baza -c "VACUUM;" — korisno za ad-hoc zadatke.
Izvođenje skripti: psql -f /putanja/do/skripte.sql ili psql --set ON_ERROR_STOP=on -f /putanja/do/skripte.sql za prekid pri grešci.
Automatizacija: koristite cron ili systemd timers za redovne zadatke, i čuvajte skripte u verzionom repozitorijumu (git) da biste imali audit trail promena.
Monitoring i alerti: integrisani metapregledi (pg_stat_*) zajedno sa eksternim alatima (Prometheus, Grafana) pomažu u brzoj reakciji pre nego što problemi postanu kritični.

Gde dalje—kratke smernice za rad

Bezbednost i predvidljivost su ključni: uvek testirajte promene u razvojnom okruženju, automatizujte rutinske zadatke, i vodite evidenciju promena konfiguracije. Kada donosite odluke koje utiču na produkciju, komunicirajte sa timom i planirajte održavanje u odgovarajućim prozorima.

Za dalje produbljivanje znanja i proverene reference posetite zvaničnu dokumentaciju PostgreSQL, gde ćete naći detaljna objašnjenja parametara, primeri migracija i najbolje prakse iz prve ruke.

Zašto koristiti psql na Linuxu i šta možete odmah postići

Pravo pokretanje i načini autentikacije

Na Linuxu najčešći načini pokretanja psql su:

Pokretanje kao PostgreSQL korisnik: sudo -u postgres psql ili su – postgres -c psql — korisno kada radite lokalno i nemate lozinku za korisnika baze.
Direktno sa parametrima: psql -U korisnik -d baza -h host -p port — koristi se za povezivanje na udaljeni ili lokalni server sa specificiranim korisnikom i portom.
Korišćenje datoteke lozinki: ~/.pgpass — omogućava automatsko prijavljivanje bez unošenja lozinke, uz odgovarajuće permisije (chmod 600).
Okruženje: PGHOST, PGPORT, PGUSER, PGDATABASE — možete postaviti promenljive okruženja da biste izbegli ponavljanje parametara.

Napomena o socket konekcijama: kada se povezujete lokalno, psql obično koristi Unix socket (bez -h) — to može promeniti pravila autentikacije definisana u pg_hba.conf.

Brzi skup meta-komandi koje ćete često koristiti

Meta-komande psql počinju kosom crtom i nisu SQL — daju vam brzi uvid i kontrolu nad okruženjem. Najvažnije su:

? – prikaz pomoći za meta-komande.
h – pomoć za SQL komandu (na primer h SELECT).
q – izlaz iz psql.
l ili list – lista baza podataka.
c baza – poveži se na drugu bazu (CONNECT).
dt – prikaži tabele u trenutnoj bazi; d za objekte, di za indekse.
i fajl.sql – izvrši SQL skriptu iz fajla; alternativno psql -f fajl.sql iz shell-a.
e – otvori trenutni upit u uređivaču (EDITOR), korisno za duže skripte.

Provera aktivnih veza i rešavanje zastoja

Brzi pregled aktivnih veza (filtrirano da izostavi idle):

SELECT pid, usename, datname, client_addr, state, query_start,
       now() - query_start AS runtime, substring(query for 200) AS query
FROM pg_stat_activity
WHERE state  'idle'
ORDER BY runtime DESC;

Pronalazak dugotrajnih upita (npr. > 5 minuta):

SELECT pid, usename, datname, now() - query_start AS duration, substring(query for 200)
FROM pg_stat_activity
WHERE query  '' AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

Ako jedna sesija drži låčku i blokira druge, proverite låčke i povezane upite:

SELECT l.relation::regclass AS table, l.mode, l.granted, a.pid, a.usename, substring(a.query for 200)
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL;

Ako je neophodno prekinuti problematičnu sesiju (pažljivo — rizik gubitka rada), korisna je funkcija pg_terminate_backend:

-- primer: terminiraj PID 12345
SELECT pg_terminate_backend(12345);

Uvek proverite razlog i obavestite korisnike pre terminate; umesto prekida, ponekad je bolje kontaktirati aplikacioni tim ili sačekati kraj transakcije.

Upravljanje konfiguracijom u runtime (bez restarta kada je moguće)

Pre nego što menjate konfiguraciju, proverite gde je učitan fajl i trenutno podešavanje:

SHOW config_file;
SELECT name, setting, source, pending_restart FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','max_connections','log_min_duration_statement');

Za mnoge parametre možete koristiti ALTER SYSTEM da promenite vrednost iz psql-a i potom ponovo učitati konfiguraciju bez restartovanja servera:

-- postavi parametar
ALTER SYSTEM SET work_mem = '16MB';
-- učitaj novu konfiguraciju
SELECT pg_reload_conf();

Brzo privremeno podešavanje za trenutnu sesiju (samo za vašu vezu):

SET work_mem = '32MB';
-- za trajanje sesije ili transakcije:
SET LOCAL work_mem = '32MB';

Za audit promena koristite SHOW posle ALTER SYSTEM i proverite sadržaj postgresql.auto.conf iz OS-a (potreban root/postgres pristup).

Upravljanje korisnicima i pravima direktno iz psql

Osnovne meta-komande i SQL komande za korisnike:

Lista role: du ili SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;
Kreiranje role sa loginom i lozinkom (izbegavajte lozinku u istoriji komandi):

CREATE ROLE app_user WITH LOGIN PASSWORD 'tajna_lozinka' NOSUPERUSER NOCREATEDB;
-- bezbednija alternativa: CREATE ROLE ...; pa uputite korisnika da izvrši password app_user

Menjanje privilegija i dodela prava:

-- dodela prava na bazu
GRANT CONNECT ON DATABASE moja_baza TO app_user;
-- dodela prava na šemu/tabele
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO app_user;
-- da nova prava automatski važe na buduće tabele:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;

Menjanje lozinke iz psql (preporučeno jer ne ostavlja lozinku u shell istoriji):

password app_user

Uklanjanje role (samo kad nema zavisnosti):

DROP ROLE app_user;
-- ili prvo onemogućite login:
ALTER ROLE app_user NOLOGIN;

Uvek proverite postoje li aktivne veze korisnika pre drop-a i po potrebi terminirajte sesije. Kao dobar običaj, pravite promene u testnom okruženju pre primene u produkciji.

Brzi skriptovi i automatizacija

Da biste ponovljive administrativne zadatke učinili pouzdanim, kombinujte psql sa shell skriptima i alatima za zakazivanje:

Jednolinijske komande iz shell-a: psql -U postgres -d moja_baza -c "VACUUM;" — korisno za ad-hoc zadatke.
Izvođenje skripti: psql -f /putanja/do/skripte.sql ili psql --set ON_ERROR_STOP=on -f /putanja/do/skripte.sql za prekid pri grešci.
Automatizacija: koristite cron ili systemd timers za redovne zadatke, i čuvajte skripte u verzionom repozitorijumu (git) da biste imali audit trail promena.
Monitoring i alerti: integrisani metapregledi (pg_stat_*) zajedno sa eksternim alatima (Prometheus, Grafana) pomažu u brzoj reakciji pre nego što problemi postanu kritični.

Backup i vraćanje podataka: brzi primeri

Redovan backup je kritičan. Za logičke dumpove koristite pg_dump/pg_dumpall, a za brze restoracije i selektivne restore kombinujte format prilagođen za pg_restore. Fizikalne (point-in-time) backup-ove radite sa pg_basebackup i WAL arhivom.

Brzi primeri:

Dump cele baze u kompresovanom formatu:

pg_dump -U postgres -F c -b -v -f moja_baza.dump moja_baza

Vraćanje sa pg_restore (kreira bazu ako treba):

pg_restore -U postgres -C -d postgres -v moja_baza.dump

Jednostavan SQL dump i restore preko psql:

pg_dump -U postgres -F p moja_baza > moja_baza.sql
psql -U postgres -d nova_baza -f moja_baza.sql

Saveti: testirajte restore redovno na odvojenom serveru, koristite –single-transaction gde je primenjivo da dobijete konzistentan snapshot, i čuvajte backup politike (retencija, enkripcija, verifikacija). Za velike baze planirajte paralelni dump (-j) i merite vreme sa timing u psql ili time u shell-u.

Gde dalje—kratke smernice za rad

Za dalje produbljivanje znanja i proverene reference posetite zvaničnu dokumentaciju PostgreSQL, gde ćete naći detaljna objašnjenja parametara, primeri migracija i najbolje prakse iz prve ruke.