Loading layers from PostGIS db (populated with osm2pgsql) in QGIS?

Question

I have downloaded some OSM data from geofabrik and used this to create a PostgreSQL/PostGIS database using the osm2pgsql tool:

osm2pgsql -G -d <db> -U postgres myfile.osm

Examining the database in pgAdmin shows everything as I would expect: there are tables for planet_osm_line etc. which all have data including geometry column. However, when I connect to the database from QGIS and attempt to add layers, none of these tables appear in the Data Source Manager|PostGIS dialog.

Searching on this site (and comparing with a working PostGIS database that I created using QGIS and some random shape files) led me to suspect that I might need a primary key field in my tables. But adding one with:

ALTER TABLE planet_osm_line ADD COLUMN id SERIAL PRIMARY KEY;

has not helped. I have also tried running osm2pgsql without the -G flag, changing the name of the geometry column to 'geom' to match what QGIS produced in my working database and changing projection in QGIS to Spherical Mercator (3857) to match the data in the PostGIS database. And I've made sure to name the connection as per the solution here: Why can't I open postgis layers in QGIS?

As I'm quite new to all this I suspect I've overlooked something in my usage of either osm2pgsql or QGIS. Could someone point out the error of my ways?

In case it's useful, I'm using:

• osm2pgsql version 0.96.0
• QGIS 3.4.3-Madeira
• PostgreSQL 11.1
• postgis_full_version(): POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.3.2, released 2018年09月21日" LIBXML="2.9.8" LIBJSON="0.13.1" LIBPROTOBUF="1.3.1" RASTER

• Mapperz: you don't mention file size try osm2pgsql -c -S /usr/share/osm2pgsql/default.style --slim -d osm-europe --flat-nodes flat-nodes.bin --number-processes 8 -C 20000 europe-latest.osm.pbf from > gis.stackexchange.com/questions/104220/…

Answer 1

First off, thanks very much to Mapperz for the pointer. The dataset I'm importing is a (fairly small?) 1.5G .osm file. Using the Mapperz-recommended command options (adjusted for the fact I don't have that much memory in my laptop) produced a database that could be imported into QGIS. A bit more experimenting showed the problem was with the -U option in my original osm2pgsql command. The following command works nicely:

osm2pgsql -d <db> myfile.osm