I need a tool like osm2po or osm2pgsql but that does the opposite thing, convert a postgis table to a pbf file. I think qgis can do this but I can't find that option.
[GIS] How to export a postgis table to a pbf file
osm2pgsqlosm2popostgisqgis
Related Solutions
I guess that you took the example createTopology function which is shown for WGS84 geometries, using degrees as a projection unit. 0.00001 degree tolerance means that your points have to be less than 1.19 meters away from each other to be connected.
As seen here : https://epsg.io/900913
The system that you're using uses meter as projection unit. Which means that your points have to be less than 0.00001 meters away from each other, which is very low tolerance. ESPG:4326 0.00001 tolerance equals ESPG:900913 1.19 tolerance. I would suggest that you tried increasing the tolerance to this value in order to get better results.
You can run osm2pgsql with null output, no RAM cache, and no slim cache. For a 2.5GB PBF extract of Canada, it took me about 37 seconds to count the nodes, on an NVMe SSD.
$ osm2pgsql --output null --cache 0 canada-latest.osm.pbf
osm2pgsql version 0.96.0 (64 bit id space)
WARNING: ram cache is disabled. This will likely slow down processing a lot.
Using projection SRS 3857 (Spherical Mercator)
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=0MB, maxblocks=0*65536, allocation method=3
Reading in file: canada-latest.osm.pbf
Using PBF parser.
Processing: Node(327502k 32750.3k/s) Way(18801k 895.29k/s) Relation(0 0.00/s) parse time: 31s
Node stats: total(327502800), max(6338602157) in 10s
Way stats: total(19016827), max(676910855) in 21s
Relation stats: total(321360), max(9397389) in 0s
Going over pending ways...
0 ways are pending
Using 12 helper-processes
Finished processing 0 ways in 0 s
Going over pending relations...
0 relations are pending
Using 12 helper-processes
Finished processing 0 relations in 0 s
node cache: stored: 0(0.00%), storage efficiency: nan% (dense blocks: 0, sparse nodes: 0), hit rate: nan%
Osm2pgsql took 37s overall
Note that there are two numbers, the total for this file, and the max for the OSM database.
The node count can then be multiplied by 8 bytes to get a good estimate for the cache size when you import into PostgreSQL. In this case, 327502800 * 8 bytes = 2.44 GB. The osm2pgsql manual recommends adding 10 to 30% overhead on top of that cache value.
Best Answer
GDAL can only read pbf files, so no chance to write it with GDAL or QGIS.
Osmosis is able to read from a postgis database and write to pbf, but only for its own snapshot or simple data scheme. These schemes use nodes, ways and relations (like they are stored in the OSM database), while a "usual" Postgis data scheme uses points, lines, multilines, polygons and multipolygons.
Converting from one scheme to the other is one of the difficult and time consuming parts of osm2pgsql.