Aus Cismet Wiki

Inhaltsverzeichnis 1 Erstellen einer Postgres Aggregationsfunktion 2 Dumps 2.1 Dumps erzeugen 2.2 Dumps einspielen 3 Wie werden die Postgis-Geometrieindizes neu angelegt ? 4 Constraints zu einer Relation/Tabelle anzeigen 5 Wie wird die Postgres Datenbank wieder frisch gemacht? 6 Zugriff für PostgreSQL übers Netzwerk 6.1 Konfiguration 6.2 Troubleshooting 6.2.1 Links 7 Postgis Statements zu Geometrie Typen GeometryType 7.1 Zeige Geometrien eines bestimmten Typs an 7.2 Zeige welche Typen vorhanden sind 8 Postgres auf Latin9 umstellen bzw. encoding verändern 8.1 Umgebungsvariable client_encoding setzen, bevor man einen Dump erzeugt 8.2 Datenbank createdb Parameter 8.3 Dumps falls falsch codiert, konvertieren (ascii->latin9) mit 8.4 Encoding rausfinden, man kann die Tabelle auch updaten um das encoding zu verändern wenn noch keine daten drinn sind 9 Datenbank dropdb scheitert, weil Benutzer verbunden 10 Sequenzen setzen 11 Zu welcher DB bestehen gerade welche Connections? 12 Wie startet/stoppt man die Postgres DB?

Erstellen einer Postgres Aggregationsfunktion

die bestehende Funktion array_accum kann jetzt als Aggregationsfunktion wie sum, oder max verwendet werden

CREATE AGGREGATE array_accum (
      sfunc = array_append,
      basetype = anyelement,
      stype = anyarray,
       initcond = '{}'
     );

Anwendung: dieses Statement Produziert eine Kommaseparierte Liste von Beschreibungen die über Group by gruppiert wurden. Hier: alle Beschreibungen zu einer tnr

SELECT tnr, array_to_string(array_accum(trim(descr)),',') AS description, 'EPSG:4326' AS crs , a7 AS ausdehnung         FROM 
 ( 
 SELECT tnr,a7, 'Bergisches Städtedreieck und größer' AS descr 
 FROM t_a7 WHERE a7=1 
 union 
 SELECT tnr, a7, 'Stadt Wuppertal' AS descr 
 FROM t_a7 WHERE a7=2 
 
 ) AS y 
 GROUP BY tnr,a7

Dumps

Dumps erzeugen

Mit dem Befehl:

pg_dump dbname > outfile

können SQL-Dumps erzeugt werden.

Dumps einspielen

Mit dem Befehl:

psql dbname < infile
psql -d database -f file

können dumps die mit dem vorherigen Befehl erzeugt wurden eingespielt werden.

Wie werden die Postgis-Geometrieindizes neu angelegt ?

SELECT update_geometry_stats();

Constraints zu einer Relation/Tabelle anzeigen

Zeigt die Constraints zu cs_class an

SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE 
 pg_class.relname='cs_class' AND pg_class.oid=pg_index.indrelid AND ( indisunique = 't' OR indisprimary = 't' )
 );

Wie wird die Postgres Datenbank wieder frisch gemacht?

vacuum analyze;

Zugriff für PostgreSQL übers Netzwerk

Dieser Artikel beschreibt wie man PostgreSQL so konfiguriert, dass andere Rechner über das Netzwerk auf die Datenbank zugreifen können.

Konfiguration

• Zuerst müssen in der Datei /usr/local/pgsql/data/postgresql.conf folgender Eintrag angepasst werden

 #listen_addresses = 'localhost'

zu

 listen_addresses = '*'

• Dann muss in der Datei /usr/localhost/pgsql/data/pg_hba.conf der Eintrag:

 host	 all	 all	 192.168.100.0/24	 trust

hinzugefügt werden. 192.168.100.0/24 bedeutet das alle Rechner die mit der IP Adresse 192.168.100. beginnen darauf zugreifen können.

• Nach den Anpassungen muss der Postmaster (der PosgreSQL Dienst) benachrichtigt werden das die Konfiguration sich geändert hat:

 pg_ctl reload

oder

 pg_ctl restart

Troubleshooting

Falls eine Verbindung immer noch nicht möglich ist sollte geprüft werden, ob auf dem Datenbankrechner eine Firewall aktiv. Falls dem so ist sollte der PostgreSQL Standardport 5432 freigegeben werden.

Links

• Connection listening
• Client Authentication

Postgis Statements zu Geometrie Typen GeometryType

Zeige Geometrien eines bestimmten Typs an

SELECT geometryType(geo_field), geo_field FROM geom WHERE geometryType(geo_field)= 'GEOMETRYCOLLECTION'; 
SELECT geometryType(geo_field), geo_field FROM geom WHERE geometryType(geo_field)= 'MULTIPOLYGON';

Zeige welche Typen vorhanden sind

SELECT geometryType(geo_field), count(*) FROM geom GROUP BY geometryType(geo_field);

Postgres auf Latin9 umstellen bzw. encoding verändern

Umgebungsvariable client_encoding setzen, bevor man einen Dump erzeugt

export PGCLIENTENCODING=LATIN9

Datenbank createdb Parameter

createdb --encoding=latin9

Dumps falls falsch codiert, konvertieren (ascii->latin9) mit

iconv -f ISO-8859-1 -t ISO-8859-15 file

Encoding rausfinden, man kann die Tabelle auch updaten um das encoding zu verändern wenn noch keine daten drinn sind

select encoding from pg_database where datname='dbname'
psql -l :-)

Datenbank dropdb scheitert, weil Benutzer verbunden

Besteht noch eine Verbindung zur Datenbank, kann diese nicht gelöscht werden. Ist es aus irgendwelchen Gründen nicht möglich, die Verbindung normal zu beenden, kann mit Hilfe des folgenden Befehls der entsprechende Prozess abgebrochen werden:

pkill -f 'postgres: postgres <database>'

Sequenzen setzen

Am Beispiel einer Tabelle geom

SELECT SETVAL('GEOM_SEQ',(SELECT max(ID)+1 FROM GEOM));

Selects für alle Tabellen generieren:

SELECT 'select setval('''||table_name||'_seq'', (select max('||primary_key_field||')+1 from ' || table_name||'));' FROM cs_class;

Zu welcher DB bestehen gerade welche Connections?

ps -ef | grep postgres

Wie startet/stoppt man die Postgres DB?

pg_ctl start/stop