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. Commented 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 Commented Jan 9, 2018 at 15:06
  • 1
    Did you change the database name? Does the table "lucz.sde.gdb_release" exist? Commented 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 Commented 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? Commented 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 Commented Jan 9, 2018 at 16:32
  • I'm contemplating uninstalling postgresql on this computer, reinstalling a newer version and trying to restore this Commented 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 Commented 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. Commented Jan 9, 2018 at 16:36
  • look at my update, once it fully restores I will go through your suggestions once more Commented Jan 9, 2018 at 17:30

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.