PostgreSQL Administration

Diese Anleitung ist gedacht für ein Upgrade von debian squeeze auf debian wheezy. Ausgangslage ist daher, dass postgresql-8.4 bereits installiert ist.

In dieser Anleitung wird auf die Verwendung von pg_upgrade verzichtet. pg_upgrade macht insbesondere bei großen Datenbeständen mit der Option -k Sinn.

Standardmäßig kommt postgresql 9.1 mit wheezy mit. Optional kann man aber auch gleich postgresql 9.3 installieren. Das folgende apt-Repo wird von postgresql aktualisiert und ist für die Verwendung von debian gedacht.

apt-get install python-software-properties
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main"
apt-get update
apt-get upgrade

Jetzt muss man die neue postgre-Version installieren (Nach Debian-Update auf wheezy ist postgresql-9.1 bereits installiert)

apt-get install postgresql-9.3 postgresql-server-dev-8.4 postgresql-server-dev-9.3

Alten Cluster der Version 9.1 und 9.3 entfernen (Achtung: Dies löscht alle Daten. Nur der Cluster von 8.4, der die ursprünglichen Daten enthält, bleibt erhalten):

pg_dropcluster 9.1 main --stop
pg_dropcluster 9.3 main --stop

Neue Binaries verwenden:

In Dateien /root/.bash_profile und /var/lib/postgresql/.bash_profile folgende Zeile unten hinzufügen/ersetzen:

PATH=$PATH:/usr/lib/postgresql/9.3/bin

Benutzer abmelden und anmelden als root.

Neuen Cluster in Version 9.3 erzeugen:

pg_createcluster 
--encoding=UTF-8 \
--locale=en_US.UTF-8 \
--lc-collate=en_US.UTF-8 \
--lc-ctype=en_US.UTF-8 \
--lc-messages=en_US.UTF-8 \
--lc-monetary=en_US.UTF-8 \
--lc-numeric=en_US.UTF-8 \
--lc-time=en_US.UTF-8 \
-d /var/lib/postgresql/9.3/main 9.3 main -- \
--auth=md5 --pwprompt

Neuer Cluster hat Port 5433!

postgres-Systemnutzer immer vertrauen (nötig für Rücksicherung), hierzu Datei /etc/postgresql/9.3/main/pg_hba.conf editieren (Zeilen einfügen):

#postgres user
local   all             postgres                                trust

service postgresql restart
sudo su - postgres
pg_dumpall -p5432 >dump.sql
cat dump.sql | psql -p 5433
exit

In /etc/postgres/8.4/main/posgresql.conf Port ändern auf 50432

In /etc/postgres/8.4/main/posgresql.conf Port ändern auf 5432

service postgres restart

Anschließend neuen Cluster auf Funktionsfähigkeit testen.

Alten Cluster löschen:

pg_dropcluster 8.4 main --stop

Alte postgresql-Versionen entfernen:

apt-get remove postgresql-8.4 postgresql-server-dev-8.4 postgresql-9.1 postgresql-server-dev-9.1

Quellen:

http://bailey.st/blog/2013/05/14/how-to-install-postgresql-9-2-on-debian-7-wheezy/

http://www.postgresql.org/docs/9.3/static/app-pg-dumpall.html

Im Befehlszeilenclient psql lässt sich die Struktur einer Relation ausgeben mit:

\d tablename

Dies entspricht in etwa einem 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 mittels Konfigurationsparametern:

shared_buffers: Arbeitsspeicher, der für Caching verwendet werden kann. Sinnvoll ist z.B. 25-40% des verfügbaren Arbeitsspeichers.

temp_buffers: Speicher für temporäre Tabellen im Arbeitsspeicher. Benötigen temporäre Tabellen mehr Speicher, wird auf Festplatte gespeichert. Dieser Wert sollte nur hochgesetzt werden, wenn oft große temporäre Tabellen (SQL: create temporary table ...) oft benutzt werden.

work_mem: Arbeitsspeicher, der für Sortier- oder Hashoperationen benutzt werden darf. Sinnvoll sind 3-10% des verfügbaren Arbeitsspeichers.

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/9.3/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 Instanz mit folgenden Einstellungen

• Das Encoding wird auf Unicode (UTF-8) gesetztDie 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 können eingesehen werden 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:

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

Außerdem kann man PostgreSQL per pg_hba.conf 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/9.3/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 stop
sudo su - postgres
cd /var/lib/postgresql/9.3/
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/9.3/main
exit
cp ./../main_/server* .  #nur bei älteren ubuntu-Versionen
sudo service postgresql start

Das alte Verzeichnis /var/lib/postgresql/9.3/main_ kann bei Gelegenheit gelöscht werden. Damit sind dann allerdings auch die alten Daten gelöscht. Insofern sollte man sich vor der Löschung vergewissern, dass alle Daten im neuen PostgreSQL Cluster verfügbar sind

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.

Abweichend oder ergänzend zur Serverkonfiguration lässt sich ein Encoding auch für die aktuelle Sitzung festlegen:

in SQL:

SET CLIENT_ENCODING TO 'UTF8';

in psql:

\encoding UTF8

weitere Encodings:

http://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';

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

SELECT datname,procpid,current_query FROM pg_stat_activity; -- pg 8.4
SELECT datname,pid,query_start, state, query FROM pg_stat_activity; -- pg 9.3

  datname  | procpid |                        current_query                        
-----------+---------+-------------------------------------------------------------
 dwmteam1b |   27527 | SELECT datname,procpid,current_query FROM pg_stat_activity;
(1 Zeile)

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, ansonsten erscheint 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 Labortest 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;