PostgreSQL is undoubtedly the second most popular open source RDBMS and it is within the top five most popular DB engines as per db-engines.com. Why not take a rest from your favorite database server for a while, start learning some more about it, and aim for another logo on your resume?
In this session, we are going to explore this RDBMS, compare it with its arch-rival, MySQL, to both correlate concepts and understand where Postgres excels. Among the topics we will cover:
* Server architecture
* Replication and HA
* Postgres specific features
* and more!
With the advent of the Health Insurance Portability and Accountability Act (HIPAA) of 1996 all entities that handle health information are required by law to secure all data which contains personally identifiable information (PII) and private health information (PHI). Fines for leaking this data can range from $100 to $50,000 per leaked record. A data breach or leak is extremely costly for both the patients as well as the companies that are entrusted with their PHI. In our presentation we introduce Gonymizer, a tool that is written in Go at SmithRx to handle the anonymization of PHI and PII data from our production database instances.
This data is anonymized and loaded into non-production environments to allow us to use representative data to develop and test against. This makes anonymization of sensitive information quick and simple using a simple column map that is defined in a single JSON file for your dataset. There is a selection of custom processors that we have built to handle basic tasks, such as: first and last name anonymization, changing data to fake locations such as: street addresses, cities, zips, and states. The interface for building processors is also completely extendable and anyone with basic Go experience should be able to build processors that can anonymize your data efficiently. We will also show how this tool decreases our development time for new features as well as simplifying testing in a compliant environment with non-sensitive data sets (HIPAA, PCI, etc).
Toward the end of our presentation we will be discussing how we built our infrastructure using Docker to containerize Gonymizer and schedule anonymization and loading of our test environments using Kubernetes. This talk is targeted for anyone working in the healthcare space where collected data contains PHI and/or PII and is regulated by HIPAA.
PostgreSQL performance varies between different releases. Every new version comes with added features and performance improvements that help the growing adoption of PostgreSQL. All these continuous These ongoing developments and improvements drive us to plan on upgrading our PostgreSQL environments, but that's not always an easy task. A few common concerns when it comes to upgrading PostgreSQL are extended downtime, converting the tables partitioned using triggers to declarative partitions, the search for the safest options to upgrade and the effort required in the upgrading process itself. In this talk we'll discuss the variety of options available that can help you upgrade your PostgreSQL servers in the best possible way.
This talk includes:
1. The most significant performance improvements in recent PostgreSQL versions including PostgreSQL 11 and 10.
2. A summary of features that have been implemented with every new version since PostgreSQL 9.1.
3. What is it that you need to consider before upgrading your PostgreSQL server.
4. What are the options you have available to help you upgrade your PostgreSQL server.
5. What are the solutions available to minimize the downtime during upgrades.
6. A list of parameters you need to consider in particular when upgrading PostgreSQL to 10 or 11.
You can have fully automated high availability PostgreSQL on your Kubernetes cluster ... today. The Patroni system for automating PostgreSQL deployment, failover, and migration is ready to use and in production in several places. In this live demo session, we will show you how you can make use of this technology.
We will run though setting up PostgreSQL clusters, both using basic Patroni and using a PostgreSQL Operator. We will then demonstrate failover and disaster recovery, go over some basic configuration options, show how security & authentication works, and explore some plugins and options. After that, you'll learn about the current state of the Patroni project as well as what the alternatives are.
If you need to administer more than one PostgreSQL replication cluster, you'll want to see how Patroni and Operators can make your daily DBA headaches and 2am wakeups go away.
1.1 What is WAL and Why is it required
1.2 Transaction Log and WAL Segment Files
1.3 WAL Writer
1.4 WAL Segment File Management
1.5 WAL Example
1.6 Overview of Replication Options based on WAL
As more and more people are moving to PostgreSQL from Oracle, a pattern of mistakes is emerging. They can be caused by the tools being used or just by not understanding how PostgreSQL is different than Oracle. In this talk, we will discuss the top mistakes people generally make when moving to PostgreSQL from Oracle and what the correct course of action.
Integrating the most suitable highly available multi master, non clustered, freely accessible
relational database management system (RDBMS)
solution for large scale environments is a challenging task; it
resembles evaluating marathon champions trained by different olympic coaches.
This presentation will show the analogies and differences of
MySQL Group Replication and PostgreSQL Bi-Directional Replication (BDR),
two freely accessible and highly-available multi master non clustered RDBMS products
that have been successfully employed in large scale environments requiring high availability.
Both scenarios have demonstrated, through validated prototypes, to be successful,
satisfying data integrity, reliability and scalability
with slightly different strategies and different upcoming pathways.
Or perhaps I should say this is my PostgreSQL "hello World".
In this presentation I will illustrate the ways, the accidents, and the surprises I had in my journey as a MySQL DBA to implement a spectacular solution with PostgreSQL as a total newbie.
I will start from the basics, covering my journey in:
- Basic configuration
- Security definition
- Create a database and tables
- The magic behind indexes
"I migrated from a proprietary database software to PostgreSQL. I am curious to know whether I can get the same features I used to have in the proprietary database software."
The market coined the term "enterprise grade" or "enterprise ready" to differentiate products and service offerings for licensed database software. For example: there may be a standard database software or an entry-level package that delivers the core functionality and basic features. Likewise, there may be an enterprise version, a more advanced package which goes beyond the essentials to include features and tools indispensable for running critical solutions in production. With such a differentiation found in commercial software, we may wonder whether a solution built on top of an open source database like PostgreSQL can satisfy all the enterprise requirements.
So, in this talk, we shall discuss how you can build an Enterprise Grade PostgreSQL using open source solutions.
We'll discuss a list of Enterprise-grade features that include -
1. Securing your PostgreSQL database cluster
2. High Availability for your PostgreSQL setup
3. Preparing a Backup strategy and the tools available to achieve it
4. Scaling PostgreSQL using connection poolers and load balancers
5. Tools/extensions available for your daily DBA life and detailed logging in PostgreSQL.
6. Monitoring your PostgreSQL and real-time analysis.
Indexes are a basic feature of relational databases, and PostgreSQL offers a rich collection of options to developers and designers. To take advantage of these fully, users need to understand the basic concept of indexes, to be able to compare the different index types and how they apply to different application scenarios. Only then can you make an informed decision about your database index strategy and design.
One thing is for sure: not all indexes are appropriate for all circumstances, and using a â€˜wrong' index can have the opposite effect to that you intend, and problems might only surface once in production. Armed with more advanced knowledge, you can avoid this worst case scenario!
We'll take a look on how to use pg_stat_statment to find opportunities for adding indexes to your database. We'll take a look at when to add an index, and when adding an index is unlikely to result in a good solution.
So should you add an index to every column? Come and discover why this strategy is rarely recommended as we take a deep dive into PostgreSQL indexing.
PostgreSQL is an advanced open source database that is completely community driven. Continuous development and performance improvements, while maintaining a secondary focus on Oracle compatibility, gave PostgreSQL a great market penetration. When a database server is deployed in production, we often wish to achieve several 9's of availability. Is that even possible with PostgreSQL? What is the combination of tools that you could combine and implement to achieve High Availability (HA) and automatic failover in PostgreSQL? How can we avoid data loss during such failovers? We'll address these questions and then some more in this talk.
We are going to discuss **
1. How the implementation of HA differs for each type of replication available in PostgreSQL.
2. How to combine HAproxy with etcd plus a detailed explanation of the RAFT Algorithm and HA using Patroni.
3. How to combine repmgr with keepalived to achieve HA.
4. HA solution built for PostgreSQL on kubernetes.
5. What are the tools and solutions that help you achieve automatic failover in AWS and other cloud-based environments.
6. How to avoid huge data loss during failover.
Considering a move to PostgreSQL? Here's a chance to learn from all the fun adventures we had moving one of our services from RethinkDB to PostgreSQL.
From materialized view refreshes, to system view queries, to torn page analysis, there's never a dull moment during that first migration!
Learn how to monitor PostgreSQL using Percona Monitoring and Management (PMM) so that you can:
* gain greater visibility of performance and bottlenecks PostgreSQL
* Consolidate your PostgreSQL servers into the same monitoring platform you already use for MySQL and MongoDB
* Respond more quickly and efficiently in Severity 1 issues
We'll show how using PMM's native support for PostgreSQL that you can have PostgreSQL integrated in only minutes!