Tips + Tricks

Tips und Tricks beim Umgang mit PostgreSQL/Postgis

CSV Import nach PostgreSQL

Beim Export von XLS oder ähnlichem am besten OpenOffice verwenden. Als Trenner "Komma" verwenden, als Codierung UTF-8, und als Quote die doppelten Anführungszeichen (").

CSV Export Dialog von Openoffice

Optional das Datumsformat auf "Deutsch" stellen oder das Encoding auf "Latin1" setzen. Danach COPY Befehl verwenden. Die Befehle sollten direkt auf SRV138 (PostgreSQL DB-Server) ausgeführt werden (ev. Netzlaufwerk mounten):

-- Optional das Datumsformat auf Deutsch setzen
SET DATESTYLE TO german;
-- Optional das encoding setzen
SET CLIENT_ENCODING TO 'value';
-- Daten mit COPY importieren
COPY nest.liegenschaften FROM
 '/projekte/nest/datentransfer/liegenschaften_2009_03_27.csv' CSV;
-- CSV-Daten mit Headerzeile importieren:
COPY nest.liegenschaften FROM
'/projekte/nest/datentransfer/liegenschaften_2009_03_27.csv' CSV HEADER;

oder mit expliziten Spalten und eigenem Delimiter:

COPY hydrologie.eigentum_awel (kreis,parzellen_nummer,status,flaeche,plannummer,gemeinde,gewaesserparzelle)
FROM '/home/an/EigentumAWEL.csv' DELIMITER AS ';' CSV HEADER;

Siehe auch:

Zähler für PostgreSQL View erstellen

QGIS verlangt zwingend einen eindeutigen Primary Key vom Typ int4 oder int8. Bei PostgreSQL views kann man diesen mit folgendem Trick erstellen:

CREATE OR REPLACE VIEW av_user.brunnen AS 
SELECT (ROW_NUMBER() over (order by eo.eob_art))::int4 as gid, eo.the_geom
FROM av_user.einzelobjekte_linien eo
WHERE eo.eob_art = 'Brunnen';

ROW_NUMBER() ist eine windowing Funktion und wurde mit PostgreSQL 8.4 eingeführt. Siehe PostgreSQL Dokumentation. Um das laden des Views zu beschleunigen kann man in QGIS auch manuell den Primary key auswählen. Das beschleunigt das laden des Views weil nicht mehr alle Spalten getestet werden müssen, ob sie als Primary Key in Frage kommen. Danke an Tim Sutton für den Tip!

Autodesk Map3D-Verbindung nach Postgis

Connection-String: db@hostname:portnumber, also z.B. uster@10.63.206.138:5432

Danach erscheint Fenster für User/Passwort und eine Auswahl der Datenbanken (data stores). Achtung: in Map3D/Topobase 2011 muss nach der erstmaligen Verbindung dann der datastore (db) weggelöscht werden, dieser leitet sich dann von der Auswahlliste der datastores (DBs) ab.

psql über Service-Definition verbinden

Mit den folgenden psql-Verbindungsparametern kann man eine Datenbank über eine Service-Definition verbinden:

psql "service=pg_uster" -U username

PostgreSQL-User migrieren

Mit dem folgenden Befehl können die Rollen (User und Gruppen) für eine DB-Migration ausgelesen werden:

pg_dumpall -r -f roles_db_uster.dump

Danach können diese mit dem folgenden Befehl wieder eingelesen werden:

psql -U postgres -d postgres -f roles_db_uster.dump

Das Backup der Rollen kann auch mit crontab automatisiert werden, etwa mit dem folgenden Eintrag:

4 21 * * * /usr/local/pgsql/bin/pg_dumpall -r -f /usr/local/pgsql/dbbackup/uster_dbroles.dump

PostgreSQL Passwort ändern

Zuerst mit psql einloggen und auf die DB "postgres" verbinden (psql -U postgres -d postgres), dann folgenden SQL-Befehl eingeben:

ALTER ROLE postgres WITH PASSWORD 'mysecretpw';

Zugangsberechtigungen setzen

Damit ein Zugriff aller Rechner übers Netz möglich ist, muss unter /usr/local/pgsql/data/postgresql.conf die Zeile

listen_addresses = '*'

gesetzt werden. Der Wert kann auch auf einzelne IP-Adressen oder Bereiche eingeschränkt sein. Danach muss noch in der Datei /usr/local/pgsql/data/pg_hba.conf definiert werden welche IPs auf welche DBs zugreifen können:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv4 remote connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Die Zugriffsmethoden sollten von trust auf mindestens md5 oder andere Methoden umgeändert werden. Nach Änderungen in postgresl.conf oder pg_hba.conf muss Postgresql mit /etc/init.d/postgresql start neu gestartet werden.

DB-Migration von Server A nach Server B

Die Daten müssen mit dem folgenden Befehl vom Server A exportiert werden:

pg_dump -Fc uster >/usr/local/pgsql/dbbackup/uster.dump

Hier wären die zugehörigen crontab-Entries für eine automatische Sicherung an 5-Tagen die Woche:

# m h  dom mon dow   command
5 21 * * 1 /usr/local/pgsql/bin/pg_dump -Fc uster >/usr/local/pgsql/dbbackup/uster_montag.dump
5 21 * * 2 /usr/local/pgsql/bin/pg_dump -Fc uster >/usr/local/pgsql/dbbackup/uster_dienstag.dump
5 21 * * 3 /usr/local/pgsql/bin/pg_dump -Fc uster >/usr/local/pgsql/dbbackup/uster_mittwoch.dump
5 21 * * 4 /usr/local/pgsql/bin/pg_dump -Fc uster >/usr/local/pgsql/dbbackup/uster_donnerstag.dump
5 21 * * 5 /usr/local/pgsql/bin/pg_dump -Fc uster >/usr/local/pgsql/dbbackup/uster_freitag.dump

Danach kann das dump-file über scp oder ähnliches transferiert werden und dann in einer ssh-Session wie folgt wieder eingelesen werden:

#Optional: dropdb uster
createdb uster
psql -f /usr/local/pgsql/share/contrib/postgis-1.5/postgis.sql -d uster
new_postgis_restore.pl uster_donnerstag.dump | psql uster
Artikelaktionen
Sitemap AGB Impressum