Categories
Database Development

Unable to import shapefile into PostGisDatabase – problem of connection

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

Leave a Reply

Your email address will not be published. Required fields are marked *