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 2023-04:

PostgreSQL 15.2
PostgreSQL 14.7
PostgreSQL 13.10
PostgreSQL 12.14
PostgreSQL 11.19

Installation & Konfiguration

Installation direkt aus den RHEL-Repos:

yum -y install postgresql-server
/usr/bin/postgresql-setup initdb

Installation aus dem Hersteller-Repo:

Hier für RHEL 8 und PostgreSQL 14:

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

Hier für RHEL 7 und PostgreSQL 12:

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

# Install PostgreSQL:
sudo yum -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 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

#!/usr/bin/env bash

USERNAME="postgres"
BACKUP_DIR="/backup/postgres-dump"


log() {
   logger --priority $1 --tag $(basename $0) $2
   echo $2
}


mkdir -p $BACKUP_DIR

# Detect paths
PSQL=$(which psql)
PG_DUMP=$(which pg_dump)

# make sure we can connect to server
DBS=$($PSQL --username=$USERNAME --tuples-only --command="select datname from pg_database;")
if [ $? -ne 0 ]
then
   echo "Cannot connect to PostgreSQL with user '$USERNAME'."
   exit 1
fi

# make sure there are databases
if [ "$DBS" == "" ]
then
   echo "No databases found."
   exit 2
fi


# let us do it
for DB in $DBS
do
   case $DB in
   template* )
       # do nothing
       ;;
   postgres )
       # do nothing
       ;;
   *)
       $PG_DUMP $DB --username=$USERNAME --schema-only > $BACKUP_DIR/structure-$DB.sql
       log "local5.debug" "Database structure for $DB dumped."
       gzip -9fn $BACKUP_DIR/structure-$DB.sql
       TABLES=$($PSQL --username=postgres --dbname=$DB --no-align --tuples-only --command="\dt" | cut -d '|' -f2 )
       for TABLE in $TABLES
       do
           $PG_DUMP $DB --username=$USERNAME --table=$TABLE --format=p --data-only > $BACKUP_DIR/data-$DB-$TABLE.sql
           log "local5.debug" "Table $TABLE in DB $DB dumped."
           gzip -9fn $BACKUP_DIR/data-$DB-$TABLE.sql
       done
   esac

done

Backup per Skript - FullDump-Methode (bevorzugt)

postgresql-dump

#!/usr/bin/env bash

USERNAME='postgres'
BACKUP_DIR='/backup/postgres-dump'

log() {
    logger --priority $1 --tag $(basename $0) $2
    echo $2
}


mkdir -p $BACKUP_DIR

# Detect paths
PSQL=$(which psql)
PG_DUMP=$(which pg_dump)

# make sure we can connect to server
DBS=$($PSQL --username=$USERNAME --tuples-only --command='select datname from pg_database;')
if [ $? -ne 0 ]
then
    echo "Cannot connect to PostgreSQL with user '$USERNAME'."
    exit 1
fi

# make sure there are databases
if [ "$DBS" == '' ]
then
    echo 'No databases found.'
    exit 2
fi


# let us do it
for DB in $DBS
do
    case $DB in
    template* )
        # do nothing
        ;;
    postgres )
        # do nothing
        ;;
    *)
        $PG_DUMP --username=$USERNAME --clean --file=$BACKUP_DIR/dump-$DB.sql $DB
        log 'local5.debug' "Database for $DB dumped."
        gzip -9fn $BACKUP_DIR/dump-$DB.sql
    esac

done

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'

Upgrading

Siehe https://www.postgresql.org/docs/current/pgupgrade.html.

OLDVER=9.6
NEWVER=11

yum install postgresql$NEWVER-server
/usr/pgsql-$NEWVER/bin/postgresql-$NEWVER-setup initdb

sudo -i -u postgres
/usr/pgsql-$NEWVER/bin/pg_upgrade --check --old-bindir=/usr/pgsql-$OLDVER/bin/ --new-bindir=/usr/pgsql-$NEWVER/bin/ --old-datadir=/var/lib/pgsql/$OLDVER/data/ --new-datadir=/var/lib/pgsql/$NEWVER/data/
# *Clusters are compatible*
exit

systemctl stop confluence
systemctl stop postgresql-$OLDVER

sudo -i -u postgres
/usr/pgsql-$NEWVER/bin/pg_upgrade --old-bindir=/usr/pgsql-$OLDVER/bin/ --new-bindir=/usr/pgsql-$NEWVER/bin/ --old-datadir=/var/lib/pgsql/$OLDVER/data/ --new-datadir=/var/lib/pgsql/$NEWVER/data/
# Upgrade Complete
# ----------------
# Optimizer statistics are not transferred by pg_upgrade so,
# once you start the new server, consider running:
#     ./analyze_new_cluster.sh
#
# Running this script will delete the old cluster's data files:
#     ./delete_old_cluster.sh
exit

# Änderungen in pg_hba.conf und postgresql.conf nachziehen
vimdiff /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/9.5/data/pg_hba.conf
vimdiff /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/9.5/data/postgresql.conf

systemctl start postgresql-$NEWVER

sudo -i -u postgres
./analyze_new_cluster.sh
exit

systemctl enable postgresql-$NEWVER
systemctl disable postgresql-$OLDVER

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"
USERNAME="myuser"

psql $DB $USERNAME << 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

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 2024-04-18