PostgreSQL - Datenbank > PSQL - Tips zur Nutzung und Konfiguration

PostgreSQL - First tips for using psql

Starting psql

You may start psql fromyour current account via: sudo -u postgres psql

Connect to database <dbname>

Within psql you may execute "\c <dbname>" to aswitch to the specific database

Show the table structure of table <tablename>

Within psql you may execute "\dn <tablename>" to show the structure of a specific table

Show all available tables within the connected database

Within psql you may execute "\d " to show all available tables

Show the size of the whole database <dbname>

Within psql you may execute "select pg_size_pretty( pg_database_size(<dbname>));"

Show the size of a spexcific <tablename> within the database <dbname>

Within psql you may execute "SELECT pg_size_pretty( pg_total_relation_size(<tablename>) );

Change the password of an user

Within psql you may execute "ALTER USER postgres PASSWORD '<new-password>';;"

PostgreSQL - Öffnung für lokale NAT basierende VMs

Wenn die VMs Zugriff auf die im host laufende PostgreSQL haben sollen und man möchte die PostgreSQL nicht öffentlich machen, dann macht man folgendes (laut claude):

Da die VMs hinter NAT sind, erreichen sie den Host über die Gateway-IP des NAT-Netzes — das ist standardmäßig 192.168.122.1.

PostgreSQL auf dem Host so konfigurieren dass sie nur auf dieser Adresse lauscht:

/etc/postgresql/xx/main/postgresql.conf:

listen_addresses = '127.0.0.1,192.168.122.1'

/etc/postgresql/xx/main/pg_hba.conf:

host mydb myuser 192.168.122.0/24 scram-sha-256

Dann PostgreSQL neu starten:

bashsudo systemctl restart postgresql

Von der VM aus verbinden:

bashpsql -h 192.168.122.1 -U myuser -d mydb

PostgreSQL ist dann nur für das interne NAT-Netz erreichbar — von außen nicht sichtbar, kein Port nach außen offen.

PostgreSQL - Öffnung für externen Zugriff

Unter /etc/postgresql/14/main findet man die Konfigurationsdateien des Datenbankservers. ("main" ist der initiale Cluster-Name)

Zuerst einmal muß man den Server dazu anhalten, nicht nur unter "localhost" erreichbar zu sein, sondern auch über das lokale Netzwerk. Dazu öffnet man die Textdatei "postgresql.conf" und editiert den Eintrag "listen address" auf z.B. "*" oder auf die spezifische IP-Adresse der gewünschten Verbindung. Nach den Änderungen muß man den DB Server einmal neu starten ("sudo service postgresql restart"):

#listen_addresses = 'localhost' # what IP address(es) to listen on;

# comma-separated list of addresses;

# defaults to 'localhost'; use '*' for all

# (change requires restart)

Dann möchte man den Zugang für bestimmte Personen ermöglichen. Dies trägt man in "pg_hba.conf" ein:

# Database administrative login by Unix domain socket

local all postgres peer

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all peer

# IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256

# IPv6 local connections:

host all all ::1/128 scram-sha-256

# Allow replication connections from localhost, by a user with the

# replication privilege.

local replication all peer

host replication all 127.0.0.1/32 scram-sha-256

host replication all ::1/128 scram-sha-256

# Dies ist ein Eintrag für einen einzelnen Rechner

host all all 192.168.178.1/32 md5

# Dies ist ein Eintrag für das Subnetz 192.168.178.0 mit der Netzwerkmaske 255.255.255.0

host all all 192.168.178.0/24 md5

Den Ausdruck unter der "ADDRESS"-Spalte kann man mittels des Befehles "ip address" erhalten. Die Netzwerkmaske ist unbedingt anzugeben. Nach den Änderungen muß die Datenbank diese neuen Konfigurationsdaten neu laden: "sudo pg_ctlcluster 14 main reload".

Ausprobieren kann man das mittels:

psql -U postgres -p 5432 -h hostname