0

I have been trying to import/load a shapefile into a PostGis database without any success despite many trials and searching an answer in the documentation.

Configuration I'm running Windows 64 bits Home (in japanese ) pgAdmin 4 PostgreSQL 12.1 postgis 3

I used 2 differents methods without any results

METHOD 1 - using the shp2pgsql-gui.exe/postGis shapefile import gui

*Here are the connection details username postgres

password : i put the same password I usually input in pgadmin4

server host "localhost" then for the port "5432"

database : gis_analysis (a postgis database)

*And I get the following error message :

"unable to connect to the database - please check your connection settings"

and the log message

"Connecting: host=localhost port=5432 user=postgres password='*********' dbname=gis_analysis client_encoding=UTF8 Connection failed."

*I checked TCPIP to check if the port 5432 was opened I got 3 sessions of pgadmin4 running on remote port 5432 with a state "established" and some sent packets

METHOD 2 - using psql/the windows prompt command

I was planning to use something like this kind of syntax : shp2pgsql -I -s SRID -W encoding shapefile_name table_name | psql -d database -U user

But even when I'm running the following command in the prompt command

psql -d gis_analysis -U postgres

*I first get a enter the password : the password does not show up in the terminal but my understanding is that it's normal. I typed slowly the I also copied pasted to make sure I don't make a mistake) *Eithe way I then get the following error message (for clarity sake, I get a japanese message but I put here the standard message in english that seems to be used)

'psql: FATAL: password authentication failed for user "postgress"'

BOTTOMLINE

I can use (import, export, select, alter) a variety of database/tables in pgadmin4 without connection trouble (at worst sometimes it fails because it's too slow but it seems unrelated to me). I can specifically create a table within my gis_analysis postgis database. I can both import csv file into this table, I can alter/update the table, I can select data from it. But somehow I cannot import a shapefile Nor can I use the command prompt, either for the postgis database or any other database.

I am inclined to think it's a username/password problem based on the fact there is no port problem and that I cannot access any kind of database with psql. But I don't understand why I don't have this connection problem with pgadmin4 and yet when I run either shp2pgsql-gui or psql it doesnt work. I am at my wit's end. Any insight ?

EDIT : as requested in answer, I m adding a snapshot of connection settings connection settings

Improve this question
2
  • Shapefiles or PostGIS have nothing to do with this. Show all the values you've entered on the "Connection" and "SSL" tabs in pgAdmin4.
    – CL.
    Jun 30, 2020 at 6:48
  • @CL As requested I took a snapshot of all the values in the tab connection and ssl it's in the original post. Truth to be told, I never looked into that/wasnt aware of that. I'm learning through a postgresq book that is really strong on showing how to practically use postgresql/for what purpose but seems light on the settings side. I had to figure out on my own other problems of the same kind before. I'm just stuck on this one and I would really like to move forward on postgis (and psql later on). If you can take a look at the values and give me some insight it would be very helpful!
    – jiquvox
    Jun 30, 2020 at 15:21

1 Answer 1

Reset to default
0

The pgAdmin4 connection goes to the database named "postgres".

Apparently, a database named "gis_analysis" does not exist, or is not configured to accept connection.

Improve this answer
5
  • Thanks for the feedback! I'm not quite sure though of the exact meaning/the change to implement. No explanation at all in my book about those aspect.
    – jiquvox
    Jul 1, 2020 at 15:56
  • Did you create that database? Or does your data show up in the "postgres" database in pgAdmin?
    – CL.
    Jul 1, 2020 at 16:03
  • If my memory is correct, postgres was created automatically. And my understanding it is by design. I did not touch anything/input any data directly in this default database. I created another database called "analysis" to perform all my other manipulations. And yet another one for the Gis data "gis_analysis". To come back on your earlier comment : how am I supposed to configure gis_analysis to "accept connection" / how was the earlier page supposed to look like ?
    – jiquvox
    Jul 1, 2020 at 16:16
  • That would be the pg_hba.conf file.
    – CL.
    Jul 1, 2020 at 19:07
  • I'm coming back late to you. I eventually solved it by granting access to every database to any user in the pg_hba.conf . It's far less elegant than I'd like to and would be a huge no no from a database security standpoint If i understand right. But at least it got me through it for the time being. Thanks for pointing out the pga_hba file. I cannot upvote you because my reputation is too low if i understand right but anyway thanks for the tip.
    – jiquvox
    Sep 1, 2020 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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