Automated database migration testing

In a heavily-loaded project, any change implies non-zero risks of downtime or performance degradation. We constantly see how system complexity, number of database nodes, deployments per week constantly grow. How grows the level of automatization of various activities in CI/CD pipelines, containers, Kubernetes.

Meanwhile, when we look at the topic of testing of database changes–from trivial index creation to complex, almost "surgical" operations like converting int4 PK to int8 one in a multi-terabyte database under load–here we observe an obvious lag in technology and methodology development. In the best case, the changes are verified and approved visually, and here it all depends on the level of experience and tiredness of the reviewer.

In this talk, we will look at how we (Postgres.ai) solve this problem using our solution, Database Lab:

• instant provisioning of independent thin clones of multi-terabyte databases, ready to be used for testing,
• integration with existing CI/CD solutions and workflow,
• collection of metrics that are the most useful for decision support on whether or not each database migration has to be approved (and even fully automated rejection of the most dangerous actions).

Managing moving objects data with MobilityDB

MobilityDB is a moving object database extension to PostgreSQL and PostGIS. It has types and functions for storing an querying geospatial trajectories, as first class citizens. The main type is called tgeompoint (temporal geometry point). It represents a complete movement track of a geometry point, such as a car, a bird, or a person. The function speed(tgeompoint) computes the time varying speed of the object, as a tfloat (temporal float). Similar to these examples, MobilityDB has 6 temporal types, and over 300 functions. As such, it is a function-rich platform for Mobility Data Management.

In this tutorial you will:

• learn about moving object databases
• write MobilityDB SQL queries and explore a database of geospatial trajectories
• walk through the different type, indexes, and functions of MobilityDB.

Python for PostgreSQL: how to use it, how to be good at it

Let's see, with practical examples, how to make Python and PostgreSQL talk to each other seamlessly: how to connect to a server, how to exchange data, manage transactions, passing parameters in a safe and expressive way, how to manage notifications.

We will cover psycopg2, the most used PostgreSQL adapter for Python, but also look and the up and coming psycopg3: what will remain the same, what will change, how to better organise a Python program to make the most of PostgreSQL.

How to get data from Oracle to PostgreSQL and vice versa

PostgreSQL has become a reality in a lot of shops today. In most cases PostgreSQL is established beside the current Oracle deployment and quite soon one question pops up regularily: How can we push data from Oracle to PostgreSQL and vice versa? Way back, in March 2001, a new extension to the SQL standard made it's way to define common APIs for managing external data: SQL/MED (ISO/IEC 9075-9:2008). The PostgreSQL community picked that up quite fast and implemented a framework for plugging in so called foreign data wrappers back in 2011 with PostgreSQL 9.1. Since then a wide range of these foreign data wrappers popped up and thanks to those, PostgreSQL today is able to integrate data from almost every external source, no matter if it is coming from flat files, other relational database systems or even unstructured sources. In this talk we will look at the foreign data wrapper for Oracle and how it can be used to get data from Oracle to PostgreSQL. But this is not a one way game: data can also be pushed from PostgreSQL to Oracle, and this might become important depending on the requirements. It is guaranteed that this talk is splitted by half: Slides and a lot of demos.