2

Is it possible to dump a postgres database that was created with osm2pgsql and a .osm.pbf file with pg_dump and import this into a remote postgres database?

After running osm2pgsql the database is 135 Gb. When I create the dump from postgres, I have an 80Gb file.

When I bzip the dump file I end up with a 17Gb compressed file.

I'm hoping this would be less resource intensive than running osm2pgsql on the remote server. The remote server has a max of 4 gigs of RAM, and resource limits are easily reached.

The import process took close to 7 hours on a 2.5 ghz MacBook Pro with 16 gigs of ram. I've been running osm2pgsql for the last two days using --slim and -C 3500.

This is the second time I'm attempting to import on the remote server with osm2pgsql. I'm thinking that importing a dump of the working database may be more efficient.

  • 1
    Once it is in Postgres, anything can be dumped and, yes, as the import programs do various processing steps, it will be faster than using osm2pgsql again. You can pipe the output of pg_dump directly to a remote machine and not even worry about intermediate compressed files. – John Powell Jun 15 '15 at 5:48
2

Using pg_dump and pg_restore is a recommended method when you have access to a powerful development machine, a less powerful production server, and sufficient bandwidth between them. I used a modified version of this method to create a simplified database dump for stylesheet development on less powerful machines.

This method brings a few advantages

  1. It's common to have access to a more powerful non-production machine.

  2. It can make it easier to manage multiple production machines or multiple databases if only pg_restore is needed.

  3. Some cloud providers are limited in their offerings and do not have good options for osm2pgsql servers, but do have good options for database servers.

  4. It is fairly easy to do a updated with pg_dump/pg_restore.

  5. A local database can be kept with slim tables (capable of updates) and only the rendering tables loaded on the production server

It also has disadvantages

  1. Does not allow minutely updates.

  2. Requires sufficient upload bandwidth from the development machine. This can be an issue on residential internet connections.

  3. Requires two machines

  4. You still need a database server powerful enough for your needs, and if rendering, also a rendering server

Loading the data

Assuming you plan to update by reloading instead of diffs, you can import with a command like osm2pgsql --create --number-processes <N> --cache <cache> -d gis --slim --drop planet-latest.osm.pbf, where <N> is the number of hardware threads (typically 4 or 8) and <cache> is about 80% of RAM, to a maximum of 28000.

Creating a dump

You then want to use pg_dump to create a dump. The "custom" format is the best one to use as it supports creating a single compressed file.

pg_dump -F custom -t planet_osm_point -t planet_osm_line -t planet_osm_polygon -t planet_osm_roads -Z 9 -d gis > gis.dump. This will create a compressed file that can be restored on another system.

Restoring the dump

To restore the dump, a database with PostGIS is needed. It is possible to dump PostGIS along with the osm2pgsql tables, but this can fail if different PostGIS versions or different install paths are used.

Assuming you are on the other server, you can create the database with createdb -T template0 gis && psql -d gis2 -c 'CREATE EXTENSION postgis;'. The database can be restored with pg_restore -d gis -j 4 -O gis.dump, which will restore the four rendering tables in parallel.

Notes

  • If you intend to do updates with diffs (e.g. if you want to update every week) you will need a more complicated pg_restore command to not create the osm_id indexes on the rendering tables.

  • This isn't really practical for anything more frequent than daily updates

  • Doing this in a production environment where you cannot shut down the rendering server will require loading into a second database and then switching to it.

  • As a rough estimate, the dump will be about 3-4 times the input PBF size, or about 95GB for the full planet

  • If loading the full slim tables onto the remote machine for updates, the remote database server should have enough RAM to create the large GIN index on planet_osm_ways (nodes).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.