The Swiss conference on PostgreSQL

This site is about a previous instance of this event. For the current event, please see the updated site.

This day is all about PostgreSQL - the world's most advanced open source database.

Take the opportunity to meet with other people interested in PostgreSQL in Switzerland. Beside the talks we offer the chance to network during the day and are pleased to invite you for a round of drinks after the meeting. The event is suitable for everybody, from first-time users to experts and from clerks to decision-makers.

We stick to the proven one-day, two track conference concept but aim to offer this year a complete track in English language beside the one in German to open the event to a broader audience. Speakers are requested to indicate the language of their talk and may even apply for both tracks.

Looking for people or a new job? Near the registration desk you'll find a whiteboard open to sponsors, companies and conference participants to post potential job offers, resumes, company profiles and the like. If you float around this during the brakes you can likely get in contact with each other. We ask our sponsors to show some presence there.

Do you have questions or suggestions? Please let us know by e-mail: info@pgday.ch.

We are looking forward to this event!
The organization committee

The Swiss PGDay is organized by the Swiss PostgreSQL Users Group (SwissPUG)
"PGDay" is a registered trademark of PostgreSQL Europe.

Code of Conduct (on SwissPUG website)

See what we did in 2017 (German)
See what we did in 2016 (German)


Our sponsors and partners (in alphabetical order)

The Call for Speakers is closed.

Registration is closed.

Time German Track - Room 5.001 English Track - Room 5.002
08:15 - 08:50 Registration - Building 1
08:50 - 09:00 Welcome: Stefan Keller (HSR) - Room 5.002

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).

Picture
09:00 - 09:50 Daniel Salem, Tipps und Tricks eines DevOp-Consultants
Picture

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)
Ziel: "Arbeiten, nicht verwalten; weniger Sand im Getriebe, mehr Effizienz"

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

Slides Picture
Lukas Eder, 10 SQL Tricks That You Didn't Think Were Possible
Picture

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.

Slides Picture
10:00 - 10:50 Laurenz Albe, Neue Wege von Oracle zu PostgreSQL
Picture

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.

Slides Picture
Stephan Müller, Building a lightweight high availability cluster using repmgr
Picture

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.

Slides Picture
11:00 - 11:50 Harald Armin Massa, ist Dein PostGres logisch genug für bidirektionalität?
Picture

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

Slides Picture
Miroslav Šedivý, Tracking Reproducible Revisions in PostgreSQL
Picture

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.

Slides Picture
12:00 - 12:50 Hans-Jürgen Schönig, Machine Learning mit PostgreSQL
Picture

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.

Slides Picture
Jan Karremans, Why I picked Postgres over Oracle?
Picture

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.

Slides Picture
12:50 - 14:00 Lunch - Location Mensa building 4
14:00 - 14:50 Stefan Keller, PostGIS für Fortgeschrittene

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).

Slides Picture
Andreas Geppert, PostgreSQL-as-a-Service at Swiss Re
Picture

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.

Slides Picture
15:00 - 15:50 Karsten Lenz, Condair DWH Projekt mit PostgreSQL und Jasper Erfahrungen nach einem Jahr produktiven Einsatz
Picture

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.

Slides Picture
Daniel Westermann, What we already know about PostgreSQL 11
Picture

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.

Slides Scripts Picture
16:00 - 16:50 Keynote: Bruce Momjian (EDB), Will Postgres Live Forever? - Room 5.002

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.

Slides Picture
16:50 - 17:00 Closing: Stefan Keller (HSR) - Room 5.002

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).

Picture
17:00 - Apéro - Cafeteria in building 1 - Picture

Looking for people or a new job? Near the registration desk you'll find a whiteboard open to sponsors, companies and conference participants to post potential job offers, resumes, company profiles and the like. If you float around this during the brakes you can likely get in contact with each other. We ask our sponsors to show some presence there.

Sponsors

The Swiss PGDay offers a great chance to reach PostgreSQL professionals and community members in Switzerland and Central Europe.

Sponsors support the event with a payment of CHF 500.00 and are mentioned with their company logos (linking to the company website) ordered alphabetically by company name from left to right on the entry page of the event. Additionally sponsors may be present at the event day with PR material and a representative of the company, if they wish. The selection of sponsors is based on a first-come, first-served policy. In order to keep the non-profit character of the event, we only can allow for a maximum of 4 sponsors.

This year's sponsors (alphabetic order):


Location

The HSR (University of applied sciences for technology Rapperswil) is located directly at the upper Zurich lake shore with a view on the Alpine foothills (Map).

How to get there

The premises of the HSR are right besides the train station of Rapperswil (southern exit, look for the signs). We recommend you therefore to use the public transportation. It is possible, however, to reach the University by car.

Registration

The registration is in the cafeteria in building Nr. 1. There is also the place where we will spend the breaks.

Ease of access

The campus is accessible for wheelchairs.

Financial disclosure

Expenses of the Swiss PGDay are covered with the income from our sponsors and from the registration fees. We aim at keeping costs for both parties as low as possible and include special rates for students in order to enable as many interested people as possible to attend. Our budget is calculated targeting a zero result. Operational losses and profits that result from differences from the budget are managed by the Swiss PostgreSQL Users Group. Profits are reinvested in the next year's edition of the Swiss PGDay.

Community Event

Responsible for the Swiss PGDay is the Swiss PostgreSQL Users Group (SwissPUG). The event does comply with the PostgreSQL Community Conference Recognition guidelines and the use of the trademark "PGDay" is granted by PostgreSQL Europe.

Organization and program commitee (alphabetic order)

Background picture CC BY-SA 3.0 Roland zh (https://commons.wikimedia.org/wiki/User:Roland_zh). Thank you!


Poster

Postcard

Feedback is closed.