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).
Видео
Другие доклады
-
90 мин
.jpg.180x180.jpg "Mahmoud SAKR")
Mahmoud SAKR université libre de bruxelles
Esteban Zimányi ULBManaging 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.
-
22 мин
Alexey Fadeev SibedgeMulticorn Foreign Data Wrapper vs plpython
Multicorn technology allows you to develop FDWs in Python, which is much easier and faster than creating FDWs in C. However, there is a downside, Multicorn FDWs work well with primitive WHERE conditions, but more complex cases cause difficulties, which I will talk about. Cases will be considered on the example of my Multicorn FDW for getting OpenStreetMap data. I will also show examples of using the same code in Multicorn FDW and plpython functions, including performance comparison. In conclusion, I will share my findings on when it is better to use plpython, and when Multicorn FDW is more preferable.
-
45 мин
Andreas Scherbaum PivotalManaging PostgreSQL with Ansible
Ansible is an open-source configuration management and deployment tool, which can be used to manage servers and software installations. This talk will briefly cover Ansible itself, and then explain how Ansible is used to install and configure PostgreSQL on a server. Examples will round up the talk.
-
45 мин
David Steele Crunchy DataBackup Best Practices with pgBackRest
Backups are a critical part of any enterprise database solution but they are often done poorly or skipped altogether, which can lead to data loss in the event of hardware failure or some other disaster.
In this talk we'll cover database backup best practices and how to implement them with pgBackRest, including:
- WAL archiving and retention
- Backup frequency and retention
- How to meet recovery time/point objectives
- Configuration options
- Performance considerations