PostgreSQL - Tipps und Tricks
Siehe auch:
- PostgreSQL, PostgreSQL optimieren und PostgreSQL-Datentyp hstore
- PostGIS und PostGIS - Tipps und Tricks
Inhaltsverzeichnis
Stefan's Notizen...
Passwörter
- Setzen/Ändern Passwort für eine Datenbank:
% psql -d unsere_db -c "ALTER USER unsere_db WITH PASSWORD 'neues_passwort';"
- TIPP: PG Programme haben nicht gern, wenn das PW fehlt!
- TIPP: Das Setzen des Passworts für Super User postgres auf PW postgres verlangt template1 und nicht postgres:
% psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'postgres';"
- psql und wohl alle weiteren PostgreSQL-Tools (dropdb, createdb, pg_dump, etc.) u.a. brauchen Angaben zu Benutzer und Passwort. Der Benutzer wird mit Parameter -U mitgegeben, das Passwort hingegen kann (mit Absicht) nicht mitgegeben werden. Es gibt aber die Möglichkeit, Umgebungsvariablen zu setzen:
SET PGUSER=postgres SET PGPASSWORD=<meinpasswort>
- Start pgsl-Client mit Umlauten (Windows):
> cmd /c chcp 1252 > psql -d gisdb -U postgres postgres=# SET CLIENT_ENCODING TO 'LATIN1'; postgres=# <do whatever you must do in psql...> > cmd /c chcp 850
Create "Read-only User"
Generate 'SELECT' grant commands from a catalog query:
SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;' FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE nspname = 'public' AND relkind IN ('r', 'v')
Good to know:
- If you only grant CONNECT to a database, the user can connect but has no other privileges.
- PG makes a distinction between GRANT SELECT and GRANT USAGE for security reasons (poking metadata about tables).
- PG knows a schema PUBLIC and also a 'default role' (which define 'DEFAULT PRIVILEGES') also called PUBLIC.
- PG sets (and 'inherits') security 'flags' at the time of the commands (e.g. GRANT). This means, 1. when a 'parent-flag' changes this change is not propagated ('inherited') and 2. A "GRANT SELECT ON ALL TABLES IN SCHEMA public TO ourtable;" only affects tables that have already been created.
- Since PostgreSQL 9.0 you still need to grant USAGE permissions on schemas, but you can grant permissions on all tables, views etc. in the schema using a single command rather than having to generate them:
Read-Only user (with variants):
# Prevent default users from creating tables REVOKE CREATE ON SCHEMA public FROM public; REVOKE USAGE ON SCHEMA public FROM public; # Create read-only user CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser; GRANT USAGE ON SCHEMA public TO readonlyuser; -- omit USAGE if needed # Restore USAGE/CREATE to default user GRANT USAGE ON SCHEMA public TO public; GRANT CREATE ON SCHEMA public TO public;
(Source: note that not all answers are uptodate or correct: https://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql )
Daten laden/importieren
Siehe http://wiki.postgresql.org/wiki/COPY
COPY Syntax:
- Tab-Delimited
COPY geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) FROM stdin; catalog public planet_osm_point way 2 900913 POINT catalog public planet_osm_line way 2 900913 LINESTRING \.
Foreign Data Wrappers
Ab Version 9.1 werden Foreign Data Wrappers (FDW) und ein Teil des SQL/MED-Standards unterstützt. file_fdw ist eine offizielle Extension (contrib module): [1].
Siehe auch PostgreSQL Wiki:
Linking open and remote (PostgreSQL) read-only databases easily! Uses postgres_fdw the SQL/MED implementation (Foreign Data Wrapper).
Caveats:
- Must be superuser to create extension postgres_fdw.
- Joins are slow or even never returning.
Future Work:
- Create a stored procedure link_pg_server(...).
- Promote sharing of endpoints like SPARQL.
Tested with PostgreSQL 11. Credits: https://robots.thoughtbot.com/postgres-foreign-data-wrapper
-- Step 1: Preparation CREATE EXTENSION IF NOT EXISTS postgres_fdw; -- Connect to remote DB server: DROP SERVER IF EXISTS gis_db_server CASCADE; CREATE SERVER gis_db_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx', port 'xxx', dbname 'xxx'); CREATE USER MAPPING FOR CURRENT_USER SERVER gis_db_server OPTIONS (user 'xxx', password 'xxx'); -- Step 2: Import Schema DROP SCHEMA IF EXISTS gis_db_public CASCADE; CREATE SCHEMA gis_db_public; CREATE EXTENSION IF NOT EXISTS postgis; CREATE EXTENSION IF NOT EXISTS hstore; -- needed by gis_db schema IMPORT FOREIGN SCHEMA public FROM SERVER gis_db_server INTO gis_db_public; -- Step 3: Go! SELECT COUNT(*) FROM gis_db_public.osm_point; -- Slow - or even never returning! No operator pushdown ??? -- (no "join op nor tmp table sent to linked db) -- See EXPLAIN: https://explain.depesz.com/s/PStr WITH tmp(osm_id, name) AS ( VALUES (4066310784, 'Tierpark Chur'), (1398864548, 'Tierpark Aletsch') ) SELECT point.* FROM gis_db_public.osm_point AS point JOIN tmp ON tmp.osm_id=point.osm_id;
PostgreSQL SQL
Siehe auch im Wiki der Vorlesung Datenbanken (Dbs1) von Prof. S. Keller.
Version:
select version();
Finding duplicate values in one or more columns:
SELECT * FROM ( SELECT row_number() OVER (PARTITION BY email), name, email FROM dedup) t WHERE t.row_number < 2;
Anfrage des Geometrie-Typs ('the_geom' ist der Name des Geometrie-Felds):
SELECT * FROM sometable WHERE GeometryType(the_geom) = 'GEOMETRYCOLLECTION'
Problem mit Datenfelder (for < 8.3):
EXTRACT(DAY FROM (end_date - NOW()) >= 10) AS GREEN AND EXTRACT(DAY FROM (end_date - NOW()) < 0) AS RED ... FROM ...
Alle Tabellen und Indexes (danalog \di):
SELECT n.nspname AS "Schema", c.relname AS "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END AS "Type", u.usename AS "Owner", c2.relname AS "Table" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('i',) AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2
hstore
Stichworte: Key-Value-Attributes, Hashtable, nosql-Database.
Siehe auch:
- PostgreSQL docs.
- Seminar thesis from M. Ott about "Key/Value Pair Versus hstore - Benchmarking Entity-Attribute-Value Structures in PostgreSQL" in [4]
- Beispiele: PostGIS Terminal.
Binary Large Objects (LOB, BLOB)
Siehe PostgreSQL - Binary Large Objects.
Data Warehousing mit PostgreSQL
Stichworte: PostgreSQL-Module cube, PostgreSQL-Module tablefunc (mit crosstab).
tbd.
Konfiguration
Die pg_hba.conf kann so eingestellt werden, dass bei der Ausführung von PostgreSQL-Kommandozeilen-Tools kein Passwort übertragen werden muss.
Bsp mit 127.0.0.1: Anstelle
# IPv4 local connections: host all all 127.0.0.1/32 md5
neu:
# IPv4 local connections: host all all 127.0.0.1/32 trust
Backup und Restore
Dump whole PostgreSQL cluster ('instance') with pg_dumpall. This includes the globals--information about users and groups, tablespaces, and so on. Outputs are just text files.
# pg_dumpall -U postgres -h localhost -p 5432 --clean --file=dump.sql
If going to backup a single database use pg_dump including globals:
# pg_dump -U postgres -h localhost -p 5432 --clean --file=sandbox.sql sandbox # pg_dumpall -U postgres -h localhost -p 5432 --clean --globals-only --file=globals.sql
To restore, load the globals first, then the database dump:
# psql -U postgres -h localhost -p 5433 < globals.sql # psql -U postgres -h localhost -p 5433 < sandbox.sql
Here's another example:
# Backup full database pg_dump -h host -U user -W database > backup.sql # Restore full database psql -d database -f backup.sql
# Backup database schema pg_dump -sv prueba -O > backup.schema.sql
# Backup database data pg_dump -Fc -f backup.data.dump -a --disable-triggers database
Tipps:
- Dont't store data in schema 'public' for several reasons. One is, that until PG 8, all db additional objects (functions, triggers) are maintainted in schema public.
- Eventually switch off Privileges. See also Tab „Dump Options #1“ > Don’t Save Privilege in pgAdmin3.
Doku.:
- 'PostgreSQL 9.0 pg_dump, pg_dumpall, pg_restore cheatsheet' from postgresonline.com.
- PostGIS Hard Upgrade
- PostgreSQL Chapter 24. Backup and Restore
Weitere Tipps...
Data Generator
Schritt 1: Demo / Tests:
select g as no, random() as rand_01_float, (random() * 2147483647)::int as rand_uint, trunc(random() * 6 + 1) as range_1_6_int, -- >=1 and <=6 md5(random()::text) as char32 from generate_series(1,20) g order by 4;
Generate series directly with dates. No need to use ints or timestamps:
select datetime::date -- only date part of datetime from generate_series('2018-03-01'::date,'2018-03-05'::date,'1 day'::interval) datetime;
Step 2: Generate table which fits' to schema:
create table foo ( ... )
Generate data and insert it into table:
insert into foo (id, remarks) values (generate_series(1,3), 'b '||md5(random()::text));
select * from foo order by 1;
Convert text to number
See [5]
DOMAIN, ENUM or Lookup-Table?
DOMAIN is ANSI SQL 92, ENUM is PostgreSQL specific. If compatibility is no issue then I'd prefer ENUM.
See also Blog about "FK, CHECK, ENUM or DOMAIN. That is the question." by Joshua Drake, Jan 21st, 2009.
DATE or TIMESTAMP?
DATE is year, month and day, whereas TIMESTAMP contains minutes, hours and seconds - and (optionally) timezone. DATE is easier to work with for arithmetic (e.g. something reoccurring at a random interval of days), takes less storage space, and doesn't trail "00:00:00" strings you don't need when printed. However, TIMESTAMP is far better for real calendar calculations (e.g. something that happens on the 15th of each month or the 2nd Thursday of leap years). See http://wiki.postgresql.org/wiki/Working_with_Dates_and_Times_in_PostgreSQL
What is the best way to insert data?
INSERT INTO sometable VALUES (13, 'test', 13, '2012-12-02'); INSERT INTO sometable VALUES (14, 'test', 14, '2012-12-02'::timestamp); INSERT INTO sometable VALUES (15, 'test', 15, timestamp '2012-12-02');
First line does not check datetype, so I'd prefer one of the other two.
SQL MERGE Command
The PostgreSQL documentation provides a solution for an MERGE/INSERT function. 'UPSERT' is the PostgreSQL slang for ANSI SQL 'Merge' and means UPDATE-SELECT. But this solution proposal has concurrency issues which have been avoided by this proposal (by Depesz's Blog [6]). See also http://wiki.postgresql.org/wiki/SQL_MERGE
PL/pgSQL-Funktion für ein MERGE/UPSERT mit einem Attribut ('in_whatever'):
-- All function parameters and all the update and insert -- statements in the body need to be adapted for specific tables CREATE OR REPLACE FUNCTION merge_db( key INT, data TEXT ) RETURNS void AS $$ BEGIN -- first try to update with primary key as id UPDATE db SET b=data WHERE a = key; --UPDATE db SET b=data,etc. WHERE a = key; IF FOUND THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key BEGIN INSERT INTO db (a,b) VALUES (key, data); --INSERT INTO db (a,b,etc.) VALUES (a,b,etc.); EXCEPTION WHEN OTHERS THEN -- try another time UPDATE db SET b=data WHERE a = key; END; RETURN; END; $$ LANGUAGE plpgsql;
Unfortunately, there's no other generic function around which would accept any table structure.
Triggers
Tipps:
- Prefer triggers over rules (since 9.0).
- ...
Q: Is there a restriction that an insert trigger can't modify own or other rows of the table that fired it?
A: There's no such restriction. The best way to change the inserted row is to do it before the insertion, though. Trying to modify the inserted row can have slightly strange effects.