Building an Enterprise-grade PostgreSQL Server using Open Source tools

解决方案从构建一个安全的PostgreSQL环境开始,针对生产工作负载调整数据库,构建一个避免单点故障的高可用性策略,使用连接池扩展PostgreSQL以避免服务器资源的过度使用,以及加载Balancing在可用的备用副本之间进行读取,以有效地利用所有数据库服务器的计算能力。
我们将发布一系列的博客文章来扩展下面的每个主题。当我们展示整个项目时,本次网络研讨会将更加详细。
博客中涉及的主题以及网络研讨会上演示的主题包括:
保护PostgreSQL数据库集群
PostgreSQL中的高可用性
准备PostgreSQL备份策略和可用工具
使用连接池和负载平衡器缩放PostgreSQL
详细登录PostgreSQL以及您可用的工具和扩展
监控PostgreSQL环境
我们将在45分钟的网络研讨会中指导您完成整个过程,向您展示我们取得的成果以及我们的解决方案在实践中的工作方式。在本次网络研讨会期间,您将看到一个简单的Web应用程序正常运行,然后演示当我们杀死主机、从设置中删除连接池以及添加另一个副本以共享读取负载时会发生什么。

展开查看详情

1. Building an Enterprise-grade PostgreSQL Server using Open Source tools Webinar Avinash Vallarapu (Avi) Jobin Augustine Fernando Laudares 10th October, 2018 1 © 2018 Percona

2. Usual Myths ▪ Security is not free ▪ Cannot take a full backup that is good for PITR (Point-in-time-recovery) ▪ High Availability is a concern ▪ Not scalable ▪ Less options for Database maintenance 2 © 2018 Percona

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 … 3 © 2018 Percona

4. Topics we cover in this Webinar and Demo ▪ 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. 4 © 2018 Percona

5. Security in PostgreSQL 5 © 2018 Percona

6. 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 © 2018 Percona

7. Backup Strategy 7 © 2018 Percona

8. 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) ▪ More on each of these solutions in our future blog posts … 8 © 2018 Percona

9. Connection Pooling and Load Balancer 9 © 2018 Percona

10. Connection Pooling & 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 10 © 2018 Percona

11. High Availability and Automatic Failover 11 © 2018 Percona

12. High Availability & 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 ▪ PostgreSQL Automatic Failover (PAF) ▪ pglookout ▪ pgPool - II 12 © 2018 Percona

13. Patroni 13 © 2018 Percona

14. PostgreSQL Extensions 14 © 2018 Percona

15. 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 … 15 © 2018 Percona

16. PostgreSQL Detailed Logging and Analyzer 16 © 2018 Percona

17. PostgreSQL Detailed Logging and Analyzer ▪ 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 17 © 2018 Percona

18. Monitoring 18 © 2018 Percona

19. Monitoring ▪ Monitoring methods ▪ Open Source tools ▪ Munin ▪ Zabbix ▪ Cacti ▪ Nagios ▪ Grafana ▪ PMM ▪ PGObserver ▪ pgCluu 19 © 2018 Percona

20. Final Architecture of our Webinar Demo 20 © 2018 Percona

21.21 © 2018 Percona

22. Percona Live - PostgreSQL track https://www.percona.com/live/e18/ 22 © 2018 Percona

23. Questions ??? 23 © 2018 Percona