Databáze

V tomto příspěvku ukáži, jak získat unikátní emaily ze sloupce databáze PostgreSQL seřazené podle domény. K tomu použijeme funkci substring(co, odkud, kam).

select email from  table_name order by substring(email, position('@' in email), length(email));

Uvedený select zobrazí záznamy ze sloupce email tabulky table_name a seřadí je dle domény (podle části řetězce, který vrátí substring). Pokud chceme unikátní, select změníme následovně:

select email from (select distinct email from table_name) as sub order by substring(email, position('@' in email), length(email));

Psql je textové rozhraní k databázi PostgreSQL. To umožňuje, kromě mnoha další věcí, i spouštění skriptů ze souboru.

psql -U uzivatel -d databaze -f soubor

Výše uvedený příkaz provede spuštění souboru pod uživatelem uzivatel nad databází databaze.

Pokud máte v souboru zálohu vytvořenou pomocí pg_dump, příkaz je následující.

psql nazev_databaze < soubor_s_daty

Mohou nastat případy, kdy potřebujete posunout sekvenci. V PostgreSQL se k tomu používá příkaz ALTER SEQUENCE. V následujícím příkladu nastaví sekvenci tak, aby začínala od čísla sto.

ALTER SEQUENCE sequence_name START WITH 100;
ALTER SEQUENCE sequence_name RESTART;

Kratší způsob zápisu:

ALTER SEQUENCE sequence_name RESTART WITH 100;

START
Nastaví hodnotu start sekvence. Nemá efekt na současnou hodnotu. Nastaví hodnotu pro budoucí volání ALTER SEQUENCE RESTART.

RESTART
Změní aktuální hodnotu sekvence.

Zdroj: postgresql.org/docs/current/sql-altersequence.html

Databáze PostgreSQL, která mi běží lokálně se zničeho nic zastavila. Zkusil jsem ji tedy nastartovat. Pro start jsem použil tento příkaz:

pg_ctl -D "C:\Program Files\PostgreSQL\9.4\data" start

PostgreSQL sice nenastartoval, ale výpis mě trochu nasměroval, i když mě překvapilo, že by konfigurační soubor postgresql.conf měl obsahovat chyby. V poslední době jsem v něm nic neměnil.

C:\Program Files\PostgreSQL\9.4\bin>2017-12-16 18:42:03 CET LOG:  invalid value for parameter "lc_monetary": "Czech_Czech Republic.1250"
2017-12-16 18:42:03 CET LOG:  invalid value for parameter "lc_numeric": "Czech_Czech Republic.1250"
2017-12-16 18:42:03 CET LOG:  invalid value for parameter "lc_time": "Czech_Czech Republic.1250"
2017-12-16 18:42:03 CET FATAL:  configuration file "C:/Program Files/PostgreSQL/9.4/data/postgresql.conf" contains errors

Chvíle googlení a odpověď je na světě. Microsoft ve svém updatu změnil locale pro Českou republiku. Zde je řešení problému: dba.stackexchange.com/…/postgresql-9-5-wont-start-after-windows-10-fall-update

Když jsem pak spustil:

pg_ctl -D "C:\Program Files\PostgreSQL\9.4\data" start

databáze fungovala. Bohužel byla spuštěna pouze, když běžel cmd. Po jeho vypnutí byla opět nedostupná. Bylo tedy třeba ve Správci úloh najít process postgresql-x64-9.4 a ten spustit.

Pohled pg_stat_activity ukazuje jaká je aktuální aktivita na databázovém serveru (jaká jsou aktuální připojení k serveru), na které databázi/databázích tato aktivita probíhá, uživatele (který se připojil), status daného připojení a třeba též sql dotaz, který probíhá (v případě, že status je active), nebo poslední dotaz, který na tomto připojení proběhl (pokud je status jiný než active).

select * from pg_stat_activity;

Některé zajímavé sloupce

Zdroje:

• postgresql.org/…/monitoring-stats.html
• question-defense.com/…pg_stat_activity-table-fields-explained

Konfigurační soubory a jejich umístění lze jednoduše získat dotazem do pohledu (view) pg_settings.

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

Ve Windows můžete dostat tento výsledek

name              settings
config_file       C:/Program Files/PostgreSQL/9.4/data/postgresql.conf"
data_directory    C:/Program Files/PostgreSQL/9.4/data"
external_pid_file
hba_file          C:/Program Files/PostgreSQL/9.4/data/pg_hba.conf"
ident_file        C:/Program Files/PostgreSQL/9.4/data/pg_ident.conf"

Na Linuxovém serveru tato umístění

       name        |                 setting
-------------------+------------------------------------------
 config_file       | /etc/postgresql/9.4/main/postgresql.conf
 data_directory    | /var/lib/postgresql/9.4/main
 external_pid_file | /var/run/postgresql/9.4-main.pid
 hba_file          | /etc/postgresql/9.4/main/pg_hba.conf
 ident_file        | /etc/postgresql/9.4/main/pg_ident.conf

Pro krátký popis jednotlivých souborů slouží sloupec short_desc

SELECT name, short_desc FROM pg_settings WHERE category = 'File Locations';
       name        |                    short_desc
-------------------+--------------------------------------------------
 config_file       | Sets the server's main configuration file.
 data_directory    | Sets the server's data directory.
 external_pid_file | Writes the postmaster PID to the specified file.
 hba_file          | Sets the server's "hba" configuration file.
 ident_file        | Sets the server's "ident" configuration file.