Monitor 2023 DB installation question

[Anonymous User]

I have a question on the installation part of Monitor 2023. During the installation, do we have to install the DB anywhere  or does it autoamtically come installed with the program?  Is the only specific thing that we need to configure after installation is the  SQL DB component?  I am a bit confused on this part and how the postgres  DB plays into Monitor.

@AndrewSakowicz 

@DerekLaw 

[AndrewSakowicz]

Please see  https://doc.arcgis.com/en/monitor/latest/install/windows/arcgis-monitor-system-requirements.htm .  This step needs to be completed by a user before the setup step.  We are in the process of providing additional clarification.

[DavidColey]

Hi @AndrewSakowicz , @Anonymous User  - I was able to muddle my way through setting up postgreSQL version 14.6 (the latest version on MyEsri) and was able to create the 'agm' user database per the help doc above using the pgAdmin gui. 

However, do either of you have a practical example of steps 3 and 4?

Configure the database to allow connections

By default, only clients running on the same machine as the PostgreSQL database cluster can connect to it. To allow remote clients to connect to the database cluster, alter the PostgreSQL pg_hba.conf configuration file. The entries you place in the pg_hba.conf file vary depending on your security policies and configurations.

Complete the following steps to allow remote connections from clients to the PostgreSQL server:

1. Make a backup copy of the pg_hba.conf file before you alter it.
2. Open the pg_hba.conf file in a text editor.
3. Add the Monitor Server IP address, designate the database that it can connect to, and specify the authentication methods used for connections using the following example:

   # TYPE    DATABASE        USER         ADDRESS                          METHOD
   host      <monitor_db>    <db_user>    <monitor_server_ip>/<ip/mask>    <auth_method>

   For more information, refer to the PostgreSQL documentation.

4. Reload server configuration settings using a PostgreSQL function such as pg_ctl reload or SELECT pg_reload_conf().

Thanks in advance for anything you can provide . . . 

David

[GeoJosh]

Hello @DavidColey,

For step 3, consider this scenario:

• My database name is "monitor"
• The database user I am using to connect is "agm"
• The IP address of the ArcGIS Monitor Server machine is "1.2.3.4" with an IP mask of "32
• The authentication method is "scram-sha-256"

In this scenario, I would add the following line to the pg_hba.conf file (located in C:\Program Files\PostgreSQL\14\data by default):

host    monitor    agm    1.2.3.4/32    scram-sha-256

Please note that each value in the row is separated by spaces, not tabs. In my testing, using tabs instead of spaces will cause the Postgres service to crash.

For step 4, you can either open pgAdmin, connect to the database, and issue this command...

SELECT pg_reload_conf()

 ...or open a command prompt on the machine where Postgres is installed, navigate to the installation directory (C:\Program Files\PostgreSQL\14\bin by default) and issue the following command:

pg_ctl reload

Please note that this set of steps only needs to be done if Postgres is installed on a different machine from ArcGIS Monitor Server. If ArcGIS Monitor Server and Postgres are installed on the same host, these steps should not be necessary. Please let me know if I can elaborate further or if there are any other questions.

Best,

Josh

[DavidColey]

This is very helpful @GeoJosh - thanks very much for this. To me, this:

"By default, only clients running on the same machine as the PostgreSQL database cluster can connect to it."

is just not clear that 'clients' is referring to the Monitor Server.

I also would have preferred to name the db differently as you have, but the help example names the db the same as the owner, which as a SQL Server admin I would never do.  But I don't know/didn't know that/if Monitor Server is expecting the database to be named 'agm' - so it looks like it can be....

[GrantSmith122]

Hey GeoJosh,

Can this new line of information be placed anywhere in the pg_hba.conf file as long as there is no # in front of it, or does it need to go somewhere specific?

[GeoJosh]

@GrantSmith122,

Please add new entries to the very bottom of the file and make sure to leave off "#" at the beginning.

Josh

[GrantSmith122 · Accepted solution]

Thanks Josh! Appreciate it.

[rshihab]

Hi 

you need to isntall the postgress DB. installing it will allow you to configure the ArcGIS monitor site. 
once installed you will need to configure the monitor DB by running the fowlloing script, 

you need to update the IP address of the Server that has the monitor installed on 

Here's a summary of the steps:

Main ideas:

1. Create a database user and a database for Monitor Server: The steps provides an example of SQL commands to create a database user named agm and a database named agm with the appropriate permissions and settings for Monitor Server.
   

   CREATE USER agm WITH PASSWORD '<password>'

     LOGIN

     NOSUPERUSER

     INHERIT

     NOCREATEDB

     NOCREATEROLE

     NOREPLICATION;

   CREATE DATABASE agm WITH OWNER=agm;

   GRANT ALL ON DATABASE agm TO agm;

   ALTER DATABASE agm SET search_path FROM CURRENT;

• Create a schema and authorize it for the database user: The steps also shows how to create a schema named agm and authorize it for the database user agm by running a SQL command in the agm database. CREATE SCHEMA agm AUTHORIZATION agm

• Configure the database to allow connections: The document explains how to modify the pg_hba.conf file to allow remote connections from clients to the PostgreSQL server, by adding a record with the relevant parameters. 
  1. Make a backup copy of the pg_hba.conf file before you alter it.
  2. Open the pg_hba.conf file in a text editor.
  3. Add a record to the pg_hba.conf file using the following syntax:
  1.  
  2. # TYPE    DATABASE                   USER                   ADDRESS                          METHOD

• host      <monitor_database_name>    <database_username>    <monitor_server_ip>/<ip_mask>    <authentication_method>

  For example, if the Monitor database name is agm, the database username is agm, the IP address of the machine on which ArcGIS Monitor Server will be installed is 1.2.3.4 (replace this IP with your machine IP) with an IP mask of 32, and the authentication method is scram-sha-256, then the record would be added as follows: 

• host agm agm [yourIP] /32 scram-sha-256 

Thanks

[HarryPlendl]

Cannot get the ArcGIS Monitor 2023 to connect to the PostgreSQL datbase in the setup Monitor step. Any suggestions are welcome. 

Steps to tried to connect ArcGIS Monitor 2023 to PostgreSQL database:

1. Updated the pg_hba.conf File

Updated the pg_hba.conf file on your PostgreSQL server to allow connections from your ArcGIS client machines. Ran the pg_ctl reload and restarted the postgresql service.

2. Attempted Connection via Web Browsers on and off the hosting server

Tried connecting to the database using both Firefox and Chrome browsers on the server where the database instance is locally hosted. This was to test if the issue was with the database server or the client machine.

3. Tested Connection from Desktop

Was able to connect to the database from your desktop using ArcGIS Pro. This suggests that the issue is not with the database itself, but possibly with the connection between the ArcGIS Monitor and the database.

4. Encountered Connection Pool Error

Received an error message stating Authentication or address for database failed "Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?"

This suggests that all connections in the pool are being used and none are currently available. Tried various “hostname:port” configurations.