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;