Postgre[[sql|SQL]] ist eine objekt-relationale [[Datenbank]]anwendung.

Aus Standardpaketquelle
<code>
user@linux:# apt install postgresql
user@linux:# systemctl start postgresql
user@linux:# sudo -u postgres psql
psql (12.12 (Ubuntu 12.12-0ubuntu0.20.04.1))
Type "help" for help.

postgres=#
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=#
postgres=#
postgres=#
postgres=#CREATE DATABASE test;
CREATE DATABASE
postgres=#
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 test    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# SELECT t1.spalte1, t2.spalte2 FROM tabelle1 AS t1 JOIN tabelle2 AS t2 ON t1.id = t2.id;
</code>

Bestimmte Version

<code>
sudo apt install curl ca-certificates gnupg -y
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql.gpg > /dev/null
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

apt update

apt-cache search postgresql | grep ^postgresql-[0-9]

apt install postgresql-16 -y
</code>

=====Passwort ändern=====

<code bash>
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'DEIN_SICHERES_PASSWORT';"
</code>

=====Netzwerkzugriff=====

<code>
psql -h <IP-ODER-HOSTNAME> -U mein_benutzer -d meine_datenbank
</code>

====Listen Address====
<code bash>
sudo nano /etc/postgresql/*/main/postgresql.conf  # Debian/Ubuntu
# oder
sudo nano /var/lib/pgsql/data/postgresql.conf     # RHEL/CentOS
</code>

<code>
listen_addresses = '*'
</code>

====Trusted Networks====
<code bash>
sudo nano /etc/postgresql/*/main/pg_hba.conf  # Debian/Ubuntu
# oder
sudo nano /var/lib/pgsql/data/pg_hba.conf     # RHEL/CentOS
</code>

<code>
host    all             all             192.168.0.0/24         md5
</code>

=====Datenbank und Benutzer anlegen=====

<code postgresql>
CREATE DATABASE meine_datenbank;
CREATE USER mein_benutzer WITH ENCRYPTED PASSWORD 'geheimes_passwort';
GRANT ALL PRIVILEGES ON DATABASE meine_datenbank TO mein_benutzer;
</code>