PostgreSQL - Tipps und Tricks

Aus Geoinformation HSR
Version vom 5. Januar 2011, 21:30 Uhr von Stefan (Diskussion | Beiträge) (→‎Daten laden/importieren)

Wechseln zu: Navigation, Suche

Siehe auch:


Meine unsortierten Notizen...

Tools und Konfiguration

  • 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: 
 > psql -h localhost -p 5432 template1 "postgres"
 oder
 > 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"

From: [1]

If you only grant CONNECT to a database, the user can connect but has no other privileges. In PostgreSQL 8.x you have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually. So something like: 

 GRANT CONNECT ON DATABASE mydb TO xxx;
 -- This assumes you're actually connected to mydb..
 GRANT USAGE ON SCHEMA public TO xxx;
 GRANT SELECT ON mytable TO xxx;

Granting 'SELECT' to each table/view individually can be a bore. Something like this can help: 

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

This should output the relevant GRANT commands to GRANT SELECT on all tables and views in public, for copy-n-paste.

PostgreSQL 9:

In PostgreSQL 9.0 its easier. 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: 

 GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

This only affects tables that have already been created, so it's the equivalent of the generated commands above. More powerfully, you can automatically have default roles assigned to new objects in future: 

 ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO osmgis;

Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.


Daten laden/importieren

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
 \.

PostgreSQL SQL

Siehe auch im Wiki der Vorlesung Datenbanken (Dbs1) von Prof. S. Keller.

Version: 

 select version();

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

 SELECT the_geom ... (end_date - NOW() >= 10) AS GREEN 
   AND (end_date - NOW() < 0) AS RED ... FROM ...

Casting changes in >= 8.3: 

 SELECT the_geom ... (end_date - NOW() >= '10 DAYS'::INTERVAL) AS GREEN 
   AND (end_date - NOW() < '0'::INTERVAL) AS RED ... FROM ...

 SELECT the_geom ...
   EXTRACT(DAY FROM (end_date - NOW()) >= 10) AS GREEN
   AND EXTRACT(DAY FROM (end_date - NOW()) < 0) AS RED ... FROM ...

hstore

Stichworte: Key-Value-Attributes, Hashtable, nosql-Database.

tbd.


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

PostgreSQL-Optimierung

Bevor man beginnt und postgresql.conf etc. verändert...

  • ANALYSE und CLUSTER-Befehl ausführen: Siehe [2].
  • Den aktuellen Zustand testen mit dem Tool pgbench.

Tipps zur Optimierung (Optimization), Performance, Tuning von PostgreSQL...:

  1. Make sure you have (spatial) indexes on your tables.
  2. Cluster on (spatial) indexes tends to improve performance but mostly good for static spatial data.
  3. PostGIS: A spatial index is more effective on small geometries than larger geometries even for MySQL.
  4. EXPLAIN ANALYZE is your friend -- especially PgAdmin III Graphical Explain - will pinpoint when indexes are not being used and what navigation strategies are being used.
  5. posgresql.conf - fiddling with that a lot has been written -- particularly work_mem is important.
  6. Then there is a whole science on how you write sql queries. In general for PostgreSQL - you can force PostgreSQL to materialize subselects by putting in an ORDER BY and in general a subselect tends to force the planner to evaluate those as a single unit of work, which tends to improve speed in many cases.

SQL:

  • Postgres kennt "Partial index": Beispiel: "create index i on table2(field2) where field2 <> 0;" Optimiert z.B. folgende Query: select * from table2 where field2<>0; (vgl. Doc.).

Konfiguration mit postgresql.conf (siehe z.B. [3]):

  • Auto Vacuum einschalten
  • Parameter heraufsetzen:
    • Sort memory - speziell bei order-by oder group-by Klauseln
    • random_page_cost – speziell bei schnellen Disks wie SCSI und RAID
    • Effective Cache Size, work_mem und Shared buffers - speziell bei genügend Memory und wenigen Applikationen und Benutzern

Siehe auch:

Weitere Tipps...

  • Code snippets auf Postgres Wiki
Von «https://giswiki.hsr.ch/index.php?title=PostgreSQL_-_Tipps_und_Tricks&oldid=31546»