Heute wird ein kleiner Ausflug in die Datenbank Welt gemacht.
Grund dafür ist die neue Version der Open Source Datenbank PostgreSQL Version 9.4., welche Ende 2014 veröffentlicht wurde.
Neben zahlreichen Verbesserungen bietet diese mit "ALTER SYSTEM" die Möglichkeit die Systemkonfiguration direkt aus der SQL Konsole vorzunehmen.
Mit dieser Neuerung lassen sich alle Systemeinstellungen der postgresql.conf mit Hilfe einer postgresql.conf.auto überschreiben.
Die postgresql.auto.conf wird beim Start als letztes geladen und enthält die mit "ALTER SYSTEM" gesetzten Werte.
Zunächst ist aber etwas Vorarbeit notwendig.
Referenzsystem ist wie immer Ubuntu Server 14.04
Installieren des PostgreSQL Servers 9.4
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql
Einloggen in die PostgreSQL Konsole
Zunächst sollte das Passwort geändert werden
sudo -u postgres psql postgres
postgres=#
\password
Enter new password:
Enter it again:
\q
Erfolgt ein Login nicht direkt auf dem Localhost, sind weitere Konfiguration notwendig
Die Konfiguration der Zugriffsrechte erfolgen unter "/etc/postgresql/9.4/main/pg_hba.conf"
Verbindungsinformationen anzeigen
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
Anlegen eines neuen Benutzers mit Superuser Rechten
Dieser Schritt dient der Übersicht, für die Verwendung des SYSTEM ALTER Befehls kann auch der Standardbenutzer "postgres" verwendet werden.
Superuser Rechte sind aber zwingend notwendig.
sudo -u postgres createuser -s -D -P yolo
Erklärung der Befehle
- -D, --no-createdb role cannot create databases (default)
- -P, --pwprompt assign a password to new role
- -s, --superuser role will be superuser
Alternativ kann ein Benutzer auch interaktiv angelegt werden
sudo -u postgres createuser --interactive yolo2
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
Datenbank in PostgreSQL anlegen
Auch dieser Schritt dient nur der Übersicht. Für die Systemkonfiguration muss dies vorgenommen werden.
sudo -u postgres createdb -O yolo datenbank
Erklärung des Befehls
- -O, --owner=OWNER database user to own the new database
Eingerichtete Benutzer und Rechte anzeigen
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication | {}
yolo | Superuser | {}
PostgreSQL mit ALTER SYSTEM konfigurieren
Nach ein paar Grundlagen nun der Schritt zur Konfiuration via ALTER SYSTEM
Im Folgenden möchte ich ein paar Werte setzen, die bei einer Grundinstallation gerne anfallen.
Als Grundlage habe ich pgTune genommen (siehe Artikel)
Folgende Werte sollen mit SQL Kommandos angepasst werden
max_connections = 200
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 2621kB
maintenance_work_mem = 128MB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
wal_buffers = 16MB
Zunächst muss ein Login erfolgen, danach können Befehle mit der Kombination ALTER SYSTEM SET abgesetzt werden.
sudo -u postgres psql postgres
postgres=# ALTER SYSTEM SET max_connections TO 200;
postgres=# ALTER SYSTEM SET shared_buffers TO 512;
postgres=# ALTER SYSTEM SET effective_cache_size = '1536MB';
postgres=# ALTER SYSTEM SET work_mem = '2621kB';
postgres=# ALTER SYSTEM SET checkpoint_segments TO 64;
postgres=# ALTER SYSTEM SET wal_buffers ='16MB';
postgres=# ALTER SYSTEM SET checkpoint_completion_target TO 0.9;
postgres=# ALTER SYSTEM SET wal_buffers = '16MB';
Neuladen der Konfiguration
Wichtig: Die Änderungen werden erst wirksam, sobald die Konfiguration neu eingelesen wurde.
Die Konfiguration lässt sich mit dem folgenden Kommando neu einlesen, ohne das ein Datenbankverbindung verloren geht.
postgres=# SELECT pg_reload_conf();
Hier ist zu bedenken das manche Einstellungen, wie z.B. "max_connections" einen richtigen Server Neustart benötigen, bevor sie greifen
sudo service postgres restart
Kontrolle der vorhanden Werte
Gesetzte Konfigurationen lassen sich ebenfalls schnell und einfach per SQL Kommando auslesen. Der Aufruf vor und nach einer Änderung bietet sich an.
postgres=# SHOW shared_buffers;
shared_buffers
----------------
400MB
(1 row)
Einzelne Systemwerte auf Standard zurücksetzen
postgres=# SHOW checkpoint_completion_target;
checkpoint_completion_target
------------------------------
0.9
(1 row)
postgres=# ALTER SYSTEM SET checkpoint_completion_target TO DEFAULT;
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# SHOW checkpoint_completion_target;
checkpoint_completion_target
------------------------------
0.5
(1 row)
Alle Werte der postgresql.auto.conf zurücksetzen
postgres=# ALTER SYSTEM RESET ALL;
Wie bereits oben erwähnt, werden die gesetzten Konfigurationen in eine postgresql.auto.conf geschrieben.
Diese befindet sich nicht im Verzeichnis der postgresql.conf sondern unter
nano /var/lib/postgresql/9.4/main/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
wal_level = 'hot_standby'
shared_buffers = '512'
max_connections = '200'
effective_cache_size = '1536MB'
work_mem = '2621kB'
checkpoint_segments = '64'
wal_buffers = '16MB'
Troubleshooting
Im Log taucht folgende Meldung auf:
LOG: parameter "wal_buffers" cannot be changed without restarting the server
LOG: configuration file "/var/lib/postgresql/9.4/main/postgresql.auto.conf" contains errors; unaffected changes were applied
Wie die Fehlermeldung selbst erklärt, ist ein Neustart des Servers mit "sudo service postgresql restart" notwendig.
Fazit
Die neue Möglichkeit in 9.4 ein System im laufenden Betrieb zu konfigurieren, ist wirklich praktisch, besonders für diejenigen die eine SQL Konsole ihre Heimat nennen. Dennoch ist Vorsicht geboten, denn auf den ersten Blick ist nicht ersichtlich, ob eine postgresql.auto.conf geladen wird und Konfigurationen in der postgresql.conf nicht beachtet werden.
