Import polygons to PostgreSQL database using ogr2ogr (Ubuntu 16.04)

Question

I want to use ogr2ogr to import data into a PostgreSQL database. Therefore, I create the database like this:

sudo add-apt-repository ppa:ubuntugis/ppa
sudo apt update
sudo apt install postgresql
sudo apt install postgis

sudo su postgres
psql
DROP DATABASE globcit;
CREATE DATABASE globcit;
\q
exit

Afterwards, I want to copy polygons from an OSM pbf file to this data base.

ogr2ogr -f PostgreSQL PG:dbname=globcit -nlt GEOMETRY 
-sql "select * from multipolygons where natural=\'water\'" 
-progress lakes turkey-latest.osm.pbf    

(formatted for better reading, usually one-liner)

However I get the error:

FAILURE:
Unable to open datasource `lakes' with the following drivers.
  -> PCIDSK
  -> netCDF
  -> JP2OpenJPEG
  -> PDF
  -> ESRI Shapefile
....

what am I missing?

Answer 1

A few things.

You installed PostGIS. But you did not activate it for the database.

So in the PSQL (after CREATE DATABASE globcit;) you need to run:

CREATE EXTENSION postgis;

Then you have a space in the file name.

So the ogr2ogr command should be:

ogr2ogr -f PostgreSQL PG:dbname=globcit -nlt GEOMETRY -sql "select * from multipolygons where natural='water'" -progress -nln lakes turkey-latest.osm.pbf

But it might still fail. The -sql statement may have errors.