PgConf.Russia 2019

This case study walks participants through a case where we decided to embark on a data recovery effort. This talk is applicable to all users, from novices to advanced PostgreSQL database administrators. Beginners will get an understanding of what data recovery is and is not, what expectations to have going into it, and how to work with contracted experts in order to ensure the best possible outcome, while more advanced users and experts will also get a fair bit out of the technical aspects of the case study.

While the talk will emphasize non-technical operational aspects of data recovery, it will also include discussions of the internals of PostgreSQL we had to work with, as well as how we went about approaching difficulties so that we could retrieve the data we hoped to.

This talk will contain 3 parts: 1. Express PITR setup to the Cloud 2. Latest advancements in WAL-G for backups 3. Why you may need or should avoid this new features, depending on your specifications and workload.

Python may not be the fastest programming language on the CPU, but its fast and easy development saves a lot of costs between the keyboard and the chair. In this workshop we'll explore the most popular library for the access to the PostgreSQL server named "psycopg2" and write a short application using some of its useful features.

JSONB in PostgreSQL has interesting properties for design and development of business systems with heavy domain, helping to fight against complexity and variability. We will discuss the trade-offs of the JSONB approach. Using an open source project for Health IT - fhirbase we will:

• load synthetic medical data into PostgreSQL
• learn how to search and index this data (gin, jsquery, json-knife)
• use JSON aggregation to build complex queries (GraphQL)
• see how this data can be modified and validated
• discuss architecture consequences of JSONB usage

Technical requirements:

• docker
• docker-compose

Detailed prerequisites