search

ESGF / esgf-installer

ESGF P2P Node Installer
https://esgf.llnl.gov/
Other
20 stars 21 forks source link

esg_postgres will fail during initial configuration if ran a second time #561

Closed nathanlcarlson closed 5 years ago

nathanlcarlson commented 5 years ago

The check to see if postgres is installed does just that, and only that. It does not check the configuration of the databases, schemas or tables. If the case occurs where the creation of some databases, schemas or tables works and some fail and postgres is opted to be reinstalled these creations will fail indefinitely as the structure attempted to be created already exists.

This of course could be avoided with the SQL condition ... IF NOT EXISTS. But, this is not an option for a couple reasons. The primary reason is that the current version of Postgres is 8.4, which is outdated and lacks this IF NOT EXISTS condition in some cases. Another being that the installer team may not be the maintainers of the SQL that is being executed to perform this database initialization.

There are a few options for resolution, listed in no particular order.

  1. The postgres database could be updated to a more recent version and the SQL could be reviewed and modified for improved reliability, security and efficiency. This appears to be a high work load, high reward option as it seems that it would potentially span all teams. To this point the work load seems to have out-weighed the reward.
  2. The installer team could write some verification SQL to check the status of the database before executing either the creation SQL directly or a script which has creation SQL in it. This is likely the option that will be taken.
  3. The problem could be ignored and assumed that there will not be such a case that leads to this error trap in actual, "production", use. I cannot responsibly advocate this option.
nathanlcarlson commented 5 years ago

There already exists functionality for listing schemas, this will be reused to perform a check before executing the creation SQL. Edit: This functionality may be useful, but see the comment below for updated info on this issue.

nathanlcarlson commented 5 years ago

@sashakames cleared up some stuff regarding the database initialization. Each component that uses the database (the publisher (esgcet), esgf-security, esgf-node-manager, esgf-dashboard) has a .egg script file that is maintained by the maintainers of the component. This .egg file uses sqlalchemy_migrate to create and manage databases, schemas and tables.

Currently some of these creations are done by the Installer, as some SQL is included with and executed by the installer. Having both the installer and the .egg files performing these creations is error prone and unneeded, see #560 and #547 . It is preferred to simply have the .egg files perform the necessary steps. The installer may still have to do some verification or pre-creation steps and these exact occasions will need to be determined.

nathanlcarlson commented 5 years ago

This is related to #452 as well.

nathanlcarlson commented 5 years ago

The issue as described here is resolved.