PostgreSQL

Řazení dat v PostgreSQL podle části řetězce

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));

Chyba relation „hibernate_sequence“ does not exist

Tuto chybu jsem dostal při použití Spring Data se Spring Boot a databází PostgreSQL. Takto vypadala třída definující entity.

@Entity
@Table(name = "t_login")
@SequenceGenerator(name = "t_login_id_seq_gen", sequenceName = "t_login_id_seq", allocationSize = 1)
data class Login(
        @Id
        @GeneratedValue
        var id: Long = 0,

        @Column
        var token: UUID = UUID.randomUUID()
)

Pro id bylo třeba více specifikovat @GeneratedValue:

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "t_login_id_seq_gen")

Tím se problém vyřešil.

Změna verze Javy z 8 na 11

Nedávno jsem prováděl změny verze Javy na projektu, který používá Spring Boot. Měnil jsem verzi Javy z verze 8 na verzi 11. Nebylo to až tak strašné, jak jsem čekal (spíše naopak), přesto se ale některé komplikace objevily. V tomto příspěvku budu postupovat od chyby k chybě, tak jak se u mě objevovaly.

Číst dálZměna verze Javy z 8 na 11

Spuštění skriptu ze souboru v psql

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

Posunutí sekvence v PostgreSQL

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

První přihlášení do PostgreSQL v Ubuntu

Pokud se po instalaci PostgreSQL pokusíte přihlásit pomocí psql, dostanete pravděpodobně chybu Peer authentication failed for user „postgres“

psql -U postgres
psql: FATAL:  Peer authentication failed for user "postgres"

Oprava: Zkoušel jsem instalaci PostgreSQL verze 10 na Linux Mint a bylo možné se přihlási uživatelem postgres tímto způsobem:
sudo su – postgres (přepnutí na uživatel postgres)
psql -U postgres

V Ubuntu je ve výchozím nastavení vypnuto přihlašování pro uživatele postgres. Do psql se můžeme připojit pomocí příkazu sudo

sudo -u postgres psql postgres

Vytvoříme nového uživatele (roli) s heslem password a schopností vytvářet databáze.

Číst dálPrvní přihlášení do PostgreSQL v Ubuntu

PostgreSQL nemůže po updatu na Windows nastartovat

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

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

datid databázové OID
datname název databáze
usename název připojeného uživatele
backend_start čas, kdy se klient připojil do databáze
query_start čas, kdy byl spuštěn sql dotaz
state_change čas, kdy došlo k poslední změně stavu (state)
waiting true, pokud se čeká na zámek (lock)
state stav připojení
query poslední sql dotaz, který proběhl (pokud je stav active tak probíhá)

Zdroje:

 

Konfigurační soubory v PostgreSQL a jejich umístění

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.