PostgreSQL Datenbankpraxis für Anwender, Administratoren und Entwickler

Kapitel 14 - Häufige Fragen

Seite 430

pgpass.conf

Hostname:Port:Datenbank:Benutzername:Passwort


Pfad zu Konfigurationsdateien herausfinden

SHOW hba_file;
SHOW data_directory;

Seite 431

Blockierende Anfragen finden

SELECT database,relation,transaction,mode,granted,pid
              (SELECT relname
                 FROM pg_class
                WHERE oid=pg_locks.relation) AS relname
          FROM pg_locks
         WHERE relation IS NOT NULL
           AND mode != 'AccessShareLock';


Alle Anfragen

SELECT procpid,current_query
         FROM pg_stat_activity
        WHERE current_query NOT LIKE '%IDLE%';

Seite 432

Zeitmessung aktivieren und deaktivieren

\timing


Zeitmessung aktivieren

\timing on


Zeit messen

SELECT lang_laufende_funktion();

Seite 433

Installation von tablelog

make USE_PGXS=1


Installation von tablelog

make USE_PGXS=1 install


Funktionen in der Datenbank registrieren

CREATE FUNCTION table_log ()
      RETURNS TRIGGER
           AS '$libdir/table_log' LANGUAGE 'C';
CREATE FUNCTION "table_log_restore_table"
              (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ)
      RETURNS VARCHAR
           AS '$libdir/table_log', 'table_log_restore_table' LANGUAGE 'C';

Seite 434

Beispieltabelle erstellen

CREATE TABLE test (
  id                    SERIAL              NOT NULL
                                            PRIMARY KEY,
  daten                 VARCHAR(20)         NOT NULL
);
CREATE TABLE


Logtabelle erstellen

SELECT * INTO test_log FROM test LIMIT 0;
ALTER TABLE test_log ADD COLUMN trigger_mode VARCHAR(10);
ALTER TABLE test_log ADD COLUMN trigger_tuple VARCHAR(5);
ALTER TABLE test_log ADD COLUMN trigger_changed TIMESTAMPTZ;
ALTER TABLE test_log ADD COLUMN trigger_id BIGINT;
CREATE SEQUENCE test_log_id;
SELECT SETVAL('test_log_id', 1, FALSE);
ALTER TABLE test_log ALTER COLUMN trigger_id
         SET DEFAULT NEXTVAL('test_log_id');
ALTER SEQUENCE test_log_id OWNED BY test_log.trigger_id;

Seite 435

Trigger erstellen

CREATE TRIGGER test_log_chg
        AFTER UPDATE OR INSERT OR DELETE ON test FOR EACH ROW
      EXECUTE PROCEDURE table_log();


Beispieldaten einfügen

INSERT INTO test (daten) VALUES ('Datensatz 23');
\x
SELECT * FROM test_log ORDER BY trigger_id;


Beispieldaten ändern

UPDATE test SET daten = 'Datensatz 42' WHERE id = 1;
SELECT * FROM test_log ORDER BY trigger_id;

Seite 436

Beispieldaten ändern

DELETE FROM test WHERE id = 1;
SELECT * FROM test_log ORDER BY trigger_id;


Datensatz wiederherstellen

SELECT table_log_restore_table('test', 'id',
              'test_log', 'trigger_id',
              'test_geschichte', '2008-07-06 14:10:00+02');
SELECT * FROM test_geschichte;

Seite 437

Ausführungsrechte ändern

ALTER FUNCTION table_log() SECURITY DEFINER;


Zugriffsrechte ändern

REVOKE ALL ON test_log FROM PUBLIC;

Seite 438

Größe einer Tabelle herausfinden

SELECT pg_relation_size('vacuum_test');


Ungefähre Größe einer Tabelle

SELECT pg_size_pretty(pg_relation_size('vacuum_test'));


Größe eines Index herausfinden

SELECT pg_size_pretty(pg_relation_size('vacuum_test_pkey'));

Seite 439

Gesamtgröße einer Tabelle herausfinden

SELECT pg_size_pretty(pg_total_relation_size('vacuum_test'));


Gesamtgröße einer Tabelle herausfinden

SELECT pg_size_pretty(pg_database_size('test'));


Verbrauchten Platz auf einem Tablespace herausfinden

SELECT pg_size_pretty(pg_tablespace_size('pg_default'));


Platzverbrauch pro Datensatz

SELECT pg_column_size(23::INTEGER);
SELECT pg_column_size(23::BIGINT);
SELECT pg_column_size('abcde'::TEXT);

Seite 440

Platzverbrauch aller Datenbanken

SELECT pg_database.datname AS datenbank,
              pg_size_pretty(pg_database_size(pg_database.datname)) 
              AS groesse
         FROM pg_database;

Seite 441

Alle Zugriffsrechte ändern

CREATE OR REPLACE FUNCTION grantall(TEXT, TEXT)
       RETURNS VOID AS $$
DECLARE
  privileg ALIAS FOR $1;
  role_name ALIAS FOR $2;

  lr RECORD;
BEGIN
  FOR lr IN
      SELECT table_schema, table_name
        FROM information_schema.tables
       WHERE table_schema != 'pg_catalog'
         AND table_schema != 'information_schema'
         AND table_type = 'BASE TABLE'
  LOOP
      RAISE NOTICE 'Tabelle: %.%', lr.table_schema, lr.table_name;
      EXECUTE 'GRANT ' || privileg || ' ON ' ||
              lr.table_schema || '.' || lr.table_name ||
              ' TO ' || role_name;
  END LOOP;

  RETURN;
END;
$$ LANGUAGE 'plpgsql';

Seite 442

Datenbank im Standalone Modus starten

/pfad/zum/postgres-binary --single -P -D /pfad/zu/den/Daten datenbankname


Datenbank mit pg_dump sichern

pg_dump --file=alte_db.sql test

Seite 443

Neue Datenbank erstellen

createdb --endocing UTF8 neue_db


Backup mit psql einspielen

psql --file=alte_db.sql neue_db


Datenbank löschen

dropdb test


Datenbank umbenennen

ALTER DATABASE neue_db RENAME TO test;


SSH-Tunnel aufbauen

ssh -L 2334:datenbankserver:5432 datenbankserver

Seite 444

Datenbankverbindung starten

psql -h 127.0.0.1 -p 2334 test


beispielhafter pg_hba.conf Eintrag

host      test      ads      127.0.0.1/32      md5


Maximale Anzahl der Verbindungen beschränken

ALTER DATABASE datenbankname CONNECTION LIMIT 5;


Anzahl Verbindungen beschränken

ALTER ROLE benutzer CONNECTION LIMIT 1;

Seite 445

Limit aufheben

ALTER ROLE benutzer CONNECTION LIMIT -1;

Seite 446

BIGINT vs. UUID

SELECT round(power(2::NUMERIC, 64::NUMERIC));


Gesamter Wertebereich der UUID

SELECT round(power(2::NUMERIC, 64::NUMERIC));

Seite 447

Eingabeformate für UUID

SELECT 'b50ae398-ff67-11dd-8830-001b3822847d'::UUID;
SELECT '{b50ae398-ff67-11dd-8830-001b3822847d}'::UUID;
SELECT 'b50ae398ff6711dd8830001b3822847d'::UUID;


DNS-Name der Maschine nutzen

SELECT uuid_ns_dns();

Seite 448

Reine Zufallswerte

SELECT uuid_generate_v1();


DNS-Name als Parameter 1 sowie eine fortlaufende Nummer

SELECT uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8', '1');
SELECT uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8', '2');

Seite 449

Beispieltabelle für ISBN

CREATE TABLE isbn_test (nr isbn13);
INSERT INTO isbn_test (nr) VALUES ('978-3-937514-69-7');
SELECT nr FROM isbn_test;


ISBN-10 und EAN ausgeben

SELECT isbn(nr) FROM isbn_test;


ISBN-10 in ISBN-13 umwandeln

SELECT isbn13('3-937514-69-4');

Seite 450

Fehlerhafte ISBN eingeben

SELECT isn_weak(TRUE);
INSERT INTO isbn_test (nr) VALUES ('978-4-34-356321-5');
SELECT isn_weak(FALSE);
SELECT nr FROM isbn_test;


„invalid“-Status ändern

UPDATE isbn_test
          SET nr = make_valid(nr)
        WHERE nr = '978-4-343-56321-7!'
SELECT nr FROM isbn_test;

Seite 451

ISBN-13 validieren

CREATE OR REPLACE FUNCTION isbn13_pruefen(TEXT)
       RETURNS BOOLEAN
AS $$
DECLARE
  isbn_nr ALIAS FOR $1;
  weak_status BOOLEAN;
  isbn_status BOOLEAN;
BEGIN

  weak_status := isn_weak(FALSE);
  isbn_status := is_valid(isbn_nr::isbn13);
  weak_status := isn_weak(weak_status);

  RETURN isbn_status;

  EXCEPTION
       WHEN invalid_text_representation THEN
     RETURN false;
END;
$$
LANGUAGE 'plpgsql';


ISBN-13 validieren

SELECT isbn13_pruefen('978-3-937514-69-7');
SELECT isbn13_pruefen('243-5-375514-69-4');

Seite 452

Per Zufall sortieren

SELECT generate_series(1,10), random() AS sort_order
     ORDER BY sort_order;
SELECT generate_series(1,10)
     ORDER BY random();


Unix-Timestamp erzeugen

SELECT EXTRACT(EPOCH FROM NOW());


Unix-Timestamp in PostgreSQL-Timestamp umrechnen

SELECT TO_TIMESTAMP(1218231720);

Seite 453

Datenbank für ip4r vorbereiten

\i /usr/share/postgresql/8.3/contrib/ip4r.sql


Datentyp IP4 nutzen

SELECT '127.0.0.1'::IP4;


Berechnungen mit IP-Adressen

SELECT '192.168.23.45'::IP4 - 100;

Seite 454

INET in IP4 umwandeln

SELECT '127.0.0.1'::INET::IP4;
SELECT '127.0.0.1'::IP4::INET;


Netzwerkbereiche angeben

ELECT '192.168.0.0/24'::IP4R;
SELECT '192.168.0.0-192.168.0.25'::IP4R;

Seite 455

Beispieltabellen

CREATE TABLE netzmaske_cidr (nm CIDR);
CREATE INDEX netzmaske_cidr_nm ON netzmaske_cidr(nm);
INSERT INTO netzmaske_cidr (nm) VALUES ('192.168.0.0/24');
CREATE TABLE netzmaske_ip4r (nm IP4R);
CREATE INDEX netzmaske_ip4r_nm ON netzmaske_ip4r USING gist(nm);
INSERT INTO netzmaske_ip4r (nm) VALUES ('192.168.0.0/24');


Sequentielle Scans vermeiden

SET enable_seqscan TO off;


IP-Adresse überprüfen

SELECT nm FROM netzmaske_cidr
        WHERE '192.168.0.23'::INET << nm;
SELECT nm FROM netzmaske_ip4r
        WHERE '192.168.0.23'::IP4R << nm;

Seite 456

EXPLAIN-Ausgabe

EXPLAIN SELECT nm FROM netzmaske_cidr
                WHERE '192.168.0.23'::INET << nm;
EXPLAIN SELECT nm FROM netzmaske_ip4r
                WHERE '192.168.0.23'::IP4R << nm;


Von CIDR auf IP4R migrieren

ALTER TABLE netzmaske_cidr ALTER COLUMN nm TYPE IP4R;


Index erzeugen

CREATE INDEX netzmaske_cidr_nm
           ON netzmaske_cidr USING gist(nm);

Seite 457

Funktionalen Index erzeugen

CREATE INDEX netzmaske_cidr_nm2
           ON netzmaske_cidr USING gist(ip4r(nm));


Funktionalen Index verwenden

EXPLAIN SELECT nm FROM netzmaske_cidr
                WHERE '192.168.0.23'::IP4R << ip4r(nm);

Seite 458

Beispielanfrage in PHP

$query = "SELECT *
            FROM benutzer
           WHERE benutzername = '$benutzername'";


Beispielanfrage mit eingeschleustem Code

$query = "SELECT *
            FROM benutzer
           WHERE benutzername = '';
          DELETE FROM benutzer;
          SELECT '1'";

Seite 459

Beispielanfrage mit gequotetem String

$query = "SELECT *
            FROM benutzer
           WHERE benutzername = '" . quote($benutzername) . "'";


Beispielanfrage mit gequotetem String

$query = "SELECT *
            FROM benutzer
           WHERE benutzername = '\';
                 DELETE FROM benutzer;
                 SELECT \'1'";

Seite 460

Beispielanfrage mit Prepared Statement

$query = "SELECT *
            FROM benutzer
           WHERE benutzername = $1";
$st_name = "benutzer_einloggen";

$statement = pg_prepare($db, $st_name, $query);


Beispielanfrage mit Prepared Statement

$ergebnis = pg_execute($db, $st_name, array($eingabe));

Seite 461

Daten mit SQL-Injection ausspähen

$query = "SELECT id,ueberschrift,inhalt
            FROM artikel
           WHERE id = $id";


Daten mit SQL-Injection ausspähen

$id = 23 UNION SELECT usesuper::INTEGER AS id,
                      usename AS ueberschrift,
                      passwd AS inhalt
                 FROM pg_shadow


Daten mit SQL-Injection ausspähen

$query = "SELECT id,ueberschrift,text
            FROM artikel
           WHERE id = 23
           UNION
          SELECT usesuper::INTEGER AS id,
                 usename AS ueberschrift,
                 passwd AS inhalt
            FROM pg_shadow";

Seite 464

Statistiken

SELECT relname, schemaname, n_live_tup, n_dead_tup
         FROM pg_stat_user_tables;