\d tablename

(entspricht desc tablename; bei mysql)

Installation

sudo apt-get install postgresql

Vorarbeiten: Um zu ermöglichen, dass die postgreSQL mittels der Variable shared_buffers einen Cache mit mehr als ca. 32MB vorhalten kann, muss zunächst ein Kernel-Parameter namens SHMMAX hochgesetzt werden:

Unter ubuntu 11.04 kann man in der Datei /etc/sysctl.d/30-postgresql-shm.conf die folgende Zeile setzen (hier: bis zu 4GB für shared_buffers)

• kernel.shmmax = 4294967296

Bei anderen Distributionen kann man diese Datei neu in /etc/sysctl.d/ anlegen oder man kann die Zeile in die /etc/sysctl.conf einfügen.

SHMMAX muss größer sein als shared_buffers. Sonst kommt folgender Fehler beim Start des postgreSQL-Servers:

• CET FATAL:  could not create shared memory segment: Invalid argument
• CET DETAIL:  Failed system call was shmget(key=...).
• CET HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter

Konfiguration:

Bei einer Standard-Installation stehen dem Benutzer postgres zunächst nicht alle Befehler auf der Konsole zur Verfügung. Zur Abhilfe kann der entsprechende Pfad zur PATH-Variable des postgres-Benutzers hinzugefügt werden:

• sudo su - postgres
• cd
• vim .bash_profile

Dort neue Zeile eintragen und speichern:

• PATH=$PATH:/usr/lib/postgresql/8.4/bin

Jetzt sollten alle postgreSQL-Befehle zur Verfügung stehen. Man muss den Pfad bei einem Wechsel der postgreSQL-Version unbedingt anpassen.

Dieses Beispiel initialisiert die postgreSQL mit folgenden Einstellungen

• Das Encoding wird auf Unicode (UTF-8) gesetzt
• Die Locales werden auf en_US gesetzt.
• Es ist eine Passwort-Authentifizierung nötig. Passwörter werden als md5-Hashes gespeichert. Bei Verbindungen über Netzwerk sollten diese unbedingt SSL-verschlüsselt oder SSH-getunnelt werden!

Hinweis: die vom Betriebsystem zur Verfügung gestellten locales kann man ansehen mit

• locale -a

Damit die locales auch beim Start Startskript verwendet werden, muss zuerst folgendes in der Datei /etc/postgresql/8.4/main/postgresql.conf gepflegt werden:

• lc_messages = 'en_US.utf8'                      # locale for system error message strings
• lc_monetary = 'en_US.utf8'                      # locale for monetary formatting
• lc_numeric = 'en_US.utf8'                       # locale for number formatting
• lc_time = 'en_US.utf8'                          # locale for time formatting
• datestyle = 'iso, ymd'      #YYYY-MM-DD ISO style

Außerdem kann man postgreSQL so konfigurieren, dass immer ein Passwort übergeben werden muss. Dies gilt auch für bereits angemeldete Benutzer:

• local   all         postgres                          md5
• # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
• # "local" is for Unix domain socket connections only
• local   all         all                               md5
• # IPv4 local connections:
• host    all         all         127.0.0.1/32          md5
• # IPv6 local connections:
• host    all         all         ::1/128               md5

Hierbei sollte man TCP-Verbindungen möglichst nicht zulassen (mit listen_addresses = 'localhost' in der Datei /etc/postgresql/8.4/main/postgresql.conf)

Achtung: Die Initialisierung löscht möglicherweise alle Datenbanken (und somit alle Daten!) sowie Benutzer. Die  postgreSQL sollte daher am besten vor dem Anlegen von Datenbanken initialisiert werden.

• sudo service postgresql-8.4 stop
• sudo su - postgres
• cd /var/lib/postgresql/8.4/
• mv main main_
• mkdir main
• initdb --auth=md5 --pwprompt --encoding=UTF8 --locale=en_US.utf8 --lc-collate=en_US.utf8 --lc-ctype=en_US.utf8 --lc-messages=en_US.utf8 --lc-monetary=en_US.utf8 --lc-numeric=en_US.utf8 --lc-time=en_US.utf8 --pgdata=/var/lib/postgresql/8.4/main
• exit
• cp ./../main_/server* .
• sudo service postgresql-8.4 start

Das alte Verzeichnis /var/lib/postgresql/8.4/main_ kann bei Gelegenheit gelöscht werden. Damit sind dann allerdings auch die alten Daten gelöscht.

Quellen:

http://www.postgresql.org/docs/8.4/static/app-initdb.html

http://www.postgresql.org/docs/8.4/static/multibyte.html

http://www.postgresql.org/docs/8.4/static/locale.html

in psql:

\db

Benutzer anlegen:

• Im Betriebsystem anmelden als Benutzer postgres
• createuser -U postgres --password --pwprompt --encrypted newuser

Zunächst wird ein neues Passwort zweimalig abgefragt. Im Anschluss fragt postgresql, ob der Benutzer superuser sein soll. Normalerweise sollte die Antwort "nein" lauten. Zum Schluss muss man evtl. noch das Datenbank-Passwort von posgres eingeben.

Zu beachten ist, dass das Passwort zur Authentifizierung nur nötig ist, wenn die Datei /etc/postgresql/8.4/main/pg_hba.conf entsprechend konfiguriert ist (md5 als method)

ALTER USER myuser with encrypted PASSWORD '123';

In der shell:

• createdb --encoding=UTF8 -U newuser newdb

In der Shell:

• psql -U newuser -d newdb

Beenden mit \q

in SQL:

• SET CLIENT_ENCODING TO 'UTF8';

in psql:

• \encoding UTF8

weitere Encodings:

www.postgresql.org/docs/8.4/static/multibyte.html

Damit PL/pgSQL zur Verfügung steht, muss es aktiviert werden mit

CREATE FUNCTION plpgsql_call_handler ()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C';

CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

[bisher nicht getestet!]

Mit folgendem SQL-Statement können alle laufenden User-Prozesse auf dem postgresql-Server angezeigt werden:

Mit folgendem SQL-Statement kann ein User-Prozess auf dem postgresql-Server beendet werden:

select pg_cancel_backend(22345);

22345 ist in diesem Fall die procpid eines User-Prozesses. Bei Erfolg gibt die Funktion true zurück, sonst false.

Man muss hierzu superuser sein, sonst kommt eine Meldung:
ERROR:  must be superuser to signal other server processes

Wenn man INSERT-Statements innerhalb einer Transaktion laufen lässt, kann man eine massive Beschleunigung erreichen. Im Test auf einem Laptop stieg der Durchsatz von 200 INSERTs pro Sekunde auf ca. 2000 INSERTs pro Sekunde. Eine Transaktion lässt sich erzeugen mit:

• BEGIN;
• INSERT ...;
• ...
• INSERT ...;
• COMMIT;