1

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

  • 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 '18 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 '18 at 15:06
  • 1
    Did you change the database name? Does the table "lucz.sde.gdb_release" exist? – Vince Jan 9 '18 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 '18 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 '18 at 16:09
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.

  • I cannot connect with the conflict user using psql – ziggy Jan 9 '18 at 16:32
  • I'm contemplating uninstalling postgresql on this computer, reinstalling a newer version and trying to restore this – ziggy Jan 9 '18 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 '18 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 '18 at 16:36
  • look at my update, once it fully restores I will go through your suggestions once more – ziggy Jan 9 '18 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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