PostgreSQL

Aufgrund der veralteten Versionen in den Standard-Repos empfiehlt sich die Installation von PostgreSQL aus dem Hersteller-Repo.

  • pgsql: Abkürzung für PostgreSQL(-Server)

  • postgres: der Linux-Benutzer, unter dem der PostgreSQL-Server läuft

  • psql: der Kommandozeilen-Client für die Abfrage des PostgreSQL-Servers

Versionen:
  • RHEL 6. PostgreSQL 8.4 (bereits EOL seit 2003-10)

  • RHEL 7: PostgreSQL 9.2 (bereits EOL seit 2017-11)

  • RHEL 8: PostgreSQL 10.6 (EOL 2022-11)

Offiziell verfügbar und maintained sind Stand 2022-03:

  • PostgreSQL 14.2

  • PostgreSQL 13.6

  • PostgreSQL 12.10

  • PostgreSQL 11.15

  • PostgreSQL 10.20

Installation & Konfiguration

Installation direkt aus den RHEL-Repos:

yum -y install postgresql-server

Installation aus dem Hersteller-Repo:

Hier für RHEL 8 und PostgreSQL 14 (womit Stand 2022-03 die Version 14.2 installiert wird):

EL=8
VER=14
yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$EL-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

# Install PostgreSQL:
sudo dnf -y install postgresql$VER-server

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-$VER/bin/postgresql-$VER-setup initdb
sudo systemctl enable --now postgresql-$VER

Tipp

Wer eine spezifische Version wie z.B. v 11.2 einsetzen muss, lässt sich mit dnf list postgresql11-server --showduplicates zunächst anzeigen, welche Minor-Versionen verfügbar sind. Die Installation erfolgt dann per dnf -y install postgresql11-server-11.2-2PGDG.rhel7.

WICHTIG: da hier eine spezifische, tiefere Version installiert wird, würde diese beim nächsten dnf update aktualisiert werden. Um das zu verhindern, muss die installierte Version per dnf -y install dnf-plugin-versionlock; dnf versionlock postgresql11* gelockt werden.

Port-Freigabe auf der Firewall:

firewall-cmd --permanent --add-service=postgresql
firewall-cmd --reload

Unter dem System-Benutzer „postgres“ mit Hilfe des Befehls psql das Passwort des DBA namens „postgres“ setzen:

sudo -u postgres psql postgres
\password postgres
\q

Einen weiteren Superuser/Admin-Account anlegen:

sudo -u postgres psql postgres
# You have to use single quotes around the password. Double quotes don't work.
CREATE ROLE "username" WITH LOGIN SUPERUSER PASSWORD 'password';

Möchte man nicht nur von Localhost, sondern aus dem eigenen und dem VPN-Netzwerk auf den PostgreSQL-Server zugreifen, kann das wie folgt konfiguriert werden:

/var/lib/pgsql/data/pg_hba.conf
#type   db   user  address       auth-method
local   all  all                 peer
host    all  all   127.0.0.1/32  trust
# VPN network:
host    all  all   10.99.0.0/24  scram-sha-256

Ist PostgreSQL ausschliesslich über SSH (auch über mehrere SSH-Hops) erreichbar, sollte man folgende Zeile ersetzen, damit man sich auf den „localhost“ SSH-Tunnel verbinden kann:

/var/lib/pgsql/data/pg_hba.conf
#host all all ::1/128 ident
host  all all ::1/128 scram-sha-256

Damit PostgreSQL auf jeder Netzwerk-Adresse lauscht:

/var/lib/pgsql/data/postgresql.conf
listen_addresses='*'

PostgreSQL restarten oder reloaden:

systemctl restart postgresql
systemctl restart postgresql-9.4

pg_ctl reload

Authentifizierung

Authentifizierungsmethoden

  • trust
    Die Authentifizierungs-Methode „trust“ erlaubt die Nutzung des PostgreSQL-Servers über einen lokalen UNIX-Socket, ganz ohne Passwort.
  • scram-sha-256
    Passwort-Authentifizierung, scram-sha-256-encrypted.
  • password
    Passwort-Authentifizierung, wird im Klartext gesendet.
  • peer
    Verwendet den OS Username als Database Username, optional mit Mapping-Funktion.
/var/lib/pgsql/data/pg_hba.conf
# Don't require a password for local connections
#local  all  all                 peer
local   all  all                 trust

DB-Benutzer & Berechtigungen

Benutzer anlegen und Berechtigungen für die Datenbank „mydb“ vergeben:

sudo -u postgres psql postgres
CREATE ROLE myuser LOGIN PASSWORD 'mypass' SUPERUSER VALID UNTIL 'infinity';
CREATE DATABASE mydb WITH ENCODING='UTF8' OWNER=myuser CONNECTION LIMIT=-1;
\q

Enthält der Benutzername einen Bindestrich / Dash / Hyphen, muss er in doppelte Anführungszeichen gesetzt werden.

Backup

Die Backup-Skripte sollten unter dem postgres-Benutzer laufen. Falls nicht möglich, müssen die Zugriffsrechte angepasst werden:

/var/lib/pgsql/data/pg_hba.conf
local all all trust

Einfaches Backup einer Datenbank

So exportiert man eine Datenbank in eine grosse Datei:

sudo -u postgres pg_dump $DB > $DB.sql

Backup per Skript - Strukturen und Daten getrennt

Wer Strukturen sowie Daten für jede Tabelle getrennt in eigene Dateien exportieren möchte, kann folgendes Skript verwenden. Aber Achtung: Bei Datenbanken, die mit Constraints arbeiten, kann diese Methode zu Problemen führen. Für solche Fälle gibt es noch die weiter unten aufgeführte Fulldump-Variante.

postgresql-dump
 1#!/usr/bin/env bash
 2
 3USER="postgres"
 4BACKUP_DIR="/backup/postgres-dump"
 5
 6
 7log() {
 8   logger --priority $1 --tag $(basename $0) $2
 9   echo $2
10}
11
12
13mkdir -p $BACKUP_DIR
14
15# Detect paths
16PSQL=$(which psql)
17PG_DUMP=$(which pg_dump)
18
19# make sure we can connect to server
20DBS=$($PSQL --username=$USER --tuples-only --command="select datname from pg_database;")
21if [ $? -ne 0 ]
22then
23   echo "Cannot connect to PostgreSQL with user '$USER'."
24   exit 1
25fi
26
27# make sure there are databases
28if [ "$DBS" == "" ]
29then
30   echo "No databases found."
31   exit 2
32fi
33
34
35# let us do it
36for DB in $DBS
37do
38   case $DB in
39   template* )
40       # do nothing
41       ;;
42   postgres )
43       # do nothing
44       ;;
45   *)
46       $PG_DUMP $DB --username=$USER --schema-only > $BACKUP_DIR/structure-$DB.sql
47       log "local5.debug" "Database structure for $DB dumped."
48       gzip -9fn $BACKUP_DIR/structure-$DB.sql
49       TABLES=$($PSQL --username=postgres --dbname=$DB --no-align --tuples-only --command="\dt" | cut -d '|' -f2 )
50       for TABLE in $TABLES
51       do
52           $PG_DUMP $DB --username=$USER --table=$TABLE --format=p --data-only > $BACKUP_DIR/data-$DB-$TABLE.sql
53           log "local5.debug" "Table $TABLE in DB $DB dumped."
54           gzip -9fn $BACKUP_DIR/data-$DB-$TABLE.sql
55       done
56   esac
57
58done

Backup per Skript - FullDump-Methode (bevorzugt)

postgresql-dump
 1#!/usr/bin/env bash
 2
 3USER='postgres'
 4BACKUP_DIR='/backup/postgres-dump'
 5
 6log() {
 7    logger --priority $1 --tag $(basename $0) $2
 8    echo $2
 9}
10
11
12mkdir -p $BACKUP_DIR
13
14# Detect paths
15PSQL=$(which psql)
16PG_DUMP=$(which pg_dump)
17
18# make sure we can connect to server
19DBS=$($PSQL --username=$USER --tuples-only --command='select datname from pg_database;')
20if [ $? -ne 0 ]
21then
22    echo "Cannot connect to PostgreSQL with user '$USER'."
23    exit 1
24fi
25
26# make sure there are databases
27if [ "$DBS" == '' ]
28then
29    echo 'No databases found.'
30    exit 2
31fi
32
33
34# let us do it
35for DB in $DBS
36do
37    case $DB in
38    template* )
39        # do nothing
40        ;;
41    postgres )
42        # do nothing
43        ;;
44    *)
45        $PG_DUMP --username=$USER --clean --file=$BACKUP_DIR/dump-$DB.sql $DB
46        log 'local5.debug' "Database for $DB dumped."
47        gzip -9fn $BACKUP_DIR/dump-$DB.sql
48    esac
49
50done

Restore

pg_restore --dbname $DB $DB.sql

Wer mit binären PostgreSQL-Dumps zu tun hat, und diese in eine Plain-SQL-Datei umwandeln möchte, verwendet:

pg_restore --file=mydatabase.sql mydatabase.dump

Logging

PostgreSQL teilt die Logfiles in Wochentage auf.

tail -f /var/lib/pgsql/data/pg_log/postgresql-Wed.log

Wer Zugriffe auf die Datenbanken protokollieren möchte, ändert die postgresql.conf wie folgt:

/var/lib/pgsql/data/postgresql.conf
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%t <Host: %h; DB: %d; User: %u; Cmd: %i> '
log_statement = 'all'

Management-Tools

psql

psql ist ein Terminal-basiertes Frontend zu PostgreSQL. Damit lassen sich Queries interaktiv eingeben, an PostgreSQL übergeben und die Ergebnisse anschauen. Die Eingaben können alternativ auch aus einer Datei stammen oder per Kommandozeilen-Argument übergeben werden. Darüber hinaus beherrscht psql diverse Meta-Kommandos und Shell-ähnliche Funktionen, um Skripting und Automatisierung zu ermöglichen.

Siehe auch https://www.postgresql.org/docs/9.3/app-psql.html

Daten aus SQL-Datei „myfile.sql“ importieren:

sudo -u postgres psql --username=myuser --host=$HOST $DB
\i myfile.sql
\q

Ausführung eines interaktiven SQL-Kommandos:

sudo -u postgres psql --username=myuser --tuples-only --command="select mycol from mydb;"

Nicht-interaktiver Aufruf, Ausführen einer SQL-Datei:

sudo -u postgres psql --username=myuser --password --host=localhost --dbname=mydb --file=myfile.sql

Ein paar typische SQL-Befehle:

# list all users
\du

# list all databases (and templates)
\list
\l

# create database "mydb"
create database mydb;

# list some data from "mydb"
select mycol from mydb;

# list the number of connections to "mydb"
select datname,numbackends from pg_stat_database where datname='mydb';

# change owner of database
alter database mydb owner to new_owner;

# delete "mydb"
drop database mydb;

# connect to database "mydb"
\c mydb

# connect to database "mydb" as role "myrole"
\c mydb myrole

# list tables after connecting
\dt

# go back and connect to the default postgres database (there is no disconnect)
\c postgres

# quit the session
\q

Die Verwendung von psql in einem Shell-Script ist nur möglich, wenn ein Login ohne Passwort per pg_hba.conf oder .pgpass eingerichtet wird:

~/.pgpass
#hostname:port:database:username:password
127.0.0.1:5432:*:mypass
chmod 0600 .pgpass

…oder:

/var/lib/pgsql/data/pg_hba.conf
...
#type   db   user  address       auth-method
host    all  all   127.0.0.1/32  trust
...

Beispiel für ein Bash-Skript, welches psql verwendet:

#!/usr/bin/env bash

DB="mydb"
USER="myuser"

psql $DB $USER << EOF
\list
EOF

pgAdmin

Die erste Wahl unter den GUI-Administrationstools. Installation unter Fedora mittels:

rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-fedora-repo-2-1.noarch.rpm
dnf -y install pgadmin4-desktop
../_images/pgadmin3.png

pgAdmin III

Adminer

Siehe Adminer.

phpPgAdmin

Webbasiertes Admin-Tool, analog zu phpMyAdmin für MySQL/MariaDB. Setzt einen Webserver mit PHP voraus.

yum -y install phpPgAdmin
/etc/httpd/conf.d/phpPgAdmin.conf
Require ip 10.80
/etc/phpPgAdmin/config.inc.php
$conf['extra_login_security'] = false;
systemctl reload httpd

LibreOffice Base

Wer LibreOffice Base verwendet, muss bei einer Verbindung über JDBC die „Driver Class“ namens org.postgresql.Driver angeben. Wer auf den JDBC-Treiber verzichten möchte, installiert und verwendet den nativen:

yum -y install libreoffice-postgresql

Der Connection-String für die native Verbindungsmethode lautet:

dbname=postgres hostaddr=$HOST port=5432

Built on 2022-06-03