Building an Enterprise Grade PostgreSQL Using Open Source Tools and Extensions

“我从一个专有的数据库软件迁移到了PostgreSQL。我很想知道我是否可以获得我以前在专有数据库软件中拥有的相同功能。”
市场创造了术语“企业级”或“企业就绪”,以区分许可数据库软件的产品和服务。例如,可能有一个标准的数据库软件或一个提供核心功能和基本功能的入门级软件包。同样,可能还有一个企业版,一个更高级的包,它超越了必要的功能,包括在生产中运行关键解决方案所必需的功能和工具。有了商业软件中的这种差异,我们可能会想,在像PostgreSQL这样的开放源代码数据库之上构建的解决方案是否能够满足所有的企业需求。
因此,在本文中,我们将讨论如何使用开源解决方案构建企业级的PostgreSQL。
我们将讨论企业级功能的列表,其中包括-
1。保护PostgreSQL数据库集群
2。PostgreSQL安装程序的高可用性
3。准备备份策略和实现该策略的可用工具
4。使用连接池和负载平衡器缩放PostgreSQL
5。可用于日常DBA生活和PostgreSQL中详细日志记录的工具/扩展。
6。监控你的PostgreSQL和实时分析。

展开查看详情

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?