Building an Enterprise Grade PostgreSQL Using Open Source Tools and Extensions

1 . Building an Enterprise Grade PostgreSQL Using Open Source Tools and Extensions Avinash Vallarapu Percona 

2 .Enterprise-Grade Support For Any Database ● Percona Server for MySQL ● Percona XtraDB Cluster ● Percona Server for MongoDB ● Percona XtraBackup ● Percona Toolkit ● Percona Monitoring and Management ● Percona Monitoring Plugins ● PostgreSQL 

3 .Enterprise-Grade PostgreSQL Using Open Source Solutions Satisfy enterprise features such as: ● Encryption ● User management ● Ability to tune ● Connection pooling/load balancing ● Logging and auditing ● Monitoring ● Durable Replication methods ● High Availability and automatic failover ● Reliable backups ● Tools for routine database maintenance tasks ● Partitioning ● Continuous bug fixes ● Fixing security vulnerabilities ● And many more … 

4 .Some Blog Posts For Your Reference Securing your PostgreSQL database cluster High Availability Preparing a Backup strategy and the tools available to achieve it Scaling PostgreSQL using connection poolers and load balancers Tools/extensions available for DBAs/Developers/Business requirements Detailed logging in PostgreSQL and Monitoring your PostgreSQL and real-time analysis 

5 .Security in PostgreSQL ▪ Authentication ○ Host based authentication ○ SCRAM-SHA-256, PAM/LDAP, Kerberos, SSL ▪ Authorization ○ User management ○ Row level security ○ Data security using Encryption ▪ Accounting and Auditing ○ Logging ○ Auditing ▪ Security Bug Fixes ○ Please subscribe to our blog posts 

6 .Backup Strategy ▪ PostgreSQL ○ pg_basebackup - Consistent Online backup - helps achieve PITR ○ pg_dump/pg_restore - Logical Backups ○ Archiving on WAL’s in Archive Mode. ▪ PostgreSQL umbrella projects ○ pgBackRest ○ BARMAN ○ WAL-g (previously WAL-e) 

7 .Connection Pooling and Load Balancing ▪ PostgreSQL Connections ○ Process-based (each connection is a process) ○ Native application connection pooler (preferred) ○ External Connection Poolers ▪ Open Source tools for Scaling PostgreSQL ○ External Connection Poolers ○ pgBouncer ○ pgPool-II ▪ Load Balancer ○ HAProxy 

8 .High Availability and Automatic Failover ▪ PostgreSQL - Replication methods ○ Streaming Replication (preferred for HA) ● Cascaded Replication ● Synchronous and Asynchronous Replication ● Warm Standby and Hot Standby ○ Logical Replication ● Cascaded Replication ▪ Open Source Contributions for Automatic Failover ○ Patroni ○ REPMGR ○ Stolon ○ pg_auto_failover ○ PostgreSQL Automatic Failover (PAF) ○ pglookout ○ pgPool - II 

9 .Patroni 

10 .PostgreSQL Extensions ▪ PostgreSQL ○ Feature-rich and Community-driven ○ Capability of adding extensions ▪ A few extensions among hundreds of extensions ○ pg_stat_statements - Query stats ○ pg_repack - Online table reorg ○ pl/profiler - Profiling of Stored procedures ○ Orafce - Oracle functions to port applications on Oracle to PostgreSQL easily ○ Language extensions like - PL/PGSQL, PL/Python, PL/Perl, PL/Java, PL/R, etc .. ○ Foreign Data Wrappers like - mysql_fdw, mongo_fdw, postgres_fdw, etc … 

11 .PostgreSQL Detailed Logging and Analyser ▪ PostgreSQL ○ Enable detailed logging of activity. ○ Customizable logging ○ Log DDLs ○ Log statements running for more than log_min_duration_statement time. ▪ Open Source Log Analyzer ○ pgBadger ○ PMM QAN (Query Analytics) - Under development 

12 .Monitoring ▪ Open Source tools ○ PMM ○ Munin ○ Zabbix ○ Cacti ○ Nagios ○ Grafana ○ POWA ○ PGObserver ○ pgCluu 

13 .

14 .Thank You to Our Sponsors 

15 .Rate My Session 15 

16 .Any Questions?