osm2pgsql-replication failed with different schema

[dooley]

What version of osm2pgsql are you using?

osm2pgsql --version
osm2pgsql version 2.0.0 (2.0.0)
Build: RelWithDebInfo
Compiled using the following library versions:
Libosmium 2.20.0
Proj 8.2.1
Lua 5.1.4 (LuaJIT 2.1.0-beta3)

What operating system and PostgreSQL/PostGIS version are you using?

Linux Ubuntu 20.04 LTS
PostgreSQL 17.0 (Ubuntu 17.0-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
"POSTGIS=""3.4.3 e365945"" [EXTENSION] PGSQL=""170"" GEOS=""3.10.2-CAPI-1.16.0"" PROJ=""8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db"" LIBXML=""2.9.13"" LIBJSON=""0.15"" LIBPROTOBUF=""1.4.0"" WAGYU=""0.5.0 (Internal)"""

Tell us something about your system

128 GB RAM, 24 CPU, bare metal

What did you do exactly?

osm2pgsql --cache 6144 -s -c -d gis -U frank -P 5435 --output pgsql --multi-geometry --merc --hstore --schema carto --middle-schema carto --expire-tiles=10-19 --expire-output=/datengrab/osm/data/dirty_tiles --style carto.style --tag-transform-script carto.lua /datengrab/osm/data/germany-latest.osm.pbf

osm2pgsql-replication init -P 5435 -d gis --schema carto

osm2pgsql-replication status -P 5435 -d gis --schema carto --middle-schema carto
Using replication service 'http://download.geofabrik.de/europe/germany-updates', which is at sequence 4195 ( 2024-09-29T20:20:51Z )
Replication server's most recent data is 15 hour(s) 34 minute(s) 8 second(s) old
Local database is 3 sequences behind the server, i.e. 3 day(s) 0 hour(s) 17 minute(s) 41 second(s)

osm2pgsql-replication update -P 5435 -d gis --schema carto --middle-schema carto

What did you expect to happen?

osm2pgsql-replication should update my tables.

What did happen instead?

Update failed.

2024-09-30 13:55:10 [INFO]: Using replication service 'http://download.geofabrik.de/europe/germany-updates'.
2024-09-30 13:55:15 osm2pgsql version 2.0.0 (2.0.0)
2024-09-30 13:55:15 Database version: 17.0 (Ubuntu 17.0-1.pgdg22.04+1)
2024-09-30 13:55:15 PostGIS version: 3.4
2024-09-30 13:55:15 Loading properties from table '"carto"."osm2pgsql_properties"'.
2024-09-30 13:55:15 Not using flat node file (same as on import).
2024-09-30 13:55:15 Using output 'pgsql' (same as on import).
2024-09-30 13:55:15 Using style file '/home/frank/entwicklung/mapproxy/configs/openstreetmap-carto/carto.style' (same as on import).
2024-09-30 13:55:15 Storing properties to table '"carto"."osm2pgsql_properties"'.
2024-09-30 13:55:15 WARNING: The pgsql (default) output is deprecated. For details see https://osm2pgsql.org/doc/faq.html#the-pgsql-output-is-deprecated-what-does-that-mean
2024-09-30 13:55:15 Storing properties to table '"carto"."osm2pgsql_properties"'.
2024-09-30 13:55:15 Setting up table 'planet_osm_point'
2024-09-30 13:55:15 ERROR: Database error: ERROR: relation "public.planet_osm_point" does not exist
LINE 1: PREPARE get_wkb(int8) AS SELECT way FROM "public"."planet_os...
^
2024-09-30 13:55:15 [CRITICAL]: Exception during execution: Command '['/usr/local/bin/osm2pgsql', '--append', '--slim', '--prefix', 'planet_osm', '--middle-schema', 'carto', '-d', 'gis', '-P', '5435', '/tmp/tmp0ubm3fm1/osm2pgsql_diff.osc.gz']' returned non-zero exit status 1.

What did you do to try analyzing the problem?

Don't know what to do? I want to have these data in schema carto, not in public.

[dooley]

All tables are in schema carto:

select tablename, schemaname from pg_tables where schemaname = 'carto';

"planet_osm_rels"|"carto"
"planet_osm_roads"|"carto"
"planet_osm_nodes"|"carto"
"planet_osm_ways"|"carto"
"planet_osm_point"|"carto"
"planet_osm_line"|"carto"
"planet_osm_polygon"|"carto"
"osm2pgsql_properties"|"carto"

[joto]

Can you show us all the contents of the osm2pgsql_properties properties table?

[dooley]

Sure.

          property           |                                         value                                          
-----------------------------+----------------------------------------------------------------------------------------
 attributes                  | false
 db_format                   | 2
 flat_node_file              | 
 output                      | pgsql
 prefix                      | planet_osm
 style                       | /home/frank/entwicklung/mapproxy/configs/openstreetmap-carto/carto.style
 updatable                   | true
 version                     | 2.0.0
 current_timestamp           | 2024-09-26T20:01:44Z
 import_timestamp            | 2024-09-26T20:01:44Z
 replication_base_url        | http://download.geofabrik.de/europe/germany-updates
 replication_sequence_number | 4192
 replication_timestamp       | 2024-09-26T20:03:10Z
(13 rows)

[lonvia]

Your update command isn't going to work because the legacy pgsql output doesn't store its command line parameters in the properties table. You need to repeat them for the update run:

osm2pgsql-replication update -P 5435 -d gis --schema carto -- --cache 6144 --output pgsql --multi-geometry --merc --hstore --expire-tiles=10-19 --expire-output=/datengrab/osm/data/dirty_tiles --style carto.style --tag-transform-script carto.lua

We should be forwarding --schema though as --schema, it seems to end up as --middle-schema in the osm2pgsql command line.

Quick workaround for now: add --schema carto to the extra arguments, i.e. like this:

osm2pgsql-replication update -P 5435 -d gis --middle-schema carto -- --cache 6144 --output pgsql --multi-geometry --merc --hstore --expire-tiles=10-19 --expire-output=/datengrab/osm/data/dirty_tiles --style carto.style --tag-transform-script carto.lua --schema carto

[dooley]

Thank you, that solved the problem.