pgpass.conf

Hostname:Port:Datenbank:Benutzername:Passwort

Pfad zu Konfigurationsdateien herausfinden

SHOW hba_file;

SHOW data_directory;

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%';

Zeitmessung aktivieren und deaktivieren

\timing

Zeitmessung aktivieren

\timing on

Zeit messen

SELECT lang_laufende_funktion();

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';

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;

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;

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;

Ausführungsrechte ändern

ALTER FUNCTION table_log() SECURITY DEFINER;

Zugriffsrechte ändern

REVOKE ALL ON test_log FROM PUBLIC;

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'));

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);

Platzverbrauch aller Datenbanken

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

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';

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

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

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;

Limit aufheben

ALTER ROLE benutzer CONNECTION LIMIT -1;

BIGINT vs. UUID

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

Gesamter Wertebereich der UUID

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

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();

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');

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');

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;

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');

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);

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;

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;

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;

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);

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);

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'";

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'";

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));

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";

Statistiken

SELECT relname, schemaname, n_live_tup, n_dead_tup
         FROM pg_stat_user_tables;