OpenStreetMap
Testupdate von osm2pgsql 1.8 zu osm2pgsql 2.0
Posted by aselnigu on 23 November 2024 in German (Deutsch).TL;DR: Testupdate von osm2pgsql 1.8 auf 2.0 gemacht, um zu prüfen, ob ein Neueinlesen der Daten nötig ist: Wir haben uns für ein Neueinlesen entschieden.
Testupdate von osm2pgsql 1.8 zu osm2pgsql 2.0
Ausgangspunkt
Ausgangspunkt ist eine Vagrant-Maschine, auf der mittels Ansible genau wie beim “echten” Tile Server die Installation durchgeführt wurde.
In unserem Fall via:
vagrant up bookworm
./init_vagrant_inventory.sh bookworm
ansible-playbook -v -i vagrant.ini site.yml -u vagrant > | tee tile.txt
(mit Memory 8196 ).
Daten
Anstelle der ganzen Welt importieren wir unter Vagrant für den Test lediglich die Daten für Monaco.
vagrant@bookworm:/var/log$ cat osm2pgsql-import.log
2024-10-20 09:17:19 osm2pgsql version 1.8.0
2024-10-20 09:17:19 WARNING: RAM cache is disabled. This will likely slow down processing a lot.
2024-10-20 09:17:19 Database version: 15.8 (Debian 15.8-0+deb12u1)
2024-10-20 09:17:19 PostGIS version: 3.3
2024-10-20 09:17:26 Reading input files done in 6s.
2024-10-20 09:17:26 Processed 30846 nodes in 1s - 31k/s
2024-10-20 09:17:26 Processed 4958 ways in 4s - 1k/s
2024-10-20 09:17:26 Processed 291 relations in 1s - 291/s
2024-10-20 09:17:26 No marked ways (Skipping stage 2).
2024-10-20 09:17:26 Clustering table 'planet_osm_hstore_point' by geometry...
2024-10-20 09:17:26 Creating index on table 'planet_osm_hstore_point' ("way")...
2024-10-20 09:17:26 Creating id index on table 'planet_osm_hstore_point'...
2024-10-20 09:17:26 Analyzing table 'planet_osm_hstore_point'...
2024-10-20 09:17:26 Clustering table 'planet_osm_hstore_line' by geometry...
2024-10-20 09:17:26 Creating index on table 'planet_osm_hstore_line' ("way")...
2024-10-20 09:17:26 Creating id index on table 'planet_osm_hstore_line'...
2024-10-20 09:17:26 Analyzing table 'planet_osm_hstore_line'...
2024-10-20 09:17:26 Clustering table 'planet_osm_hstore_roads' by geometry...
2024-10-20 09:17:26 Creating index on table 'planet_osm_hstore_roads' ("way")...
2024-10-20 09:17:26 Creating id index on table 'planet_osm_hstore_roads'...
2024-10-20 09:17:26 Analyzing table 'planet_osm_hstore_roads'...
2024-10-20 09:17:26 Clustering table 'planet_osm_hstore_polygon' by geometry...
2024-10-20 09:17:26 Creating index on table 'planet_osm_hstore_polygon' ("way")...
2024-10-20 09:17:26 Creating id index on table 'planet_osm_hstore_polygon'...
2024-10-20 09:17:26 Analyzing table 'planet_osm_hstore_polygon'...
2024-10-20 09:17:26 No indexes to create on table 'planet_osm_hstore_route'.
2024-10-20 09:17:26 Creating id index on table 'planet_osm_hstore_route'...
2024-10-20 09:17:26 Analyzing table 'planet_osm_hstore_route'...
2024-10-20 09:17:26 Done postprocessing on table 'planet_osm_nodes' in 0s
2024-10-20 09:17:26 Building index on table 'planet_osm_ways'
2024-10-20 09:17:27 Done postprocessing on table 'planet_osm_ways' in 0s
2024-10-20 09:17:27 Building index on table 'planet_osm_rels'
2024-10-20 09:17:27 Done postprocessing on table 'planet_osm_rels' in 0s
2024-10-20 09:17:27 All postprocessing on table 'planet_osm_hstore_point' done in 0s.
2024-10-20 09:17:27 All postprocessing on table 'planet_osm_hstore_line' done in 0s.
2024-10-20 09:17:27 All postprocessing on table 'planet_osm_hstore_roads' done in 0s.
2024-10-20 09:17:27 All postprocessing on table 'planet_osm_hstore_polygon' done in 0s.
2024-10-20 09:17:27 All postprocessing on table 'planet_osm_hstore_route' done in 0s.
2024-10-20 09:17:27 osm2pgsql took 7s overall.
Using replication service 'http://download.geofabrik.de/europe/monaco-updates', which is at sequence 4215 ( 2024-10-19T20:21:15Z )
Replication server's most recent data is 12 hour(s) 56 minute(s) old
Local database is up to date with server
Local database's most recent data is 12 hour(s) 56 minute(s) old
Updates
Wir stellen sicher, dass die Datenbank fehlerfrei aktualisiert wird.
vagrant@bookworm:~$ sudo journalctl -u updatedb -f
Oct 28 07:19:56 bookworm osm2pgsql-replication[6667]: 2024-10-28 07:19:56 [INFO]: Data imported until 2024-10-27 21:20:44+00:00. Backlog remaining: 9:59:12.804542
Oct 28 07:19:56 bookworm systemd[1]: updatedb.service: Deactivated successfully.
Oct 28 07:19:56 bookworm systemd[1]: updatedb.service: Consumed 1.187s CPU time.
Oct 28 07:32:05 bookworm systemd[1]: Stopped updatedb.service - render database update.
Oct 28 07:32:05 bookworm systemd[1]: updatedb.service: Consumed 1.187s CPU time.
Oct 28 07:32:05 bookworm systemd[1]: Starting updatedb.service - render database update...
Oct 28 07:32:05 bookworm systemd[1]: Started updatedb.service - render database update.
Oct 28 07:32:05 bookworm osm2pgsql-replication[8082]: 2024-10-28 07:32:05 [INFO]: Using replication service 'http://download.geofabrik.de/europe/monaco-updates'. Current sequence 4223 (2024-10-27 21:20:44+00:00).
Oct 28 07:32:05 bookworm osm2pgsql-replication[8082]: 2024-10-28 07:32:05 [INFO]: Database already up-to-date.
Oct 28 07:32:05 bookworm systemd[1]: updatedb.service: Deactivated successfully.
Konkret sieht der Dienst wie folgt aus:
vagrant@bookworm:/etc/systemd/system$ cat updatedb.service
[Unit]
Description=render database update
After=syslog.target network.target
[Service]
Type=simple
User=_tirex
Group=_tirex
ExecStartPre=truncate -s 0 /tmp/latest_changes.osc
ExecStart=osm2pgsql-replication update -d osm --max-diff-size 10 \
--diff-file /tmp/latest_changes.osc --post-processing /srv/tile/bin/expire-tiles.sh -- \
-G --slim -C 0 -O flex \
--number-processes 1 -S /srv/tile/sources/osml10n/openstreetmap-carto-hstore-only-l10n.lua \
Restart=always
RestartSec=50
StandardOutput=journal
[Install]
WantedBy=multi-user.target
Datenbank
Als nächstes halten wir den aktuellen Stand der Datenbank fest:
vagrant@bookworm:~$ sudo -u postgres psql
psql (15.8 (Debian 15.8-0+deb12u1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
osm | _tirex | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# \c osm
You are now connected to database "osm" as user "postgres".
osm=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------------------+-------+----------
public | external_data | table | _tirex
public | icesheet_outlines | table | _tirex
public | icesheet_polygons | table | _tirex
public | ne_110m_admin_0_boundary_lines_land | table | _tirex
public | planet_osm_hstore_line | table | _tirex
public | planet_osm_hstore_point | table | _tirex
public | planet_osm_hstore_polygon | table | _tirex
public | planet_osm_hstore_roads | table | _tirex
public | planet_osm_hstore_route | table | _tirex
public | planet_osm_nodes | table | _tirex
public | planet_osm_rels | table | _tirex
public | planet_osm_replication_status | table | _tirex
public | planet_osm_ways | table | _tirex
public | simplified_water_polygons | table | _tirex
public | spatial_ref_sys | table | postgres
public | water_polygons | table | _tirex
(16 rows)
osm=# copy (
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position) to '/tmp/osmdb.log';
Dieser Befehl listet alle Spalten aus allen Tabellen des public-Schemas auf und gibt für jede Spalte den Schema-Namen (table_schema), den Tabellennamen (table_name), den Spaltennamen (column_name) und den Datentyp der Spalte (data_type) aus. Die Ergebnisse sind nach Tabellennamen und der Position der Spalte in der Tabelle sortiert.
vagrant@bookworm:/tmp$ cat /tmp/osmdb.log
public external_data name text
public external_data last_modified text
public geography_columns f_table_catalog name
public geography_columns f_table_schema name
public geography_columns f_table_name name
public geography_columns f_geography_column name
public geography_columns coord_dimension integer
public geography_columns srid integer
public geography_columns type text
public geometry_columns f_table_catalog character varying
public geometry_columns f_table_schema name
public geometry_columns f_table_name name
public geometry_columns f_geometry_column name
public geometry_columns coord_dimension integer
public geometry_columns srid integer
public geometry_columns type character varying
public icesheet_outlines ice_edge character varying
public icesheet_outlines way USER-DEFINED
public icesheet_polygons fid numeric
public icesheet_polygons way USER-DEFINED
public ne_110m_admin_0_boundary_lines_land scalerank numeric
public ne_110m_admin_0_boundary_lines_land featurecla character varying
public ne_110m_admin_0_boundary_lines_land name character varying
public ne_110m_admin_0_boundary_lines_land name_alt character varying
public ne_110m_admin_0_boundary_lines_land min_zoom numeric
public ne_110m_admin_0_boundary_lines_land fclass_iso character varying
public ne_110m_admin_0_boundary_lines_land fclass_us character varying
public ne_110m_admin_0_boundary_lines_land fclass_fr character varying
public ne_110m_admin_0_boundary_lines_land fclass_ru character varying
public ne_110m_admin_0_boundary_lines_land fclass_es character varying
public ne_110m_admin_0_boundary_lines_land fclass_cn character varying
public ne_110m_admin_0_boundary_lines_land fclass_tw character varying
public ne_110m_admin_0_boundary_lines_land fclass_in character varying
public ne_110m_admin_0_boundary_lines_land fclass_np character varying
public ne_110m_admin_0_boundary_lines_land fclass_pk character varying
public ne_110m_admin_0_boundary_lines_land fclass_de character varying
public ne_110m_admin_0_boundary_lines_land fclass_gb character varying
public ne_110m_admin_0_boundary_lines_land fclass_br character varying
public ne_110m_admin_0_boundary_lines_land fclass_il character varying
public ne_110m_admin_0_boundary_lines_land fclass_ps character varying
public ne_110m_admin_0_boundary_lines_land fclass_sa character varying
public ne_110m_admin_0_boundary_lines_land fclass_eg character varying
public ne_110m_admin_0_boundary_lines_land fclass_ma character varying
public ne_110m_admin_0_boundary_lines_land fclass_pt character varying
public ne_110m_admin_0_boundary_lines_land fclass_ar character varying
public ne_110m_admin_0_boundary_lines_land fclass_jp character varying
public ne_110m_admin_0_boundary_lines_land fclass_ko character varying
public ne_110m_admin_0_boundary_lines_land fclass_vn character varying
public ne_110m_admin_0_boundary_lines_land fclass_tr character varying
public ne_110m_admin_0_boundary_lines_land fclass_id character varying
public ne_110m_admin_0_boundary_lines_land fclass_pl character varying
public ne_110m_admin_0_boundary_lines_land fclass_gr character varying
public ne_110m_admin_0_boundary_lines_land fclass_it character varying
public ne_110m_admin_0_boundary_lines_land fclass_nl character varying
public ne_110m_admin_0_boundary_lines_land fclass_se character varying
public ne_110m_admin_0_boundary_lines_land fclass_bd character varying
public ne_110m_admin_0_boundary_lines_land fclass_ua character varying
public ne_110m_admin_0_boundary_lines_land ne_id numeric
public ne_110m_admin_0_boundary_lines_land brk_a3 character varying
public ne_110m_admin_0_boundary_lines_land fclass_tlc character varying
public ne_110m_admin_0_boundary_lines_land way USER-DEFINED
public planet_osm_hstore_line osm_id bigint
public planet_osm_hstore_line way USER-DEFINED
public planet_osm_hstore_line tags USER-DEFINED
public planet_osm_hstore_line layer integer
public planet_osm_hstore_line z_order integer
public planet_osm_hstore_line name_l10n ARRAY
public planet_osm_hstore_point osm_id bigint
public planet_osm_hstore_point way USER-DEFINED
public planet_osm_hstore_point tags USER-DEFINED
public planet_osm_hstore_point layer integer
public planet_osm_hstore_point name_l10n ARRAY
public planet_osm_hstore_polygon osm_id bigint
public planet_osm_hstore_polygon way USER-DEFINED
public planet_osm_hstore_polygon tags USER-DEFINED
public planet_osm_hstore_polygon layer integer
public planet_osm_hstore_polygon z_order integer
public planet_osm_hstore_polygon way_area real
public planet_osm_hstore_polygon name_l10n ARRAY
public planet_osm_hstore_roads osm_id bigint
public planet_osm_hstore_roads way USER-DEFINED
public planet_osm_hstore_roads tags USER-DEFINED
public planet_osm_hstore_roads layer integer
public planet_osm_hstore_roads z_order integer
public planet_osm_hstore_roads name_l10n ARRAY
public planet_osm_hstore_route osm_id bigint
public planet_osm_hstore_route member_id bigint
public planet_osm_hstore_route member_position integer
public planet_osm_hstore_route tags USER-DEFINED
public planet_osm_line osm_id bigint
public planet_osm_line access text
public planet_osm_line addr:interpolation text
public planet_osm_line aerialway text
public planet_osm_line aeroway text
public planet_osm_line barrier text
public planet_osm_line building text
public planet_osm_line bicycle text
public planet_osm_line bridge text
public planet_osm_line construction text
public planet_osm_line covered text
public planet_osm_line culvert text
public planet_osm_line disused text
public planet_osm_line embankment text
public planet_osm_line foot text
public planet_osm_line highway text
public planet_osm_line historic text
public planet_osm_line horse text
public planet_osm_line intermittent text
public planet_osm_line junction text
public planet_osm_line leisure text
public planet_osm_line lock text
public planet_osm_line man_made text
public planet_osm_line name text
public planet_osm_line name:de text
public planet_osm_line int_name text
public planet_osm_line name:en text
public planet_osm_line natural text
public planet_osm_line oneway text
public planet_osm_line operator text
public planet_osm_line power text
public planet_osm_line proposed text
public planet_osm_line railway text
public planet_osm_line ref text
public planet_osm_line route text
public planet_osm_line service text
public planet_osm_line surface text
public planet_osm_line tracktype text
public planet_osm_line tunnel text
public planet_osm_line waterway text
public planet_osm_line width text
public planet_osm_line way USER-DEFINED
public planet_osm_line z_order integer
public planet_osm_line localized_name_second text
public planet_osm_line localized_name_first text
public planet_osm_line localized_name text
public planet_osm_line localized_streetname text
public planet_osm_line name_hrb text
public planet_osm_line layer integer
public planet_osm_line tags USER-DEFINED
public planet_osm_nodes id bigint
public planet_osm_nodes lat integer
public planet_osm_nodes lon integer
public planet_osm_point osm_id bigint
public planet_osm_point access text
public planet_osm_point addr:housename text
public planet_osm_point addr:housenumber text
public planet_osm_point admin_level text
public planet_osm_point aerialway text
public planet_osm_point aeroway text
public planet_osm_point amenity text
public planet_osm_point barrier text
public planet_osm_point boundary text
public planet_osm_point building text
public planet_osm_point capital text
public planet_osm_point denomination text
public planet_osm_point ele text
public planet_osm_point generator:source text
public planet_osm_point highway text
public planet_osm_point historic text
public planet_osm_point iata text
public planet_osm_point junction text
public planet_osm_point landuse text
public planet_osm_point leisure text
public planet_osm_point man_made text
public planet_osm_point military text
public planet_osm_point name text
public planet_osm_point name:de text
public planet_osm_point int_name text
public planet_osm_point name:en text
public planet_osm_point natural text
public planet_osm_point operator text
public planet_osm_point place text
public planet_osm_point population text
public planet_osm_point power text
public planet_osm_point power_source text
public planet_osm_point railway text
public planet_osm_point ref text
public planet_osm_point religion text
public planet_osm_point ruins text
public planet_osm_point service text
public planet_osm_point shop text
public planet_osm_point sport text
public planet_osm_point tourism text
public planet_osm_point waterway text
public planet_osm_point wetland text
public planet_osm_point way USER-DEFINED
public planet_osm_point localized_name_second text
public planet_osm_point localized_name_first text
public planet_osm_point localized_name text
public planet_osm_point localized_streetname text
public planet_osm_point name_hrb text
public planet_osm_point layer integer
public planet_osm_point tags USER-DEFINED
public planet_osm_polygon osm_id bigint
public planet_osm_polygon access text
public planet_osm_polygon addr:housename text
public planet_osm_polygon addr:housenumber text
public planet_osm_polygon admin_level text
public planet_osm_polygon aerialway text
public planet_osm_polygon aeroway text
public planet_osm_polygon amenity text
public planet_osm_polygon barrier text
public planet_osm_polygon bicycle text
public planet_osm_polygon boundary text
public planet_osm_polygon bridge text
public planet_osm_polygon building text
public planet_osm_polygon covered text
public planet_osm_polygon denomination text
public planet_osm_polygon generator:source text
public planet_osm_polygon highway text
public planet_osm_polygon historic text
public planet_osm_polygon iata text
public planet_osm_polygon junction text
public planet_osm_polygon landuse text
public planet_osm_polygon leaf_type text
public planet_osm_polygon leisure text
public planet_osm_polygon man_made text
public planet_osm_polygon military text
public planet_osm_polygon name text
public planet_osm_polygon name:de text
public planet_osm_polygon int_name text
public planet_osm_polygon name:en text
public planet_osm_polygon natural text
public planet_osm_polygon operator text
public planet_osm_polygon place text
public planet_osm_polygon power text
public planet_osm_polygon power_source text
public planet_osm_polygon railway text
public planet_osm_polygon ref text
public planet_osm_polygon religion text
public planet_osm_polygon ruins text
public planet_osm_polygon shop text
public planet_osm_polygon sport text
public planet_osm_polygon surface text
public planet_osm_polygon tourism text
public planet_osm_polygon tunnel text
public planet_osm_polygon water text
public planet_osm_polygon waterway text
public planet_osm_polygon wetland text
public planet_osm_polygon way USER-DEFINED
public planet_osm_polygon way_area real
public planet_osm_polygon z_order integer
public planet_osm_polygon localized_name_second text
public planet_osm_polygon localized_name_first text
public planet_osm_polygon localized_name text
public planet_osm_polygon localized_streetname text
public planet_osm_polygon country_name text
public planet_osm_polygon name_hrb text
public planet_osm_polygon layer integer
public planet_osm_polygon tags USER-DEFINED
public planet_osm_rels id bigint
public planet_osm_rels way_off smallint
public planet_osm_rels rel_off smallint
public planet_osm_rels parts ARRAY
public planet_osm_rels members ARRAY
public planet_osm_rels tags ARRAY
public planet_osm_replication_status url text
public planet_osm_replication_status sequence integer
public planet_osm_replication_status importdate timestamp with time zone
public planet_osm_roads osm_id bigint
public planet_osm_roads admin_level text
public planet_osm_roads covered text
public planet_osm_roads highway text
public planet_osm_roads name text
public planet_osm_roads name:de text
public planet_osm_roads int_name text
public planet_osm_roads name:en text
public planet_osm_roads railway text
public planet_osm_roads ref text
public planet_osm_roads service text
public planet_osm_roads surface text
public planet_osm_roads tunnel text
public planet_osm_roads aerialway text
public planet_osm_roads addr:housenumber text
public planet_osm_roads aeroway text
public planet_osm_roads amenity text
public planet_osm_roads barrier text
public planet_osm_roads boundary text
public planet_osm_roads building text
public planet_osm_roads historic text
public planet_osm_roads lock text
public planet_osm_roads man_made text
public planet_osm_roads power text
public planet_osm_roads route text
public planet_osm_roads shop text
public planet_osm_roads waterway text
public planet_osm_roads width text
public planet_osm_roads way USER-DEFINED
public planet_osm_roads z_order integer
public planet_osm_roads localized_name_second text
public planet_osm_roads localized_name_first text
public planet_osm_roads localized_name text
public planet_osm_roads localized_streetname text
public planet_osm_roads name_hrb text
public planet_osm_roads layer integer
public planet_osm_roads tags USER-DEFINED
public planet_osm_ways id bigint
public planet_osm_ways nodes ARRAY
public planet_osm_ways tags ARRAY
public simplified_water_polygons x numeric
public simplified_water_polygons y numeric
public simplified_water_polygons way USER-DEFINED
public spatial_ref_sys srid integer
public spatial_ref_sys auth_name character varying
public spatial_ref_sys auth_srid integer
public spatial_ref_sys srtext character varying
public spatial_ref_sys proj4text character varying
public water_polygons x numeric
public water_polygons y numeric
public water_polygons way USER-DEFINED
Wir erstellen eine neue Datenbank als exakte Kopie der bestehenden Datenbank osm, um später den Inhalt vergleichen zu können.
vagrant@bookworm:~$ sudo -u postgres psql
psql (15.8 (Debian 15.8-0+deb12u1))
Type "help" for help.
postgres=# CREATE DATABASE osm_copy WITH TEMPLATE osm OWNER _tirex;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
osm | _tirex | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
osm_copy | _tirex | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(5 rows)
postgres=#
Backup
Wir erstellen eine Momentaufnahme der virtuellen Maschine und speichert sie unter dem Namen afterimportanddoc. Diese Momentaufnahme speichert den aktuellen Zustand der VM zu diesem Zeitpunkt, sodass wir später zu diesem Zustand zurückkehren können.
amaschine@amaschine-hp-laptop:~/openstreetmap/osm-server/ansible_openstreetmap.de$ vagrant snapshot save bookworm afterimportanddoc
==> bookworm: Snapshotting the machine as 'afterimportanddoc'...
==> bookworm: Snapshot saved! You can restore the snapshot at any time by
==> bookworm: using `vagrant snapshot restore`. You can delete it using
==> bookworm: `vagrant snapshot delete`.
amaschine@amaschine-hp-laptop:~/openstreetmap/osm-server/ansible_openstreetmap.de$ vagrant status
Current machine states:
bullseye not created (libvirt)
bookworm running (libvirt)
This environment represents multiple VMs. The VMs are all listed
above with their current state. For more information about a specific
VM, run `vagrant status NAME`.
amaschine@amaschine-hp-laptop:~/openstreetmap/osm-server/ansible_openstreetmap.de$ vagrant snapshot list
==> bullseye: VM not created. Moving on...
==> bookworm:
afterimportanddoc
amaschine@amaschine-hp-laptop:~/openstreetmap/osm-server/ansible_openstreetmap.de$
Backports
Debian Backports stellt neue Pakete für Debian-Stable-Versionen bereit. Backports werden nicht so umfassend getestet wie Stable-Pakete und können zu Inkompatibilitäten führen, weshalb sie mit Vorsicht verwendet werden sollten. Die Verwendung einzelner Pakete aus den Backports gilt jedoch als sicher, und es wird empfohlen, gezielt nur benötigte Pakete zu aktivieren.
Links:
https://manpages.debian.org/bookworm-backports/osm2pgsql/osm2pgsql.1.en.html
https://osm2pgsql.org/doc/install/linux.html
https://backports.debian.org/Instructions/
Ausgang
Zu Beginn überprüfen wir die Versionen und stoppe die Aktualisierung der Datenbank, um Probleme aufgrund von Inkonsistenzen zu vermeiden.
vagrant@bookworm:~$ osm2pgsql --version
2024-10-29 07:34:04 osm2pgsql version 1.8.0
Build: None
Compiled using the following library versions:
Libosmium 2.19.0
Proj [API 6] 9.1.1
Lua 5.3.6
vagrant@bookworm:/etc/apt$ sudo systemctl stop updatedb
vagrant@bookworm:/etc/apt$ sudo systemctl disable updatedb
Installation der Backports vorbereiten
Die Zeilte deb https://deb.debian.org/debian bookworm-backports main ist bereits in /etc/apt/sources.list vorhanden. sudo apt update aktualisiert die Liste der verfügbaren Pakete und ihrer Versionen auf dem System, basierend auf den aktuellen Repository-Quellen. Es ist ein notwendiger Schritt, bevor man neue Pakete installiert oder bestehende Pakete aktualisiert.
vagrant@bookworm:/etc/apt$ cat /etc/apt/sources.list
deb https://deb.debian.org/debian bookworm main
deb-src https://deb.debian.org/debian bookworm main
deb https://security.debian.org/debian-security bookworm-security main
deb-src https://security.debian.org/debian-security bookworm-security main
deb https://deb.debian.org/debian bookworm-updates main
deb-src https://deb.debian.org/debian bookworm-updates main
deb https://deb.debian.org/debian bookworm-backports main
deb-src https://deb.debian.org/debian bookworm-backports main
vagrant@bookworm:/etc/apt$ sudo apt update
Hit:1 https://deb.debian.org/debian bookworm InRelease
Get:2 https://deb.debian.org/debian bookworm-updates InRelease [55.4 kB]
Get:3 https://deb.debian.org/debian bookworm-backports InRelease [59.0 kB]
Get:4 https://security.debian.org/debian-security bookworm-security InRelease [48.0 kB]
Get:5 https://deb.debian.org/debian bookworm-backports/main Sources.diff/Index [63.3 kB]
Get:6 https://deb.debian.org/debian bookworm-backports/main Sources T-2024-10-28-2123.03-F-2024-10-28-2123.03.pdiff [2519 B]
Get:6 https://deb.debian.org/debian bookworm-backports/main Sources T-2024-10-28-2123.03-F-2024-10-28-2123.03.pdiff [2519 B]
Fetched 228 kB in 2s (143 kB/s)
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
All packages are up to date.
Installation
osm2pgsql
sudo apt install osm2pgsql/bookworm-backports installiert das Paket osm2pgsql aus den Backports für die Debian-Version „Bookworm“. Dadurch wird eine neuere Version von osm2pgsql als die in den Standard-Repositories verfügbare Version installiert.
vagrant@bookworm:/etc/apt$ sudo apt install osm2pgsql/bookworm-backports
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Selected version '2.0.0+ds-1~bpo12+1' (Debian Backports:stable-backports [amd64]) for 'osm2pgsql'
The following packages will be upgraded:
osm2pgsql
1 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
Need to get 655 kB of archives.
After this operation, 376 kB of additional disk space will be used.
Get:1 https://deb.debian.org/debian bookworm-backports/main amd64 osm2pgsql amd64 2.0.0+ds-1~bpo12+1 [655 kB]
Fetched 655 kB in 0s (1472 kB/s)
Reading changelogs... Done
(Reading database ... 118746 files and directories currently installed.)
Preparing to unpack .../osm2pgsql_2.0.0+ds-1~bpo12+1_amd64.deb ...
Unpacking osm2pgsql (2.0.0+ds-1~bpo12+1) over (1.8.0+ds-1) ...
Setting up osm2pgsql (2.0.0+ds-1~bpo12+1) ...
Processing triggers for man-db (2.11.2-2) ...
Voila!
vagrant@bookworm:/etc/apt$ osm2pgsql --version
osm2pgsql version 2.0.0
Build: None
Compiled using the following library versions:
Libosmium 2.19.0
Proj 9.1.1
Lua 5.3.6
osml10n
Die Version 2.0.0 von osm2pgsql erfordert Anpassungen am LUA-Skript.
vagrant@bookworm:/srv/tile/sources/osml10n$ sudo git config --global --add safe.directory /srv/tile/sources/osml10n
vagrant@bookworm:/srv/tile/sources/osml10n$ sudo git checkout master
Previous HEAD position was 8e3004b * get rid of deprecated sklearn package * Update to tltk 1.8 * therefore call this version 1.2.0
Switched to branch 'master'
Your branch is up to date with 'origin/master'.
vagrant@bookworm:/srv/tile/sources/osml10n$ sudo git pull origin master
remote: Enumerating objects: 14, done.
remote: Counting objects: 100% (14/14), done.
remote: Compressing objects: 100% (10/10), done.
remote: Total 14 (delta 7), reused 8 (delta 4), pack-reused 0 (from 0)
Unpacking objects: 100% (14/14), 8.72 KiB | 234.00 KiB/s, done.
From https://github.com/giggls/osml10n
* branch master -> FETCH_HEAD
f84119f..d06b251 master -> origin/master
Updating f84119f..d06b251
Fast-forward
openstreetmap-carto-hstore-only-l10n.lua | 86 ++++++++++++++++++++++++++++++++++++++++++++++----------------------------------------
1 file changed, 46 insertions(+), 40 deletions(-)
mode change 100644 => 100755 openstreetmap-carto-hstore-only-l10n.lua
vagrant@bookworm:/srv/tile/sources/osml10n$ sudo git fetch origin
remote: Enumerating objects: 14, done.
remote: Counting objects: 100% (14/14), done.
remote: Compressing objects: 100% (10/10), done.
remote: Total 14 (delta 7), reused 8 (delta 4), pack-reused 0 (from 0)
Unpacking objects: 100% (14/14), 8.72 KiB | 446.00 KiB/s, done.
From https://github.com/giggls/osml10n
f84119f..d06b251 master -> origin/master
vagrant@bookworm:/srv/tile/sources/osml10n$ sudo git reset origin/master --hard
HEAD is now at d06b251 Merge pull request #40 from astridx/master
Daten neu einlesen
Wir lese die Daten mit den neuen Versionen ein.
vagrant@bookworm:/usr/local/sbin$ sudo import-osm2pgsql
Removed "/etc/systemd/system/multi-user.target.wants/updatedb.service".
--2024-10-29 09:37:31-- http://download.geofabrik.de/europe/monaco-latest.osm.pbf
Resolving download.geofabrik.de (download.geofabrik.de)... 65.109.48.72, 65.109.50.43, 2a01:4f9:5a:2797::2, ...
Connecting to download.geofabrik.de (download.geofabrik.de)|65.109.48.72|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 549017 (536K) [application/octet-stream]
Saving to: ‘monaco-latest.osm.pbf’
monaco-latest.osm.pbf 100%[=============================================================>] 536.15K 582KB/s in 0.9s
2024-10-29 09:37:32 (582 KB/s) - ‘monaco-latest.osm.pbf’ saved [549017/549017]
2024-10-29 09:37:33 osm2pgsql version 2.0.0
2024-10-29 09:37:33 WARNING: RAM cache is disabled. This will likely slow down processing a lot.
2024-10-29 09:37:33 Database version: 15.8 (Debian 15.8-0+deb12u1)
2024-10-29 09:37:33 PostGIS version: 3.3
2024-10-29 09:37:33 Initializing properties table '"public"."osm2pgsql_properties"'.
2024-10-29 09:37:33 Storing properties to table '"public"."osm2pgsql_properties"'.
2024-10-29 09:37:33 WARNING: You should use the syntax 'object:get_bbox()' (with the colon, not a point) to call functions on the OSM object.
2024-10-29 09:37:35 Reading input files done in 2s.
2024-10-29 09:37:35 Processed 30859 nodes in 0s - 31k/s
2024-10-29 09:37:35 Processed 4973 ways in 1s - 5k/s
2024-10-29 09:37:35 Processed 291 relations in 1s - 291/s
2024-10-29 09:37:35 No marked nodes or ways (Skipping stage 2).
2024-10-29 09:37:35 Clustering table 'planet_osm_hstore_point' by geometry...
2024-10-29 09:37:35 Creating index on table 'planet_osm_hstore_point' ("way")...
2024-10-29 09:37:35 Creating id index on table 'planet_osm_hstore_point'...
2024-10-29 09:37:35 Analyzing table 'planet_osm_hstore_point'...
2024-10-29 09:37:36 Clustering table 'planet_osm_hstore_line' by geometry...
2024-10-29 09:37:36 Creating index on table 'planet_osm_hstore_line' ("way")...
2024-10-29 09:37:36 Creating id index on table 'planet_osm_hstore_line'...
2024-10-29 09:37:36 Analyzing table 'planet_osm_hstore_line'...
2024-10-29 09:37:36 Clustering table 'planet_osm_hstore_roads' by geometry...
2024-10-29 09:37:36 Creating index on table 'planet_osm_hstore_roads' ("way")...
2024-10-29 09:37:36 Creating id index on table 'planet_osm_hstore_roads'...
2024-10-29 09:37:36 Analyzing table 'planet_osm_hstore_roads'...
2024-10-29 09:37:36 Clustering table 'planet_osm_hstore_polygon' by geometry...
2024-10-29 09:37:36 Creating index on table 'planet_osm_hstore_polygon' ("way")...
2024-10-29 09:37:36 Creating id index on table 'planet_osm_hstore_polygon'...
2024-10-29 09:37:36 Analyzing table 'planet_osm_hstore_polygon'...
2024-10-29 09:37:36 No indexes to create on table 'planet_osm_hstore_route'.
2024-10-29 09:37:36 Creating id index on table 'planet_osm_hstore_route'...
2024-10-29 09:37:36 Analyzing table 'planet_osm_hstore_route'...
2024-10-29 09:37:36 Done postprocessing on table 'planet_osm_nodes' in 0s
2024-10-29 09:37:36 Building index on table 'planet_osm_ways'
2024-10-29 09:37:36 Done postprocessing on table 'planet_osm_ways' in 0s
2024-10-29 09:37:36 Building index on table 'planet_osm_rels'
2024-10-29 09:37:36 Done postprocessing on table 'planet_osm_rels' in 0s
2024-10-29 09:37:36 All postprocessing on table 'planet_osm_hstore_point' done in 0s.
2024-10-29 09:37:36 All postprocessing on table 'planet_osm_hstore_line' done in 0s.
2024-10-29 09:37:36 All postprocessing on table 'planet_osm_hstore_roads' done in 0s.
2024-10-29 09:37:36 All postprocessing on table 'planet_osm_hstore_polygon' done in 0s.
2024-10-29 09:37:36 All postprocessing on table 'planet_osm_hstore_route' done in 0s.
2024-10-29 09:37:36 Storing properties to table '"public"."osm2pgsql_properties"'.
2024-10-29 09:37:36 osm2pgsql took 3s overall.
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
using database: osm
CREATE VIEW
GRANT
CREATE VIEW
GRANT
CREATE VIEW
GRANT
CREATE VIEW
GRANT
2024-10-29 09:37:37 [INFO]: Initialised updates for service 'http://download.geofabrik.de/europe/monaco-updates'.
2024-10-29 09:37:37 [INFO]: Starting at sequence 4224 (2024-10-28T21:21:30Z).
Using replication service 'http://download.geofabrik.de/europe/monaco-updates', which is at sequence 4224 ( 2024-10-28T21:21:30Z )
Replication server's most recent data is 12 hour(s) 16 minute(s) 8 second(s) old
Local database is up to date with server
Local database's most recent data is 12 hour(s) 16 minute(s) 8 second(s) old
INFO:root:Starting load of external data into database
INFO:root:Checking table simplified_water_polygons
INFO:root: Table simplified_water_polygons did not require updating
INFO:root:Checking table water_polygons
INFO:root: Table water_polygons did not require updating
INFO:root:Checking table icesheet_polygons
INFO:root: Table icesheet_polygons did not require updating
INFO:root:Checking table icesheet_outlines
INFO:root: Table icesheet_outlines did not require updating
INFO:root:Checking table ne_110m_admin_0_boundary_lines_land
INFO:root: Table ne_110m_admin_0_boundary_lines_land did not require updating
--2024-10-29 09:37:40-- https://robinson.openstreetmap.de/carto-fonts/carto-fonts.zip
Resolving robinson.openstreetmap.de (robinson.openstreetmap.de)... 23.88.123.196, 2a01:4f8:1c17:c4e7::1
Connecting to robinson.openstreetmap.de (robinson.openstreetmap.de)|23.88.123.196|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 65323245 (62M) [application/zip]
Saving to: ‘carto-fonts.zip.1’
carto-fonts.zip.1 100%[=============================================================>] 62.30M 5.51MB/s in 15s
2024-10-29 09:37:55 (4.29 MB/s) - ‘carto-fonts.zip.1’ saved [65323245/65323245]
Archive: carto-fonts.zip
inflating: HanaMinA.ttf
inflating: HanaMinB.ttf
inflating: NotoEmoji-Bold.ttf
inflating: NotoEmoji-Regular.ttf
inflating: NotoSansAdlamUnjoined-Bold.ttf
inflating: NotoSansAdlamUnjoined-Regular.ttf
inflating: NotoSansArabicUI-Bold.ttf
inflating: NotoSansArabicUI-Regular.ttf
inflating: NotoSansArmenian-Bold.ttf
inflating: NotoSansArmenian-Regular.ttf
inflating: NotoSansBalinese-Bold.ttf
inflating: NotoSansBalinese-Regular.ttf
inflating: NotoSansBamum-Bold.ttf
inflating: NotoSansBamum-Regular.ttf
inflating: NotoSansBatak-Regular.ttf
inflating: NotoSansBengaliUI-Bold.ttf
inflating: NotoSansBengaliUI-Regular.ttf
inflating: NotoSans-Bold.ttf
inflating: NotoSansBuginese-Regular.ttf
inflating: NotoSansBuhid-Regular.ttf
inflating: NotoSansCanadianAboriginal-Bold.ttf
inflating: NotoSansCanadianAboriginal-Regular.ttf
inflating: NotoSansChakma-Regular.ttf
inflating: NotoSansCham-Bold.ttf
inflating: NotoSansCham-Regular.ttf
inflating: NotoSansCherokee-Bold.ttf
inflating: NotoSansCherokee-Regular.ttf
inflating: NotoSansCJKjp-Bold.otf
inflating: NotoSansCJKjp-Regular.otf
inflating: NotoSansCoptic-Regular.ttf
inflating: NotoSansDevanagariUI-Bold.ttf
inflating: NotoSansDevanagariUI-Regular.ttf
inflating: NotoSansEthiopic-Bold.ttf
inflating: NotoSansEthiopic-Regular.ttf
inflating: NotoSansGeorgian-Bold.ttf
inflating: NotoSansGeorgian-Regular.ttf
inflating: NotoSansGujaratiUI-Bold.ttf
inflating: NotoSansGujaratiUI-Regular.ttf
inflating: NotoSansGurmukhiUI-Bold.ttf
inflating: NotoSansGurmukhiUI-Regular.ttf
inflating: NotoSansHanunoo-Regular.ttf
inflating: NotoSansHebrew-Bold.ttf
inflating: NotoSansHebrew-Regular.ttf
inflating: NotoSans-Italic.ttf
inflating: NotoSansJavanese-Bold.ttf
inflating: NotoSansJavanese-Regular.ttf
inflating: NotoSansKannadaUI-Bold.ttf
inflating: NotoSansKannadaUI-Regular.ttf
inflating: NotoSansKayahLi-Bold.ttf
inflating: NotoSansKayahLi-Regular.ttf
inflating: NotoSansKhmerUI-Bold.ttf
inflating: NotoSansKhmerUI-Regular.ttf
inflating: NotoSansLaoUI-Bold.ttf
inflating: NotoSansLaoUI-Regular.ttf
inflating: NotoSansLepcha-Regular.ttf
inflating: NotoSansLimbu-Regular.ttf
inflating: NotoSansLisu-Bold.ttf
inflating: NotoSansLisu-Regular.ttf
inflating: NotoSansMalayalamUI-Bold.ttf
inflating: NotoSansMalayalamUI-Regular.ttf
inflating: NotoSansMandaic-Regular.ttf
inflating: NotoSansMongolian-Regular.ttf
inflating: NotoSansMyanmarUI-Bold.ttf
inflating: NotoSansMyanmarUI-Regular.ttf
inflating: NotoSansNewTaiLue-Regular.ttf
inflating: NotoSansNKo-Regular.ttf
inflating: NotoSansOlChiki-Bold.ttf
inflating: NotoSansOlChiki-Regular.ttf
inflating: NotoSansOriyaUI-Bold.ttf
inflating: NotoSansOriyaUI-Regular.ttf
inflating: NotoSansOsage-Regular.ttf
inflating: NotoSansOsmanya-Regular.ttf
inflating: NotoSans-Regular.ttf
inflating: NotoSansSamaritan-Regular.ttf
inflating: NotoSansSaurashtra-Regular.ttf
inflating: NotoSansShavian-Regular.ttf
inflating: NotoSansSinhalaUI-Bold.ttf
inflating: NotoSansSinhalaUI-Regular.ttf
inflating: NotoSansSundanese-Bold.ttf
inflating: NotoSansSundanese-Regular.ttf
inflating: NotoSansSymbols2-Regular.ttf
inflating: NotoSansSymbols-Bold.ttf
inflating: NotoSansSymbols-Regular.ttf
inflating: NotoSansSyriac-Black.ttf
inflating: NotoSansSyriac-Regular.ttf
inflating: NotoSansTagalog-Regular.ttf
inflating: NotoSansTagbanwa-Regular.ttf
inflating: NotoSansTaiLe-Regular.ttf
inflating: NotoSansTaiTham-Bold.ttf
inflating: NotoSansTaiTham-Regular.ttf
inflating: NotoSansTaiViet-Regular.ttf
inflating: NotoSansTamilUI-Bold.ttf
inflating: NotoSansTamilUI-Regular.ttf
inflating: NotoSansTeluguUI-Bold.ttf
inflating: NotoSansTeluguUI-Regular.ttf
inflating: NotoSansThaana-Bold.ttf
inflating: NotoSansThaana-Regular.ttf
inflating: NotoSansThaiUI-Bold.ttf
inflating: NotoSansThaiUI-Regular.ttf
inflating: NotoSansTifinagh-Regular.ttf
inflating: NotoSansVai-Regular.ttf
inflating: NotoSansYi-Regular.ttf
inflating: NotoSerifTibetan-Bold.ttf
inflating: NotoSerifTibetan-Regular.ttf
Created symlink /etc/systemd/system/multi-user.target.wants/updatedb.service → /etc/systemd/system/updatedb.service.
vagrant@bookworm:/usr/local/sbin$
##### Daten vergleichen
Wir exportieren erneut die Struktur.
vagrant@bookworm:/usr/local/sbin$ sudo -u postgres psql
psql (15.8 (Debian 15.8-0+deb12u1))
Type "help" for help.
postgres=# \c osm
You are now connected to database "osm" as user "postgres".
osm=# copy (
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position) to '/tmp/osmdb2.log';
COPY 310
osm=# exit
vagrant@bookworm:/usr/local/sbin$ cat /tmp/osmdb2.log
public external_data name text
public external_data last_modified text
public geography_columns f_table_catalog name
public geography_columns f_table_schema name
public geography_columns f_table_name name
public geography_columns f_geography_column name
public geography_columns coord_dimension integer
public geography_columns srid integer
public geography_columns type text
public geometry_columns f_table_catalog character varying
public geometry_columns f_table_schema name
public geometry_columns f_table_name name
public geometry_columns f_geometry_column name
public geometry_columns coord_dimension integer
public geometry_columns srid integer
public geometry_columns type character varying
public icesheet_outlines ice_edge character varying
public icesheet_outlines way USER-DEFINED
public icesheet_polygons fid numeric
public icesheet_polygons way USER-DEFINED
public ne_110m_admin_0_boundary_lines_land scalerank numeric
public ne_110m_admin_0_boundary_lines_land featurecla character varying
public ne_110m_admin_0_boundary_lines_land name character varying
public ne_110m_admin_0_boundary_lines_land name_alt character varying
public ne_110m_admin_0_boundary_lines_land min_zoom numeric
public ne_110m_admin_0_boundary_lines_land fclass_iso character varying
public ne_110m_admin_0_boundary_lines_land fclass_us character varying
public ne_110m_admin_0_boundary_lines_land fclass_fr character varying
public ne_110m_admin_0_boundary_lines_land fclass_ru character varying
public ne_110m_admin_0_boundary_lines_land fclass_es character varying
public ne_110m_admin_0_boundary_lines_land fclass_cn character varying
public ne_110m_admin_0_boundary_lines_land fclass_tw character varying
public ne_110m_admin_0_boundary_lines_land fclass_in character varying
public ne_110m_admin_0_boundary_lines_land fclass_np character varying
public ne_110m_admin_0_boundary_lines_land fclass_pk character varying
public ne_110m_admin_0_boundary_lines_land fclass_de character varying
public ne_110m_admin_0_boundary_lines_land fclass_gb character varying
public ne_110m_admin_0_boundary_lines_land fclass_br character varying
public ne_110m_admin_0_boundary_lines_land fclass_il character varying
public ne_110m_admin_0_boundary_lines_land fclass_ps character varying
public ne_110m_admin_0_boundary_lines_land fclass_sa character varying
public ne_110m_admin_0_boundary_lines_land fclass_eg character varying
public ne_110m_admin_0_boundary_lines_land fclass_ma character varying
public ne_110m_admin_0_boundary_lines_land fclass_pt character varying
public ne_110m_admin_0_boundary_lines_land fclass_ar character varying
public ne_110m_admin_0_boundary_lines_land fclass_jp character varying
public ne_110m_admin_0_boundary_lines_land fclass_ko character varying
public ne_110m_admin_0_boundary_lines_land fclass_vn character varying
public ne_110m_admin_0_boundary_lines_land fclass_tr character varying
public ne_110m_admin_0_boundary_lines_land fclass_id character varying
public ne_110m_admin_0_boundary_lines_land fclass_pl character varying
public ne_110m_admin_0_boundary_lines_land fclass_gr character varying
public ne_110m_admin_0_boundary_lines_land fclass_it character varying
public ne_110m_admin_0_boundary_lines_land fclass_nl character varying
public ne_110m_admin_0_boundary_lines_land fclass_se character varying
public ne_110m_admin_0_boundary_lines_land fclass_bd character varying
public ne_110m_admin_0_boundary_lines_land fclass_ua character varying
public ne_110m_admin_0_boundary_lines_land ne_id numeric
public ne_110m_admin_0_boundary_lines_land brk_a3 character varying
public ne_110m_admin_0_boundary_lines_land fclass_tlc character varying
public ne_110m_admin_0_boundary_lines_land way USER-DEFINED
public osm2pgsql_properties property text
public osm2pgsql_properties value text
public planet_osm_hstore_line osm_id bigint
public planet_osm_hstore_line way USER-DEFINED
public planet_osm_hstore_line tags USER-DEFINED
public planet_osm_hstore_line layer integer
public planet_osm_hstore_line z_order integer
public planet_osm_hstore_line name_l10n ARRAY
public planet_osm_hstore_point osm_id bigint
public planet_osm_hstore_point way USER-DEFINED
public planet_osm_hstore_point tags USER-DEFINED
public planet_osm_hstore_point layer integer
public planet_osm_hstore_point name_l10n ARRAY
public planet_osm_hstore_polygon osm_id bigint
public planet_osm_hstore_polygon way USER-DEFINED
public planet_osm_hstore_polygon tags USER-DEFINED
public planet_osm_hstore_polygon layer integer
public planet_osm_hstore_polygon z_order integer
public planet_osm_hstore_polygon way_area real
public planet_osm_hstore_polygon name_l10n ARRAY
public planet_osm_hstore_roads osm_id bigint
public planet_osm_hstore_roads way USER-DEFINED
public planet_osm_hstore_roads tags USER-DEFINED
public planet_osm_hstore_roads layer integer
public planet_osm_hstore_roads z_order integer
public planet_osm_hstore_roads name_l10n ARRAY
public planet_osm_hstore_route osm_id bigint
public planet_osm_hstore_route member_id bigint
public planet_osm_hstore_route member_position integer
public planet_osm_hstore_route tags USER-DEFINED
public planet_osm_line osm_id bigint
public planet_osm_line access text
public planet_osm_line addr:interpolation text
public planet_osm_line aerialway text
public planet_osm_line aeroway text
public planet_osm_line barrier text
public planet_osm_line building text
public planet_osm_line bicycle text
public planet_osm_line bridge text
public planet_osm_line construction text
public planet_osm_line covered text
public planet_osm_line culvert text
public planet_osm_line disused text
public planet_osm_line embankment text
public planet_osm_line foot text
public planet_osm_line highway text
public planet_osm_line historic text
public planet_osm_line horse text
public planet_osm_line intermittent text
public planet_osm_line junction text
public planet_osm_line leisure text
public planet_osm_line lock text
public planet_osm_line man_made text
public planet_osm_line name text
public planet_osm_line name:de text
public planet_osm_line int_name text
public planet_osm_line name:en text
public planet_osm_line natural text
public planet_osm_line oneway text
public planet_osm_line operator text
public planet_osm_line power text
public planet_osm_line proposed text
public planet_osm_line railway text
public planet_osm_line ref text
public planet_osm_line route text
public planet_osm_line service text
public planet_osm_line surface text
public planet_osm_line tracktype text
public planet_osm_line tunnel text
public planet_osm_line waterway text
public planet_osm_line width text
public planet_osm_line way USER-DEFINED
public planet_osm_line z_order integer
public planet_osm_line localized_name_second text
public planet_osm_line localized_name_first text
public planet_osm_line localized_name text
public planet_osm_line localized_streetname text
public planet_osm_line name_hrb text
public planet_osm_line layer integer
public planet_osm_line tags USER-DEFINED
public planet_osm_nodes id bigint
public planet_osm_nodes lat integer
public planet_osm_nodes lon integer
public planet_osm_nodes tags jsonb
public planet_osm_point osm_id bigint
public planet_osm_point access text
public planet_osm_point addr:housename text
public planet_osm_point addr:housenumber text
public planet_osm_point admin_level text
public planet_osm_point aerialway text
public planet_osm_point aeroway text
public planet_osm_point amenity text
public planet_osm_point barrier text
public planet_osm_point boundary text
public planet_osm_point building text
public planet_osm_point capital text
public planet_osm_point denomination text
public planet_osm_point ele text
public planet_osm_point generator:source text
public planet_osm_point highway text
public planet_osm_point historic text
public planet_osm_point iata text
public planet_osm_point junction text
public planet_osm_point landuse text
public planet_osm_point leisure text
public planet_osm_point man_made text
public planet_osm_point military text
public planet_osm_point name text
public planet_osm_point name:de text
public planet_osm_point int_name text
public planet_osm_point name:en text
public planet_osm_point natural text
public planet_osm_point operator text
public planet_osm_point place text
public planet_osm_point population text
public planet_osm_point power text
public planet_osm_point power_source text
public planet_osm_point railway text
public planet_osm_point ref text
public planet_osm_point religion text
public planet_osm_point ruins text
public planet_osm_point service text
public planet_osm_point shop text
public planet_osm_point sport text
public planet_osm_point tourism text
public planet_osm_point waterway text
public planet_osm_point wetland text
public planet_osm_point way USER-DEFINED
public planet_osm_point localized_name_second text
public planet_osm_point localized_name_first text
public planet_osm_point localized_name text
public planet_osm_point localized_streetname text
public planet_osm_point name_hrb text
public planet_osm_point layer integer
public planet_osm_point tags USER-DEFINED
public planet_osm_polygon osm_id bigint
public planet_osm_polygon access text
public planet_osm_polygon addr:housename text
public planet_osm_polygon addr:housenumber text
public planet_osm_polygon admin_level text
public planet_osm_polygon aerialway text
public planet_osm_polygon aeroway text
public planet_osm_polygon amenity text
public planet_osm_polygon barrier text
public planet_osm_polygon bicycle text
public planet_osm_polygon boundary text
public planet_osm_polygon bridge text
public planet_osm_polygon building text
public planet_osm_polygon covered text
public planet_osm_polygon denomination text
public planet_osm_polygon generator:source text
public planet_osm_polygon highway text
public planet_osm_polygon historic text
public planet_osm_polygon iata text
public planet_osm_polygon junction text
public planet_osm_polygon landuse text
public planet_osm_polygon leaf_type text
public planet_osm_polygon leisure text
public planet_osm_polygon man_made text
public planet_osm_polygon military text
public planet_osm_polygon name text
public planet_osm_polygon name:de text
public planet_osm_polygon int_name text
public planet_osm_polygon name:en text
public planet_osm_polygon natural text
public planet_osm_polygon operator text
public planet_osm_polygon place text
public planet_osm_polygon power text
public planet_osm_polygon power_source text
public planet_osm_polygon railway text
public planet_osm_polygon ref text
public planet_osm_polygon religion text
public planet_osm_polygon ruins text
public planet_osm_polygon shop text
public planet_osm_polygon sport text
public planet_osm_polygon surface text
public planet_osm_polygon tourism text
public planet_osm_polygon tunnel text
public planet_osm_polygon water text
public planet_osm_polygon waterway text
public planet_osm_polygon wetland text
public planet_osm_polygon way USER-DEFINED
public planet_osm_polygon way_area real
public planet_osm_polygon z_order integer
public planet_osm_polygon localized_name_second text
public planet_osm_polygon localized_name_first text
public planet_osm_polygon localized_name text
public planet_osm_polygon localized_streetname text
public planet_osm_polygon country_name text
public planet_osm_polygon name_hrb text
public planet_osm_polygon layer integer
public planet_osm_polygon tags USER-DEFINED
public planet_osm_rels id bigint
public planet_osm_rels members jsonb
public planet_osm_rels tags jsonb
public planet_osm_replication_status url text
public planet_osm_replication_status sequence integer
public planet_osm_replication_status importdate timestamp with time zone
public planet_osm_roads osm_id bigint
public planet_osm_roads admin_level text
public planet_osm_roads covered text
public planet_osm_roads highway text
public planet_osm_roads name text
public planet_osm_roads name:de text
public planet_osm_roads int_name text
public planet_osm_roads name:en text
public planet_osm_roads railway text
public planet_osm_roads ref text
public planet_osm_roads service text
public planet_osm_roads surface text
public planet_osm_roads tunnel text
public planet_osm_roads aerialway text
public planet_osm_roads addr:housenumber text
public planet_osm_roads aeroway text
public planet_osm_roads amenity text
public planet_osm_roads barrier text
public planet_osm_roads boundary text
public planet_osm_roads building text
public planet_osm_roads historic text
public planet_osm_roads lock text
public planet_osm_roads man_made text
public planet_osm_roads power text
public planet_osm_roads route text
public planet_osm_roads shop text
public planet_osm_roads waterway text
public planet_osm_roads width text
public planet_osm_roads way USER-DEFINED
public planet_osm_roads z_order integer
public planet_osm_roads localized_name_second text
public planet_osm_roads localized_name_first text
public planet_osm_roads localized_name text
public planet_osm_roads localized_streetname text
public planet_osm_roads name_hrb text
public planet_osm_roads layer integer
public planet_osm_roads tags USER-DEFINED
public planet_osm_ways id bigint
public planet_osm_ways nodes ARRAY
public planet_osm_ways tags jsonb
public simplified_water_polygons x numeric
public simplified_water_polygons y numeric
public simplified_water_polygons way USER-DEFINED
public spatial_ref_sys srid integer
public spatial_ref_sys auth_name character varying
public spatial_ref_sys auth_srid integer
public spatial_ref_sys srtext character varying
public spatial_ref_sys proj4text character varying
public water_polygons x numeric
public water_polygons y numeric
public water_polygons way USER-DEFINED
vagrant@bookworm:/usr/local/sbin$ ^C
Unterschiede
Es gibt eine neue Datei:
osm=# select * from osm2pgsql_properties;
property | value
-----------------------------+--------------------------------------------------------------------
attributes | false
db_format | 2
flat_node_file |
output | flex
prefix | planet_osm
style | /srv/tile/sources/osml10n/openstreetmap-carto-hstore-only-l10n.lua
updatable | true
version | 2.0.0
current_timestamp | 2024-10-28T14:24:04Z
import_timestamp | 2024-10-28T14:24:04Z
replication_base_url | http://download.geofabrik.de/europe/monaco-updates
replication_sequence_number | 4224
replication_timestamp | 2024-10-28T21:21:30Z
(13 rows)
Zusätzlich gibt es neue Spalten und andere Datentypen:
vagrant@bookworm:/tmp$ diff -w osmdb.log osmdb2.log
61a62,63
> public osm2pgsql_properties property text
> public osm2pgsql_properties value text
142a145
> public planet_osm_nodes tags jsonb
252,256c255,256
< public planet_osm_rels way_off smallint
< public planet_osm_rels rel_off smallint
< public planet_osm_rels parts ARRAY
< public planet_osm_rels members ARRAY
< public planet_osm_rels tags ARRAY
---
> public planet_osm_rels members jsonb
> public planet_osm_rels tags jsonb
299c299
< public planet_osm_ways tags ARRAY
---
> public planet_osm_ways tags jsonb
Anpassungsversuche
Theoretisch wäre es möglich, die Datenbank so anzupassen, dass alles passt. Hier unsere ersten Befehle:
ALTER TABLE planet_osm_nodes
ADD COLUMN IF NOT EXISTS tags jsonb;
ALTER TABLE planet_osm_rels
ALTER COLUMN members SET DATA TYPE json USING to_jsonb(members),
ALTER COLUMN tags SET DATA TYPE json USING to_jsonb(tags),
DROP COLUMN way_off,
DROP COLUMN rel_off,
DROP COLUMN parts;
ALTER TABLE planet_osm_ways
ALTER COLUMN tags SET DATA TYPE json USING to_jsonb(tags);
CREATE TABLE osm2pgsql_properties (
property TEXT PRIMARY KEY,
value TEXT
);
ALTER TABLE osm2pgsql_properties OWNER TO _tirex;
Nach ersten Versuchen entscheiden wir uns jedoch für den sicheren Weg und lesen die Daten auch im Echtbetrieb neu ein.
Discussion