PostgreSQL - Tipps und Tricks: Unterschied zwischen den Versionen

Aktuelle Version vom 9. November 2020, 10:34 Uhr

Siehe auch:

• PostgreSQL, PostgreSQL optimieren und PostgreSQL-Datentyp hstore
• PostGIS und PostGIS - Tipps und Tricks

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:

• Foreign_data_wrappers [2]
• SQL/MED [3]

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.

Full Text Search

Siehe PostgreSQL FTS - Tipps und Tricks