title

text

Nikolay Samokhvalov
Nikolay Samokhvalov Nombox LLC
14:30 01 March
45 мин

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

Видео

Другие доклады

  • Daria Vilkova
    Daria Vilkova Postgres Professional
    22 мин

    Zabbix Agent 2: new features and how to configure the agent for PostgreSQL monitoring

    In Zabbix Server 5.0.1 the PostgreSQL monitoring plugin has become available for Zabbix Agent 2. It was developed by Postgres Professional in collaboration with Zabbix. In the presentation, we will talk about how the plugin works, discuss some options for its configuration, as well as how to add custom metrics to it.

  • Andreas Scherbaum
    Andreas Scherbaum Pivotal
    45 мин

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

  • Álvaro Hernández
    Álvaro Hernández OnGres
    180 мин

    Deconstructing Postgres into a Cloud Native Platform

    Is deploying Postgres in Kubernetes just repackaging it into a container? Can’t Postgres leverage the wide range of Cloud-Native software and integrate well with K8s? Join this journey that will cover and demonstrate, with demos running on StackGres:

    • How to structure Postgres into an init-less container, plus several sidecar containers for connection pooling, backups, agents, etc.
    • Defining high level CRDs as the single API to interact with the Postgres operator.
    • Using K8s RBAC for user authentication of a web UI management interface.
    • Using Prometheus for monitoring; bundling a node, Postgres and PgBouncer exporters together.
    • Proxying Postgres traffic through Envoy. Terminate Postgres SSL with an Envoy plugin, that also exports wire protocol metrics to Prometheus.
    • Using Fluentbit to capture Postgres logs and forward them to Fluentd, which stores them on a centralized Postgres database.

    You will be able to follow the session on your own Kubernetes cluster, and go from zero to a Postgres hero on Kubernetes with little effort! Create in minutes your own Postgres-as-a-Service on your Kubernetes.

  • Sushant Pandey
    Sushant Pandey Microsoft
    Alicja Kucharczyk
    Alicja Kucharczyk Microsoft
    22 мин

    The Story About The Migration

    In this talk we want to present how Microsoft team composed of people from two different teams approached the project and solved the migration issues using ora2pg and was able to prove that Postgres Single Server can perform equally well as Oracle Exadata. We will present our ways of working and also some main technical challenges that we faced including migration of BULK COLLECT’s, hierarchical queries, refcursors and others more complicated Oracle constructs.

    The story about a challenging PoC that proved that Postgres can achieve the same performance as Oracle Exadata. The schema that was migrated wasn’t the simplest one you might see. It was quite the opposite. The code was loaded with dynamic queries, BULK COLLECT’s, nested loops, CONNECT BY statements, global variables and lot of dependencies. Ora2pg did a great job converting the schema but left a lot of work to do manually. Also estimates produced by the tool were highly inaccurate since the logic required not the migration but total re-architecture of the code. In this talk we want to present how Microsoft team composed of people from two different teams approached the project and solved the migration issues using ora2pg and was able to prove that Postgres Single Server can perform equally well as Oracle Exadata. We will present our ways of working and also some main technical challenges that we faced including:

    • How estimates do (not) work
    • How we handled BULK COLLECT’s
    • Why we got rid of refcursors
    • How we got stuck with testing of one the packages and how the help from a friend solved the problem
    • How we handled hierarchical queries and drilling down the hierarchy