Arches installation with database on a separate server

[Markus S.]

Hi,

Does anyone have experience installing Arches 5.1 using two servers, one for the web server/app and one for the PostgreSQL database? I'm looking at doing that on Azure. It does not seem as simple as just setting the database server host name and port in the settings file (I tried it). Furthermore, the Arches app installation process seems to require the Postgre 12 dependency on the app's server, implying that the default expectation is that the db and the app reside on the same server.

Any tips on this would be appreciated.

Markus

[Ryan Anderson]

Hi Markus,

I've done this a bunch with Amazon's RDS service. It sounds like generally you are on the right track. More detail about the errors you're seeing would be helpful, but I'll try to recount additional things that may be necessary here.

First, check that your postgres port is open on your db server (usually this is 5432) and accessible from your application server. Sometimes I do this by installing psql on the application server and trying to connect to the db server with that. On AWS there are some network settings that need to be tweaked to make sure that the application and db server can talk to each other.

The command to connect should be something like this:

`psql -U username -h db_server`

If this works, then add a postgis template to your db instance (instructions for this can be found here) and then try manually creating your project database from this template in psql. This will get overwritten when you run setup_db/load_package in Arches, but sometimes Arches likes to see that there is already a database there.

If things are still not working at this point then double check your settings/settings_local.py file.

Arches does not require that you have postgres server installed on the application server, but it is usually helpful in these situations to have the psql client installed - which requires you to select a version you would like it to work with.

I hope this helps. Post back with any updates or additional info.

Cheers,

Ryan

[Markus S.]

Hi Ryan,

Thanks, this is very helpful. That's pretty much what I had already tried. On the app server, I had used PGAdmin to connect to the db server and the connection was successful. Using pgAdmin, I had also created the blank db with the PostGIS template on it.

It sounds like I just need to try again and double-check the settings file. I'll report back my findings.

Markus

[Adam Cox]

Hi Markus, I use this setup for my installations. It works as expected, but you make a few good points. This is definitely a section of the documentation I've been wanting to update for a while... https://github.com/archesproject/arches-docs/issues/115

For your Azure connection issues, like Ryan said it could be a firewall issue. Make sure your db server allows access via port 5432 from your app server. On AWS I found that I needed to allow access from the app server's private ip address, not public one, to facilitate this connection. Easiest of course would be to open up 5432 to any incoming connections, but that's not a very secure solution....

As for the local postgres installation, originally arches did require the postgres client pqsl locally to facilitate all of the setup_db commands, but we've tried to factor that out (https://github.com/archesproject/arches/issues/4803). It's really a consequence of relying for a long time on an installation process that expects a postgres superuser (discussed a bit here: https://github.com/archesproject/arches/issues/2636). Ultimately, you can now run all of the normal arches development management commands without having postgres/psql locally, but you will need to create your database ahead of time and add the postgis and uuid-ossp extensions to it.

Hope that helps a bit,

Adam

--
-- To post, send email to arches...@googlegroups.com. To unsubscribe, send email to archesprojec...@googlegroups.com. For more information, visit https://groups.google.com/d/forum/archesproject?hl=en
---
You received this message because you are subscribed to the Google Groups "Arches Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to archesprojec...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/archesproject/657bf66c-dd85-49b0-8567-d6c50e2ef793n%40googlegroups.com.

[Markus S.]

Hi,

Thanks for all the tips. After a fresh install, when I run setup_db, I still get the following error, pasted below.

On the app server, I can connect to the database server without issue using pgAdmin. I have created the blank db for Arches there, as per Ryan's suggestion. The postgis template exists on the server and I used it as a template to create the db. This should not be a port issue since I am able to connect with pgAdmin.

I have set the db connection parameters in the settings_local file. I know the host, database name, username and password strings should be correct because I get a different error if I intentionally mess them up. The error below is likely caused by something else, and it seems that the SSL connection just breaks right away. It is not complaining about SSL version, but I might look into that a bit.

(ENV) C:\Projects\arches_parks>python manage.py setup_db

Are you sure you want to destroy and rebuild your database? [y/N] y

Drop and recreate the database...

SELECT pg_terminate_backend(pid) FROM pg_stat_activity

    WHERE datname IN ('azure_arches', 'template_postgis');

Traceback (most recent call last):

  File "manage.py", line 30, in <module>

    execute_from_command_line(sys.argv)

  File "C:\Projects\ENV\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line

    utility.execute()

  File "C:\Projects\ENV\lib\site-packages\django\core\management\__init__.py", line 375, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File "C:\Projects\ENV\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv

    self.execute(*args, **cmd_options)

  File "C:\Projects\ENV\lib\site-packages\django\core\management\base.py", line 364, in execute

    output = self.handle(*args, **options)

  File "C:\Projects\ENV\lib\site-packages\arches\management\commands\setup_db.py", line 52, in handle

    self.setup_db()

  File "C:\Projects\ENV\lib\site-packages\arches\management\commands\setup_db.py", line 186, in setup_db

    self.drop_and_recreate_db(cursor)

  File "C:\Projects\ENV\lib\site-packages\arches\management\commands\setup_db.py", line 137, in drop_and_recreate_db

    cursor.execute(terminate_sql)

psycopg2.errors.AdminShutdown: terminating connection due to administrator command

SSL connection has been closed unexpectedly

[andrew...@historicengland.org.uk]

Markus, we are running a duel Azure server setup on Windows with no issues - but only at 5.0. 

As we are running it in a private network we've not needed to enable SSL but for a side project I have connected it up to Azure Database for Postgresql Server instance and I have to add  the sslmode option to the DB connection string.

DATABASES = {

    "default": {

        "ENGINE": "django.contrib.gis.db.backends.postgis",

        "NAME": get_env_variable("PGDBNAME"),

        "USER": get_env_variable("PGUSERNAME"),

        "PASSWORD": get_env_variable("PGPASSWORD"),

        "HOST": get_env_variable("PGHOST"),

        "PORT": get_env_variable("PGPORT"),

        "POSTGIS_TEMPLATE": "template_postgis",

        "OPTIONS": {

            "sslmode": get_env_variable("PGSSLMODE") #needs to use 'require' if connecting to Azure Database for Postgresql

        },

    }

}

If you can, perhaps test with SSL disabled so you can isolate the issue further.

[Adam Cox]

Also, it looks like the failure comes on this specific line https://github.com/archesproject/arches/blob/master/arches/management/commands/setup_db.py#L137. As I recall, that line cleans up existing connections, but it is not technically necessary for the process if you don't have other connections to the database. So you could also try simply commenting out that line before running the command and seeing what happens.

Adam

To view this discussion on the web visit https://groups.google.com/d/msgid/archesproject/7ade08a5-aebb-49fe-8398-a2a0ed4e9112n%40googlegroups.com.

[Markus S.]

Hi all,

Status update: no luck yet.

I am currently trying to use Azure Database for PostgreSQL server with a Private Endpoint that links the server to the same VNet where the app server resides. SSL has been disabled on the db server. I have allowed all IP address to contact the db server in the testing phase.

On the app server, I am able connect to the db server using pgAdmin. I also tested the db connection successfully using a Python script that utilizes psycopg2 and sslmode=require (even though SSL is disabled). (See at https://docs.microsoft.com/en-us/azure/postgresql/flexible-server/connect-python.)

Now, the db server only allows Postgre version up to 11. Arches needs version 12. There is a "flexible" version of the server that goes to 12 but it's a preview/beta version and I ran into some other issues with it, so I discarded that route. Perhaps the version discrepancy is my issue at the moment?

When running setup_db, I get this error . So, I've actually only made backward progress because now I only get to line 30, haha! I think I'll give this a little break and try again another time, starting from scratch. Thank you for all your suggestions so far.

(ENV) C:\Projects\arches_parks>python manage.py setup_db

Are you sure you want to destroy and rebuild your database? [y/N] y

Traceback (most recent call last):

  File "manage.py", line 30, in <module>

    execute_from_command_line(sys.argv)

  File "C:\Projects\ENV\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line

    utility.execute()

  File "C:\Projects\ENV\lib\site-packages\django\core\management\__init__.py", line 375, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File "C:\Projects\ENV\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv

    self.execute(*args, **cmd_options)

  File "C:\Projects\ENV\lib\site-packages\django\core\management\base.py", line 364, in execute

    output = self.handle(*args, **options)

  File "C:\Projects\ENV\lib\site-packages\arches\management\commands\setup_db.py", line 52, in handle

    self.setup_db()

  File "C:\Projects\ENV\lib\site-packages\arches\management\commands\setup_db.py", line 180, in setup_db

    conninfo = self.get_connection()

  File "C:\Projects\ENV\lib\site-packages\arches\management\commands\setup_db.py", line 103, in get_connection

    cancreate = cursor.fetchone()[0]

TypeError: 'NoneType' object is not subscriptable

[Alexei Peters]

Hi Markus,

Have you set these settings in settings.py?

PG_SUPERUSER

PG_SUPERUSER_PW

These should be set to a user who can create a database in Postgres.

If you set those that should get you past the error above.

Cheers,

Alexei