Using PostgreSQL with ArcGIS

Question

Have mercy ... with Stackexcachange I'm not yet so familiar, so this is initially somewhat opaque. I try to do my best to organize this thread as well as possible. Recently I've started to get WFS and Feature Access working. WFS works for now. For Feature Access I need a database...

This is the "old" thread: WFS & Feature Access -> Database connection / Database support (ArcGIS 10.2) .

To make it more easy to figure out, what's going wrong, I've started from scratch. Until now the status quo is::

• I've downloaded the file "PostgreSQL_DBMS_for_Windows_922_136133.exe" from the esri Customer Portal. This package include the PostgreSQL 9.2.2 executable installation. So...no I have a new installation of PostgreSQL 9.2.2 64Bit without PostGIS and without modifying something except of the pg_hba.conf file to enable remote access (Remote Access is working)
• I've also downloaded from the esri Customer Portal the PostgreSQL_Client_Libraries for_Windows_922_136131.exe" file, which includes the Client files for database support. I've copied the 64Bit files to the \bin directory of my ArcGIS Sever installation and I've copied the 32bit files to the \bin directory of my ArcGIS Desktop installation, which is running in my personal computer.
• As mentioned...the only thing, which I've modified for my psotgreSQL Database is the pg_hba.conf file to grant access via remote connections. This is working! I can access the postgreSQL database running on my server via my personal computer using pg Admin. Here is a small overview of the database: pg Admin "postgres" is the only existing database. There I haven't modified anything.

Now I want to add the Database connection to ArcCatalog. So I open ArcCatalog , go to "Add Database Connection" and there I fill in the necessary information. The first initial problem is: I can't add the connection to the database, because I haven't got the chance to select one database. The only existing database "postgres" (look screenshot of pg Admin; the database exists!) is not selectable via the "Add Database connection" assistant ArcCatalog. It also doesn't work to type the "postgres" manually in the "Database field". There I get the following error: Connections to system databases are not allowed. Please provide a different database for the connection."error

So....the next step would be to grant access to the postgreSQL database, so I can access it via the "Add Database Connection" wizard or, maybe better?, setting up a new database. But here I don't know which settings the database needs.

I think, it would be the best to figure out step by step what I have to do. So it's easier for you and me. Thanks a lot and I'm looking forward for some feedback :), Dominik

• RyanKDalton: Can we assume you have read through Setting up a geodatabase in PostgreSQL?
• schlomm: Yes...you can! Even through the 10.2 specific one: resources.arcgis.com/en/help/main/10.2/index.html#//… ;)
• Mapperz: st_geometry.dll is missing in C:\Program Files (x86)\ArcGIS\Desktop<release#>\DatabaseSupport\PostgreSQL\Windows64 resources.arcgis.com/en/help/main/10.2/index.html#//…
• schlomm: @Mapperz st_geometry.dll is the directory. I've checked it twice and it's definitely in C:\Program Files\PostgreSQL9円.2\lib. Referring to gis.stackexchange.com/questions/69214/… (comments) I'm now able to set up a connection.

Answer 1

It looks like your error is telling you not to use the postgres database. Probably because that database is normally reserved for the postgresql admin user, postgres.

To get rid of this, create a new database called gistest or whatever you like - the owner can still be postgres - and try connecting to the new db.

• Theo F: Can confirm. For some reason ArcGIS won't allow a connection to the default database 'postgres'. It will allow connection to other databases on the same server, just not 'postgres'. Very frustrating if all your data is kept on the 'postgres' db.
• SaultDon: @TheoF I think they built it like that as a safety guard against populating "reserved" schemas like public and postgres. It's not ideal to store objects in those and discouraged - your database should be designed to avoid this with proper search_path, schemas created, extensions and functions stored securely but accessible, etc...

Answer 2

I would start here: http://workshops.opengeo.org/postgis-intro/

That's a pretty good walkthrough for setting up a new spatial database using pgAdmin.

It's written for folks who are using the OpenGeo suite so you might have to do a little figuring on your own but the bulk of it should be directly applicable.

• schlomm: Thanks for the guide. I'll go through it. It might be very useful :)

Answer 3

I would use the gdb tools in the toolbox.
open arccatalog and the toolbox.

gdbtools

• schlomm: Hi, I've done it and hopefully it's working in the right way. I've used the "Create Enterprise Geodatabase" Tool. imgur.com/a/iVMkk#2 First attempt: Tool without defining sde. This does not working, although sde should be optionally (1. screenshot) Second attempt: I've set a sde passwort. This time the creation of the database worked, but unfortunately with an error (2. screenshot) Anyway: It seems, that it has worked. pgAdmin shows the created "data" Database (3. screenshot).
• schlomm: I am also able to connect to this database by using ArcCatalog "Add Database Connection". I am also able to add a user via "Create Database User" and after that, connecting to the database with the previously created user. So...is everything right for the moment?
• jonaktiv: I have nothing to say to help, but I am self trying to create postgres database to work with 10.2.2 and I am wondering what i need to install, I have PostgreSQL_DBMS_for_Windows_922_136133 and PostgreSQL_Client_Libraries_for_Windows_922_136131 do i need arcgis arcsde for postresSQL ? and /or arcgis server?

Answer 4

Simple answer:

in the instances, just type:

localhost, 5432

Or change to your port number it might be 54321 depending on your installation

Colon and semi-colon doesn't work!

comma is the answer. I just did it now.

Answer 5

Want to improve this post? Provide detailed answers to this question, including citations and an explanation of why your answer is correct. Answers without enough detail may be edited or deleted.

try to make another db in your postgres version | for me work fine.

• Midavalo: Welcome to GIS SE! As a new user please take the tour to learn about our focused Question and Answer format. Please edit your answer to include more information about how to do what you're suggesting, and how it will solve the problem