Postgres Database Schema

[Eric Friedrich]

As part of the conversion to Postgres a question on the database schema: 

The MySQL database today holds both configuration information (Server, Caches Groups, Delivery Services) and monitoring information (server check results, data from Traffic Stats). There are some occasions where it would be helpful to have more of a separation between these two types of data. 

Specifically, I'd like to be able to backup and restore the configuration parts of the database without overwriting or losing any of the monitoring data. 

Any thoughts on splitting the tables into two databases that are hosted by the same Postgres daemon? The configuration database would contain only data that comes in via the API/GUI and the other database would contain the rest of the data. 

--Eric

[Jan van Doorn]

Agree. One of the things we should discuss is a complete redo of the server_checks table (in the UI _and_ in the database).

I've started (re-) gathering some of these ideas on the wiki: https://github.com/Comcast/traffic_control/wiki/Traffic%20Control%203  and https://github.com/Comcast/traffic_control/wiki/Traffic_Ops_30_design specifically for TO. 

The goal of 2.x is to just convert what we have to Postgres, and not much more. Changes like this should go in to 3.x IMO. 

--
You received this message because you are subscribed to the Google Groups "traffic_control-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to traffic_control-d...@googlegroups.com.
To post to this group, send email to traffic_con...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/traffic_control-discuss/38dbc6da-79b2-4f31-8730-b451fa00253e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Robert Butts]

Postgres' `pg_dump` can do exactly what you want. See the `--table` flag. https://www.postgresql.org/docs/9.1/static/app-pgdump.html

I'd vote we retain a single database, to keep things simple and easier to use. Users can manipulate or backup only the tables they need in each script or app.

[Phil Sorber]

I think we should split them at a minimum. Really I think that the monitoring info should all be in influx or similar.

On Thursday, September 22, 2016 at 11:52:29 AM UTC-6, Eric Friedrich wrote:

[Jan van Doorn]

I thought you were going to say it should be in something like Mongo... ;-) 

But, yes, I agree, the status stuff doesn't have the same requirements as the config stuff, and quite likely is better off outside of Postgres.

Rgds,

JvD

-- 
You received this message because you are subscribed to the Google Groups "traffic_control-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to traffic_control-d...@googlegroups.com.
To post to this group, send email to traffic_con...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/traffic_control-discuss/09dd897b-4723-4005-8737-102f3cc4c756%40googlegroups.com.

[Robert Butts]

> the status stuff ... is better off outside of Postgres.

Definitely agree. Logging to SQL = pain. TO may be small enough, but still better practice to log newline-delimited text, probably to a time-series (Influx, Splunk).

[David Neuman]

+1 on putting it in Influx (or something similar).

On Thu, Sep 22, 2016 at 12:30 PM, Robert Butts <robert....@gmail.com> wrote:

> the status stuff ... is better off outside of Postgres.

Definitely agree. Logging to SQL = pain. TO may be small enough, but still better practice to log newline-delimited text, probably to a time-series (Influx, Splunk).

--

You received this message because you are subscribed to the Google Groups "traffic_control-discuss" group.

To unsubscribe from this group and stop receiving emails from it, send an email to traffic_control-discuss+unsub...@googlegroups.com.
To post to this group, send email to traffic_control-discuss@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/traffic_control-discuss/971991a9-654c-4a6f-8d5e-cccb473da484%40googlegroups.com.