sql: postgis extension is not enabled error when importing OpenStreetMap data

[florence-crl]

Describe the problem I hit the error below when I ran step 3 documented on this page: Migrate from OpenStreetMap

ERROR: The postgis extension is not enabled on the database 'defaultdb'. Are you using the correct database? Enable with 'CREATE EXTENSION postgis;'

To Reproduce

0.a. create a local CRDB 22.1.1 cluster 0.b. install osm2pgsql

1. Download the OpenStreetMap data

wget https://download.geofabrik.de/australia-oceania/australia-latest.osm.pbf

2. Prepare the database 2.a

   cockroach sql --insecure

2.b create a database to hold the Australia map data:

CREATE DATABASE IF NOT EXISTS australia;
USE australia;

2.c. Run this recommended step that is mentioned in the error message

CREATE EXTENSION postgis;

3. Import the OpenStreetMap data

osm2pgsql -C 2048 -d australia -U root -H localhost -P 26257 australia-latest.osm.pbf

Error above is returned

Expected behavior A successful run that looks like

osm2pgsql version 1.3.0

Allocating memory for dense node cache
Allocating dense node cache in one big chunk
Allocating memory for sparse node cache
Sharing dense sparse
Node-cache: cache=2048MB, maxblocks=32768*65536, allocation method=3
Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
WARNING:  setting session var "synchronous_commit" is a no-op
WARNING:  setting session var "synchronous_commit" is a no-op
Setting up table: planet_osm_point
NOTICE:  UNLOGGED TABLE will behave as a regular table in CockroachDB
NOTICE:  storage parameter "autovacuum_enabled = 'off'" is ignored
WARNING:  setting session var "synchronous_commit" is a no-op
Setting up table: planet_osm_line
NOTICE:  UNLOGGED TABLE will behave as a regular table in CockroachDB
NOTICE:  storage parameter "autovacuum_enabled = 'off'" is ignored
WARNING:  setting session var "synchronous_commit" is a no-op
Setting up table: planet_osm_polygon
NOTICE:  UNLOGGED TABLE will behave as a regular table in CockroachDB
NOTICE:  storage parameter "autovacuum_enabled = 'off'" is ignored
WARNING:  setting session var "synchronous_commit" is a no-op
Setting up table: planet_osm_roads
NOTICE:  UNLOGGED TABLE will behave as a regular table in CockroachDB
NOTICE:  storage parameter "autovacuum_enabled = 'off'" is ignored

Reading in file: australia-latest.osm.pbf
Using PBF parser.
Processing: Node(66994k 411.0k/s) Way(4640k 7.13k/s) Relation(124777 1313.4/s)  parse time: 909s
Node stats: total(66994811), max(7888181047) in 163s
Way stats: total(4640490), max(845495883) in 651s
Relation stats: total(124777), max(11596803) in 95s
Sorting data and creating indexes for planet_osm_point
node cache: stored: 66994811(100.00%), storage efficiency: 50.93% (dense blocks: 800, sparse nodes: 62492547), hit rate: 100.00%
Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_roads
Using native order for clustering
Using native order for clustering
Using native order for clustering
Using native order for clustering
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on planet_osm_roads
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on planet_osm_point
Creating indexes on planet_osm_roads finished
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on planet_osm_polygon
All indexes on planet_osm_roads created in 318s
Completed planet_osm_roads
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on planet_osm_line
Creating indexes on planet_osm_point finished
All indexes on planet_osm_point created in 1084s
Completed planet_osm_point
Creating indexes on planet_osm_polygon finished
All indexes on planet_osm_polygon created in 1897s
Completed planet_osm_polygon
Creating indexes on planet_osm_line finished
All indexes on planet_osm_line created in 1961s
Completed planet_osm_line

Osm2pgsql took 2879s overall

Additional data / screenshots

Environment:

• CockroachDB version 22.1.1
• Server OS: Mac
• Client app osm2pgsql

Additional context I could not Import the OpenStreetMap data.

A user encountered this error using a serverless cluster

Jira issue: CRDB-16748

[otan]

It fails here:

SELECT regexp_split_to_table(extversion, e'\\.') FROM pg_extension WHERE extname = 'postgis';

I thought we populated the table but guess not:

root@localhost:26257/defaultdb> SELECT * FROM pg_extension WHERE extname = 'postgis';
  oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
------+---------+----------+--------------+----------------+------------+-----------+---------------
(0 rows)

[otan]

root cause is #80012

[otan]

(marking as duplicate)

[florence-crl]

Workaround To unblock this error, you can build osm2pgsql with a patch. replace https://github.com/openstreetmap/osm2pgsql/blob/master/src/pgsql.cpp#L228-L244 with {3, 0} (we are 3.0.1) follow https://github.com/openstreetmap/osm2pgsql#building