Enterprise geodatabase on PostgreSQL backup and restore

Question

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

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 FilesPostgreSQL9.5bin>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

Evan Carroll · Answer

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

A python script and a DLL
The Create Enterprise Geodatabase tool

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

Enterprise geodatabase on PostgreSQL backup and restore

One Answer

Can you connect with the `conflict` user using psql?

If you can connect

Add your own answers!

Ask a Question