PostgreSQL in heterogener Netzumgebung, Installation und Officeanbindung

Datenbankbetriebssytem für Arbeitsgruppen

von Jörn Eisenkrätzer
24.10.2002
  

PostgreSQL ist ein OpenSource-Datenbankbetriebssystem, welches den SQL/92-Standart beinahe vollständig umsetzt.Aufgrund seiner Leistungsfähigkeit ist es durchaus für kleine und mittlere Unternehmen geeignet.

Desktop-Datenbanken sind Zubehör zu fast jedem Office-Komplettpaket. Ihre Leistungsgrenze ist aber bei kleineren Unternehmen schnell erreicht, auch wenn über eine Netz-Laufwerk-Verbindung eine Art von Multi-User-Fähigkeit erreicht wird. PostgreSQL ist ein Datenbankbetriebssystem, bei welchen der Client-Server-Zugriff über TCP/IP funktioniert. Es können mehrere Nutzer durchaus in der selben Tabelle einer Datenbank arbeiten, solange sie nicht den selben Eintrag (Tabellen-Zeile) manipolieren. Es ist eines der wenigen freien Systeme, welche auch mit Fremdschlüsseln und Transaktionen umgehen können. Um diese Datenbank auch bis zu dem Endnutzer heranzuführen, bietet sich die Verbindung dieser mittels ODBC an StarOffice oder OpenOffice an. Ich möchte in diesem Artikel die Installation von PostgreSQL und die Anbindung von StarOffice/OpenOffice an dieser Datenbank vorstellen.

Installation von PostgreSQL

Benötigte Programmpakete:



postgresql
PostgreSQL-Client




postgresql-server
PostgreSQL-Server




postgresql-odbc
ODBC-Treiber für PostgreSQL




unixODBC
ODBC-Treiber-Manager für Linux




postgresql-tcl
Tcl für PostgreSQL, wird für pgaccess benötigt




postgresql-tk 
pgaccess, Client und grafische Oberfläche




 
 




unixODBC-kde, -gui,- bin, 
Je nach Distribution eines der aufgeführten grafischen




gODBCConfig
 Konfigurationstools für odbc.ini und odbcinst.ini

Kontrolle bzw. Anpassungen nach der Installation

Es wird bei der Installation der Pakete etwas Distributionsspezifisch ein Unix-User postgres (Datenbank-Administrator) einschlieslich Gruppe postgres angelegt, sowie das Arbeits- Verzeichnisse "pgsql" oder "postgres" in /usr/lib sowie das Home-Verzeichnis unter /var/lib oder unter /etc.
Folgende Schritte sind nach der Installation weiterhin als root auszuführen:
Password vergeben für postgres, in der Konsole eintippen:
passwd postgres

und Passwort festlegen und bestätigen.
Verzeichnis-Eigentum im Home- und Arbeitsverzeichnis von postgres kontrollieren:
ls -la  /var/lib | grep postgres

In Ordnung ist das folgende Ergebnis:
drxw------ 3 postgres postgres 1024 ...

Sollte aber statt "postgres" "root" stehen, so ist das Verzeichnis-Eigentum zu ändern:
cd /usr/lib
chown -R postgres:postgres

Ebeso ist sicherzustellen, dass das Arbeitsverzeichnis /usr/lib/psql oder /etc/postgres dem postgres gehört.

Start des Datenbankbetriebsystemes

Mit der Installation wird in /etc/ini.d (bzw. /etc/rc.d/init.d) ein Script zum Starten und Beenden von PostgreSQL abgelegt.
Durch Aufruf des Scriptes und der Aufrufoption [start|stop] wird PostgreSQL gestartet bzw. beendet:
/etc/init.d/postgres start

Es ist wichtig, dass das Datenbankbetriebssystems vor Herunterfahren des Computers gestoppt wird. Der Star/Stop-Vorgang wird sinvollerweise mit den Runlevel passieren, meistens wird es durch die Distribution schon so konfiguriert.
Wenn nicht, dann können wir es wie folgt nachholen:
ln -s /etc/init.d/postgresql /etc/init.d/rc3.d/S97postgresql
ln -s /etc/init.d/postgresql /etc/init.d/rc5.d/S97postgresql
ln -s /etc/init.d/postgresql /etc/init.d/rc1.d/K97postgresql
ln -s /etc/init.d/postgresql /etc/init.d/rc6.d/K97postgresql

Bei Bedarf kann der DB-Admin postmaster die Datenbank manuell wie folgt starten
: 
[Pfad_zu_Postmaster]/postmaster [-Option]

Zum Beispiel:
/usr/lib/postgresql/bin/postmaster -i -D /var/lib/postgres/data &

Das "-i" steht für Netzwerkverbindung aktiv und dem "-D" folgt der Pfad zu "data".

Sollte nach einem PC-Absturz und Neustart der Datenbankserver nicht mehr starten mit der Behauptung:
pg_ctl: Another postmaster may be running

pg_ctl: cannot start postmaster
so ist noch im data-Verzeichnis eine alte postmaster.pid, welche noch zu löschen ist.


Anlegen eines Datenbank-Nutzers und einer Beispiel-Datenbank

Login als Datenbank-Administrator postgres. Anlegen eines ( im Unix vorhandenen) Datenbank-Nutzers durch CREATEUSER:
createuser joern
Shall the new user be allowed to create database ? (y/n)
Shall the new user be allowed to create more new users ? (y/n)
CREATE USER

Jetzt können wir uns als der eben mit Datenbankrechten versehene User joern 
su - joern, password: ...
anmelden und eine Datenbank anlegen:
createdb test_db
CREATE DATABASE

Durch die Meldung "CREATE DATABSE" wurde uns das erfolgreiche Anlegen der Datenbank "test_db" mitgeteilt. Jetzt könne wir durch die Eingabe von "psql" in der Konsole den Datenbank-Client aufrufen:

psql test_db

Welcome to psql, the PostgreSQL interaktive terminal.

Type:     \c copyright for distributions terms
    \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
  c \q to quit 

test_db=#

Durch den "=#"-Promt wird signalisiert, dass wir uns in der SQL-Shell befinden.
Abb.1.

Hier können wir unsere SQL-Fähigkeiten freien Lauf lassen oder wir schliessen diese Shell durch Eingabe von \q.


Wir haben das Tool"pgaccess" installiert, mit dem wir auch ohne SQL-Kenntnisse eine Tabelle in der Datenbank anlegen können.
Abb.2.


PostgreSQL für den TCP/IP-Netzbetrieb konfigurieren

In der Datei /var/lib/pgsql/data/pg_hba.conf werden Berechtigungen festgelegt. Wird am Ende dieser Datei die Zeile
'host all [IP-Nummer] [Netzwerkmaske] trust' angefügt, 

# host all   0.0.0.0   0.0.0.0     reject 
host all 192.168.1.0 255.255.255.0 trust

so ist der PostgreSQL-Server von allen Computern dieser Range erreichbar.
Eine eventueller Eintrag "host all 0.0.0.0. 0.0.0.0 reject" ist auszukommentieren.
Es ist zu kontrollieren, ob in der postgresql.conf im postmaster-home der Netzverkehr freigegeben ist. Am Ende dieser Datei steht
tcpip_socket = 1 oder tcpip_socket = "true"
.

Test der Netzwerkverbindung zur Datenbank im Syntax: psql -h [IP des Servers] -d [Datenbankname] 

psql -h 192.168.1.12 -d test_db

Im Erfolgsfall haben wir jetzt das gleiche Bild des SQL-Shell wie bei einem Aufruf ohne Optionen und TCP/IP-Nummer.
Änderungen in den Konfigurationdatein bedingen einen Datenbank-Neustart.

OpenOffice/StarOffice als Datenbankfrontend

Office kontaktiert PostgreSQL in diesem Beispiel über ODBC. Damit ist eine plattformunabhängige Anbindung geschaffen.

ODBC-Konfiguration auf dem Linux-SQL-Server

Aufruf des ODBC-Konfigurations-Frontends, in diesem Fall: 

ODBCConfig

bzw. wenn root-Grafik im Userwindows nicht erlaubt ist :
kdesu ODBCConfig

Abb.3.

Wir haben jetzt die Maske "ODBC Data Source Administrator" in Ausgangszustand, wählen in diesem Beispiel den Reiter "System DNS" und danach die Schaltfläche [Add...] und kommen zu der Maske "Select a Driver", und wählen wiederum [Add...]
Jetzt haben wir die Maske "Driver Properities (new)" vor und, welche wir zur besseren Übersicht halber mit der Maus etwas breiter ziehen.
In diser Maske werden die ODBC-Bibliotheken libodbpsql.so und libodbcpsqlS.so eingetragen.
Abb.4.

Die Lage dieser Libraries ist je nach Distribution etwas unterschiedlich und wird am besten mit locate ermittelt.

Wir bestätigen (save und exit, am Häckchen Maske oben links), verlassen die nächste Maske "Select a Driver" nach Markierung unseres neuen Eintrages mit [Ok].
Abb.5.

Es öffnet sich nun die Maske "Data Source Properities", in der wir noch ein paar Eintragungen vornehmen, so z.B.:
Name, ODBC-Verbindungsname.
Database, der Name der Datenbank.
Servername, z.B die IP des Servers
UserName, der Datenbank-Benutzername.
Password, das hier gewählte Passwort wird bei der ODBC-Clientverbindung abgefragt.

Der ODBC-Server ist konfiguriert.
Abb.6. 		Als Ergenbis sind zwei Dateien entstanden, die /etc/odbc.ini und die /etc/odbcinst.ini.
Abb.7.


ODBC-Konfiguration auf dem lokalen Linux-SQL-Client

Auf dem Client muss die Bibliothek libodbc.so vorhanden sein. Diese wird vom Paket unixodbc geliefert.
Ist diese so nicht vorhanden, sondern z.B nur als libodbc.so.1.0.0, so muss sie als link angelegt werden (root):
ln -s /usr/lib/libodbc.so.1.0.0 /usr/lib/libodbc.so

(Evtl. geöffnetes Office dann neu starten.)

Den Datenbank-Explorer durch Klick aud das Symbol auf der Linken Symbolleiste (unter Fernglas) öffnen.
In einem freien Feld im Explorer-Feld mit der rechten Maustaste "Administrate Data Source" wählen.
Es öffnet sich eine Maske mit diesem Titel. Siehe Abb.9.
Abb.8



Abb.9  
Wir gehen wieder mit der Maus in das Feld unter "New Data Source",(Datenquelle verwalten) und wählen im Kontexmenue "New Data Source" (Neue Datenquelle).
In der Kateikarte "General",im Namens-Feld können wir jetzt einen Namen vergeben, dieser stellt den Datenbanknamen auf dem Client dar.
Ein Feld darunter, bei Database type, wählen wir ODBC aus.
Im Feld "Data source URL" gehen wir auf den Such-Knopf [...] und bekommen jetzt unsere Datenquelle(n) zur Auswahl, in unseren Fall Adress_DB.

In der Karteikarte "ODBC" werden User-Namen und Passwort eingetragen.
Unter Karteikarte "Tables" werden die zu sich Tabellen ausgewählt, welche sichtbar sein sollen.


Abb.10  
Unsere Datenbank als "Rohergebnis".


ODBC-Konfiguration auf dem netzverbundenen Linux-SQL-Client

Es muss ausser unixODBC noch das Programmpaket für ein grafisches ODBC-Konfigurationsfrontend, wie unixODBC-kde installiert sein. ODBC wird dann wieder, wie auf den Server bereits geschehen, konfiguriert.
Der weitere Ablauf entspricht der lokalen Installation wie im vorigen Punkt.
Es darf auch nicht vergessen werden, den Link von libodbc.so.1.0.0 auf libodbc.so zu legen.

ODBC-Konfiguration auf dem Windows-SQL-Client

Die Konfiguration des Office im Windows-Client ist analog der des netzverbundenen Linux-Clients .

Vorher aber ist der PostgreSQL-ODBC-Treiber für Windows zu installieren. Dieser Treiber, die postdrv.exe ist hier erhältlich:

ftp://ftp.postgresql.org/pub/odbc

Der Stamp der z.Zt.aktuellen Version: psqsqlodbc-07_02_0001.zip 3339 KB 15.04.2002


   
Abb.11.
Nach der Installation ist die ODBC-Ikone in der Systemsteuerung vorhanden, nach dessen anklicken wir wieder den ODBC Data Source Administrator vorfinden.

Abb. 12. Datenbank und Zugriff werden definiert.



Abb.13  
Nach durchgeführter ODBC-Konfiguration starten wir den StarOffice-Database-Manager wählen wieder unter Typ ODBC und tragen unsere Daten ein.


Abb.14  
Auch im Office unter Windows können wir auf unseres PostgreSQL-Datenbank zugreifen, wenn zunächst erstmal nur lesend.

Durch diese kleine Musterdatenbank haben wir sozusagen einen ODBC-Pfad geöffnet, d.h. auf Grundlage dieser Datenbank können wir nun weitere Tabellen anlegen.
Dazu entfernen wir zunächst in der ODBC-Konfiguration unter dem Reiter DNS, Option(Advanced) jeweils unter Driver und Data Source das Häckchen Read Only.
Jetzt gehen wir mit der rechten Maustaste auf unsere Mustertabelle. Im Kontexmenue wählen wir Neu - Tabelle - Tabellenentwurf (StarOffice 5.2) oder Neuer Tabellenentwurf (OpenOffice).

Die Tabelle muss einen Primärschlüssel bekommen, damit wir auf den SQL-Server schreibend zuzugreifen können.

Das Frontend

Wem die Datenbankdarstellung in Tabellenform nicht gefällt, kann noch eine Datenbankmaske bauen:
Wir wählen dzu Datei - Autopilot - Formular , dann werden wir über einer Maske mit entsprechenden Auswahlkriterien zu unserem Formulardesign geführt, welches im Nachhinein aber noch editierbar ist.

Mit dem Fernglas-Symbol wird der im Client dargestellte Datensatz durchsucht, mit dem Trichter-Symbol wird die Abfrage auf SQL-Basis ausgeführt.


Datenimport

Jetzt habe wir ein SQL-Client-Server-Datenbank-Betriebssystem und wollen auch die Daten von anderen Systemen hier migrieren.
Eine Datenbank besteht aus einer oder mehrere Tabellen. Im letzteren Fall sind diese Tabellen wahrscheinlich auch über Schlüssel referentiell miteinander verknüpft. Diese Tabellen mit ihrer Struktur müssen nachgebaut und durch Datenkopie gefüllt werden.
Wir erstellen darum, wie oben beschrieben, der Ursprungstabelle(n) identischen Tabelle(n) bezüglich Spaltenanzahl und Datentypen und füllen diese mit der Methode
COPY <Tabellenname> FROM </Pfad/Dateiname> DELIMITERS <Feldtrennzeichen>

aus einer CSV-Tabelle. Dazu müssen wir zuvor noch diese Tabelle erzeugen, welche aus Text und einen zu definierenden Trennzeichen besteht.
Aus einer Accessdatenbank habe ich in der Tabellenansicht den Tabelleninhalt über die Zwischenablage kopiert, in eine leere Exceltabelle eingefügt und als Datei.xls gespeicher. Diese wurde in Gnumeric, dem Gnome-Tabellenkalkulationsprogramm, zu einer Text-Datei konvertiert. In groben Schritten folgender Ablauf:

Datei - Speichern unter - Dateiformat = Textdatei Export - Auswahl der Exportvorlage

Im Menue Auswahl der Exportvorlage wird als Trennzeichen (Delimitter)  |  (PIPE) gewählt und Anführen auf   Nie   gesetzt.
Es ist auch möglich, dies im OpenOffice auszuführen, allerdings gibt es die Option der Auswahl von "Anführen" dort nicht, man muss dann in einem zweiten Schritt (in einem Text-Editor und ausgeschalteten Zeilenumbruch) alle Anführungszeichen (Hochkommas) entfernen, die um jeden Text-Feldeintrag gesetzt wurden.

Diese so aufbereitete Datei wird nun wie folgt in dei PostgreSQL-Tabelle kopiert:

psql meine_db
.....

COPY meine_tabelle FROM '/home/joern/csv_datei.txt' DELIMITERS '|';

Der Pfad und der Dateinamen, sowie das Trennzeichen werden in einfache Hochkommas gesetzt. Wenn alles Fehlerfrei kopiert wird, so wird dies mit einem COPY quittiert. Im Fehlerfall kommt dan eine entsprechende Meldung mit Angabe der Zeilenzahl des Konfliktes. Am schnellsten ist die CSV-Datei im Editor NEdit zu betrachten, er macht mühellos eine Tabelle mie 10.000 Zeilen in wenigen Sekunden auf.
Mögliche Fehler beim Datenimport können sein:
Der Speicherbedarf in der neuen Tabelle (Länge Feldtyp) ist kleiner definiert als an Daten importiert werden soll.
Die Anzahl der Spalten der neuen mit der alten Tabelle stimmt nicht überein.
Der Datentyp der jeweiligen Spalte ist nicht mit dem der anderen Tabelle gleich oder kompatibel.
In der Datei ist ein leerer Feldeintrag, welcher in der neuen Tabelle mit nicht erlaubt ist, z.B NOT NULL-Deklarierung oder Datum.

Schlussbetrachtung

Mit etwas Zeitinvestition können wir völlig ohne Mehrkosten eine leistungsfähige SQL-Datenbank aufbauen und diese auch über die Schnittstelle ODBC an ein Office als SQL-Client anbinden. PostgreSQL ist aufgrund der Konfigurierbarkeit der Rechtevergabe durchaus für grössere Arbeitsgruppen geeignet. Weiterhin ist es möglich den Veschlüsslungsmechanismus SSL einzusetzen, um sich gegen Abhören zu sichern.
Es gibt aber auch Einschränkungen bezüglich der Nutzbarkeit des Funktionsumfanges von PostgreSQL über ODBC, es sind einige fortgeschrittene SQL-Funktionen nicht über ODBC-Treiber ansprechbar.

Infos

[1] PostgreSQL und StarOffice, Eckart Höffner http://www.fifoost.org/allgemein/psql-so/index.html
[2] PostgrSQL, Jens Hartwig, ADDISON-WESLEY, ISBN 3-8273-1860-2



Der Autor

Jörn Eisenkrätzer selbst hat gar kein Windows auf seinem PC. Er beschäftigt sich damit nur um anderen zu helfen, auch wenn damit manchmal Geld zu verdienen ist. aed@gmx.net.