Tips + Tricks
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 (").
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:
- PostgreSQL COPY-Befehl
- PostgreSQL - Setting Runtime Environment Variables
- PostgreSQL Character Set Support
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.
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
