Building an Enterprise-grade PostgreSQL Server using Open Source tools

市场创造了术语“企业级”或“企业就绪”,以区分许可数据库软件的产品和服务。例如:可能有一个标准的数据库软件或一个提供核心功能和基本功能的入门级软件包。同样,可能还有一个企业版,一个更高级的包,它超越了必要的功能,包括在生产中运行关键解决方案所必需的功能和工具。有了商业软件中的这种差异,我们可能会想,在像PostgreSQL这样的开放源代码数据库之上构建的解决方案是否能够满足所有的企业需求。
因此,在本文中,我们将讨论如何使用开源解决方案构建企业级的PostgreSQL。

展开查看详情

1.Building an Enterprise-grade PostgreSQL Server using Open Source tools Avinash Vallarapu (Avi)
 Fernando Laudares
 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 ▪ No support for Bugs 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. Few 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. 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) 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 ▪ Open Source tools ▪ Munin ▪ Zabbix ▪ Cacti ▪ Nagios ▪ Grafana ▪ PMM ▪ PGObserver ▪ pgCluu 19 © 2018 Percona

20. Final Architecture of our Demo 20 © 2018 Percona

21.21 © 2018 Percona

22.© 2018 Percona

23.Rate My Session !23

24. Questions ??? 24 © 2018 Percona