MBCDN

Dies ist eine alte Version des Dokuments!

PostgreSQL ist eine objekt-relationale Datenbankanwendung.

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;

Passwort ändern

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'DEIN_SICHERES_PASSWORT';"

Netzwerkzugriff

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

Listen Address

sudo nano /etc/postgresql/*/main/postgresql.conf  # Debian/Ubuntu
# oder
sudo nano /var/lib/pgsql/data/postgresql.conf     # RHEL/CentOS

listen_addresses = '*'

Trusted Networks

sudo nano /etc/postgresql/*/main/pg_hba.conf  # Debian/Ubuntu
# oder
sudo nano /var/lib/pgsql/data/pg_hba.conf     # RHEL/CentOS

host    all             all             192.168.0.0/24         md5

Datenbank und Benutzer anlegen

CREATE DATABASE meine_datenbank;
CREATE USER mein_benutzer WITH ENCRYPTED PASSWORD 'geheimes_passwort';
GRANT ALL PRIVILEGES ON DATABASE meine_datenbank TO mein_benutzer;