Artikel mit Tag postgresql

PGCenter - PostgreSQL Datenbank Statistiken und Leistungsdaten auf einen Blick

PostgreSQL ist die Tage in Version 9.5 erschienen (siehe Artikel). 
Egal ob neues oder altes DB System, eine Analyse der Performance und Auswertung von Zugriffszeiten- oder daten, usw. ist bei vielen Anwendungsszenarien unerlässlich.

PGCenter

Eine Auswertungsmöglichkeit bringt PostgreSQL selbst mit. Unter dem Prefix "pg_stats" stehen verschiedene Mechanismen für Auswertungen zur Verfügung.

Weiter geht PGCenter. Es bündelt diese Auswertungen in einer Ausgabe, die der Prozessüberwachung top ähnelt. Dies sorgt für eine schnelle Übersicht und bringt zusätzlich die Möglichkeit selbst Aktionen auszuführen.

Die Hauptfunktionen von PGCenter werden wie psql Befehle ausgeführt und bieten momentan folgende Möglichkeiten:

postgresql

  • top-like interface
  • use same connection options as with psql
  • show current system load and cpu/memory/swap usage
  • show input/output statistics for devices and partitions like iostat
  • show network traffic statistics for network interfaces like nicstat
  • show current postgres state (connections state, longest transaction, autovacuum)
  • show statistics about tables, indexes, functions, activity, replication
  • show pg_stat_statements statistics: calls, rows
  • show pg_stat_statements statistics: cpu timings, io timings
  • show pg_stat_statements statistics: blks i/o (hits, reads, dirtied, written, temp)
  • show relations sizes info
  • configuration files editor and postgres reload
  • log files viewing (full log or tail)
  • cancel/terminate queries or processes by pid or whole group
  • query reporting


Installation von PGCenter Statistik

Referenzsystem ist ein Ubuntu Server 14.04. Weitere Installationsarten werden auf der Homepage beschrieben.

sudo add-apt-repository ppa:lesovsky/pgcenter
sudo apt-get update
sudo apt-get install pgcenter

Benutzung von PGCenter

Wie bereits oben erwähnt, ist die Nutzung von PGCenter an psql Befehle angelehnt. Damit alle Daten ausgewertet werden können, muss ein superuser account wie "postgres" verwendet werden, ansonsten kann es zu Einschränkungen kommen

Eine Möglichkeit die PGCenter Konsole zu starten wäre beispielsweise

sudo -u postgres pgcenter

Hier wird das Tool direkt als Benutzer Postgres gestartet.

pgcenterpgcenter

Befehle für PGCenter

Eine interaktive Bedienung erfolgt mit folgenden Befehlen (Hilfe mit F1).


    general actions:
      a,d,i,f,r       mode: 'a' activity, 'd' databases, 'i' indexes, 'f' functions, 'r' replication,
      s,t,T           's' sizes, 't' tables, 'T' tables IO,
      x,X,c,v         'x' stmt timings, 'X' stmt general, 'c' stmt IO, 'v' stmt temp.
      Left,Right,/    'Left,Right' change column sort, '/' change sort desc/asc.
      C,E,R           config: 'C' show config, 'E' edit configs, 'R' reload config.
      p                       'p' start psql session.
      l               'l' open log file with pager.
      N,Ctrl+D,W      'N' add new connection, Ctrl+D close current connection, 'W' write connections info.
      1..8            switch between consoles.
    subscreen actions:
      B,I,L           'B' iostat, 'I' nicstat, 'L' logtail.
    activity actions:
      -,_             '-' cancel backend by pid, '_' terminate backend by pid.
      >,.             '>' set new mask, '.' show current mask.
      Del,Shift+Del   'Del' cancel backend group using mask, 'Shift+Del' terminate backend group using mask.
      A               change activity age threshold.
      G               get report about query using hash.

    other actions:
      , Q             ',' show system tables on/off, 'Q' reset postgresql statistics counters.
      z,Z             'z' set refresh interval, 'Z' change color scheme.
      space           pause program execution.
      F1              show help screen.
      q               quit.

Fazit

PGCenter bindet sich gut in die Umgebung ein und liefert viele wichtige Werte rund um einen PostgreSQL Datenbankbetrieb. Anwender, welche mit "top" arbeiten, werden pgcenter mögen.

https://github.com/lesovsky/pgcenter

PostgreSQL 9.5 - So gelingt die Installation des Datenbanksystems auf Ubuntu Server

Gestern ist eine neue PostgreSQL Version erschienen. Das offene Datenbanksystem bringt diverse Neuerungen mit sich.

So beherrscht PostgreSQL 9.5 unter anderem UPSERT. Dabei handelt es sich um eine Erweiterung des INSERT-Befehls mit ON CONFLICT UPDATE. Das heißt UPSERT erstellt einen neuen Datensatz oder ändert bereits vorhandene ohne zusätzliche Abfragen.

postgresql

Zugriffsrechte auf Zeilenebene können nun ebenfalls vergeben werden. (Row Level Security). Für die Big Data Zukunft sind ebenfalls einige Funktionen hinzugekommen. 

Ein detaillierter Überblick ist unter https://wiki.postgresql.org/wiki/PostgreSQL_9.5 zu finden.


PostgreSQL 9.5 unter Ubuntu, Mint oder ElementaryOS installieren

In den Standardrepositories des Ubuntu Server 14.04 ist momentan nur PostgreSQL 9.3 verfügbar. Darum muss bei einer Installation der Version 9.5 etwas Hand angelegt werden.

wget -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main 9.5" >> /etc/apt/sources.list.d/postgresql.list'

sudo apt-get update

sudo apt-get install postgresql-9.5

Eine Kontrolle der erfolgreichen Installation kann mit netstat gemacht werden. Hier sollte auf Port 5432 der Postgres Dienst laufen

netstat -lnp

Weitere erste Schritte können der Anleitung zu PostgreSQL 9.4 entnommen werden.


Das könnte die interessieren:

Praktische PostgreSQL Tools und Links in der Übersicht

Das Release von PostgreSQL 9.5 rückt immer näher, vor wenigen Tagen ist bereits der erste Release Candidate erschienen. Bis zur finalen Version möchte ich die Zeit mit einer kleinen, aber praktischen Linksammlung zum PostgreSQL Universum überbrücken.

postgresql

Auf Github gibt es einige Link- bzw. Toolsammlungen. (Ich hatte in der Vergangenheit schon auf eine Toolsammlung für Node.js verwiesen). Neben Serversystemen gibt es solche Listen auch für Datenbanksystem wie MySQL oder PostgreSQL.

Eine davon, bekannt unter Awesome-Postgres, bietet einen kleinen Überblick über freie Tools, Skripte oder Anleitungen rund um PostgreSQL und soll in dieser Sammlung einen ersten Einblick geben.

Die Liste wurde in Bereiche namens Backup, Gui oder Erweiterungen usw. unterteilt. Darunter finden sich beispielsweise Erweiterungen wie PostGIS, welches bei OpenStreetMap zum Einsatz kommt oder diverse Tools, um eine Datenbank über eine Oberfläche zu verwalten.

Als vollständig würde ich diese Übersicht nicht bezeichnen, da Programme wie beispielsweise PostgreSQL Studio (gerade erst in Version 2.0 erschienen) außen vor bleiben. 

Hier ein paar eigene Ergänzungen zu dieser Postgres Linksammlung.

Datenbank Verwaltung

Es gibt zu Postgres bereits bestehende Sammlungen, die ebenfalls einen Blick wert sind. Unter anderem der Community Guide to PostgreSQL GUI Toools, welcher alleine 33 OpenSource Tools zu Datenbank Verwaltung listet.

Erweiterungen

Neben den oben erwähnten Erweiterungen gibt es eine Vielzahl an Extensions für Postgres. Für eine Suche bietet sich das PostgreSQL Extension Network an. Dabei handelt es sich um ein Suchmaschine für Erweiterungen.

pgxn

Monitoring und Analyse

Auch im Bereich der Analyse und Überwachung ist der Blick auf die offizielle Seite sinnvoll, hier werden neben klassischen Auswertungen auch Plugins für Nagios bzw. Icinga oder Zabbix angeboten.

Handbücher und Tutorials

Für Einsteiger und als Grundlage bietet sich "Start mit PostgreSQL" an. Das freie Buch ist auf Deutsch vorhanden und bietet einen ersten Einblick in Datenbanksysteme. 

Ein guter Überlick ist zusätzlich in den offiziellen Manuals oder auf postgresguide.com zu finden.

Offene Fragen

Für Fragen gibt es ein deutschsprachiges Forum, in dem jeder seine offenen Fragen oder Antworten zu PostgreSQL veröffentlichen kann. 

Newsletter

Für E-Mail und RSS Freunde bietet es sich an den Newsletter Postgres Weekly zu abonnieren. Hier wird der Datenbank Freund regelmäßig über aktuelle Entwicklungen und Neuerungen zu PostgreSQL informiert.

Postgres_Weekly


Das könnte dich interessieren

22 praktische PostgreSQL Befehle

PostgreSQL 9.4 Server auf Ubuntu installieren und mit der neuen SQL Funktion ALTER SYSTEM konfigurieren

PgTune - Performance Einstellungen für PostgreSQL Datenbanken automatisch erstellen

Ubuntu - PostgreSQL Migration - Update eines 8.x Datenbank Servers auf 9.x

pgBadger 4.0 - PostgreSQL Logs analysieren und auswerten

22 praktische PostgreSQL Befehle

Zum Wochenende eine kleine Auflistung praktischer PostgreSQL Befehle.

postgresql

Zu PostgreSQL verbinden

In eine PostgreSQL Konsole Einloggen

sudo -u postgres psql

Einloggen und direkt zu einer Datenbank verbinden

sudo -u postgres psql -d datanbank


Arbeiten mit Konsolenbefehlen

Alle Datenbanken anzeigen

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

oder ohne vorherigen Login mit "sudo -u postgres psql -l"

Benutzer auflisten

postgres-# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 yolo      | Superuser                                      | {}

Zu einer Datenbank verbinden

postgres=#\c Datenbankname

You are now connected to database "Datenbankname" as user "postgres".

Tabellen anzeigen

postgres=#\dt 

Schemas anzeigen

postgres=#\dn

Funktionen anzeigen

postgres=#\df oder df+

Sequenzen anzeigen

postgres=#\ds

Views anzeigen

postgres=#\dv

Datenbank und Benutzer mit Rechten erstellen

CREATE datenbank;
CREATE USER benutzer WITH PASSWORD 'Geheim';  
GRANT ALL PRIVILEGES ON DATABASE datenbank to benutzer; 

Alle verfügbaren Konsolenbefehle anzeigen

postgres=#\?

Eine bestimmte Datenbankgröße auslesen

select pg_database_size('Datenbankname');

oder in schön

SELECT pg_size_pretty(pg_database_size('Datenbankname'));

oder alle vorhanden Datenbankgrößen anzeigen

select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database;


Offene Datenbankverbindungen anzeigen

SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

Die Konfiguration neu laden (ohne Neustart)

select pg_reload_conf();

Logfile Rotation anwerfen

select pg_rotate_logfile()

PostgreSQL verlassen

postgres=#\q

Backup und Wiederherstellung

Ein Datenbank Backup erstellen

pg_dump --user username --host hostname -b -F c datenbank > /tmp/datenbank.dump 

Alle verfügbaren Datenbanken sichern

pg_dumpall

Datenbank wiederherstellen

pg_restore --user username --host hostname -c -d datenbank datenbank.dump


Das könnte dich auch interessieren

PostgreSQL 9.4 Server auf Ubuntu installieren und mit der neuen SQL Funktion ALTER SYSTEM konfigurieren

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

postgresql


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.