1 psql [toc]

• Alle Befehle einer Datei in einer Transaktion ausführen:

  psql -1 -f mycommands.sql

2 Constraints [toc]

• Nachträglich eine Unique Constraint hinzufügen:

  ALTER TABLE mytable ADD UNIQUE(mycolumn);

• Leider sind derzeit (Version 8.3) nur Foreign-Key Constrains "deferrable". Check Constraints werden sofort geprüft. Ausweg: docs: CREATE CONSTRAINT TRIGGER

• foo=# ALTER TABLE foo_table ADD UNIQUE (ref_id, col1, col2);
  HINWEIS:  ALTER TABLE / ADD UNIQUE erstellt implizit einen Index »foo_table_ref_id_key« für Tabelle
  »foo_table«
  FEHLER:  konnte Unique Index nicht erstellen
  DETAIL:  Tabelle enthält duplizierte Werte.

  Aber welche Werte sind doppelt?

  SELECT ref_id, col1, col2, COUNT(*) FROM foo_table GROUP BY ref_id, col1, col2 HAVING COUNT(*)>1;

3 DB-Server [toc]

• Fehlermeldungen in englischer Sprache:

  # Datei postgresql.conf:
  lc_messages = 'C'

• Controlldata

  postgres@host:~> pg_controldata data/

  pg_control-Versionsnummer:               822
  Katalog-Versionsnummer:                  200611241
  Datenbanksystemidentifikation:           5167133601488526335
  Datenbank-Cluster-Status:                im Produktionsmodus
  pg_control zuletzt geändert:             Mi 26 Mär 2008 08:41:44 CET
  Aktuelle Logdatei-ID:                    1
  Nächstes Logdatei-Segment:               119
  Letzter Checkpoint-Ort:                  1/760CB188
  Voriger Checkpoint-Ort:                  1/760CB138
  REDO-Ort des letzten Checkpoints:        1/760CB188
  UNDO-Ort des letzten Checkpoints:        0/0
  TimeLineID des letzten Checkpoints:      1
  NextXID des letzten Checkpoints:         0/341639
  NextOID des letzten Checkpoints:         225928
  NextMultiXactId des letzten Checkpoints: 1
  NextMultiOffset des letzten Checkpoints: 0
  Zeit des letzten Checkpoints:            Di 25 Mär 2008 16:55:05 CET
  Minimaler Wiederherstellungsendpunkt:    0/0
  Maximale Datenausrichtung (Alignment):   8
  Datenbank-Blockgröße:                    8192
  Blöcke pro Segment:                      131072
  WAL-Blockgröße:                          8192
  Bytes pro WAL-Segment:                   16777216
  Höchstlänge von Namen:                   64
  Maximale Spalten in einem Index:         32
  Speicherung von Datum/Zeit-Typen:        64-Bit Ganzzahlen
  Maximallänge eines Locale-Namens:        128
  LC_COLLATE:                              de_DE@euro
  LC_CTYPE:                                de_DE@euro

• Startet der Datenbankserver nicht richtig, und sie finden keine Fehlermeldung?
  • postgres.conf:

    log_destination = 'syslog'  # anstatt 'stderr'
    silent_mode = off

  • Postgres im Single User Mode starten:

    postgres@host> /usr/bin/postgres --single -D ~/data

• Was macht der Server gerade?
  Unter Unix sieht man mit 'top' und 'c' (Show comandline) im Groben, was der Server gerade macht. (INSERT, UPDATE, DELETE). Mit folgender Abfrage sieht man jedoch noch mehr:

  SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
         pg_stat_get_backend_activity(s.backendid) AS current_query
         FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

  Siehe Postgres Doku Monitoring

4 Unsortiert [toc]

• Alle Tabellen, die mit xyz_ beginnen löschen:

  user@host> psql -t -c "SELECT 'DROP TABLE ' || tablename  || ' CASCADE ;' FROM pg_catalog.pg_tables where tablename like 'xyz_%'" > droptables
  user@host> less droptables # Vertrauen ist gut, Kontrolle ist besser
  user@host> psql -f droptables

• Leerzeichen am Anfang und am Ende entfernen:
  UPDATE foo_table SET name=regexp_replace(name, E'^ *(.*?) *$', E'\\1');

5 ALTER ... [toc]

• Datentyp einer Spalte ändern:

  ALTER TABLE mytable ALTER COLUMN "name" TYPE VARCHAR(100);

• So weit ich weiß, können das Oracle und MySQL nicht: Änderungen an der Tabellenstruktur können in einer Transaktion ausgeführt werden, und ggf. mit ROLLBACK zurückgenommen werden.

  BEGIN;
  ALTER TABLE ...
  ROLLBACK;

6 Introspection [toc]

• psql -E:

  Mit der Option -E (--echo-hidden) werden die Befehle angezeigt, die psql an den Server schickt, wenn Backslash-Befehle ausgeführt werden. So erfährt man mit welchen internen Tabellen Postgres arbeitet.

  ### Bsp: \d --> Zeige Infos zur Tabelle 'relayrace_raceteam'
  traverse=> \d relayrace_raceteam
  ******** ANFRAGE *********
  SELECT c.oid,
    n.nspname,
    c.relname
  FROM pg_catalog.pg_class c
       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relname ~ '^(relayrace_raceteam)$'
    AND pg_catalog.pg_table_is_visible(c.oid)
  ORDER BY 2, 3;
  **************************
  ...

• Tabellennamen die einem Muster entsprechen zeilenweise ausgeben.

  psql -tA -c "SELECT tablename FROM pg_catalog.pg_tables WHERE tablename ILIKE 'myname%'"
  
   -t: tuples only: Nur die Ergebniszeilen ausgeben, nicht die Spaltennamen. 
   -A: no align: Keine Einrückung, keine führenden Leerzeichen.

7 Backup / Restore [toc]

• pg_dump -Fc

  Das Postgres-Format ist einiges flexibler als die anderen Formate. Es gibt eigentlich keinen Grund diese Angabe nicht immer zu verwenden.

• pg_restore -O:

  Mit der Option -0 (--no-owner) werden bei einem Restore die ALTER TABLE ... ALTER OWNER Ausgaben nicht ausgeführt. Bei meinen Anwendungen gehört dem Datenbankbesitzer auch alle Tabellen und Views. Wenn man zB aus der DB db_produktiv die Daten nach db_test kopieren möchte, sollen die Daten im Testsystem nicht dem DB-Nutzer des Produktivsystem gehören. Ähnlich ist auch die Option -x (--no-privileges).

• pg_restore -1:

  Das Einspielen des Datenbank Dumps wird in einer Transaktion ausgeführt. Falls ein Fehler auftritt werden alle Änderungen zurückgenommen.

• Nur Teile eines Dumps einspielen:

  pg_restore -l db.dump > db.list
  vi db.list
  pg_restore -L db.list db.dump
  

  (Siehe auch "man pg_restore" ganz unten)

8 Volltextsuche [toc]

• Fehlermeldung bei integrierten Methoden
  Verwendet man die integrierten Methoden (to_tsvector, ...) kommt es häufig zu folgender Fehlermeldung: FEHLER: could not find tsearch config by locale

  -- lc_collate steht ggf. auf de_DE@euro. Diesen Wert als Default eintragen.
  UPDATE pg_ts_cfg SET locale=current_setting('lc_collate') WHERE ts_name='default';
  

• Eigene Methoden für to_tsvector und to_tsquery: Ich finde das Anpassen der beiden Methoden per ISpell nicht flexibel genug. Es ist einfacher, wenn ich das Zerlegen des Textes in eine Wortliste komplett selber mache.
  
  Die Funktion muss nur eine Zeichenkette zurückgeben, die sich zu einem ts_vector umwandeln lässt. Siehe PostgreSQL Doku: postgresql.org: Datatype textsearch

9 Permissions [toc]

• psql

  \du	Liste aller Rollen.
  \dp	Zeige die Zugriffsrechte der aktuellen Datenbank

10 Links [toc]

• Postgres Tricks