Error on basic usage of osm2pgsql

Question

I need only basic osm2pgsql usage. Using postgres user and sandbox (created) database.

 sudo -u postgres osm2pgsql -s -U postgres -d sandbox brazil-latest.osm.pbf

---

NOTES

Using UBUNTU 14 LTS, osm2pgsql --version say "osm2pgsql SVN version 0.82.0 (64bit id space)". At SQL terminal (psql) the select version() says "9.3.9 on x86_64-unknown-linux-gnu".

Prepare, all steps:

wget -c http://download.geofabrik.de/south-america/brazil-latest.osm.pbf
wget -c http://download.geofabrik.de/south-america/brazil-latest.osm.pbf.md5
md5sum -c brazil-latest.osm.pbf.md5
# OK
sudo -u postgres psql sandbox
# ... SELECT PostGIS_version(); -- 2.1 OK
\q
sudo -u postgres osm2pgsql -s -U postgres -d sandbox brazil-latest.osm.pbf

last command errors:

osm2pgsql SVN version 0.82.0 (64bit id space)
Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
NOTICE: table "planet_osm_point" does not exist, skipping
NOTICE: table "planet_osm_point_tmp" does not exist, skipping
Setting up table: planet_osm_line
NOTICE: table "planet_osm_line" does not exist, skipping
NOTICE: table "planet_osm_line_tmp" does not exist, skipping
Setting up table: planet_osm_polygon
NOTICE: table "planet_osm_polygon" does not exist, skipping
NOTICE: table "planet_osm_polygon_tmp" does not exist, skipping
Setting up table: planet_osm_roads
NOTICE: table "planet_osm_roads" does not exist, skipping
NOTICE: table "planet_osm_roads_tmp" does not exist, skipping
Allocating memory for dense node cache
Out of memory for node cache dense index, try using "--cache-strategy sparse" instead 
Error occurred, cleaning up

Try also sudo -u postgres osm2pgsql --create --cache-strategy sparse -s -U postgres -d sandbox brazil-latest.osm.pbf but some error: "Allocating memory for sparse node cache Out of memory for sparse node cache, reduce --cache size Error occurred, cleaning up "

Try also osm2pgsql -c -d sandbox -U postgres -H localhost -S brazil-latest.osm.pbf that say "Usage error".

Try also osm2pgsql -U postgres --slim -C 20000 -d sandbox --host localhost --number-processes 20 ./brazil-latest.osm.pbf but "Error: Connection to database failed: fe_sendauth: no password supplied".

sudo -u postgres osm2pgsql -U postgres --slim -C 20000 -d sandbox --host localhost --number-processes 20 ./brazil-latest.osm.pbf say "Error: Connection to database failed: fe_sendauth: no password supplied".

• Giacomo Catenazzi: Maybe the -C 20000 is too high, and the system cannot allocate 20GB or memory. Try to lower such number
• xunilk: I found out a osm2pgsql usage that it worked for me. See my answer.
• Peter Krauss: Hi @GiacomoCatenazzi your clue about -C option is correct (!), I try with -C 2000 (same error) them -C 200 and it works! It is a 1GB RAM DigitalOcean servelet.
• Peter Krauss: @xunilk, you say "See my answer", do you have a link? ... Well now I found help.openstreetmap.org/5956, help.openstreetmap.org/8322.
• xunilk: My answer, of course, in your question in gis.stackexchange.com: "Error on basic usage of osm2pgsql". I didn't use --slim parameter.

Answer 1

I used the osm2pgsql command (installed with synaptic) in my Debian system in a different way. After creating and connecting the sandbox database (at a bash console):

su
********* [password]
su - postgres
psql
postgres=# CREATE DATABASE sandbox OWNER my_owner;
CREATE DATABASE
postgres=# \connect sandbox
You are now connected to database "sandbox" as user "postgres".

I created postgis and hstore extensions with these instructions:

sandbox=#CREATE EXTENSION postgis;
CREATE EXTENSION
sandbox=# CREATE EXTENSION hstore;
CREATE EXTENSION

The last one extension implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This is especially useful when you are working with OpenStreetMap data.

After downloading brazil-latest.osm.pbf, as it was specified above, I used this command (in a bash console):

osm2pgsql -d sandbox -s -W --hstore brazil-latest.osm.pbf

to load the layers to sandbox database.

An extract of all process at the bash console (my password was required):

osm2pgsql SVN version 0.86.0 (64bit id space)
Password:
Using projection SRS 900913 (Spherical Mercator)
.
.
.
Reading in file: brazil-latest.osm.pbf
Processing: Node(35458k 124.9k/s) Way(3307k 8.82k/s) Relation(71370 41.45/s) parse time: 2381s
.
.
.
Stopped table: planet_osm_ways in 2613s
Osm2pgsql took 5697s overall

After finished the process, I loaded the planet_osm_polygon layer by using 'Add PostGIS Layers' button in the 'Manage Layers Toolbar' of QGIS:

enter image description here

It works!

• Peter Krauss: Thanks, you show a lot more context and steps (!). Well, about the question, the key answer is -C option, that must be compatible with the RAM of the server. The only command that run on my 1Gb RAM was sudo -u postgres osm2pgsql -C 400 --slim --cache-strategy sparse -s -U postgres -d sandbox brazil-latest.osm.pbf

Answer 2

The errors are clear. On first you don't have enough memory, so you should use --slim (but you should do this in any case, if you want to update the data).

The last errors are database connection error. Check again the configuration of database and permissions of the user.

Note: if you installed a new version of postgresql (over a ond one, in Debian and ubuntu), the database engine uses a new port number, so try to give explicitly a port number.

• Peter Krauss: Yes, see "NOTES" I used... The best try with slim was sudo -u postgres osm2pgsql --slim -s -U postgres -d sandbox brazil-latest.osm.pbf that, as error, suggested to use "--cache-strategy sparse"... Adding cache option, sudo -u postgres osm2pgsql --cache-strategy sparse --slim -s -U postgres -d sandbox brazil-latest.osm.pbf, where the error is "Allocating memory for sparse node cache\n Out of memory for sparse node cache, reduce --cache size"
• Peter Krauss: What is "new version of postgresql"? I am using pg9.3.9 (as "select version()" say), so, if it is "new", what the port number?
• Giacomo Catenazzi: I imported the entire planet with --slim and -C 12000 (which are much less then yours, and for a larger area), so I tend to think there is an other error. I used also "--input-reader pbf" and --style ... , but these should have given you an other error.
• Giacomo Catenazzi: The first version of postgresql will use port 5432, but when you install a new version (sometime also because an update, and during update the other port is still used), the new server will use 5433 or successive ports.