[[oktatas:linux:adatbázis-szerver|< Adatbázis-szerver]]

====== PostgreSQL ======

  * **Szerző:** Sallai András
  * Copyright (c) 2020, Sallai András
  * Szerkesztve: 2020, 2023
  * Licenc: [[https://creativecommons.org/licenses/by-sa/4.0/|CC BY-SA 4.0]]
  * Web: https://szit.hu

===== A PostgreSQL-ről =====

A PostgreSQL egy nyílt forráskódú adatbáziskezelő rendszer. 

A Berkeley Egyetemen egy Ingres nevű adatbáziskezelő szoftvert fejlesztettek az 1970-s 
évek óta, amit később átneveztek POST-inGRES névre, 1985 körül. 1995-ig a POSTQUEL
lekérdező nyelvet használta, ekkor váltottak SQL-re. 1996-tól fejlesztik az egyetemen kívül.

Az Ingres alapján több kereskedelmi szoftver jött létre:
  * Sybase
  * Microsoft SQL Server
  * NonStop SQL

===== Telepítés =====

  apt install postgresql

===== Ellenőrzés =====

Telepítés után ellenőrizzük a foglalt TCP portokat:
<code>
$ ss -lt
State      Recv-Q    Send-Q     Local Address:Port       Peer Address:Port
LISTEN     0         128        127.0.0.1:postgresql     0.0.0.0:*
LISTEN     0         128        [::1]:postgresql         [::]:*
</code>


<code>
$ pstree -c
systemd
   └─postgres
      ├─postgres
      ├─postgres
      ├─postgres
      ├─postgres
      ├─postgres
      └─postgres     
</code>


<code>
sudo -u postgres psql -c "SELECT version();"
</code>

Kérdezzük le a státuszt:
<code>
# systemctl status postgresql
</code>

===== A PostgreSQL vezérlése =====

Leállítás:
<code>
# systemctl stop postgresql
</code>

Indítás:
<code>
# systemctl start postgresql
</code>

Újraindítás:
<code>
# systemctl restart postgresql
</code>

Státusz:
<code>
# systemctl status postgresql
</code>


===== A postgres felhasználó =====

Telepítés után létrejön egy postgres felhasználó.
Rendszergazdaként sem férünk hozzá az adatbázis rendszerhez,
csak a postgres felhasználón keresztül.

Rendszergazdaként váljunk váljunk posgres felhasználóvá,
ha kapcsolódni akarunk:
<code>
# su - postgres
</code>

Ha van sudo felhasználónk, akkor így is válthatunk felhasználót:
<code>
$ sudo su - postgres
</code>

===== Parancssor =====

A PostgreSQL adatbázishoz a psql klienssel kapcsolódhatunk. 
Váltsunk postgres felhaszálóra, majd indítsuk el a klienst:

<code>
# su - postgres
$ psql
psql (11.5 (Debian 11.5-1+deb10u1))
Type "help" for help.

postgres=#
</code>

Kilépés:
<code>
postgres=# \q
</code>

==== Felhasználó és adatbázis ====
pstgres felhasználóként:
<code>
$ createuser mari
$ createdb maridb
</code>

Más felhasználóként:
<code>
sudo su - postgres -c "createuser mari"
sudo su - postgres -c "createdb maridb"
</code>

Adjunk meg minden jogot:
<code postgresql>
postgres=# grant all privileges on database maridb to mari;
</code>

<code postgresql>
alter user mari password 'titok';
</code>



Jelszóval:
<code postgresql>
create user myuser with encrypted password 'mypass';
</code>

===== Adatbázisok =====

Adatbázis létrehozása:
<code>
postgres=# create database test_db;
</code>


Adatbázisok listázása:
<code>
\l
</code>


<code>
select datname from pg_database;
</code>

==== Kapcsolódás ====

<code>
# su - postgres
$ psql
postgres=# \c zolzrt
You are now connected to database "zoldzrt" as user "postgres".
zoldzrt=#
</code>

==== Táblázat ====

Listázás:
<code>
zoldzrt=# \dt
Did not find any relations.
zoldzrt=#
</code>

Létrehozás:
<code>
zoldzrt=# create table dolgozok (
zoldzrt(# id integer primary key,
zoldzrt(# name varchar,
zoldzrt(# city varchar);
CREATE TABLE
</code>

===== Kliensek hitelesítése =====
Az adatbázis rendszerhez alapértelmezetten csak
a postgres linuxos felhasználó fér hozzá. 

Ha szeretnénk más felhasználók számára is engedélyezni
a hozzáférést a következőket állítsuk be:

<code>
# nano /etc/postgresql/11/main/pg_hba.conf 
</code>

<code>
# "local" is for Unix domain socket connections only
local   all          all                   md5
</code>

Indítsuk újra a szolgáltatást:
<code>
# systemctl restart postgresql
</code>


==== Felhasználók ====

<code>
postgres=# CREATE USER kati PASSWORD ‘titok’; 
</code>


<code>
$ psql -d  katidb  -U kati
</code>
===== Biztonság =====
Ha szeretnék fokozni a védelmet a psql 
jelszavazásával:

<code>
# su - postgres
# psql -c "ALTER USER postgres WITH PASSWORD 'titok';"
</code>

===== Távoli hozzáférés =====

A távoli hozzáférés alapértelmezetten tiltva van.
Szerkesszük a postgresql.conf állományt:
<code>
# nano /etc/postgresql/11/main/postgresql.conf
</code>

Keressük meg a CONNECTIONS AND AUTHENTICATION részt,
és állítsuk be a listen_addresses tulajdonságot:

<code>
#-----------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#-----------------------------------------------------

# - Connection Settings -

listen_addresses = '*'
</code>



  nano /etc/postgresql/15/main/pg_hba.conf


Keressük meg a következő sort:
  host   all     all       127.0.0.1/32   scram-sha-256

Cseréljük erre:
  host   all     all       0.0.0.0/0      scram-sha-256



<code>
# systemctl restart postgresql
</code>

===== Backup/Restore =====
==== Mentés ====


Az egész adatbázis mentése:

<code>
# su - postgres
$ pg_dumpall > pg_backup.sql
</code>

Egy adatbázis mentése:
<code>
$ pg_dump zoldzrt > zoldzrt.sql
</code>
==== Visszaállítás ====

Visszaállítás vagy restore:

<code>
psql uresdb < cegdb.bak
</code>

A parancs nem hozza létre az adatbázist, annak már léteznie kell.

<code>
createdb -T template0 uresdb
psql uresdb < mentes.bak
</code>

Az adatbázis visszaállítás előtt, ha volt egy felhasználó
akinek a tulajdonában volt, azt is vegyük fel:

<code>
createuser valaki
psql uresdb < mentes.bak
</code>

A psql hiba esetén folytatja a helyreállítást és csak a 
folyamat végén írja ki milyen hibák voltak. 
Ha bekapcsoljuk az ON_ERROR_STOP tulajdonságot, akkor
a hibát azonnal a képernyőre írja. 

<code>
psql --set ON_ERROR_STOP=on uresdb < mentes.bak
</code>


Másik lehetőség, ha a helyreállítást egyetlen tranzakcióként
szeretnénk végrehajtani. Így ha hiba van egyáltalán nem történik
semmi:

<code>
psql -1 uresdb < mentes.bak
</code>

<code>
psql --single-transaction uresdb < mentes.bak
</code>

==== Teljes mentés visszaállítása ====

<code>
psql -f teljesMentes.bak postgres
</code>

==== Távoli szerver mentése ====


<code>
pg_dump -h gépnév -p port dbnev > mentes.sql
</code>

<code>
pg_dump -U usernev -h gépnév -p port dbnev > mentes.sql
</code>