Blogposts tagged postgresf.zz.dehttps://f.zz.de/tags/postgres/f.zz.deikiwiki2022-10-05T08:48:54ZPostgis in multiple schemahttps://f.zz.de/posts/202210051030.postgis_in_multiple_schema/Florian Lohoff2022-10-05T08:48:54Z2022-10-05T08:30:19Z
<p>Restoring the very same dump into multiple schemas is not that easy
when using postgis. Typically a pg_dump contains <strong>public.geometry</strong>
when creating the tables which is not reachable as postgis will be in
a different schema at least on the second import.</p>
<p>First create a different schema for the postgis extension</p>
<pre><code>create schema postgis;
create extension postgis with schema postgis;
</code></pre>
<p>Then set the search path for non schema qualified postgis functions
to be found:</p>
<pre><code>alter database joineddb SET search_path TO public,postgis;
</code></pre>
<p>Then feed your psql dumps into psql using sed. This import will end
up in the public schema:</p>
<pre><code>zcat "dump-with-postgis-new.sql.gz" \
| sed -e 's/public.geometry/postgis.geometry/ig'\
| psql -f - joineddb
</code></pre>
<p>Then connect to the database and rename schema:</p>
<pre><code>alter schema public rename to new;
create schema public;
</code></pre>
<p>And import the second file:</p>
<pre><code>zcat "dump-with-postgis-old.sql.gz" \
| sed -e 's/public.geometry/postgis.geometry/ig'\
| psql -f - joineddb
</code></pre>
<p>Now you have both dumps in the database for comparison.</p>
Ansible - Die ersten Gehversuchehttps://f.zz.de/posts/201604012107.ansible_-_die_ersten_gehversuche/Florian Lohoff2016-04-01T19:28:32Z2016-04-01T19:07:45Z
<p><a href="https://www.ansible.com/">Ansible</a> ist schon ganz cool.
Im Gegensatz zu <a href="https://cfengine.com">cfengine</a>, mit
dem ich ja 10 Jahre viel gemacht habe, braucht <em>ansible</em> keinen
client auf dem target System. Alles wird mithilfe von ssh
auf dem target ausgeführt. Einige "spezialmodule" wie
postgresql_* brauchen dann vielleicht noch psycopg2 was man aber
ja wieder automatisiert installieren kann.</p>
<p>So ist dann mal eben ein ansible playbook entstanden was
auf einer Kiste postgres + postgis installiert, ein ganz
bischen tuned, den tablespace verschiebt auf ein definiertes
disk volume, eine Datenbank namens <strong>osm</strong> anlegt mit dem entsprechenden
owner, permissions etc und entsprechenden extensions für die
Datenbank aktiviert. Was dann nur noch fehlt ist halt
ein wget + osm2pgsql und 6 Stunden später hat man eine
fertige OSM Datenbank laufen.</p>
<p>Im Gegensatz zu cfengine bringt <em>ansible</em> jede menge module
mit die mehr oder minder intelligent sind. So merkt das
<em>lineinfile</em> modul das zwar die edit regel gelaufen ist aber
sich die Datei gar nicht geändert hat. Damit kann man
dann verhindern das z.b. Postgres jedesmal restartet wird.
Auch schön gelöst ist im <em>postgresql_user</em> Modul das wenn
man im playbook das password des db users ändert der
auch nur das password auf dem target setzt.</p>
<p>Natürlich legt <em>ansible</em> auch vorher entsprechend mir einen
user an mit password und einem ssh key so das ich direkt
nachdem das playbook gelaufen ist mich einloggen kann.</p>
<p>Nachdem ich glaube ich das <em>ansible</em> Thema anfange zu verstehen
wird es Zeit das an den Installer zu kleben so das man in
der FiDB nicht nur Distribution und Classes definieren kann
sondern auch gleich <em>ansible</em> playbooks die nach dem booten
ausgeführt werden. Dann ist das aufsetzen einer neuen OSM Datenbank
irgendwann nur noch das kleben der Installationsanleitung an einen
Server und ein Klick für den Reboot via IPMI und dann
warten (So 6-10 Stunden für den Import).</p>
<p>Irgendwie ist es ja schon fast langweilig wie einfach das alles
aneinanderzukleben ist.</p>
<pre><code> - hosts: osmtest
remote_user: root
tasks:
- name: install
apt: name={{item}} state=present update_cache=yes cache_valid_time=3600
with_items:
- postgresql
- postgresql-contrib-9.4
- postgresql-9.4-postgis-2.1
- postgresql-9.4-postgis-2.1-scripts
- python-psycopg2
[ ... ]
- hosts: osmtest
vars:
dbname: osm
username: flo
sudo: yes
sudo_user: postgres
tasks:
[ ... ]
- name: Create OSM Database
postgresql_db: name={{dbname}} owner={{username}}
- name: Add Postgis extensions
command: psql --dbname={{dbname}} --command="CREATE EXTENSION IF NOT EXISTS {{item}}"
with_items:
- postgis
- hstore
- name: Fix permissions on postgis tables
command: psql --dbname={{dbname}} --command="ALTER TABLE {{item}} OWNER TO {{username}}"
with_items:
- geography_columns
- geometry_columns
- raster_columns
- raster_overviews
- spatial_ref_sys
[ ... ]
</code></pre>
Männer die auf Schemas starrenhttps://f.zz.de/posts/201603262044.manner_die_auf_schemas_starren/Florian Lohoff2016-03-26T19:47:29Z2016-03-26T19:44:37Z
<p>Wenn man so auf 2-5 Jahre alte SQL Schemas von einem selbst schaut
dann möchte man weinen. Irgendwie verstehe ich nicht mehr was ich gemacht
habe. Visualisierung hilft irgendwie auch nicht. Die essentiellen dinge
sind schon irgendwie mit constrains und foreign keys verbunden. Aber
was sind das für 200 andere Tabellen?</p>
postgres hstore und perl DBIhttps://f.zz.de/posts/201409251109.postgres_hstore_und_perl_dbi/2014-09-25T09:11:39Z2014-09-25T09:09:55Z
<p>HStore ist ja schon ein ziemlich netter Datatype in postgres und ich benutze
den auch zunehmend mehr. Heute bin ich allerdings aufgeschmissen. In einem
SQL Statement prüfe ich die existenz eines keys im hstore. Operator
ist das "?".</p>
<p> and ways.tags ? 'highway'</p>
<p>Leider ist im Perl DBI Interface das ? auch der placeholder für die variable
bindings. D.h. das obige statement wirft jetzt die Fehlermeldung:</p>
<p> DBD::Pg::st execute failed: execute called with an unbound placeholder at ./calckvzpos line 247.</p>
Volltextsuche für Armehttps://f.zz.de/posts/201307161639.volltextsuche_fur_arme/2013-07-16T14:41:06Z2013-07-16T14:39:58Z
<p>'pg_dump -t kvzaddr | grep partial >partial'</p>