Rücksichern von Tabellen

Achtung! Abhängigkeiten beachten!

• Rückzusichernde Tabelle markieren

• Abfragewerkzeug starten (strg-E)

• durch Sicherung angelegte *.sql-Datei öffnen. (strg-o)

• eventuelle Anpassungen vornehmen (search_path,

pSQL

Kommandos

\q Verlassen
\z [Tabellenname] Rechte einer Tabelle
\h \? Help
\d [Tabellenname] Feldbeschreibung einer Tabelle
\dt [Suchmuster] Tabellen einer Datenbank
\ds [Suchmuster] Sequenzen einer Datenbank
\df [Suchmuster] Funktionen einer Datenbank
\dn Listet alle Schematas

\s [Filename] Listet File
\i [Filename] Ausführen einesBefehls aus einer Datei
\o [Filename] Ausgabe in Datei

Allgemeines

Neuerungen von Version 8.4. ( Andreas Scherbaum auf Heise.de)

Zahlreiche Verbesserungen bekam das Kommandozeilenprogramm psql spendiert. So ist die Hilfe freundlicher gestaltet, weil immer wieder Nutzer die Startmeldung schlicht ignorieren. Die überarbeitete Tabulator-Komplettierung zeigt jetzt alle Möglichkeiten an, nicht nur wie bisher eine Auswahl. Verschiedene Funktionen (zum Beispiel \d+ ) informieren über die Größe der Objekte in der Datenbank oder die Zugriffsrechte:

ix_84=# \dt+ Liste der Relationen Schema | Name | Typ | Eigentümer | Größe ... --------+-------+-------+------------+-----------...
			public | daten | table | ads | 8192 bytes ix_84=# \l+ Liste der Datenbanken Name | Eigentümer | Kodierung
			| ... Access Privileges ... -------+------------+-----------+-... ------------------------------ ... ix_84
			| ads | UTF8 | ... {=Tc/ads,ads=CTc/ads,ix=C/ads} ...
			

Der Befehl zum Anzeigen der Rechte ( \z ) umbricht die unter Umständen recht lange Liste zur besseren Lesbarkeit nun:

  Zugriffsrechte Schema | Name | Typ | Zugriffsrechte --------+-------+-------+-----------------
			public | daten | table | ads=arwdDxt/ads : ix=r/ads : u_84=r/ads
			

Mehr Bedienkomfort in psql

Verschiedene Schalter wie \timing lassen sich nicht mehr nur umschalten, sondern mit on oder off explizit auf den gewünschten Wert einstellen. \dT zum Anzeigen von Datentypen gibt die Werte eines ENUM an. psql beachtet die Umgebungsvariable $COLUMNS und passt die Breite der Ausgabe entsprechend an. Ein weiteres nützliches Detail: Das Programm listet bei einer Tabelle alle auf sie verweisenden Referenzen. Bisher musste man sie umständlich per Hand heraussuchen.

Der SQL-Befehl TRUNCATE zum Löschen aller Datensätze aus einer Tabelle löst alle operationsbezogenen Trigger aus (statement-based Trigger). Auf Datensätze (row-based) bezogene Trigger bleiben weiterhin unbeachtet. Sinn von TRUNCATE ist es gerade, das Auslösen der zeilenbezogenen Trigger zu umgehen.

TRUNCATE wertet die Option RESTART IDENTITY aus, indem es alle SEQUENCE s der Tabelle auf ihre Anfangswerte zurücksetzt. Anschließende INSERT -Befehle beginnen also wieder mit den ursprünglichen Werten der Sequenz. Außerdem gibt es das neue Recht truncate , das der DBA wie gehabt mit GRANT und REVOKE vergibt.

PostgreSQL kennt neben Funktionen mit OUT - und INOUT -Parametern solche, die TABLE(...) zurückliefern. Dieses Verhalten entspricht dem SQL:2003-Standard. Des Weiteren können Funktionen eine variable Anzahl von Parametern akzeptieren. Das spart umständliche Wrapper, die nur wieder "die eine" Funktion mit passenden Parametern aufrufen. Den Spaß beim Erstellen von Funktionen vervollständigen Default-Parameter, die beim Aufruf fehlende Werte ersetzen.

Zugriffsrechte für einzelne Spalten

Bisher ließen sich Spalten zu einem VIEW nur durch sein Löschen und Neuerstellen hinzufügen. In Zukunft reicht ein CREATE OR REPLACE VIEW dafür. Allerdings dürfen sich die bisherigen Spalten, ihre Reihenfolge und die Datentypen nicht ändern.

Ab Version 8.4 bietet PostgreSQL spaltenorientierte Zugriffsrechte. Ein einzelnes Recht wie SELECT oder DELETE gilt dabei nicht nur für die ganze Tabelle, sondern auch für einzelne Spalten. Die Tabellenrechte haben Vorrang, erst wenn kein passendes Recht auf Tabellenebene vorhanden ist, wertet die Datenbank die spaltenbasierten Rechte aus. Im folgenden Beispiel bekommen alle Nutzer ( PUBLIC) nur Zugriff auf den Benutzernamen und die ID, das Passwort bleibt geheim:

CREATE TABLE benutzer (id SERIAL, benutzername TEXT, passwort TEXT); REVOKE SELECT ON benutzer
			FROM public; GRANT SELECT (id, benutzername) ON benutzer TO public;
			

Arrays haben die Entwickler mit einigen nützliche Funktionen ausgerüstet: array_fill() belegt Werte vor;

SELECT array_fill(23, ARRAY[4]);
			

etwa gibt ein Array aus, dessen vier Elemente mit 23 besetzt sind.

Die Funktion array_length() ermittelt die Größe eines (mehrdimensionalen) Array. Im folgenden Beispiel wählt 1 die erste Dimension aus

SELECT array_length(ARRAY[1,2,3], 1);
			

sodass das Ergebnis der Abfrage 3 lautet.

Windowing Functions

Eine der größten Detailverbesserungen sind "Windowing Functions" beziehungsweise "Common Table Expressions". Damit lassen sich umfangreiche Abfragen erstellen, wie sie bei Data-Warehouse-Anwendungen üblich sind. Auch rekursive Anfragen und Baumstrukturen stellen mit dieser Neuerung keine Hürde mehr dar, die neuen Möglichkeiten sind sehr vielfältig. Das Beispiel im folgenden Listing erzeugt eine Tabelle mit abhängigen Einträgen: ein Land, Bundesländer, Orte, Ortsteile. Jede Angabe gehört zu einem übergeordneten Element, nur das Land bildet die Spitze des Baumes und hat keinen Elterneintrag. Die Anfrage liest die Daten rekursiv aus und sortiert sie gleichzeitig in den Baum ein. Als Ergebnis erhält man eine Liste mit Ortsbezeichnungen, in der die abhängigen Einträge jeweils unter dem Elterneintrag stehen.

CREATE TABLE orte ( id INT NOT NULL PRIMARY KEY, gehoert_zu INT, name TEXT NOT NULL UNIQUE ); --
			irgendwo muss ein Baum beginnen INSERT INTO orte (id, gehoert_zu, name) VALUES (1, NULL, 'Deutschland');
			INSERT INTO orte (id, gehoert_zu, name) VALUES (2, 1, 'Sachsen-Anhalt'); INSERT INTO orte (id, gehoert_zu,
			name) VALUES (3, 1, 'Niedersachsen'); INSERT INTO orte (id, gehoert_zu, name) VALUES (4, 2, 'Magdeburg');
			INSERT INTO orte (id, gehoert_zu, name) VALUES (5, 4, 'Magdeburg/Hasselbachplatz'); INSERT INTO orte (id,
			gehoert_zu, name) VALUES (6, 3, 'Hannover'); WITH RECURSIVE rekursion(eintrag, name, pfad) AS ( -- selektiere
			erst mal alles aus Deutschland -- das ist der Kopf des Baumes, ohne Zugehoerigkeit SELECT id, name, ARRAY[id]
			FROM orte WHERE gehoert_zu IS NULL UNION ALL -- hier die Rekursion erzeugen SELECT o.id, o.name, rekursion.pfad
			|| ARRAY[o.id] FROM orte o JOIN rekursion ON (o.gehoert_zu = rekursion.eintrag) WHERE id NOT IN (rekursion.eintrag)
			) SELECT * FROM rekursion ORDER BY pfad; eintrag | name | pfad ---------+---------------------------+-----------
			1 | Deutschland | {1} 2 | Sachsen-Anhalt | {1,2} 4 | Magdeburg | {1,2,4} 5 | Magdeburg/Hasselbachplatz
			| {1,2,4,5} 3 | Niedersachsen | {1,3} 6 | Hannover | {1,3,6}
			

Die integrierte Programmiersprache pl/pgSQL hat einige nützliche Detailverbesserungen erfahren. So lassen sich mit CASE-WHEN -Statements zahllose IF-THEN -Zeilen vermeiden. Prozeduren können Prepared Statements in dynamisch generierten Anfragen erzeugen und dort Parameter mittels USING beim Ausführen einfügen.

Dynamische Abfragen mit Parametern

Im Zusammenhang mit dem ebenfalls neuen RETURN QUERY können Entwickler Funktionen wesentlich kürzer und übersichtlicher schreiben. Das folgende Listing zeigt den Einsatz von USING anhand eines Beispiels, das übergebenen Text (enthält Hochkommas) als Parameter einfach in die Anfrage einfügt. Da die Bearbeitung mit quote_literal() zum Schutz der Hochkommas entfällt, erhöhen sich die Lesbarkeit und Sicherheit der Funktion.

CREATE OR REPLACE FUNCTION using_test (TEXT, TEXT) RETURNS TEXT AS $$ DECLARE param1 ALIAS FOR
			$1; param2 ALIAS FOR $2; anfrage TEXT; BEGIN RAISE NOTICE 'Nutze Parameter: %, %', param1, param2 USING
			detail = 'Parameter werden ausgegeben', hint = 'Auf Hochkommas achten'; EXECUTE 'SELECT $1 || ' ' || $2'
			INTO anfrage USING param1, param2; RETURN anfrage; END; $$ LANGUAGE 'plpgsql'; SELECT using_test(E'O\'Reilly',
			'Bücher'); NOTICE: Nutze Parameter: O'Reilly, Bücher DETAIL: Parameter werden ausgegeben TIP: Auf Hochkommas
			achten using_test ----------------- O'Reilly Bücher
			

Benutzern können Funktionen nun mehr Informationen geben. Dazu lassen sich mit RAISE -Optionen weitere Details ausgeben. Das kann ein Hinweis ( hint ), der Fehlercode oder ein Log-Eintrag sein. Das zuvor gezeigte Listing nutzt diese Erweiterung und gibt zur Laufzeit einige nützliche Hinweise aus.

Der neue Konfigurationsparameter track_functions veranlasst den Server, die Häufigkeit und Gesamtlaufzeit von Funktionsaufrufen in der Systemtabelle pg_stat_user_functions zu protokollieren. Den Zugriff darauf vereinfachen einige Hilfsfunktionen.

Wie jede größere Release dreht 8.4 an der Performance-Schraube. Statistikdaten schreibt der Server in der Regel einmal pro Minute (durch Autovacuum ausgelöst) oder wenn ein Benutzer Statistiken abruft. Vorher erledigte er das zweimal pro Sekunde, meistens viel zu häufig. Außerdem gab es viele Verbesserungen im Planer, um Anfragen effektiver zu berechnen und durchzuführen.

Wenn vorhanden, nutzt PostgreSQL die POSIX-Funktion fadvise() . Damit teilt es dem Betriebssystem mit, wie es Daten von den Festplatten voraussichtlich lesen wird, zum Beispiel sequenziell oder wahlfrei.

SELECT DISTINCT arbeitet jetzt schneller und verliert damit seinen Nachteil gegenüber GROUP BY . Allerdings benötigt die Klausel immer ein ORDER BY , wenn sortierte Ausgaben gewünscht sind. Das funktionierte bei SELECT DISTINCT bisher auch ohne diese Angabe, aber ab 8.4 sind die Ergebnisse nicht mehr von Haus aus sortiert – das soll mehr Tempo bringen. Wenn sich Applikationen also auf die geordnete Ausgabe verlassen, ist hier Nacharbeit angesagt.

Mehr Tempo für VACUUM

Bisher waren die beiden Parameter max_fsm_pages und max_fsm_relations eine Quelle für schlechte VACUUM -Performance, da die Default-Konfiguration beide Werte recht niedrig setzt. Für PostgreSQL 8.4 schrieben die Entwickler den Algorithmus der Free Space Map komplett um und ließen beide Parameter fallen. Den notwendigen Speicherplatz für die Verwaltung reserviert PostgreSQL dynamisch. Außerdem bilden diese Änderungen die Grundlage für ein Feature in einer späteren Version: Mit dem Code können sogenannte "visibility maps" abgebildet werden, die VACUUM wiederum genauer informieren, welche Dateiblöcke es überhaupt bearbeiten muss.

Eine komplette Datenbank kann der DBA mit PostgreSQL 8.4 auf einen anderen Tablespace verschieben:

ALTER DATABASE <datenbankname> SET TABLESPACE <tablespace name>;
			

In der Datei pg_hba.conf dürfen statt Benutzernamen in Zukunft reguläre Ausdrücke stehen. Damit lassen sich zum Beispiel Benutzergruppen anhand einer Gruppenkennung im Namen erkennen. Das Neuladen von pg_hba.conf nach einer Änderung scheitert jetzt bei einem Fehler, und die alte Version bleibt aktiv. Wie bislang startet die Datenbank jedoch nicht, wenn die Datei bereits einen Fehler enthält.

Da SSL-Verbindungen jetzt auch Client-Zertifikate akzeptieren, ist beidseitige Authentifizierung und Verschlüsselung möglich. Der neue Konfigurationsparameter IntervalStyle erlaubt die Formatierung von Intervallen (das Ergebnis einer Operation mit zwei Zeiten) entsprechend dem SQL-Standard. Die Ausgabe ändert sich danach zum Beispiel von "377 days 19:03:00" oder "@ 377 days 19 hours 3 mins" in "377 19:03:00".

Als Contrib-Modul ist auto_explain hinzugekommen. Einmal geladen, schreibt es für jede Anfrage, die länger läuft als die in log_min_duration eingestellte Zeit, automatisch ein EXPLAIN in die Log-Datei. Das vereinfacht das Identifizieren und Analysieren langsamer Abfragen erheblich.

Das Ende von crypt

PostgreSQL unterstützt nicht länger die crypt() -Verschlüsselung. Diese Methode sollte nur noch mit ganz alten Datenbanken (7.3 und früher) zum Einsatz kommen.

psql erlaubte bisher, dass einem einbuchstabigen Parameter die Argumente direkt und ohne Leerzeichen folgten. Diese Möglichkeit erwähnte die Dokumentation schon seit mehreren Versionen nicht, und der entsprechende Code ist jetzt komplett entfernt. Deshalb ältere Skripte überprüfen, ob sie noch dieses Format verwenden.

Nun können Anwender und Entwickler also etliche Verbesserungen nutzen. Die inkompatiblen Änderungen gegenüber der Vorgängerversion dürften zu verschmerzen sein.

Kleinere Änderungen in PostgreSQL 8.4

pg_dump und pg_dumpall sind nicht mehr von einem konfigurierten Statement Timeout betroffen. Diese Einstellung konnte vorher lästige Probleme während eines Backups verursachen. Außerdem kennt pg_dump die neue Option lock-wait-timeout ; dies bricht die Ausführung ab, wenn nach der eingestellten Zeit noch nicht alle notwendigen LOCK s vorliegen.

Mit pg_get_keywords() erhält man eine Liste aller reservierten Schlüsselwörter. Ursprünglich für das Verwaltungswerkzeug pgAdminIII geschrieben, können jetzt andere Anwendungen ebenfalls Funktionen wie Autovervollständigung oder Syntax-Hervorhebung anbieten.

Die Systemtabelle pg_settings enthält nicht mehr nur die aktuellen Einstellungen für einen Konfigurationsparameter, sondern eine weitere Spalte ( reset_val ) mit dem Default-Wert. Das hilft, auf die Schnelle Änderungen an der Konfiguration festzustellen. Außerdem sind die Spalten sourcefile und sourceline hinzugekommen. Sie geben Konfigurationsdatei und die Zeile in ihr an, in der der Wert gesetzt wird.

GIN-Indizes (Generalized Inverted Index) beherrschen jetzt partielle Suchen. Das ist für die Volltextsuche sinnvoll, da sie nun Wortteile zumindest am Wortanfang findet. Des Weiteren sind GIN-Indizes über mehrere Spalten hinzugekommen sowie ein Mechanismus zum Einfügen größerer Datenmengen.

Das Contrib-Modul citext enthält einen Datentyp, der Text unabhängig von Groß-/Kleinschreibung darstellen kann. Dies vermeidet umständliche Aufrufe von LOWER() für Textspalten, bei denen die Schreibweise keine Rolle spielt (zum Beispiel Benutzernamen oder E-Mail-Adressen). Timestamps sind jetzt lokalisiert möglich und der Prä-Compiler ECPG für eingebettete SQL-Statements spricht neuerdings mehrere Sprachen.

SQL

Export / Import

Keine Netzlaufwerke benutzen!

Export - Projekt-DB

set client_encoding = 'win1252';

COPY schemata.db_name TO 'D:\\PGSQL\\Data\\ordner_x\\dateiname.csv' DELIMITER AS ';' ;

Import

Projekt-db

set client_encoding = 'win1252';

COPY schemata.db_name FROM 'O:\\DownLoad\\bak_opensc\\dateiname.csv' DELIMITER AS ';' ;

SQL-Beispiele INSERT INTO ... SELECT

-- Erzeugen der Fehlereinträge in p_implausibledatalog und löschen der Fehler aus U_examination

--select * from u_examination where U_Property like 'Date%' and TO_DATE(SUBSTR(U_Value,1,10),'YYYY-MM-DD') < TO_DATE('1900-01-02 BC','YYYY-MM-DD');

DROP TABLE tmp_implausibledatalog;

CREATE TABLE tmp_implausibledatalog

(

rdf_id serial NOT NULL,

domindizes character(1),

imppackageid integer,

aktion character(1),

patient integer,

p_resource character varying(50),

p_property character varying(50),

p_value text,

p_error character varying(50),

CONSTRAINT tmp_implausibledatalog_pkey PRIMARY KEY (rdf_id)

)

WITH (

OIDS=FALSE

);

CREATE TABLE tmp_examination

(

u_rdf_id serial NOT NULL,

u_patient integer,

u_resource character varying(50),

u_property character varying(50),

u_value text,

CONSTRAINT tmp_examination_pkey PRIMARY KEY (u_rdf_id)

)

WITH (

OIDS=FALSE

);

insert into tmp_examination select * from u_examination WHERE U_Property like 'Date%' and TO_DATE(SUBSTR(U_Value,1,10),'YYYY-MM-DD') < TO_DATE('1900-01-02 BC','YYYY-MM-DD');

select * FROM tmp_examination;

insert INTO tmp_implausibledatalog (domindizes , imppackageid , aktion , patient , p_resource , p_property , p_value , p_error )

select 'U', 4, 1, u_patient,u_resource , u_property,u_value,'Datum unter Quellenschmerzgrenze (1900-01-02)' from tmp_examination;

-- Anpassen der imppavckageid

update tmp_implausibledatalog set imppackageid = 1 WHERE patient <= 3182;

update tmp_implausibledatalog set imppackageid = 2 WHERE patient >= 3183 and patient <= 8199;

update tmp_implausibledatalog set imppackageid = 3 WHERE patient >= 8200;

select * from tmp_implausibledatalog ;

-- Eintragen in die Echte PlausiLog

--insert into p_implausibledatalog (domindizes , imppackageid , aktion , patient , p_resource , p_property , p_value , p_error ) select domindizes , imppackageid , aktion , patient , p_resource , p_property , p_value , p_error from tmp_implausibledatalog;

--Erstellen einer Fehlerübersicht

--select p_error AS Fehler, imppackageid AS Aus_Quelle,count(imppackageid) AS Anz_Fehler from p_implausibledatalog group by p_error,imppackageid;

--Löschen aus u_examination

--delete from u_examination where U_Property like 'Date%' and TO_DATE(SUBSTR(U_Value,1,10),'YYYY-MM-DD') < TO_DATE('1900-01-02 BC','YYYY-MM-DD');

Feststellen der Größe von Tabellen

Anmelden als Admin

-Größe von Tabellen und Indexen

SELECT relname,relacl,relfilenode, relpages, relpages*8/1024 AS Space_MByte FROM pg_class order by relpages DESC;

- Für bestimmte Tabelle

SELECT relname,relacl,relfilenode, relpages, relpages*8/1024 AS Space_MByte FROM pg_class WHERE relname = 'u_examination';

-Anzahl von Transaktionen für eine DB (Nähert sich 2 Milliarden so sollte spätestens VACUUM ausgeführt werden

SELECT datname, age(datfrozenxid) FROM pg_database;