PGCon2013 - Final Release
PGCon 2013
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Roland Sonnenschein |
| Schedule | |
|---|---|
| Day | Talks - Day 1 - 2013-05-23 |
| Room | MRT 256 |
| Start time | 10:00 |
| Duration | 01:00 |
| Info | |
| ID | 548 |
| Event type | Lecture |
| Track | Applications |
| Language used for presentation | English |
Monitoring of Machines using PostgreSQL
We have developed a versatile system to monitor machines and groups of machines. The system is well proven and used in industrial production, power supply industry, research and development.
PostgreSQL is used to store all the measurements and metadata. The system also containes some SCADA elements.
This talk explains the database related essentials of the monitoring part of the software.
Beside configuration, a monitoring system mainly consists of four main functionalities. Solutions to some important topics will be explained.
- Reading of measurements from devices
- Connection of heterogeneous datasources (PLCs, sensors, computer programs, …) implementing different protocols.
- Managing network problems between devices and monitoring system.
- Consideration of time zones.
- Synchronizing of timestamps between connected devices.
Handling of reconfiguration.
Storage in PostgreSQL
- Efficient database structures to avoid an inflation of tables (no EAV!).
- Optimizing the size of the database.
- Unified treatment of the simple data types: boolean, integer, floating point numbers.
- Working with complex data like text, images or spectra.
- Implementing storage strategies and pretriggers to minimize disk space.
- Realizing high input rates (>3 million measurements per second over weeks).
- Handling large amounts of data.
- Preparing the database structure for fast retrieval and statistical evaluations.
Avoiding disk overflow.
Fast retrieval
- Minimizing dataflow by using dynamic aggregations.
- Processing of statistical analytics.
Generating charts and reports.
Alarming
We see our solution as a new way to implement the basic ideas of a Round-Robin Database.