Support #11217

Project WP #641: WP5 - Supporting Blue Assessment: VREs Development [Months: 1-30]

Project Task #642: T5.1 Stock Assessment VRE [Months: 1-30]

Project Activity #1674: VRE Stock Assessment Workplan

Project Activity #5135: WECAFC-FIRMS

Project Activity #11214: WECAFC-FIRMS data / map dissemination tools

WECAFC-FIRMS - Need RDB database connection parameters

Added by Emmanuel Blondel over 1 year ago. Updated over 1 year ago.

Status:ClosedStart date:Feb 20, 2018
Priority:HighDue date:
Assignee:Kostas Kakaletris% Done:

100%

Category:Default
Sprint:WP05
Infrastructure:Production
Milestones:
Duration:

Description

Hello Dimitris, as discussed at 7th Tcom in Rome, could you send us the RDB connection parameters (as private message), so we could start exploiting the data content (dsd, timeseries) for setting the data/map dissemination workflow?

If there is any documentation of the DB schema, can you send it? otherwise, a brief summary of the schema used would help a lot.

Thanks in advance!

Database Schema.docx (39.6 KB) Dimitris Katris, Feb 20, 2018 02:01 PM

History

#1 Updated by Dimitris Katris over 1 year ago

  • Assignee changed from Dimitris Katris to Kostas Kakaletris
  • Status changed from New to In Progress

I am reassigning the ticket to Kostas (our admin) who can give you access to the db. In order to do so, we need you to give us an ip address or a subnet that we will allow access.

#2 Updated by Emmanuel Blondel over 1 year ago

I will connect to the database from online Rstudio in D4science.org domain, not sure which instance / domain will be in use in this VRE, @roberto.cirillo@isti.cnr.it just configured it in #11215, so he can give you the exact info that you need.

#3 Updated by Dimitris Katris over 1 year ago

Here are some brief instructions regarding the database schema and our API. If you need to explain something in more detail please feel free to ask us.

#4 Updated by Roberto Cirillo over 1 year ago

Emmanuel Blondel wrote:

I will connect to the database from online Rstudio in D4science.org domain, not sure which instance / domain will be in use in this VRE, @roberto.cirillo@isti.cnr.it just configured it in #11215, so he can give you the exact info that you need.

you should allow access to the following subnets:

146.48.122.0/23
90.147.166.0/23

#5 Updated by Pasquale Pagano over 1 year ago

Please register to the Information System this database and please put the credentials to access it in this description (clearly the credentials will be encrypted). Thanks

#6 Updated by Emmanuel Blondel over 1 year ago

I forgot to say that the DB should be reachable (read-only) by GeoServer app requested at #11216.

#7 Updated by Kostas Kakaletris over 1 year ago

Dear @emmanuel.blondel@fao.org @dkatris@di.uoa.gr , should we do that read only user directly to the Production or is better to do it first on PreProduction to be tested and then on Production?

#8 Updated by Kostas Kakaletris over 1 year ago

Dear @roberto.cirillo@isti.cnr.it the geoserver will be in the same subnets?

#9 Updated by Roberto Cirillo over 1 year ago

Kostas Kakaletris wrote:

Dear @roberto.cirillo@isti.cnr.it the geoserver will be in the same subnets?

Yes, they will

#10 Updated by Kostas Kakaletris over 1 year ago

  • % Done changed from 0 to 50

Created read only users for both production and preproduction databases. We are in the process of creating the service endpoints for both of them

#11 Updated by Emmanuel Blondel over 1 year ago

I will also need to upload GIS shapes as Postgis table in this database. Please let me know how to proceed, if you give me a writing privileges, or if you want me to send you the shapefile. Thanks

#12 Updated by Kostas Kakaletris over 1 year ago

I created read only user for the database. Additionally this postgres installation has no postgis.
If these are needed to the specific database then it means that you need a normal user and not a read only user and that we need to alter the postgres installation and database to add postgis support.
In that last case I strongly suggest to close this ticket and create a new one for preproduction in order to make the needed changes there, test it and then proceed to production.

#13 Updated by Emmanuel Blondel over 1 year ago

Ok, from discussions we had in Tcom, I understood that Postgis was not an issue.

Indeed it's a prerequisite for the activity we have to perform, as we need to set a reference spatial table (otherwise no map dissemination will be possible).

I understand the fact we should start from a pre-prod database. However I will need to have access to datasets and their DSD as tested by @yann.laurent@fao.org through the VRE (ongoing task on his side). @dkatris@di.uoa.gr Please advise here. Possibly what we could do is 1) to wait Yann has finalized his tests with DSD created, and test datasets uploaded, 2) create a pre-production database with enabled postgis extension , 3)upload Yann'sdata from production DB dump,

Once we will have clarifeid th way to proceed, I will create a ticket.

Thanks

#14 Updated by Dimitris Katris over 1 year ago

There is no technical problem to add postgis in our database nor create a reference table. The problem that Kostas describes is related to the processes that the project follows in order to deliver software and database schemas to the production environment.
Performing updates directly to the production database that have not be tested, means that there is a probability of breaking a functionality without noticing. Also if we perform an upgrade of our functionality we may not consider your modifications and harm your app.
So, in general we believe that it is safer if you could first work on the pre-production database and give Kostas instructions and/or some database scripts that upgrade and set up the production database.
In order to be sure that we or you do not break the other's app it is better to collaborate and follow a common process of deploying, testing and releasing.

#15 Updated by Emmanuel Blondel over 1 year ago

I can work on the pre-production database on the following conditions:
* you enable Postgis on it (let me know if you want a separate ticket...). Meanwhile i'm going to prepare the Shapefile that we need to import and send it to you. For this, I recommend you use the Postgis shapefile uploader, it's the easiest way to proceed.
* you liaise with @yann.laurent@fao.org so he's able to point to pre-production database to perform all his tests (including production of DSDs, and upload of detailed datasets that I require for setting up the dissemination part).

Is it ok for you?

#16 Updated by Dimitris Katris over 1 year ago

  1. Yes please, I think it would be better if you could create a new ticket so as to have a dedicated thread for the installation of postgis and shapefile uploader. I am also sure that @k.kakaletris@cite.gr may need some more detailed information regarding this tool and postgis. e.g. which version you need because your tools may or may not properly cooperate with certain versions. Maybe some commands that upload data.

  2. Could we ask @yann.laurent@fao.org to just add some test data in pre-production and give you access to the database to work there and experiment with our database? As soon as we have fully understood the changes that should be done by you to set up the dissemination and you are familiar with our schema we can proceed to do the same thing in production.

I also have another question regarding the work you plan to perform on our db. Are you going to perform a manual process of linking Yann's datasets with specific regions or are you going to install an app that just requires a database and access to our data? So, if you perform some work in pre-production, is it something that you need to redo in production or is it an automatic process?

#17 Updated by Emmanuel Blondel over 1 year ago

Dimitris Katris wrote:

  1. Yes please, I think it would be better if you could create a new ticket so as to have a dedicated thread for the installation of postgis and shapefile uploader. I am also sure that @k.kakaletris@cite.gr may need some more detailed information regarding this tool and postgis. e.g. which version you need because your tools may or may not properly cooperate with certain versions. Maybe some commands that upload data.

See #11286
some doc link about how to upload shapefile with the command line shp2pgsql can be found in #11287

  1. Could we ask @yann.laurent@fao.org to just add some test data in pre-production and give you access to the database to work there and experiment with our database? As soon as we have fully understood the changes that should be done by you to set up the dissemination and you are familiar with our schema we can proceed to do the same thing in production.

Fine with me, but I don't know if @yann.laurent@fao.org can actually push data in pre-production, does he have access to your pre-production tool?

I also have another question regarding the work you plan to perform on our db. Are you going to perform a manual process of linking Yann's datasets with specific regions or are you going to install an app that just requires a database and access to our data? So, if you perform some work in pre-production, is it something that you need to redo in production or is it an automatic process?

Well, I think i'm going to reply you with a question :-) (or for Yann maybe), although I remember we talked of that at Tcom. Do you collate all data collections into a single table. What I need to query (per dataset) is the full timeseries at regional level (not per country basis). I will then configure Geoserver layers to compute dynamic views on top this timeseries while geo-referencing it with the shapefile we will have uploaded into the database.
I will need to re-trigger the work in production then because Geoserver web-services will need to connect to the production database.

#18 Updated by Dimitris Katris over 1 year ago

Probably we talked about it while we were drinking wine and eating pizza, so forgive me if I miss some details ;)

Regarding your question, data for all countries related to a data collection are stored in the same table. I think I wrote this in the instructions. A CDT_* table contains the data for the whole region.

Moreover, based on your description, it seems to me that if Kostas installs successfully the postgis and the shapefiles you don't need further modifications to the database. Is this correct? This means that if you verify that the shapefiles have correctly been installed in the pre-production database, we can continue by performing the same steps to the production and let you work there.

#19 Updated by Yann Laurent over 1 year ago

I have access to the machine http://dl013.madgik.di.uoa.gr:8080/group/devvre/regional-data-collection I don't know if this is the pre prod environment.

#20 Updated by Pasquale Pagano over 1 year ago

This seems to me more a blog discussion instead of a tailored support request. Please close this request if you think the original support request has been satisfied and open specific tickets as already pointed out.
Thanks

#21 Updated by Emmanuel Blondel over 1 year ago

  • % Done changed from 50 to 100
  • Status changed from In Progress to Closed

Thanks @k.kakaletris@cite.gr for having set-up quickly the postgis and provided the connection params in IS as mentioned in #11286.
And thanks to all for having participated promptly to this (blog) discussion. I'm closing the ticket

Also available in: Atom PDF