topic Re: Connecting to Postgres database in ArcGIS Online Questions

Connecting to Postgres database

TheGamer — Mon, 20 Jun 2022 13:23:06 GMT

Hi, is there an easy way to connect to a postgres database (hosted through AWS) to ArcGIS Online?

Re: Connecting to Postgres database

George_Thompson — Mon, 20 Jun 2022 13:28:46 GMT

You have to publish data to ArcGIS Online. There is not a way to connect an "external" DB (as far as I know). The published data is hosted in ArcGIS Online.

If you need this DB to be published, then I would recommend using ArcGIS Enterprise and referencing the services in ArcGIS Online.

Re: Connecting to Postgres database

TheGamer — Sun, 28 Aug 2022 21:24:45 GMT

@George_ThompsonI am making a ModelBuilder model which will use a table from the PostgreSQL and perform some calculations (tabulate intersection, joins, etc). If data changes in the database, will the changes automatically appear in ArcGIS Pro? If not, how can I make sure changes appear without reconnecting the database

Re: Connecting to Postgres database

George_Thompson — Sun, 28 Aug 2022 23:41:04 GMT

You would need to create and publish a DB view (via ArcGIS Enterprise or ArcGIS Server) if you want the changes to show up.

If you use a model, the data will be updated after the model is run, unless you script it to look for updates.

Re: Connecting to Postgres database

TheGamer — Thu, 01 Sep 2022 19:18:32 GMT

@George_Thompson can you tell me what libraries I would need to connect/update the database in ArcGIS Pro notebook? Also, I'm no longer use a model builder instead I will be connecting the table from the database with a feature class

Re: Connecting to Postgres database

George_Thompson — Thu, 01 Sep 2022 19:21:03 GMT

the client libraries for Postgres are included with the Pro client. What version of Pro are you using?

I have not had to install Postgres libraries in a while.....

Re: Connecting to Postgres database

TheGamer — Thu, 01 Sep 2022 19:37:53 GMT

@George_Thompson I'm currently using Version 2.9 and for my script, all i want to do is connect to the Postgres database (hosted in AWS server), keep data updated and use the table from the database to join it with feature class. Also, my ArcGIS Pro is through AWS Server as well

Re: Connecting to Postgres database

George_Thompson — Thu, 01 Sep 2022 19:59:30 GMT

I would that would work. Can you make a connection to the Postgres DB on AWS?

https://pro.arcgis.com/en/pro-app/latest/help/data/databases/connect-postgresql.htm

Are you getting an error?

If it is not an Enterprise Geodatabase, I do not think that you can edit the data in the table. You can delete / create new spatial (or non-spatial) tables.

Re: Connecting to Postgres database

TheGamer — Fri, 02 Sep 2022 12:50:10 GMT

@George_ThompsonI don't want to edit the data in the table, I basically just want to use the data from the database and join it to the shapefile. Below is the code, I'm implementing for connecting to the database

import arcpy

path = r"C:\Users\Administrator\Documents\ArcGIS\Projects\Oakville\MappingModel.gdb"
username = ""
password = ""
instance = “<region_id>.rds.amazonaws.com"
arcpy.management.CreateDatabaseConnection(path, "Dataset.sde", "POSTGRESQL", instance,
"DATABASE_AUTH", username, password, "SAVE_USERNAME", {database})

Both the ArcGIS Server and Database are in EC2 instances

Re: Connecting to Postgres database

George_Thompson — Fri, 02 Sep 2022 12:59:51 GMT

are you trying to save the connection into the file geodatabase?

that is what it looks like with your path variable. Can you move that up one level?

Are you able to create a connection in Pro, not in arcpy?