2

windows 7, PostgreSQL 9.4/ArcSDE/PostGIS backend database that I am transferring to another computer.

My backup command is as follows. host 5433

pg_dumpall -h localhost -p 5433 -U postgres -c -f "file.backup"

backups up all nice and everything.

when I go to restore it on windows 10, PostgreSQL 9.4, port 5433 I run

psql -h localhost -p 5433 -U postgres -f file.backup

so everything restores okay. All tables, schemas, logins are good. I can connect with QGIS and everything works fine.

Problem

When I go to add an ArcGIS database connection I get this error

enter image description here

UPDATE

I uninstalled postgres on the new computer reinstalled 9.5, ran psql -h localhost -p 5432 -U postgres -f file.backup on port 5432 and in the CMD I found 1 error it should while it is running the restore

psql:R:/Data/LUCZ_2017/blah.backup:12531010: ERROR:  relation "gdb_items" does not exist

whats going on here!

Based on Evan Carroll's suggestion I ran 

C:\Program Files\PostgreSQL\9.5\bin>psql \duS+
Password:
psql: FATAL:  password authentication failed for user "rizagha"

the CMD wouldn't let me enter my password, I pressed enter and received the above error

Improve this question
12
  • 1
    5433 is not a valid instance. The proper syntax is hostname,5433. Please remember to include the entire error message as text in the body of your question.
    – Vince
    Jan 9, 2018 at 15:02
  • 1
    5433 works as a proper instance for me on my old computer. and even with localhost,5433 I get the exact same error
    – ziggy
    Jan 9, 2018 at 15:06
  • 1
    Did you change the database name? Does the table "lucz.sde.gdb_release" exist?
    – Vince
    Jan 9, 2018 at 15:08
  • I did not change the database name, and the table "lucz.sde.gdb_release" does not even exists in my old working database
    – ziggy
    Jan 9, 2018 at 15:14
  • Are you sure you're restoring to port 5433 on Windows, 5432 is the default? Also you're connecting with user:conflict and restoring from user:postgres can you connect with user:postgres?
    – Evan Carroll
    Jan 9, 2018 at 16:09

1 Answer 1

Reset to default
0

Can you connect with the conflict user using psql?

I'm guessing strongly that you don't have your pg_hba.conf configured properly. Check out the difference between the old install and the new install. It's not backed up with restore/dump. I assume you're missing something like this

host lucz conflict all md5

You need to at lest enable host to connect with md5.

See this guide on "PostgreSQL is installed on a Windows server on ESRI" for more information.

If you can connect

You may need the ESRI schema, sde which provides the GDB_Release table. ESRI provide either

  1. A python script and a DLL
  2. The Create Enterprise Geodatabase tool

You'll have to run one of those to turn your PostgreSQL into a geodatabase.

Improve this answer
5
  • I cannot connect with the conflict user using psql
    – ziggy
    Jan 9, 2018 at 16:32
  • I'm contemplating uninstalling postgresql on this computer, reinstalling a newer version and trying to restore this
    – ziggy
    Jan 9, 2018 at 16:32
  • this did nothing host conflict all all md5 and conflict is a schema and login role not a database for me
    – ziggy
    Jan 9, 2018 at 16:33
  • @ziggy sorry I typeoed that pg_hba wrong, try that. and paste the result of \duS+ as requested previously.
    – Evan Carroll
    Jan 9, 2018 at 16:36
  • look at my update, once it fully restores I will go through your suggestions once more
    – ziggy
    Jan 9, 2018 at 17:30

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.