Merken

Auditing PostgreSQL Databases Using Logical Decoding

Zitierlink des Filmsegments
Embed Code

Automatisierte Medienanalyse

Beta
Erkannte Entitäten
Sprachtranskript
so they let everyone I will talk about post-disco databases and the bald auditing the data inside so we and get to know what is happening inside and so I will make a shopping mall for a
little program I wrote to eject those changes of the database and presented the software and the background and said post-Christian database so
just some words about me and the Java programming based in Munich and I work for the rest of the convention which is the local utilities company and we are concerned with the management of all the network infrastructure like Prolog us and so on water networks and and indigestible document and solution architect for the network information system so I'm concerned with GIS software for about 6 years now and during namely programming jealous of so now
about the problem we want to talk about today than you might have been well 10 or 20 people editing a database then the right and you might want to know what it's a really happening inside my database so which features were inserted which features were deleted you could ask when future change the last time where you could ask where did data change in general so you want to know the region where the data has changed or someone has deleted data and this might be of interest for technical purposes so you just think you have the rendering of your data and put it into a cash and the data changes in the database you might want to update the cash you're and the data and could be of interest as well for all process-related reasons because other people might want to review that change operates you have to trigger a workflow because of the data has to be adjusted as well you have to and from different systems in your manual system landscape
so I will now describe a little application of wrote to extract changes using mechanisms of oppose this database so little architectures lights you have some data somewhere in post database and you you edited with some might be curious but it doesn't have to MIT model which is and then we put onto so called logical decoding which is able to pull out the changes and you can consume them why a skill and then you can pass those changes and said this job application and then you have it inside the runtime of the job application of the change sets and then you can start doing fancy thanks for the 1 s 2 coats and making it so difficult to give a question stands as sending a little JC document saying well here that it has changed please up education make receipt of a truncated here's or you could also and from other applications like this 1 you would have to write itself because it only applications but you can write a little mini job class which processes the state and does something and the 2nd thing my application is doing is it's feeding was database into the poster so database into metadata at table which builds the complete history of all the records that have been changed and when you have to be careful that you don't consume that changes well and that was it would go round and round and then then you could have a look at into this table AGS program as again because we also compute the bounding box where the data has changed and then you see on your map where the data has been changing yeah and what are the
responsibilities of this little program periodically check have the change sets from the post-disco database and then pass the output of special packing inside the posters could surpassed the real change compute bounding box and then for every change drawn every change table it will create an order record the other table and con taining all the old and although new variously so see the whole state of the future before and after the change and then can also do it it's difficult to do so to truncate the received the affected region and this piece of software is highly configurable you can switch parts of it on or off and then you can define the polling interval and which database schemes you want to watch and so on so that it could be helpful for many people hopefully and what the use as a technical step Java and the runtime container sprinkled which is a very cool every phone and getting productive very soon and very fast because you don't have to write much of boilerplate code so you just write the application code and the rest is nearly done for you as force and expanding the use under large for the parsing of the of the of the of the plug and then a geometric processing is tedious and other come and libraries you find if you do job programming this implication is it's a plant varieties of nearly stateless and you can inject all the confirmation from outside from a simple configuration file and then uh I give it the MIT license so you can do what you like with and I shattered and it up so have a look and try it out like but if you want to run at
a you don't need to write on at the application of properties file which contains you configuration saying Mr. a database and what do you want to do and so on and then you have drawn it's just saying job question John and then the just and so will do that now
I give you a little demo all this it's going to work and way it
on and I got a little to
this project and I want to have a little the use case is I want to inform the public about the construction work that's going on in the public so we need to replace some parts and parts of us want to know that there there will be a construction site in this area so low at new feature to my to my database and say OK here we're
going to do something and so I put some attributes and so that's me to the edited this record water networks and they give it a start and an end date so that's that's the day you want to and tool and a week later and put this speech into my database so up to now nothing special and then I start my my little program consuming changes so I say just to job and run this as an application properties configured correctly inset here to manifest this 1 you go that will wake up and tracking changes and states publishing 1 change metadata the check table because it's just found out this change we have just been
doing and divide the refresh now I see it then you wanna changes level as see came in this region something has been added to the database and then I could move this feature around and say OK I want to move somewhere else and save again and wait some seconds every 10 seconds on the background of a pool new changes and so we get a different region it's updated features is now the bonding of substitute of the state before and after it has been changed because also there's some headed off properties mismatches due to pronounced and compare itself the k m now if we look at the database now and in the end I don't delete this feature again so this is perhaps the most important interesting part because you can find was features of them there anymore so far going to delete this feature selector 2010 the and safe again and it's in seconds and then we will get a bit of Frederick saying OK here's something has been deleted and if we look into into a
database into the into the submitted at a table I would show that use well so comes from you see you see it 3 entries and then the stable because we have done 3 edits we have an entity something updating and deleting it and then you have some attributes the region the already been seen and we also get some more data which was the name of the table to name of the scheme which is the transaction
Computeranimation
Datenhaltung
Software
Datenhaltung
Mathematisierung
Optimierung
Datenhaltung
Caching
Datennetz
Datenhaltung
Wasserdampftafel
Mathematisierung
Applet
Gewichtete Summe
Stellenring
Softwarewerkzeug
Physikalisches System
Extrempunkt
Computeranimation
Datenhaltung
Systemprogrammierung
MKS-System
Datenmanagement
Rendering
Software
Rechter Winkel
Wort <Informatik>
Optimierung
Informationssystem
MUD
Prozess <Physik>
Quader
Applet
Klasse <Mathematik>
Mathematisierung
Parser
Kartesische Koordinaten
Unrundheit
Code
Computeranimation
Datenhaltung
Metadaten
Datensatz
Informationsmodellierung
Syntaktische Analyse
Prozess <Informatik>
Software
Endogene Variable
Programmbibliothek
Optimierung
Konfigurationsraum
Funktion <Mathematik>
Kraftfahrzeugmechatroniker
Architektur <Informatik>
Datenhaltung
Mathematisierung
Rechenzeit
Nummerung
Elektronische Publikation
Sorte <Logik>
Keller <Informatik>
Datensatz
Mapping <Computergraphik>
Quelle <Physik>
Quader
Forcing
Menge
COM
Mereologie
Computerarchitektur
Decodierung
Ordnung <Mathematik>
Textbaustein
Tabelle <Informatik>
Aggregatzustand
Demo <Programm>
Euler-Winkel
Prozess <Informatik>
Kategorie <Mathematik>
Datenhaltung
Konfigurationsraum
Kartesische Koordinaten
Konfigurationsraum
Computeranimation
Demo <Programm>
Flächeninhalt
Mereologie
Projektive Ebene
Zeiger <Informatik>
Computeranimation
Subtraktion
Bit
Datennetz
Kategorie <Mathematik>
Datenhaltung
Wasserdampftafel
Zwei
Mathematisierung
Kartesische Koordinaten
Sprachsynthese
Zeiger <Informatik>
Übergang
Metadaten
Datensatz
Einheit <Mathematik>
Mereologie
Substitution
Optimierung
Attributierte Grammatik
Aggregatzustand
Tabelle <Informatik>
Metropolitan area network
Transaktionsverwaltung
URN
Datenhaltung
Acht
Weitverkehrsnetz
Nummerung
Gleitendes Mittel
Computeranimation
Attributierte Grammatik
Tabelle <Informatik>

Metadaten

Formale Metadaten

Titel Auditing PostgreSQL Databases Using Logical Decoding
Serientitel FOSS4G Bonn 2016
Teil 13
Anzahl der Teile 193
Autor Schmidt, Sebastian
Lizenz CC-Namensnennung 3.0 Deutschland:
Sie dürfen das Werk bzw. den Inhalt zu jedem legalen Zweck nutzen, verändern und in unveränderter oder veränderter Form vervielfältigen, verbreiten und öffentlich zugänglich machen, sofern Sie den Namen des Autors/Rechteinhabers in der von ihm festgelegten Weise nennen.
DOI 10.5446/20281
Herausgeber FOSS4G
OSGeo
Erscheinungsjahr 2016
Sprache Englisch

Technische Metadaten

Dauer 21:35

Inhaltliche Metadaten

Fachgebiet Informatik
Abstract Have you ever been wondering what edits are happening inside your databases? Logcial Decoding, introduced in PostgreSQL 9.4, allows to keep track of changes commited to the database. This talk presents how this mechanism can be used to audit PostGIS/PostgreSQL databases. After an introduction to the concepts of logical decoding, two use cases are presented: Quality Assurance: writing an audit log into the database after each commit so that someone else can do a review of the modified data. Cache Invalildation: refreshing a GeoWebCache instance at the regions in which the data has changed after each commit. To support these two use cases, a little Java program able to be run as a mircoservice was developed and will be shared under an open source license with the community via github.

Zugehöriges Material

Ähnliche Filme

Loading...