PostgreSQL
Installation
root@zero:~# apt-get install postgresql
Bei der Installation über einen Paketmanager wird automatisch ein Datenbank-Cluster erzeugt.
Prüfen, ob der Server läuft:
root@zero:~# netstat -lntp | grep 5432 tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 5359/postgres
Verzeichnisstruktur
Konfigurationsdateien:
/etc/postgresql/*/main/*
Datenbank(en):
/var/lib/postgresql/*/main/
Logdatei(en):
/var/log/postgresql/
Kommandoverzeichnis für mitgelieferte Kommandos, die nicht im Pfad liegen:
/usr/lib/postgresql/*/bin
Starten und Stoppen
root@zero:~# /etc/init.d/postgresql* start
root@zero:~# /etc/init.d/postgresql* stop
Administration
Zugriff auf das Datenbanksystem
Bei der Installation von PostgreSQL wird ein Console-Client mitinstalliert. Da direkt nach der Installation ausser dem Superuser-Account noch keine Benutzer angelegt sind, kann man zu diesem Zeitpunkt nur als der PostgreSQL Superuser postgres auf das System zugreifen. Die Datenbank 'template1' ist eine Systemdatenbank und existiert immer. Dazu muss man zunächst auf den Systembenutzer postgres wechseln:
root@zero:~# su - postgres
Einfache Konsolenkomandos
Aufruf der psql-konsole und Verinden mit der Datenbank template1
postgres@zero:~$ psql template1 psql (9.1.8) Type "help" for help. template1=#
Listen der vorhandenen Datenbanken
template1=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
template0 | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
Verbinden mit der Datenbank postgres
template1-# \c postgres You are now connected to database "postgres" as user "postgres".
Hilfe bei SQL Komandos am Beispiel CREATE USER
tuxmendb=# \h CREATE USER
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
.........
Anlegen einer Datenbank
postgres=# CREATE DATABASE tuxmendb; CREATE DATABASE
Verbinden mit der Datenbank
postgres=# \c tuxmendb You are now connected to database "tuxmendb" as user "postgres".
Anlegen einer Tabelle
tuxmendb=# CREATE TABLE tuxmen_tab_one (tux_field_one varchar(12), tux_field_two int, tux_field_three boolean);
CREATE TABLE
tuxmendb=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | tuxmen_tab_one | table | postgres
(1 row)
User anlegen inklusive Passwort
tuxmendb=# CREATE USER tuxmen_adm PASSWORD 'geheim'; CREATE ROLE tuxmendb=# CREATE USER tuxmen_user PASSWORD 'secret'; CREATE ROLE
Passwort ändern
tuxmendb=# ALTER USER tuxmen_user PASSWORD '1234';
User anzeigen
tuxmendb=# SELECT * FRom pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig -------------+----------+-------------+----------+-----------+---------+----------+----------+----------- postgres | 10 | t | t | t | t | ******** | | tuxmen_adm | 16388 | f | f | f | f | ******** | | tuxmen_user | 16389 | f | f | f | f | ******** | | (3 rows)
Passwörter anzeigen
tuxmendb=# SELECT * FRom pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig
-------------+----------+-------------+----------+-----------+---------+-------------------------------------+----------+-----------
postgres | 10 | t | t | t | t | | |
tuxmen_adm | 16388 | f | f | f | f | md5426fceae0be03f2a5d1a355e2cd8fcfd | |
tuxmen_user | 16389 | f | f | f | f | md5c1ae969863ea72aa7e24f0e202469344 | |
(3 rows)
Berechtigungen für User auf Tabelle setzen
tuxmendb=# GRANT ALL ON tuxmen_tab_one TO tuxmen_adm; GRANT tuxmendb=# GRANT SELECT ON tuxmen_tab_one TO tuxmen_user; GRANT
Anzeigen der Berechtigungen
tuxmendb=# \z
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+----------------+-------+-----------------------------+--------------------------
public | tuxmen_tab_one | table | postgres=arwdDxt/postgres +|
| | | tuxmen_adm=arwdDxt/postgres+|
| | | tuxmen_user=r/postgres |
Rechte auf Datenbank für einen User setzen
Auswahl von Kommandos in der Console:
| Kommando | Beschreibung |
| \? | Zeigt eine Liste von console-spezifischen Kommandos an. |
| \h | Zeigt eine Liste der SQL-Anweisungen an. |
| \h <SQL-Anweisung> | Zeigt eine Hilfe zur angegebenen SQL-Anweisung an |
| \l | Zeigt eine Liste der vorhandene Datenbanken an. |
| \c <Datenbankname> | Verbindet mit der angegebenen Datenbank. |
| \d | Gibt eine Liste von Datenbankobjekten aus. |
| \dt | Gibt eine Liste der Tabellen aus. |
| \d <Tabellenname> | Zeigt die Struktur der angegebenen Tabelle. |
| \z | Zeigt eine Übersicht der Datenbankobjekte mit Zugriffsrechten an. |
| \z <Tabellenname> | Zeigt die Berechtigungen auf eine Tabelle an. |
| \i <Dateiname> | Führt die in der datei einthaltenen SQL-Anweisungen aus. |
| \q | Verlassen der Console |
Beim Absetzen einer SQL-Anweisung in der Console wird dieses mit einem Semikolon abgeschlossen.
Anlegen und Löschen von Datenbanken
1. In der psql Console:
template1=# CREATE DATABASE dummy_database;
template1=# DROP DATABASE dummy_database;
2. Über mitgelieferte Scripte:
postgres@zero:~$ createdb test
postgres@zero:~$ dropdb test
Anlegen und Löschen von Tabellen
dummy_database=#CREATE TABLE namen (id INTEGER, vorname VARCHAR(25),nachname VARCHAR(40));
Da Tabellendefinitionen recht komplex sein können, werden diese meist in einer Datei gespeichert, die dann an psql übergeben wird.
dummy_database=#DROP TABLE namen;
Primässchlüssel
Bei der Erstellung der Tabelle:
dummy_database=# CREATE TABLE namen (id INTEGER, vorname VARCHAR(25),nachname VARCHAR(40),PRIMARY KEY(id));
SERIAL Datentyp
Beim SERIAL Datentyp handelt es sich um einen Autoinkrement-Wert, d.h. ein Wert, der automatisch vom Datenbanksystem erhöht wird.
dummy_database=#CREATE TABLE namen (id SERIAL, vorname VARCHAR(25),nachname VARCHAR(40));
Anlegen und Löschen von Benutzern
1. In der psql Console:
dummy_database=# CREATE USER tina WITH PASSWORD 'geheim';
dummy_database=# DROP USER tina;
2. Über mitgelieferte Scripte:
postgres@zero:~$ createuser -P tina
postgres@zero:~$ dropuser tina
Zugriffsrechte auf Datenbanken / Tabellen
Innerhalb des Datenbanksystems
dummy_database=# GRANT ALL ON namen TO tina;
Das Schlüsselwort ALL steht für alle Rechte.
| Schlüsselwort | Bedeutung |
| SELECT | Erlaubt SELECT Anweisungen auf dem Objekt. |
| INSERT | Erlaubt INSERT Anweisungen auf dem Objekt. |
| UPDATE | Erlaubt UPDATE Anweisungen auf dem Objekt. |
| DELETE | Erlaubt DELETE Anweisungen auf dem Objekt. |
| REFERENCES | Wird für die Erstellung eines Fremdschlüssels benötigt. Auf beiden betroffenen Tabellen muss dieses Recht gesetzt sein. |
| CONNECT | Benutzer darf sich mit der Datenbank verbinden. Wird zusätzlich zu den in der Konfigurationsdatei pg_hba.conf vorhandenen Einstellungen ausgewertet. |
Bei eventuell schon vorhandenen Rechten, gelten die hier angegebenen Rechte zusätzlich.
dummy_database=# \z namen
Zugriffsrechte für Datenbank »dummy_database«
Schema | Name | Typ | Zugriffsrechte
--------+-------+---------+-------------------------------------------------
public | namen | Tabelle | {postgres=arwdxt/postgres,tina=arwdxt/postgres}
(1 Zeile)
dummy_database=# REVOKE ALL ON namen FROM tina;
Über Konfigurationsdateien
/etc/postgresql/8.3/main/pg_ident.conf
In dieser Datei können Zuordnungen von Systembenutzern zu PostgreSQL-Benutzern definiert werden. Diese können dann mit einem Namen in der Datei pg_hba.conf verwendet werden. Die Authentifizierung über ident funktioniert nur, wenn auf dem Server ein ident-Server läuft.
/etc/postgresql/8.3/main/pg_hba.conf
In dieser Datei wird festgelegt, welche User sich von wo mit welcher Datenbank verbinden können.
Die Syntax lautet:
TYPE DATABASE USER CIDR-ADDRESS METHOD
TYPE bezeichnet dabei den Verbindungstyp (UNIX Sockets oder über TCP/IP).
DATABASE gibt die Datenbank an (Datenbankname oder Schlüsselwort 'all').
USER ist der Benutzer, der sich mit der Datenbank verbindet (Benutzername oder Schlüsselwort 'all').
CIDR-ADDRESS gibt den Host bzw. die Hosts an, von denen aus zugegriffen werden darf.
Beispiel:
192.168.254.27/32 - nur dieser Host 192.168.254.0/24 - kleines Netzwerk 10.6.0.0/16 - großes Netzwerk
METHOD kennzeichnet die Methode, die zum Anmelden benutzt wird.
| trust | Die Verbindung wird bedingungslos erlaubt. |
| reject | Verbindung wird zurückgewiesen. |
| md5 | Md5-verschlüsseltes Passwort wird zum Verbinden benötigt. |
| crypt | Ein crypt()-verschlüsseltes Passwort wird zum Verbinden benötigt. |
| password | Ein unverschlüsseltes Passwort wird zum Verbinden benötigt. |
| ident | Die pg_ident.conf wird zum Verbinden benutzt. |
Werden die Dateien pg_hba.conf und / oder pg_ident.conf geändert, muss PostgreSQL neu gestartet werden, damit die Änderungen wirksam werden.
Konfiguration
Hauptkonfigurationsdatei von PostgreSQL:
/etc/postgresql/8.3/main/postgresql.conf
Ausgewählte Parameter:
| Name | Beschreibung |
| data_directory | PostgreSQL Datenverzeichnis |
| hba_file | Name und Pfad zur Konfigurationsdatei für Zugriffsrechte |
| ident_file | Name und Pfad zur ident Konfigurationsdatei |
| listen_addresses | IP-Adresse, an der der Server auf Verbindungen lauscht. |
| port | Der Port, an dem gelauscht wird. |
| max_connections | maximale Anzahl der gleichzeitigen Verbindungen. |
| shared_buffers | Hauptspeicher, den die Datenbank für Shared Memory Puffer benutzt. Empfohlener Wert: 5%-25% RAM (Achtung: eventuell muss der kernel Parameter kernel.shmmax angepasst werden) |
| work_mem | Hauptspeicher, der für interne Sortieroperationen benutzt wird. |
Bei vielen Optionen ist nach einer Änderung ein Neustart des Servers erforderlich.
Anpassen kernel.shmmax
sysctl kernel.shmmax = <WERT> (bei Reboot wieder auf ursprünglichem Wert.)
oder:
Anpassen der Datei /etc/sysctl.conf:
kernle.shmmax = <WERT>
einfügen
Anlegen eines neuen Datenbank-Clusters
Anlegen eines Verzeichnisses für den neuen DB-Cluster und setzen der Rechte:
root@zero:~# mkdir /var/test_cluster root@zero:~# chown postgres /var/test_cluster
Neuen Cluster mit allen benötigten Strukturen erzeugen:
postgres@zero:~$ /usr/lib/postgresql/8.3/bin/initdb -D /var/test_cluster/
Starten des neuen DB-Clusters
/usr/lib/postgresql/8.3/bin/postgres -D /var/test_cluster
Backup
komplette Datenbank:
postgres@zero:~$ pg_dump -f dummy_database.dmp dummy_database
Einzelne Tabelle:
postgres@zero:~$ pg_dump -t namen -f namen.dmp dummy_database
Nur Struktur einer Tabelle:
postgres@zero:~$ pg_dump -s -t namen -f namen.schema dummy_database
Nur Daten einer Tabelle:
postgres@zero:~$ pg_dump -a -t namen -f namen.data dummy_database
PostgreSQL Log Shipping
Prinzip
2 Rechner: 1 Primary, 1 Standby
auf dem Primary wird kontinuierlich geloggt, Logs werden vom Standby gelesen und Änderungen werden auf dem Standby durchgeführt.
allgemeine Vorbereitungen
ssh Schlüssel für Benutzer postgres erstellen und auf den Standby kopieren (Benutzer postgres hat Standardmäßig kein Passwort und hat als Homeverzeichnis /var/lib/postgresql - eventuell sollte man da ein anderes Verzeichnis nehmen?)
auf dem Standby wird pg_standby benötigt (enthalten im Paket postgresql-contrib)
root@test2:~# apt-get install postgresql-contrib-8.4
Installation PostgreSQL auf beiden Systemen
apt-get install postgresql
Konfiguration des Primary
Datei /etc/postgresql/8.4/main/postgresql.conf Archivierung aktivieren
archive_mode = on archive_timeout = 60 # in Sekunden, nach spaetestens dieser Zeit wird ein neues Logfile Segment erstellt archive_command = 'rsync -a %p postgres@test2:/var/postgresql/walfiles/%f' # muss im Erfolgsfall 0 zurueckgeben
%p - wird durch Pfad und Dateiname der WAL Datei ersetzt
%f - wird durch Dateiname der WAL Datei ersetzt
/etc/init.d/postgresql-8.4 restart
Etwas warten (je nach archive_timeout) und kontrollieren, ob WAL Files in das in archiv_command angegebene Verzeichnis kopiert werden (vorher anlegen!).
Vorbereiten des Standby Systems
Falls noch nicht geschehen:
Verzeichnis wie in archiv_command angegeben anlegen, in das die WAL Dateien vom Primary kopiert werden und Besitzer anpassen.
root@test2:~# mkdir -p /var/postgresql/walfiles/ root@test2:~# chown -R postgres.postgres /var/postgresql/
root@test2:~# /etc/init.d/postgresql-8.4 stop root@test2:~# rm -r /var/lib/postgresql/8.4/main/* root@test2:~# cd /var/lib/postgresql/8.4/main/ root@test2:/var/lib/postgresql/8.4/main# echo "restore_command = '/usr/lib/postgresql/8.4/bin/pg_standby -d -s 60 -t /tmp/pgsql.trigger.5432 \ /var/postgresql/walfiles %f %p %r 2>>/var/log/standby.log'" > /var/lib/postgresql/recovery.conf root@test2:/var/lib/postgresql/8.4/main# ln -s /var/lib/postgresql/recovery.conf recovery.conf root@test2:/var/lib/postgresql/8.4/main# chown postgres.postgres recovery.conf
root@test2:/var/lib/postgresql/8.4/main# touch /var/log/standby.log root@test2:/var/lib/postgresql/8.4/main# chown postgres /var/log/standby.log
Erstellen Basis Backup auf dem Primary und Kopieren auf den Standby
postgres@test1:~$ psql -c "SELECT pg_start_backup('replication');"
postgres@test1:~$ rsync -avz --exclude 'pg_xlog/*' /var/lib/postgresql/8.4/main/* test2:/var/lib/postgresql/8.4/main/
postgres@test1:~$ psql -c "SELECT pg_stop_backup();"
Starten des Standby und Kontrolle
root@test2:~# /etc/init.d/postgresql-8.4 start root@test2:~# tail -f -n 100 /var/log/postgresql/postgresql-8.4-main.log root@test2:~# tail -f -n 100 /var/log/standby.log