The Swiss conference on PostgreSQL

Stefan Keller

Professor for information systems, founder and head of Geometa Labs. Keller is a member of the Swiss PGDay board and involved in various open source projects such as PostGIS and QGIS and open data initiatives, e.g. OpenStreetMap. Besides, he is generally interested in geographic data and their visualization as well as in the the integration of open source and proprietary solutions (e.g. QGIS and ArcGIS).

Professor für Informationssysteme sowie Leiter und Gründer des Geometa Labs. Ist u.a. Mitglied des Swiss PGDay OK und engagiert in verschiedenen Open Source Projekten (z.B. PostGIS und QGIS) und Open Data (v.a. OpenStreetMap). Zudem interessieren ihn allgemein Geodaten und Geovisualisierung sowie die Integration von Open Source und proprietärer Software (z.B. QGIS und ArcGIS).

Daniel Salem

Anstatt über Features von Postgres wird hier der Umgang mit Postgres und allgemeine Arbeitstechniken eines DevOp-Consultants erläutern

• 1. Postgres in "entwicklerfeindlicher" Umgebung
• 2. Postgres in Entwickler-Umgebung ohne Admin
• 3. Ein DevOps entwickelt in Postgres (Postgres/Emacs)
• 4. DB-Organisation & Denkmuster innerhalb der DB-Welt
• 5. Arbeitstechnik eines DevOps (Postgres/Emacs/Perl)

Dr. Daniel Salem Daten- & Prozess-Ingenieur DevOp DatawareHouse Statistik (R, SAS) Web

Lukas Eder

SQL is the winning language of Big Data. Whether you're running a classic relational database, a column store ("NewSQL"), or a non-relational storage system ("NoSQL"), a powerful, declarative, SQL-based query language is what makes the difference. The SQL standard has evolved drastically in the past decades, and so have its commercial and open source implementations. However, most people in the Java ecosystem haven't noticed, and are thus using only 10% of their database's features. In this fast-paced talk, we're going to look at very peculiar, yet very interesting data problems and how we can solve them with SQL. We'll explore common table expressions, hierarchical SQL, table-valued functions, lateral joins, ordered-set aggregate functions, row value expressions, window functions, and advanced data types, such as XML and JSON. Devices whose mystery is only exceeded by their power. Most importantly, however, we're going to learn that everyone can write advanced SQL. Once you learn the basics in these tricks, you're going to love SQL even more.

Lukas Eder is founder and CEO of Data Geekery GmbH, the company behind jOOQ, located in St. Gallen, Switzerland. He is fascinated by the interaction of Java and SQL. Most of this experience he has obtained in the Swiss E-Banking field through various variants (JDBC, jOOQ, Hibernate, mostly with Oracle and PostgreSQL). As a Java Champion and Oracle ACE, he is happy to share this knowledge at various conferences, JUGs, in-house presentations and on his blog.

Laurenz Albe

Migration von Oracle ist ein heisses Thema. In diesem Vortrag beleuchte ich die Probleme, die einem bei der Oracle-Migration begegnen können und zeige einen neuen Weg zur Migration unter Verwendung des Oracle Foreign Data Wrappers.

PostgreSQL contributor, senior database engineer and maintainer of oracle_fdw and pgreplay.

Stephan Müller

The presentation features a lightweight high availability (HA) setup for postgres 9.6 clusters using repmgr, barman and pgbouncer. This solution comprises a master, hot stand-by nodes as well as a decent backup. To start with, essentials about postgres built-in streaming replication and write-ahead logs (WAL) are provided. Concepts as log sequence numbers (LSN) and timelines are briefly explained. After illustrating a possible deployment, all tools, their configuration, possibilities and limitations are explained in more detail. The main focus lies on the repmgr daemon. It monitors the cluster and will trigger the election of a new master if necessary. However, all other steps for a fail-over have to be provided in custom scripts. The possibilities and interactions with the repmgr daemon are non-obvious and will be presented enriched with hints and caveats encountered in an actual production environment. Some ideas on how to implement a STONITH mechanism to avoid split-brain situations are given. Pgbouncer fulfills the role of a database proxy which can hide the database fail-overs conveniently from the application. The barman tool introduces backup solutions which enable all sorts of point-in-time recovery (PITR). It allows parallel backups as well as de-duplication using rsync. Some pitfalls are highlighted and some configuration and recovery examples will be shown. This 40 minutes high-level introduction will leave space for questions and discussions at the end. Little to no knowledge about the tools or even postgres itself will be assumed. Nevertheless, it will contain helpful pointers and hopefully valuable experiences for the more advanced audience.

Since 2 years I am working as system administrator at OLMeRO based in Zurich. While not dedicated to database administration, it is nevertheless part of my duties. I learned a lot about postgres and it community on swiss pgday 2017 and wish to give something back.

Harald Armin Massa

Logical decoding ist Teil von PostgreSQL seit Verson 9.4. Seit dem Release von PostrgeSQL 9.4 haben diverse Verbesserungen und Weiterentwicklungen rund um diese Technologie stattgefunden. Es entwickelte sich ein Ecosystem von Nutzanwendungen zu diesem Feature: Beispielhaft Near Zero Downtime Upgreades zwischen PostgreSQL Major Versions und Exportieren von Änderungen in Message-Busse. Mit PostgreSQL 10 wurde logische Replikation zwischen PostgreSQL Datenbanken Teil von Core PostgreSQL. Dieser Vortrag strebt an, eine Übersicht über die aktuell verfügbaren Nutzungen der logischen Decodierung und logischen Replication zu geben. Unterschiede, Anwendungen und Kontraindikationen von In-Core Logischer Replikation, pg_logical und BiDirectionaler Replikation werden untersucht.

Geschäftsführender Gesellschafter der 2ndQuadrant Deutschland GmbH, PostgreSQL-Nutzer seit 1995, LightningTalkMan, Pythonista

Miroslav Šedivý

So you finally have your database model for your application and you fill it in with current data. How do you keep it up to date? While INSERT may still be transparent, UPDATE and DELETE will overwrite your previous data, so you won't be able to reproduce them. Cloning the whole huge content for each minor update is not an option. For rich and complex data about hundreds of thousands of power generators in Germany and worldwide, I built a model using range data types in recent PostgreSQL which allows me to insert, update and delete data while granting the full access to the whole state of the database at any historical moment. I'll present a very simplified version of the database so the audience will be immediately able to apply it for their cases. I'll also show a few tricks in Python and Psycopg2 that will allow a whole team to prepare, review, and deploy all revisions to this database without merge conflicts. And I'll give a few ideas on how to retrieve this data efficiently.

Senior Software Architect at UBIMET GmbH. Using Python to make the sun shine and the wind blow. Imports psycopg2 or asyncpg and prefers psql cli. hjkl juggler and languages enthusiast.

Hans-Jürgen Schönig

Machine Learning und AI sind die Top Themen der nächsten Jahre. Weltweit sollen sich bereits 400.000 Entwickler mit diesem Thema beschaffen. Der Punkt ist: Um maschinell lernen zu können, braucht man Daten und die müssen irgendwo gespeichert werden. PostgreSQL eignet sich hervorragend als Platform zum Speichern von Daten aber auch für die Implementierung der Algorithmen selbst.

Hans-Jürgen Schönig ist CEO der Cybertec Schönig & Schönig GmbH (https://www.cybertec-postgresql.com) und arbeitet seit fast 20 Jahren als PostgreSQL Consultant und Trainer.

Jan Karremans

Picking a specific database technology is not done overnight. It is not a decision that is taken lightly. You will most probably select a database technology for a long time, as the dogma of database agnostic has proven not to exist. Switching database technology is even more challenging. To even consider switching the workhorse under you prized, mission critical applications, the switch would have to bring serious advantages. Many years, if not decades, have been invested in building and testing everything, why change? I have invested nearly a quarter of a century working with Oracle technology, yet I made a switch. In this talk I want to take you through some of the considerations of this change and perhaps show that picking Postgres over Oracle makes sense…

Jan started his IT career in a medical facility of the Royal Dutch Army in 1992 as an administrator on a Datapoint Mainframe. In 1994 the medical facility switched to using Oracle 7.1 on a Micro VAX 3100, which kicked of his career in Oracle. From 1998 Jan supported the development of a system for healthcare administration. Over the years Ecaris evolved to an APEX based solution. In recent years Jan came in contact with Dbvisit from New Zealand. In 2011 he started working as a Business Development Manager for Dbvisit. Most recently Jan joined the German based company of Portrix as Director Operations ai. responsible for reorganizing the operations department. In a latest venture, Jan joined EnterpriseDB as Sales Engineer for the DACH-region. Also Jan made his appearance as a regular speaker at Oracle User Groups around the world, with a debut at UKOUG_Tech13, including DOAG14 and OOW14. His work for the Oracle User Community was recently recognized with an Oracle ACE award.

Stefan Keller

PostGIS ist eine der mächtigsten Erweiterungen innerhalb der Open Source-Datenbanksysteme und sogar auch der proprietären GIS. An Beispielen wird gezeigt, wie die Geodaten-Verwaltung optimiert und Anfragen beschleunigt werden können. Dabei werden ausgewählte - teilweise verblüffende - Anfrage-Funktionen vorgestellt. Speziell eingegangen wird auf Optimierungsmöglichkeiten und Anwendungs-Patterns, sowie auf die Geodaten-Analyse von (Location/GPS-)Punkt- und Bewegungs-Daten (Trajectories). Schliesslich werden die Grenzen von PostGIS diskutiert und dessen künftige Entwicklung vorgestellt (beispielsweise Indexe und Parallelisierung).

Professor für Informationssysteme sowie Leiter und Gründer des Geometa Labs. Ist u.a. Mitglied des Swiss PGDay OK und engagiert in verschiedenen Open Source Projekten (z.B. PostGIS und QGIS) und Open Data (v.a. OpenStreetMap). Zudem interessieren ihn allgemein Geodaten und Geovisualisierung sowie die Integration von Open Source und proprietärer Software (z.B. QGIS und ArcGIS).

Andreas Geppert

At Swiss Re Global Technology Management, we have been building up a PostgreSQL-as-a-Service offering over the last one and a half years. This project is based on the strategic decision to offer an open-source based alternative to closed-source RDBMS to our internal customers. The offering consists of three service classes (Basic, Standard, Premium), which are characterized by different service levels (SLAs). Each service class is implemented by its specific blueprint. For instance, the "Standard" blueprint consists of two virtual machines (VMs), which run in different data centers and each of which hosts a PostgreSQL server. High availability and disaster recovery are implemented through replication (using repmgr) between the servers. Lifecycle management function such as creation (or destruction) of an entire cluster, creation and deletion of databases and users are automated and are offered via RESTful services. The automation and REST support enables the integration into CI/CD tool chains, which is an important requirement from our customers. Our PostgreSQL-as-a-Service is integrated into the enterprise's eco system for Identity and Access management, configuration and inventory management, change management, and monitoring/problem management.

Data platform architect and long-term Postgres user.

Karsten Lenz

In diesem Vortrag geht es um die User Erfahrungen mit dem System und dem Kostenseitigen Vergleich mit den Vorgängersystemen / Wirtschaftlichkeit des Systems im Vergleich zu den Vorgängersystemen. Die Ergebnisse diese Projektes waren so überzeugend das die Thematik Open Source basierte Software auch für weitere Projekte in Betracht gezogen wird.

Dr.-Ing. Karsten Lenz war mehrere Jahre im Telekom Umfeld tätig, dort wurden und mehrere Monitoring und Reporting Tools von Sybase ASE und Oracle auf PostgreSQL migriert, mit Jasper Server und PostgreSQL 8.X wurde ein Telekom Reporting Tool entwickelt welches auf Reporting auf extrem grosse Datenmengen ermöglichte. Um die Daten laden zu können arbeitete er mehrere Jahre zusammen mit den Kollegen von NTT am PG Bulkload Projekt. Unter anderem war er am PostGIS Cluster bei Harman Becker beteiligt, der zur Kompilierung der Karten für Navigationssysteme verwendet wird. Seit 2014 ist er Head of System Engineering der Condair Group.

Daniel Westermann

PostgreSQL 10 is not even one year old and PostgreSQL 11 is just around the corner. This is a (very personal) selection of improvements and new features with some demos. There are improvements to partitioning, parallel query and much more.

Click here to learn more about the speaker.

Bruce Momjian

This presentation explains how open source software can live for a very long time, and covers the differences between proprietary and open source software life cycles. It also covers the increased adoption of open source, and many of the ways that Postgres is innovating to continue to be relevant.

Bruce Momjian is co-founder and core team member of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996. He has been employed by EnterpriseDB since 2006. He has spoken at many international open-source conferences and is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley. Prior to his involvement with PostgreSQL, Bruce worked as a consultant, developing custom database applications for some of the world's largest law firms. As an academic, Bruce holds a Masters in Education, was a high school computer science teacher, and lectures internationally at universities.

Stefan Keller

Professor for information systems, founder and head of Geometa Labs. Keller is a member of the Swiss PGDay board and involved in various open source projects such as PostGIS and QGIS and open data initiatives, e.g. OpenStreetMap. Besides, he is generally interested in geographic data and their visualization as well as in the the integration of open source and proprietary solutions (e.g. QGIS and ArcGIS).

Professor für Informationssysteme sowie Leiter und Gründer des Geometa Labs. Ist u.a. Mitglied des Swiss PGDay OK und engagiert in verschiedenen Open Source Projekten (z.B. PostGIS und QGIS) und Open Data (v.a. OpenStreetMap). Zudem interessieren ihn allgemein Geodaten und Geovisualisierung sowie die Integration von Open Source und proprietärer Software (z.B. QGIS und ArcGIS).