cant connect to postgres with GDAL ogr2ogr remotely

Question

I am trying to insert a shapefile into PostgreSQL via Gdal ogr2ogr, the command looks like this:

ogr2ogr -f 'PostgreSQL' PG:dbname='dbname',user='user',host='172.17.2.176',password='password',port='5432' shapefile -nln 'cities'

However, ogr2ogr doesn't seem to be able to connect to PosgreSQL which is in a separate docker container. All the connection parameters are correct, other connections are successfully made and executed with the same connection parameters to the same PostgreSQL container. Laravel migrations, command line sql that checks whether databases and tables exist before executing commands on those tables, and imports of csv files using the COPY command, all run successfully to the separate PostgreSQL docker container using the same connection parameters used in the ogr2ogr command.

when I run:

ogrinfo 'PostgreSQL' PG:dbname='dbname',user='user',host='172.17.2.176',password='password',port='5432'

I get an error: "Unable to open datasource `PostgreSQL' with the following drivers..."

one of the drivers listed is " -> PostgreSQL"

which means that the PostgreSQL driver is loaded and enabled? <- one point of confusion

The user being connected with has superuser permissions, created by the .sh script in the official PostgreSQL docker image here: https://github.com/docker-library/postgres/tree/master/9.4

As far as I can see and find through my research on google and documentation, everything is correct and should be working..

superuser permissions, correct syntax for db connection, drivers loaded.. what am I missing? I have been trying to make this work for a few days now, I need help.

Answer 1

The syntax of the connection string is important, particulary with the use of quotations. See the PostgreSQL driver for an example of what this should look like. For example, try:

$ ogrinfo PG:"host=172.17.2.176 port=5432 user='user' password='password' dbname='dbname'"

Some handy tips:

• If you frequently connect to this database, you can use environment variables to specify the host, port, database name, and/or user name.
• A password file can be used to avoid revealing your password while typing commands or within scripts.